Showing posts with label append. Show all posts
Showing posts with label append. Show all posts

Wednesday, October 10, 2012

SQL Loader's Append

I've recently bumped into one peculiarity of SQLLoader, particularly its APPEND mode. SQLLoader's LOAD APPEND works in the same way as DML INSERT /*+ APPEND*/, that is makes Oracle to allocate new blocks above HWM, right? Well, not quite.
Here's a little demo on 10g R2 on Solaris:
SQL> create table t1
  2  (
  3    id  number(12),
  4    padding varchar2(255)
  5  );

Table created.

SQL> INSERT /*+ APPEND*/
  2  INTO t1
  3    SELECT rownum,
  4           rpad('x', 255)
  5      FROM dual
  6    CONNECT BY LEVEL <= 1000;

1000 rows created.

SQL> commit;

Commit complete.

SQL> delete from t1;

1000 rows deleted.

SQL> commit;

Commit complete.

SQL> set serveroutput on
SQL> DECLARE
  2    free_blks NUMBER;
  3  BEGIN
  4    dbms_space.free_blocks(USER, 'T1', 'TABLE', 0, free_blks);
  5    dbms_output.put_line(free_blks);
  6  END;
  7  /
19                                                                            

PL/SQL procedure successfully completed.
So far I've created a small table, populated it with a thousand rows, then deleted all the data and committed the transaction. Then I checked the number of free blocks in the freelist - it is 19.

Now let's load some data via SQL Loader using a control file as follows:
OPTIONS(readsize=20000000, bindsize=20000000, rows=8000)
LOAD DATA
INFILE *
APPEND INTO TABLE T1
FIELDS TERMINATED BY "|" optionally enclosed by '"' TRAILING NULLCOLS
(
ID,
PADDING
)
BEGINDATA
1|"x     <<254 whitespaces="whitespaces">>"
...
I've removed the data section to save space, but you've got the idea. Now let's run it:
SQL> host sqlldr userid=user/pwd@db control=ldr.ctl log=ldr.log

SQL> DECLARE
  2    free_blks NUMBER;
  3  BEGIN
  4    dbms_space.free_blocks(USER, 'T1', 'TABLE', 0, free_blks);
  5    dbms_output.put_line(free_blks);
  6  END;
  7  /
1                                                                              

PL/SQL procedure successfully completed.

SQL> BEGIN
  2    dbms_stats.gather_table_stats(ownname => USER, tabname => 'T1',
  3                                  cascade => TRUE,
  4                                  method_opt => 'for all columns size 1');
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL> select blocks from user_tab_statistics where table_name = 'T1';

    BLOCKS                                                                    
----------                                                                     
        19                                                                     
As you can see, we still have got the same 19 blocks in the table and only 1 of them is listed as free in the free list.