Thursday, January 28, 2016

INSERT /*+ APPEND */ Hint Does Not Seem to Work Consistently

I ran the following SQL in an Oracle 11.2.0.1 database:

SQL> create table tab1
  2  (col1 number)
  3  /
 
Table created.
 
SQL> alter session set sql_trace = true
  2  /
 
Session altered.
 
SQL> insert /*+ append */ into tab1 select 1 from dual
  2  /
 
1 row created.
 
SQL> commit
  2  /
 
Commit complete.
 
SQL> insert /*+ append */ into tab1 values(2)
  2  /
 
1 row created.
 
SQL> commit
  2  /
 
Commit complete.
 
SQL> select * from tab1
  2  /
 
      COL1
----------
         1
         2
 
SQL> alter session set sql_trace = false
  2  /
 
Session altered.
 
SQL>

Then I ran the trace file through tkprof. For some reason, the first SQL used a Direct Path load:

SQL ID: f57gyxg0uqcgb
Plan Hash: 1432430773
insert /*+ append */ into tab1 select 1 from dual
 
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.02       0.02          0          3         30           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.02       0.02          0          3         30           1
 
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 8354  (ORACLE)
 
Rows     Row Source Operation
-------  ---------------------------------------------------
      0  LOAD AS SELECT  (cr=0 pr=0 pw=0 time=0 us)
      1   FAST DUAL  (cr=0 pr=0 pw=0 time=0 us cost=2 size=0 card=1)
 
 
Rows     Execution Plan
-------  ---------------------------------------------------
      0  INSERT STATEMENT   MODE: ALL_ROWS
      0   LOAD AS SELECT OF 'TAB1'
      1        FAST DUAL

 
... but the second one didn’t. The strange reformatting of the insert statement took place in tkprof, not in Blogger:

SQL ID: 6pb8960hm1hpy
Plan Hash: 0
insert /*+ append */ into tab1
values
(2)
 
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          7         25           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          7         25           1
 
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 8354  (ORACLE)
 
Rows     Row Source Operation
-------  ---------------------------------------------------
      0  LOAD TABLE CONVENTIONAL  (cr=7 pr=0 pw=0 time=0 us)
 
 
Rows     Execution Plan
-------  ---------------------------------------------------
      0  INSERT STATEMENT   MODE: ALL_ROWS
      0   LOAD TABLE CONVENTIONAL OF 'TAB1'


P.S. Shortly after publishing this post, a number of people corrected it in the comments below. I won't be changing the post; if I did, the comments would then be out of place. However, I will be testing and understanding the comments before using them as the basis for another post in the near future.

Thursday, December 31, 2015

Clustering_Factor = 0

I looked at clustering_factor in the previous post and wondered what might cause it to be well below the number of blocks in the table. Chris Saxon suggested it might be due to rows with null values. I decided to look first at the two indexes with a clustering_factor of zero in the same Oracle 11.2 database and checked which columns they were on. Then I looked to see how many of the rows had null values in the columns concerned. The table had > 100 million rows and I found it much quicker to see if any rows had not null values instead. In both cases, there were none at all i.e. all rows in the table had null values in the columns in question:
 
SQL> select owner, index_name
  2  from dba_indexes
  3  where table_owner = 'UIMSMGR'
  4  and table_name = 'UABOPEN'
  5  and clustering_factor = 0
  6  /
 
OWNER                     INDEX_NAME
------------------------- -------------------------
TFE_INDEXES               TFE_UABOPEN_COPA_CODE
UIMSMGR                   UABOPEN_COURT_CASE_INDEX
 
SQL> select column_position, column_name
  2  from dba_ind_columns
  3  where index_owner = 'TFE_INDEXES'
  4  and index_name = 'TFE_UABOPEN_COPA_CODE'
  5  order by 1
  6  /
 
COLUMN_POSITION COLUMN_NAME
--------------- ------------------------------
              1 UABOPEN_COPA_CODE
 
SQL> select column_position, column_name
  2  from dba_ind_columns
  3  where index_owner = 'UIMSMGR'
  4  and index_name = 'UABOPEN_COURT_CASE_INDEX'
  5  order by 1
  6  /
 
COLUMN_POSITION COLUMN_NAME
--------------- ------------------------------
              1 UABOPEN_COURT_CASE_NUMBER
 
SQL> select count(*)
  2  from uabopen
  3  where uabopen_copa_code is not null
  4  /
 
  COUNT(*)
----------
         0
 
SQL> select count(*)
  2  from uabopen
  3  where uabopen_court_case_number is not null
  4  /
 
  COUNT(*)
----------
         0
 
SQL>
 
As for the indexes with clustering_factor > 0 but less than the number of blocks in the table, I guess that some but not all of the rows had null values in the indexed columns. This is something I will leave you to check for yourselves if you feel so inclined.