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.

No comments:

Post a Comment