Big Data SQL Insert

Question

When executing a query like “insert into table”, how do we handle the commit size? That is, all records from another table inserted into one transaction OR is there a way to set the commit size?

Thanks a lot ~ Sri PS: I am the first timer here, and this site looks very good! C>

Answer 1

In good databases, which are an atomic statement, no, there is no way to limit the number of inserted records - this is good!

Answer 2

, , . , , . .

Answer 3

, Java, , , . , , - - , ( 4000 ) DAO, . , . , " " , .

Answer 4

, . , where .

Answer 5

, , , INSERT , .

:

( ), ALTER TABLE NOLOGGING. , , .

- - , , INSERT INTO LOG ERRORS INTO.

Answer 6

, , .

, Microsoft SQL Server "TOP N", , .

INSERT INTO thisTable
  SELECT TOP 100 * FROM anotherTable;

Answer 7

, , , , . , , .

where, . ?

~ Sri

Answer 8

, . SQL Server, , - , . , .

http://www.tek-tips.com/faqs.cfm?fid=3141

Answer 9

", , , , . , ".

-, . undo - , . , , , , .

v $session_longops / rows_processed v $sql

Answer 10

INSERT INTO TableInserted
SELECT *
FROM (
   SELECT  *,
          ROW_NUMBER() OVER (ORDER BY ID) AS RowNumber
   FROM TableSelected
) X
WHERE RowNumber BETWEEN 101 AND 200

while, 101 200. , 1 .

, Oracle .

Answer 11

, , NOLOGGING UNDO REDO INSERT.

, NOLOGGING DML UNDO REDO. NOLOGGING DML, , INDEX. , NOLOGGING INSERT.

drop table table_no_index;
drop table table_w_log_index;
drop table table_w_nolog_index;

--#0: Before
select name, value from v$mystat natural join v$statname where display_name in ('undo change vector size', 'redo size') order by 1;

--#1: NOLOGGING table with no index.  This is the best case scenario.
create table table_no_index(a number) nologging;
insert /*+ append */ into table_no_index select level from dual connect by level <= 100000;
commit;
select name, value from v$mystat natural join v$statname where display_name in ('undo change vector size', 'redo size') order by 1;

--#2: NOLOGGING table with LOGGING index.  This should generate REDO and UNDO.
create table table_w_log_index(a number) nologging;
create index table_w_log_index_idx on table_w_log_index(a);
insert /*+ append */ into table_w_log_index select level from dual connect by level <= 100000;
commit;
select name, value from v$mystat natural join v$statname where display_name in ('undo change vector size', 'redo size') order by 1;

--#3: NOLOGGING table with NOLOGGING index.  Does this generate as much REDO and UNDO as previous step?
create table table_w_nolog_index(a number) nologging;
create index table_w_nolog_index_idx on table_w_nolog_index(a) nologging;
insert /*+ append */ into table_w_nolog_index select level from dual connect by level <= 100000;
commit;
select name, value from v$mystat natural join v$statname where display_name in ('undo change vector size', 'redo size') order by 1;

. . № 2 № 3 UNDO REDO.

--#0: BEFORE: Very little redo or undo since session just started.
redo size      35,436
undo change vector size    10,120

--#1: NOLOGGING table, no index: Very little redo or undo.
redo size      88,460
undo change vector size    21,772

--#2: NOLOGGING table, LOGGING index: Large amount of redo and undo.
redo size   6,895,100
undo change vector size 3,180,920

--#3: NOLOGGING table, NOLOGGING index: Large amount of redo and undo.
redo size   13,736,036
undo change vector size 6,354,032

Answer 12

NOLOGGING. , , , . .