|
Overview
The CREATE TABLE ... AS SELECT
statement (CTAS) is a powerful tool for manipulating large sets of
data. CTAS provides a mechanism for efficiently executing a SQL query
and storing the results of that query in a new database table.
The INSERT /*+APPEND*/ ... AS SELECT statement offers the same capabilities with existing database tables.
APPEND into Tables
By using the APPEND hint, you ensure that Oracle
always grabs "fresh" data blocks by raising the high-water-mark for the
table. If you are doing parallel insert DML, the Append mode is the
default and you don't need to specify an APPEND hint.
When you use the APPEND hint for INSERT, data is
simply appended to a table above the HWM which has the effect of not
creating UNDO. Existing free space in blocks is not used.
High Water Mark
The high water mark is the boundary between used
and unused space in a segment. As requests for new free blocks that
cannot be satisfied by existing free lists are received, the block to
which the high water mark points becomes a used block, and the high
water mark is advanced to the next block. In other words, the segment
space to the left of the high water mark is used, and the space to the
right of it is unused.
Suggest Way for fast INSERTs
- Mark indexes unuasble
- Disable primary key
- Alter table nologging
- Do an insert /*+ append */ into table (select ..)
- Enable primary key
- Rebuild indexes nologging
Example
create table bigtab
as
select rownum id, a.*
from all_objects a
where 1=0;
alter table bigtab nologging;
declare
l_cnt number;
l_rows number := 1000000;
begin
insert /*+ append */
into bigtab
select rownum, a.*
from all_objects a;
l_cnt := sql%rowcount;
commit;
while (l_cnt < l_rows)
loop
insert /*+ APPEND */ into bigtab
select rownum+l_cnt,
OWNER, OBJECT_NAME, SUBOBJECT_NAME,
OBJECT_ID, DATA_OBJECT_ID,
OBJECT_TYPE, CREATED, LAST_DDL_TIME,
TIMESTAMP, STATUS, TEMPORARY,
GENERATED, SECONDARY
from bigtab
where rownum <= l_rows-l_cnt;
l_cnt := l_cnt + sql%rowcount;
commit;
end loop;
end;
/
Now we have a Table with 1'000'000 Rows. Now
delete some Rows, to force Oracle to refill this freespace using the
FREELISTS in a normal INSERT. However in APPEND Mode the FREELISTS are
not used and the freespace are not reoccupied.
DELETE FROM bigtab WHERE id between 1000 and 2500;
DELETE FROM bigtab WHERE id between 3500 and 6500;
DELETE FROM bigtab WHERE id between 15000 and 20000;
DELETE FROM bigtab WHERE id between 350000 and 370000;
COMMIT;
CREATE TABLE insert_test AS SELECT * FROM bigtab;
SQL> CREATE TABLE insert_test AS SELECT * FROM bigtab;
Table created.
SQL> INSERT INTO insert_test SELECT rownum, a.* FROM all_objects a;
49483 rows created.
Elapsed: 00:00:03.92 <==================== !
SQL> DROP TABLE insert_test;
Table dropped.
SQL> CREATE TABLE insert_test AS SELECT * FROM bigtab;
Table created.
SQL> ALTER TABLE insert_test NOLOGGING;
Table altered.
SQL> INSERT /*+ append */ INTO insert_test SELECT rownum, a.* FROM all_objects a;
49483 rows created.
Elapsed: 00:00:02.54 <==================== !
As you can see, only to insert about 50'000 Rows,
the APPEND Insert is much faster, due the free space in the Oracle
blocks are not refilled, the Rows are appended and the Highwater Mark
is moved.
|
