Oracle Tips and Tricks — David Fitzjarrell

January 8, 2016

“Say What?!?!?”

Filed under: General,Indexes,Performance — dfitzjarrell @ 16:04

"The only thing you can do easily is be wrong, and that's hardly worth the effort." 
Norton Juster, The Phantom Tollbooth

Oracle can lie to you. Not like a disreputable used-car salesman but more like the ‘little white lie’ sometimes told in order to hide less-than-desirable parts of the truth. And it’s not Oracle, really, it’s the optimizer and it does it by reporting query plans that may not accurately report the execution path. Sometimes we can make Oracle tell lies that it doesn’t know its telling, by using features not everyone uses. Take, for example, NLS settings. There have been improvements in National Language Support in most Oracle releases, and those improvements can introduce behavior that causes Oracle to lie to you. Let’s look at an example where this is true.

Jonathan Lewis brought this example to light in a recent blog post where he dislplayed the plan using dbms_xplan. After reading the post I decided to add to it by using autotrace to generate the plan, just to see if the two plans matched. Let’s look at the modified example:


SQL> --
SQL> --  Setup NLS parameters
SQL> --
SQL> ALTER session SET nls_sort=binary_ci;

Session altered.

SQL> ALTER session SET nls_comp=linguistic;

Session altered.

SQL> 
SQL> --
SQL> -- Create table, populate table and
SQL> -- create index
SQL> --
SQL> CREATE TABLE log_data(
  2    account_id NUMBER,
  3    log_type NUMBER,
  4    sys_name VARCHAR2(30),
  5    log_time TIMESTAMP,
  6    msg varchar2(4000)
  7  )
  8  nologging
  9  ;

Table created.

SQL> 
SQL> insert /*+ append */ into log_data(
  2    account_id,
  3    log_type,
  4    sys_name,
  5    log_time,
  6    msg
  7  )
  8  select
  9  	     5,
 10  	     2,
 11  	     dbms_random.string('a',1),
 12  	     sysdate + dbms_random.value,
 13  	     rpad('x',200)
 14  from
 15  	     dual
 16  connect by
 17  	     level  user,
  4  		     tabname	      =>'LOG_DATA',
  5  		     method_opt       => 'for all columns size 1'
  6  	     );
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL> 

The setup is complete so let’s see what autotrace reports as the execution plan:


SQL> 
SQL> column msg format a20
SQL> 
SQL> --
SQL> -- Use autotrace to generate the plan
SQL> --
SQL> set autotrace on
SQL> 
SQL> SELECT
  2  	     *
  3  FROM
  4    (
  5  	 SELECT
  6  	     sys_name, log_time,  substr(msg,1,40) msg
  7  	 FROM log_data
  8  	 WHERE
  9  	   account_id=5
 10  	   AND log_type=2
 11  	   AND sys_name='a'
 12  	 ORDER BY
 13  	   log_time  desc
 14    )
 15  WHERE
 16    rownum <= 10
 17  ;

SYS_NAME                       LOG_TIME                                                                    MSG                                        
------------------------------ --------------------------------------------------------------------------- --------------------                       
A                              09-JAN-16 02.42.54.000000 PM                                                x                                          
a                              09-JAN-16 02.41.02.000000 PM                                                x                                          
a                              09-JAN-16 02.40.54.000000 PM                                                x                                          
a                              09-JAN-16 02.36.38.000000 PM                                                x                                          
A                              09-JAN-16 02.36.02.000000 PM                                                x                                          
a                              09-JAN-16 02.34.15.000000 PM                                                x                                          
a                              09-JAN-16 02.31.15.000000 PM                                                x                                          
A                              09-JAN-16 02.30.59.000000 PM                                                x                                          
a                              09-JAN-16 02.30.36.000000 PM                                                x                                          
a                              09-JAN-16 02.29.53.000000 PM                                                x                                          

10 rows selected.


Execution Plan
----------------------------------------------------------                                                                                            
Plan hash value: 1444908817                                                                                                                           
                                                                                                                                                      
--------------------------------------------------------------------------------------------                                                          
| Id  | Operation                       | Name     | Rows  | Bytes | Cost (%CPU)| Time     |                                                          
--------------------------------------------------------------------------------------------                                                          
|   0 | SELECT STATEMENT                |          |    10 |  1120 |    13   (0)| 00:00:01 |                                                          
|*  1 |  COUNT STOPKEY                  |          |       |       |            |          |                                                          
|   2 |   VIEW                          |          |    11 |  1232 |    13   (0)| 00:00:01 |                                                          
|*  3 |    SORT ORDER BY STOPKEY        |          |    11 |  2453 |    13   (0)| 00:00:01 |                                                          
|   4 |     TABLE ACCESS BY INDEX ROWID | LOG_DATA |  1000 |   217K|    13   (0)| 00:00:01 |                                                          
|*  5 |      INDEX RANGE SCAN DESCENDING| LOG_DATE |    11 |       |     2   (0)| 00:00:01 |                                                          
--------------------------------------------------------------------------------------------                                                          
                                                                                                                                                      
Predicate Information (identified by operation id):                                                                                                   
---------------------------------------------------                                                                                                   
                                                                                                                                                      
   1 - filter(ROWNUM<=10)                                                                                                                             
   3 - filter(ROWNUM 
SQL> set autotrace off
SQL> 

SQL> 
SQL> --
SQL> -- Now use dbms_xplan to generate the plan
SQL> --
SQL> SELECT /*+ gather_plan_statistics */
  2  	     *
  3  FROM
  4    (
  5  	 SELECT
  6  	     sys_name, log_time,  substr(msg,1,40) msg
  7  	 FROM log_data
  8  	 WHERE
  9  	   account_id=5
 10  	   AND log_type=2
 11  	   AND sys_name='a'
 12  	 ORDER BY
 13  	   log_time  desc
 14    )
 15  WHERE
 16    rownum 'allstats last'));

PLAN_TABLE_OUTPUT                                                                                                                                     
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  36qykj7j9tsrp, child number 0                                                                                                                 
-------------------------------------                                                                                                                 
SELECT /*+ gather_plan_statistics */         * FROM   (     SELECT                                                                                    
   sys_name, log_time,  substr(msg,1,40) msg     FROM log_data                                                                                        
WHERE       account_id=5       AND log_type=2       AND sys_name='a'                                                                                  
 ORDER BY       log_time  desc   ) WHERE   rownum <= 10                                                                                               
                                                                                                                                                      
Plan hash value: 1444908817                                                                                                                           
                                                                                                                                                      
---------------------------------------------------------------------------------------------------------------------------------                     
| Id  | Operation                       | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |                     

PLAN_TABLE_OUTPUT                                                                                                                                     
------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------                     
|   0 | SELECT STATEMENT                |          |      1 |        |     10 |00:00:00.01 |     979 |       |       |          |                     
|*  1 |  COUNT STOPKEY                  |          |      1 |        |     10 |00:00:00.01 |     979 |       |       |          |                     
|   2 |   VIEW                          |          |      1 |     11 |     10 |00:00:00.01 |     979 |       |       |          |                     
|*  3 |    SORT ORDER BY STOPKEY        |          |      1 |     11 |     10 |00:00:00.01 |     979 |  2048 |  2048 | 2048  (0)|                     
|   4 |     TABLE ACCESS BY INDEX ROWID | LOG_DATA |      1 |   1000 |    975 |00:00:00.01 |     979 |       |       |          |                     
|*  5 |      INDEX RANGE SCAN DESCENDING| LOG_DATE |      1 |     11 |    975 |00:00:00.01 |       5 |       |       |          |                     
---------------------------------------------------------------------------------------------------------------------------------                     
                                                                                                                                                      
Predicate Information (identified by operation id):                                                                                                   
---------------------------------------------------                                                                                                   

PLAN_TABLE_OUTPUT                                                                                                                                     
------------------------------------------------------------------------------------------------------------------------------------------------------
                                                                                                                                                      
   1 - filter(ROWNUM<=10)                                                                                                                             
   3 - filter(ROWNUM 

Notice that both methods report the same execution plan; it’s the access predicates that differ, and autotrace reports the index as the access point rather than the hidden column in the table. Historically it’s been dbms_xplan.display_cursor that’s been the ‘truth’, but in this case it may be autotrace reporting the actual predicates in use.

And that’s the truth.

Advertisements

July 28, 2015

It Isn’t The Index

Filed under: Indexes,Performance — dfitzjarrell @ 15:36

 


"If you want sense, you'll have to make it yourself."
- Norton Juster, The Phantom Tollbooth

One of the more confusing statistics in Oracle is, apparently, one called the clustering factor. Associated with an index (as evidenced by the columns in DBA_INDEXES) it’s actually dependent on the table data, more specifically the distance between ‘jumps’ for a given index key. Let’s define a ‘jump’ to be the number of blocks between rows containing the given index key starting with the first block found containing that key. That’s confusing but don’t despair, this will be explained in detail.

Oracle, in its infinite wisdom, computes a clustering factor for an index based upon the key value and the scattering of the rows containing that key across the table data. Looking at a simple example let’s look at a simple non-unique index (indexes for which the clustering factor matters) and the associated table data. The example starts by building the table and loading the data:


SQL> 
SQL> create table clust_fact(
  2  	     id      number,
  3  	     nm      varchar2(40),
  4  	     dt      date,
  5  	     sq      number);

Table created.

SQL> 
SQL> begin
  2  	     for i in 1..1000000 loop
  3  		     insert into clust_fact(id,nm,dt,sq)
  4  		     values(mod(i,77), 'Record '||i, sysdate - mod(i, 77), i);
  5  	     end loop;
  6  
  7  	     commit;
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL> 

Next, build the index and generate the statistics:


SQL>
SQL> create index clust_fact_idx on clust_fact(id);

Index created.

SQL> 
SQL> exec dbms_stats.gather_table_stats(user, 'CLUST_FACT', cascade=>true);

PL/SQL procedure successfully completed.

SQL> 

Query the clustering factor computed by Oracle:


SQL> select index_name, clustering_factor
  2  from user_indexes
  3  where table_name = 'CLUST_FACT';

INDEX_NAME                     CLUSTERING_FACTOR                                                                                    
------------------------------ -----------------                                                                                    
CLUST_FACT_IDX                            370731                                                                                    

SQL> 

The clustering factor provides a representation of how far Oracle needs to ‘jump’, on average, between blocks containing the index key value. To understand this a bit better let’s look at the code Oracle actually uses to compute the clustering factor and explain what it does:


SQL> 
SQL> --
SQL> -- 1 block limit (Oracle default)
SQL> --
SQL> select /*+  no_parallel_index(t, "CLUST_FACT_IDX")  dbms_stats
  2    cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring
  3    no_substrb_pad  no_expand index(t,"CLUST_FACT_IDX") */
  4    sys_op_countchg(substrb(t.rowid,1,15),1) as clf
  5  from
  6   "BING"."CLUST_FACT" t where "ID" is not null;

       CLF                                                                                                                          
----------                                                                                                                          
    370731                                                                                                                          

SQL> 

Oracle uses a function named sys_op_countchg, which takes two parameters, the first 15 bytes of the rowid and the number of blocks to traverse to trigger the count to increase; by default Oracle chooses to increment the clustering factor counter every time the block address changes. If three rows, for example, exist in the current block the counter does not increment; if the next three rows are in a different block the counter is incremented by 1 (Oracle doesn’t change blocks for the next two reads after the change). This keeps going througout the table to compute the final clustering factor. Knowing the function used by Oracle and its parameters it is possible to modify the call to accept larger values for the number of block changes before the clustering factor counter is incremented. To allow Oracle to consider 3 blocks ‘close enough’ to not change the counter the query is modified as follows:


SQL> 
SQL> --
SQL> -- 3 block limit
SQL> --
SQL> select /*+  no_parallel_index(t, "CLUST_FACT_IDX")  dbms_stats
  2    cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring
  3    no_substrb_pad  no_expand index(t,"CLUST_FACT_IDX") */
  4    sys_op_countchg(substrb(t.rowid,1,15),3) as clf
  5  from
  6   "BING"."CLUST_FACT" t where "ID" is not null;

       CLF                                                                                                                          
----------                                                                                                                          
    370731                                                                                                                          

SQL> 

Given how the values were computed for the ID column the clustering factor doesn’t change when we increase the block change count Oracle considers to be within range of the starting block address. Possible values for this second parameter are 1, 3, 5, 9 and 16. Going through the list of possible values it’s obvious with the current data loading the clustering factor won’t improve no matter which value we choose:


SQL> 
SQL> --
SQL> -- 5 block limit
SQL> --
SQL> select /*+  no_parallel_index(t, "CLUST_FACT_IDX")  dbms_stats
  2    cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring
  3    no_substrb_pad  no_expand index(t,"CLUST_FACT_IDX") */
  4    sys_op_countchg(substrb(t.rowid,1,15),5) as clf
  5  from
  6   "BING"."CLUST_FACT" t where "ID" is not null;

       CLF                                                                                                                          
----------                                                                                                                          
    370731                                                                                                                          

SQL> 
SQL> --
SQL> -- 9 block limit
SQL> --
SQL> select /*+  no_parallel_index(t, "CLUST_FACT_IDX")  dbms_stats
  2    cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring
  3    no_substrb_pad  no_expand index(t,"CLUST_FACT_IDX") */
  4    sys_op_countchg(substrb(t.rowid,1,15),9) as clf
  5  from
  6   "BING"."CLUST_FACT" t where "ID" is not null;

       CLF                                                                                                                          
----------                                                                                                                          
    370731                                                                                                                          

SQL> 
SQL> --
SQL> -- 16 block limit (ASM configuration)
SQL> --
SQL> select /*+  no_parallel_index(t, "CLUST_FACT_IDX")  dbms_stats
  2    cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring
  3    no_substrb_pad  no_expand index(t,"CLUST_FACT_IDX") */
  4    sys_op_countchg(substrb(t.rowid,1,15),16) as clf
  5  from
  6   "BING"."CLUST_FACT" t where "ID" is not null;

       CLF                                                                                                                          
----------                                                                                                                          
    370731                                                                                                                          

SQL> 

Since how the table data is ordered affects the clustering factor the table is reloaded with data ordered using the ID column:


SQL> 
SQL> create table clust_tmp as select * from clust_fact order by id;

Table created.

SQL> 
SQL> truncate table clust_fact;

Table truncated.

SQL> 
SQL> insert into clust_fact select * from clust_tmp;

1000000 rows created.

SQL> 
SQL> commit;

Commit complete.

SQL> 
SQL> exec dbms_stats.gather_table_stats(user, 'CLUST_FACT', cascade=>true);

PL/SQL procedure successfully completed.

SQL> 

Querying the clustering factor reveals it has significantly decreased since the keys are much closer together:


SQL> 
SQL> select index_name, clustering_factor
  2  from user_indexes
  3  where table_name = 'CLUST_FACT';

INDEX_NAME                     CLUSTERING_FACTOR                                                                                    
------------------------------ -----------------                                                                                    
CLUST_FACT_IDX                              4889                                                                                    

SQL> 
SQL> --
SQL> -- 1 block limit (Oracle default)
SQL> --
SQL> select /*+  no_parallel_index(t, "CLUST_FACT_IDX")  dbms_stats
  2    cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring
  3    no_substrb_pad  no_expand index(t,"CLUST_FACT_IDX") */
  4    sys_op_countchg(substrb(t.rowid,1,15),1) as clf
  5  from
  6   "BING"."CLUST_FACT" t where "ID" is not null;

       CLF                                                                                                                          
----------                                                                                                                          
      4889                                                                                                                          

SQL> 
SQL> --
SQL> -- 3 block limit
SQL> --
SQL> select /*+  no_parallel_index(t, "CLUST_FACT_IDX")  dbms_stats
  2    cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring
  3    no_substrb_pad  no_expand index(t,"CLUST_FACT_IDX") */
  4    sys_op_countchg(substrb(t.rowid,1,15),3) as clf
  5  from
  6   "BING"."CLUST_FACT" t where "ID" is not null;

       CLF                                                                                                                          
----------                                                                                                                          
      4888                                                                                                                          

SQL> 
SQL> --
SQL> -- 5 block limit
SQL> --
SQL> select /*+  no_parallel_index(t, "CLUST_FACT_IDX")  dbms_stats
  2    cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring
  3    no_substrb_pad  no_expand index(t,"CLUST_FACT_IDX") */
  4    sys_op_countchg(substrb(t.rowid,1,15),5) as clf
  5  from
  6   "BING"."CLUST_FACT" t where "ID" is not null;

       CLF                                                                                                                          
----------                                                                                                                          
      4888                                                                                                                          

SQL> 
SQL> --
SQL> -- 9 block limit
SQL> --
SQL> select /*+  no_parallel_index(t, "CLUST_FACT_IDX")  dbms_stats
  2    cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring
  3    no_substrb_pad  no_expand index(t,"CLUST_FACT_IDX") */
  4    sys_op_countchg(substrb(t.rowid,1,15),9) as clf
  5  from
  6   "BING"."CLUST_FACT" t where "ID" is not null;

       CLF                                                                                                                          
----------                                                                                                                          
      4885                                                                                                                          

SQL> 
SQL> --
SQL> -- 16 block limit (ASM configuration)
SQL> --
SQL> select /*+  no_parallel_index(t, "CLUST_FACT_IDX")  dbms_stats
  2    cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring
  3    no_substrb_pad  no_expand index(t,"CLUST_FACT_IDX") */
  4    sys_op_countchg(substrb(t.rowid,1,15),16) as clf
  5  from
  6   "BING"."CLUST_FACT" t where "ID" is not null;

       CLF                                                                                                                          
----------                                                                                                                          
      4881                                                                                                                          

SQL> 

It is worth noting that the clustering factor slightly decreases with larger values for the number of blocks Oracle can skip before updating the change count. The changes are not huge, and only differ by 8 from the value Oracle calculates by default to setting the ‘blocks to skip’ parameter to 16, its highest possible value. Since the original load created the SQ values in sequence the table is reloaded with the SQ values in order and a new concatenated index using both the ID and SQ columns is created. This is done to show how the clustering factor behaves for other indexes when it’s ‘optimized’ for one index:


SQL> 
SQL> drop table clust_tmp purge;

Table dropped.

SQL> 
SQL> create table clust_tmp as select * from clust_fact order by sq;

Table created.

SQL> 
SQL> truncate table clust_fact;

Table truncated.

SQL> 
SQL> insert into clust_fact select * from clust_tmp;

1000000 rows created.

SQL> 
SQL> commit;

Commit complete.

SQL> 
SQL> create index clust_fact_idx2 on clust_fact(id, sq);

Index created.

SQL> 
SQL> exec dbms_stats.gather_table_stats(user, 'CLUST_FACT', cascade=>true);

PL/SQL procedure successfully completed.

SQL> 

Querying the clustering factor the value for the original table load again appears and, again, doesn’t change even when the block skip parameter to the sys_op_countchg function is increased through the available values:


SQL> 
SQL> select index_name, clustering_factor
  2  from user_indexes
  3  where table_name = 'CLUST_FACT';

INDEX_NAME                     CLUSTERING_FACTOR                                                                                    
------------------------------ -----------------                                                                                    
CLUST_FACT_IDX2                           370731                                                                                    
CLUST_FACT_IDX                            370731                                                                                    

SQL> 
SQL> --
SQL> -- 1 block limit (Oracle default)
SQL> --
SQL> select /*+  no_parallel_index(t, "CLUST_FACT_IDX")  dbms_stats
  2    cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring
  3    no_substrb_pad  no_expand index(t,"CLUST_FACT_IDX") */
  4    sys_op_countchg(substrb(t.rowid,1,15),1) as clf
  5  from
  6   "BING"."CLUST_FACT" t where "ID" is not null;

       CLF                                                                                                                          
----------                                                                                                                          
    370731                                                                                                                          

SQL> 
SQL> --
SQL> -- 3 block limit
SQL> --
SQL> select /*+  no_parallel_index(t, "CLUST_FACT_IDX")  dbms_stats
  2    cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring
  3    no_substrb_pad  no_expand index(t,"CLUST_FACT_IDX") */
  4    sys_op_countchg(substrb(t.rowid,1,15),3) as clf
  5  from
  6   "BING"."CLUST_FACT" t where "ID" is not null;

       CLF                                                                                                                          
----------                                                                                                                          
    370731                                                                                                                          

SQL> 
SQL> --
SQL> -- 5 block limit
SQL> --
SQL> select /*+  no_parallel_index(t, "CLUST_FACT_IDX")  dbms_stats
  2    cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring
  3    no_substrb_pad  no_expand index(t,"CLUST_FACT_IDX") */
  4    sys_op_countchg(substrb(t.rowid,1,15),5) as clf
  5  from
  6   "BING"."CLUST_FACT" t where "ID" is not null;

       CLF                                                                                                                          
----------                                                                                                                          
    370731                                                                                                                          

SQL> 
SQL> --
SQL> -- 9 block limit
SQL> --
SQL> select /*+  no_parallel_index(t, "CLUST_FACT_IDX")  dbms_stats
  2    cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring
  3    no_substrb_pad  no_expand index(t,"CLUST_FACT_IDX") */
  4    sys_op_countchg(substrb(t.rowid,1,15),9) as clf
  5  from
  6   "BING"."CLUST_FACT" t where "ID" is not null;

       CLF                                                                                                                          
----------                                                                                                                          
    370731                                                                                                                          

SQL> 
SQL> --
SQL> -- 16 block limit (ASM configuration)
SQL> --
SQL> select /*+  no_parallel_index(t, "CLUST_FACT_IDX")  dbms_stats
  2    cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring
  3    no_substrb_pad  no_expand index(t,"CLUST_FACT_IDX") */
  4    sys_op_countchg(substrb(t.rowid,1,15),16) as clf
  5  from
  6   "BING"."CLUST_FACT" t where "ID" is not null;

       CLF                                                                                                                          
----------                                                                                                                          
    370731                                                                                                                          

SQL> 
SQL> --
SQL> -- 1 block limit (Oracle default)
SQL> --
SQL> select /*+  no_parallel_index(t, "CLUST_FACT_IDX2")  dbms_stats
  2    cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring
  3    no_substrb_pad  no_expand index(t,"CLUST_FACT_IDX2") */
  4    sys_op_countchg(substrb(t.rowid,1,15),1) as clf
  5  from
  6   "BING"."CLUST_FACT" t where "ID" is not null
  7  and "SQ" is not null;

       CLF                                                                                                                          
----------                                                                                                                          
    370731                                                                                                                          

SQL> 
SQL> --
SQL> -- 3 block limit
SQL> --
SQL> select /*+  no_parallel_index(t, "CLUST_FACT_IDX2")  dbms_stats
  2    cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring
  3    no_substrb_pad  no_expand index(t,"CLUST_FACT_IDX2") */
  4    sys_op_countchg(substrb(t.rowid,1,15),3) as clf
  5  from
  6   "BING"."CLUST_FACT" t where "ID" is not null
  7  and "SQ" is not null;

       CLF                                                                                                                          
----------                                                                                                                          
    370731                                                                                                                          

SQL> 
SQL> --
SQL> -- 5 block limit
SQL> --
SQL> select /*+  no_parallel_index(t, "CLUST_FACT_IDX2")  dbms_stats
  2    cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring
  3    no_substrb_pad  no_expand index(t,"CLUST_FACT_IDX2") */
  4    sys_op_countchg(substrb(t.rowid,1,15),5) as clf
  5  from
  6   "BING"."CLUST_FACT" t where "ID" is not null
  7  and "SQ" is not null;

       CLF                                                                                                                          
----------                                                                                                                          
    370731                                                                                                                          

SQL> 
SQL> --
SQL> -- 9 block limit
SQL> --
SQL> select /*+  no_parallel_index(t, "CLUST_FACT_IDX2")  dbms_stats
  2    cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring
  3    no_substrb_pad  no_expand index(t,"CLUST_FACT_IDX2") */
  4    sys_op_countchg(substrb(t.rowid,1,15),9) as clf
  5  from
  6   "BING"."CLUST_FACT" t where "ID" is not null
  7  and "SQ" is not null;

       CLF                                                                                                                          
----------                                                                                                                          
    370731                                                                                                                          

SQL> 
SQL> --
SQL> -- 16 block limit (ASM configuration)
SQL> --
SQL> select /*+  no_parallel_index(t, "CLUST_FACT_IDX2")  dbms_stats
  2    cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring
  3    no_substrb_pad  no_expand index(t,"CLUST_FACT_IDX2") */
  4    sys_op_countchg(substrb(t.rowid,1,15),16) as clf
  5  from
  6   "BING"."CLUST_FACT" t where "ID" is not null
  7  and "SQ" is not null;

       CLF                                                                                                                          
----------                                                                                                                          
    370731                                                                                                                          

SQL> 

Ordering the data by ID shows that for both indexes the clustering factor decreases, since the indexes both include the ID column. The presence of the SQ column causes the second index clustering factor to be greater when the default parameters are used; interestingly as the block skip count increases for the second index the clustering factor decreases to that of the single-column index:


SQL> 
SQL> drop table clust_tmp purge;

Table dropped.

SQL> 
SQL> create table clust_tmp as select * from clust_fact order by id;

Table created.

SQL> 
SQL> truncate table clust_fact;

Table truncated.

SQL> 
SQL> insert into clust_fact select * from clust_tmp;

1000000 rows created.

SQL> 
SQL> commit;

Commit complete.

SQL> 
SQL> exec dbms_stats.gather_table_stats(user, 'CLUST_FACT', cascade=>true);

PL/SQL procedure successfully completed.

SQL> 
SQL> select index_name, clustering_factor
  2  from user_indexes
  3  where table_name = 'CLUST_FACT';

INDEX_NAME                     CLUSTERING_FACTOR                                                                                    
------------------------------ -----------------                                                                                    
CLUST_FACT_IDX2                            75994                                                                                    
CLUST_FACT_IDX                              4889                                                                                    

SQL> 
SQL> --
SQL> -- 1 block limit (Oracle default)
SQL> --
SQL> select /*+  no_parallel_index(t, "CLUST_FACT_IDX")  dbms_stats
  2    cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring
  3    no_substrb_pad  no_expand index(t,"CLUST_FACT_IDX") */
  4    sys_op_countchg(substrb(t.rowid,1,15),1) as clf
  5  from
  6   "BING"."CLUST_FACT" t where "ID" is not null;

       CLF                                                                                                                          
----------                                                                                                                          
      4889                                                                                                                          

SQL> 
SQL> --
SQL> -- 3 block limit
SQL> --
SQL> select /*+  no_parallel_index(t, "CLUST_FACT_IDX")  dbms_stats
  2    cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring
  3    no_substrb_pad  no_expand index(t,"CLUST_FACT_IDX") */
  4    sys_op_countchg(substrb(t.rowid,1,15),3) as clf
  5  from
  6   "BING"."CLUST_FACT" t where "ID" is not null;

       CLF                                                                                                                          
----------                                                                                                                          
      4889                                                                                                                          

SQL> 
SQL> --
SQL> -- 5 block limit
SQL> --
SQL> select /*+  no_parallel_index(t, "CLUST_FACT_IDX")  dbms_stats
  2    cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring
  3    no_substrb_pad  no_expand index(t,"CLUST_FACT_IDX") */
  4    sys_op_countchg(substrb(t.rowid,1,15),5) as clf
  5  from
  6   "BING"."CLUST_FACT" t where "ID" is not null;

       CLF                                                                                                                          
----------                                                                                                                          
      4889                                                                                                                          

SQL> 
SQL> --
SQL> -- 9 block limit
SQL> --
SQL> select /*+  no_parallel_index(t, "CLUST_FACT_IDX")  dbms_stats
  2    cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring
  3    no_substrb_pad  no_expand index(t,"CLUST_FACT_IDX") */
  4    sys_op_countchg(substrb(t.rowid,1,15),9) as clf
  5  from
  6   "BING"."CLUST_FACT" t where "ID" is not null;

       CLF                                                                                                                          
----------                                                                                                                          
      4886                                                                                                                          

SQL> 
SQL> --
SQL> -- 16 block limit (ASM configuration)
SQL> --
SQL> select /*+  no_parallel_index(t, "CLUST_FACT_IDX")  dbms_stats
  2    cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring
  3    no_substrb_pad  no_expand index(t,"CLUST_FACT_IDX") */
  4    sys_op_countchg(substrb(t.rowid,1,15),16) as clf
  5  from
  6   "BING"."CLUST_FACT" t where "ID" is not null;

       CLF                                                                                                                          
----------                                                                                                                          
      4882                                                                                                                          

SQL> 
SQL> --
SQL> -- 1 block limit (Oracle default)
SQL> --
SQL> select /*+  no_parallel_index(t, "CLUST_FACT_IDX2")  dbms_stats
  2    cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring
  3    no_substrb_pad  no_expand index(t,"CLUST_FACT_IDX2") */
  4    sys_op_countchg(substrb(t.rowid,1,15),1) as clf
  5  from
  6   "BING"."CLUST_FACT" t where "ID" is not null
  7  and "SQ" is not null;

       CLF                                                                                                                          
----------                                                                                                                          
     75994                                                                                                                          

SQL> 
SQL> --
SQL> -- 3 block limit
SQL> --
SQL> select /*+  no_parallel_index(t, "CLUST_FACT_IDX2")  dbms_stats
  2    cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring
  3    no_substrb_pad  no_expand index(t,"CLUST_FACT_IDX2") */
  4    sys_op_countchg(substrb(t.rowid,1,15),3) as clf
  5  from
  6   "BING"."CLUST_FACT" t where "ID" is not null
  7  and "SQ" is not null;

       CLF                                                                                                                          
----------                                                                                                                          
      4967                                                                                                                          

SQL> 
SQL> --
SQL> -- 5 block limit
SQL> --
SQL> select /*+  no_parallel_index(t, "CLUST_FACT_IDX2")  dbms_stats
  2    cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring
  3    no_substrb_pad  no_expand index(t,"CLUST_FACT_IDX2") */
  4    sys_op_countchg(substrb(t.rowid,1,15),5) as clf
  5  from
  6   "BING"."CLUST_FACT" t where "ID" is not null
  7  and "SQ" is not null;

       CLF                                                                                                                          
----------                                                                                                                          
      4861                                                                                                                          

SQL> 
SQL> --
SQL> -- 9 block limit
SQL> --
SQL> select /*+  no_parallel_index(t, "CLUST_FACT_IDX2")  dbms_stats
  2    cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring
  3    no_substrb_pad  no_expand index(t,"CLUST_FACT_IDX2") */
  4    sys_op_countchg(substrb(t.rowid,1,15),9) as clf
  5  from
  6   "BING"."CLUST_FACT" t where "ID" is not null
  7  and "SQ" is not null;

       CLF                                                                                                                          
----------                                                                                                                          
      4861                                                                                                                          

SQL> 
SQL> --
SQL> -- 16 block limit (ASM configuration)
SQL> --
SQL> select /*+  no_parallel_index(t, "CLUST_FACT_IDX2")  dbms_stats
  2    cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring
  3    no_substrb_pad  no_expand index(t,"CLUST_FACT_IDX2") */
  4    sys_op_countchg(substrb(t.rowid,1,15),16) as clf
  5  from
  6   "BING"."CLUST_FACT" t where "ID" is not null
  7  and "SQ" is not null;

       CLF                                                                                                                          
----------                                                                                                                          
      4861                                                                                                                          

SQL> 

For multiple indexes on the same table, where no column is common between them, the clustering factor can skyrocket for some indexes when the table data is ordered to favor one particular index. That example wasn’t run and is left for you to investigate. Let’s do something similar and change the order of the columns in the second index and see how that affects the clustering factor:


SQL%gt; select index_name, clustering_factor
  2  from user_indexes
  3  where table_name = 'CLUST_FACT';

INDEX_NAME                     CLUSTERING_FACTOR                                                                                    
------------------------------ -----------------                                                                                    
CLUST_FACT_IDX2                           999810                                                                                    
CLUST_FACT_IDX                              4889                                                                                    

SQL> 
SQL> --
SQL> -- 1 block limit (Oracle default)
SQL> --
SQL> select /*+  no_parallel_index(t, "CLUST_FACT_IDX")  dbms_stats
  2    cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring
  3    no_substrb_pad  no_expand index(t,"CLUST_FACT_IDX") */
  4    sys_op_countchg(substrb(t.rowid,1,15),1) as clf
  5  from
  6   "BING"."CLUST_FACT" t where "ID" is not null;

       CLF                                                                                                                          
----------                                                                                                                          
      4889                                                                                                                          

SQL> 
SQL> --
SQL> -- 3 block limit
SQL> --
SQL> select /*+  no_parallel_index(t, "CLUST_FACT_IDX")  dbms_stats
  2    cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring
  3    no_substrb_pad  no_expand index(t,"CLUST_FACT_IDX") */
  4    sys_op_countchg(substrb(t.rowid,1,15),3) as clf
  5  from
  6   "BING"."CLUST_FACT" t where "ID" is not null;

       CLF                                                                                                                          
----------                                                                                                                          
      4889                                                                                                                          

SQL> 
SQL> --
SQL> -- 5 block limit
SQL> --
SQL> select /*+  no_parallel_index(t, "CLUST_FACT_IDX")  dbms_stats
  2    cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring
  3    no_substrb_pad  no_expand index(t,"CLUST_FACT_IDX") */
  4    sys_op_countchg(substrb(t.rowid,1,15),5) as clf
  5  from
  6   "BING"."CLUST_FACT" t where "ID" is not null;

       CLF                                                                                                                          
----------                                                                                                                          
      4889                                                                                                                          

SQL> 
SQL> --
SQL> -- 9 block limit
SQL> --
SQL> select /*+  no_parallel_index(t, "CLUST_FACT_IDX")  dbms_stats
  2    cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring
  3    no_substrb_pad  no_expand index(t,"CLUST_FACT_IDX") */
  4    sys_op_countchg(substrb(t.rowid,1,15),9) as clf
  5  from
  6   "BING"."CLUST_FACT" t where "ID" is not null;

       CLF                                                                                                                          
----------                                                                                                                          
      4886                                                                                                                          

SQL> 
SQL> --
SQL> -- 16 block limit (ASM configuration)
SQL> --
SQL> select /*+  no_parallel_index(t, "CLUST_FACT_IDX")  dbms_stats
  2    cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring
  3    no_substrb_pad  no_expand index(t,"CLUST_FACT_IDX") */
  4    sys_op_countchg(substrb(t.rowid,1,15),16) as clf
  5  from
  6   "BING"."CLUST_FACT" t where "ID" is not null;

       CLF                                                                                                                          
----------                                                                                                                          
      4882                                                                                                                          

SQL> 
SQL> --
SQL> -- 1 block limit (Oracle default)
SQL> --
SQL> select /*+  no_parallel_index(t, "CLUST_FACT_IDX2")  dbms_stats
  2    cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring
  3    no_substrb_pad  no_expand index(t,"CLUST_FACT_IDX2") */
  4    sys_op_countchg(substrb(t.rowid,1,15),1) as clf
  5  from
  6   "BING"."CLUST_FACT" t where "ID" is not null
  7  and "SQ" is not null;

       CLF                                                                                                                          
----------                                                                                                                          
    999810                                                                                                                          

SQL> 
SQL> --
SQL> -- 3 block limit
SQL> --
SQL> select /*+  no_parallel_index(t, "CLUST_FACT_IDX2")  dbms_stats
  2    cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring
  3    no_substrb_pad  no_expand index(t,"CLUST_FACT_IDX2") */
  4    sys_op_countchg(substrb(t.rowid,1,15),3) as clf
  5  from
  6   "BING"."CLUST_FACT" t where "ID" is not null
  7  and "SQ" is not null;

       CLF                                                                                                                          
----------                                                                                                                          
    999810                                                                                                                          

SQL> 
SQL> --
SQL> -- 5 block limit
SQL> --
SQL> select /*+  no_parallel_index(t, "CLUST_FACT_IDX2")  dbms_stats
  2    cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring
  3    no_substrb_pad  no_expand index(t,"CLUST_FACT_IDX2") */
  4    sys_op_countchg(substrb(t.rowid,1,15),5) as clf
  5  from
  6   "BING"."CLUST_FACT" t where "ID" is not null
  7  and "SQ" is not null;

       CLF                                                                                                                          
----------                                                                                                                          
    999810                                                                                                                          

SQL> 
SQL> --
SQL> -- 9 block limit
SQL> --
SQL> select /*+  no_parallel_index(t, "CLUST_FACT_IDX2")  dbms_stats
  2    cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring
  3    no_substrb_pad  no_expand index(t,"CLUST_FACT_IDX2") */
  4    sys_op_countchg(substrb(t.rowid,1,15),9) as clf
  5  from
  6   "BING"."CLUST_FACT" t where "ID" is not null
  7  and "SQ" is not null;

       CLF                                                                                                                          
----------                                                                                                                          
    999810                                                                                                                          

SQL> 
SQL> --
SQL> -- 16 block limit (ASM configuration)
SQL> --
SQL> select /*+  no_parallel_index(t, "CLUST_FACT_IDX2")  dbms_stats
  2    cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring
  3    no_substrb_pad  no_expand index(t,"CLUST_FACT_IDX2") */
  4    sys_op_countchg(substrb(t.rowid,1,15),16) as clf
  5  from
  6   "BING"."CLUST_FACT" t where "ID" is not null
  7  and "SQ" is not null;

       CLF                                                                                                                          
----------                                                                                                                          
    999810                                                                                                                          

SQL> 

Even though there is a common column between the indexes the column order matters; ordering the data by the key column for the single-column index caused the clustering factor for the second index to be much greater than it was when the leading column for the second index was the ID column.

Although associated with an index, the clustering factor is affected by the table data so no series of index rebuilds will ‘fix’ a ‘bad’ value. The table data needs to be ordered to favor an index (or indexes if they contain the column used to order the table data). This, of course, improves performance for the positively affected indexes and, unfortunately, adversely affects other indexes created against that same table. Care must be taken when deciding whether or not to order the table data; ‘reasonable’ performance metrics must be decided upon before embarking on such a change, and it should be done in test or dev before considering it in production. It may be that overall ‘acceptable’ performance across all of the indexes is better than ‘outstanding’ performance for one index that causes ‘terrible’ performance for all others. It’s not a simple decision and needs to be carefully considered.

If you want to implement the modified clustering factor computed by modifying the query Oracle uses to generate this statistic you will need to manually update the index statistics using the DBMS_STATS package using one of the SET_INDEX_STATS procedures:


SQL> 
SQL> --
SQL> -- 16 block limit (ASM configuration)
SQL> --
SQL> 
SQL> column clf new_value cl_f
SQL> 
SQL> select /*+  no_parallel_index(t, "CLUST_FACT_IDX2")  dbms_stats
  2    cursor_sharing_exact use_weak_name_resl dynamic_sampling(0) no_monitoring
  3    no_substrb_pad  no_expand index(t,"CLUST_FACT_IDX2") */
  4    sys_op_countchg(substrb(t.rowid,1,15),16) as clf
  5  from
  6   "BING"."CLUST_FACT" t where "ID" is not null
  7  and "SQ" is not null;

       CLF                                                                                                                          
----------                                                                                                                          
      4861                                                                                                                          

SQL> exec dbms_stats.set_index_stats(ownname => 'BING', indname => 'CLUST_FACT_IDX2', clstfct => &cl_f);

PL/SQL procedure successfully completed.

SQL> 

Using the new_value functionality in SQL*Plus saves the computed value into a reusable variable to pass to procedures and functions that need it. The above code would set the clustering factor for the CLUST_FACT_IDX2 to 4861, replacing the Oracle-calculated value of 75994. Again, care must be taken when undertaking such a change since you cannot predict the resulting performance change.

Since this post was originally written Oracle has implemented an enhancement to DBMS_STATS to allow the DBA to tell Oracle how many table blocks can expect to be cached. The SET_TABLE_PREFS, SET_SCHEMA_PREFS and SET_DATABASE_PREFS procedures have been modified to accept the TABLE_CACHED_BLOCKS parameter; this tells Oracle how many index blocks will be cached so these can be ignored when incrementing the clustering factor when statistics are gathered. Looking at an example we see:


SQL> create table mytab(
  2  myid    number,
  3  mydata  varchar2(40),
  4  mydate  date)
  5  cache;

Table created.

SQL> 
SQL> begin
  2  	     for i in 1..1000000 loop
  3  		     insert into mytab
  4  		     values(trunc(9999*(dbms_random.value)), 'Line '||i, trunc(sysdate - mod(i, 4637)));
  5  	     end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> 
SQL> commit;

Commit complete.

SQL> 
SQL> create index mytab_idx
  2  on mytab(myid, mydate);

Index created.

SQL> 
SQL> exec dbms_stats.gather_table_stats(ownname=>'BING', tabname=>'MYTAB', cascade=>true, estimate_percent=>null)

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_index_stats(ownname=>user, indname=>'MYTAB_IDX', estimate_percent=> null);

PL/SQL procedure successfully completed.

SQL> 
SQL> select table_name, index_name, clustering_factor
  2  from user_indexes
  3  where table_name = 'MYTAB';

TABLE_NAME                     INDEX_NAME                     CLUSTERING_FACTOR                                                     
------------------------------ ------------------------------ -----------------                                                     
MYTAB                          MYTAB_IDX                                 996238                                                     

SQL> 

For no adjustment to the default TABLE_CACHED_BLOCKS parameter we see a really large clustering factor (and this is due to the random nature of the MYID values generated). Let’s set TABLE_CACHED_BLOCKS to 47 for MYTAB and see what Oracle calculates for the clustering factor:


SQL> exec dbms_stats.set_table_prefs(ownname=>'BING', tabname=>'MYTAB', pname=>'TABLE_CACHED_BLOCKS', pvalue=>47);

PL/SQL procedure successfully completed.

SQL> 
SQL> commit;

Commit complete.

SQL> 
SQL> exec dbms_stats.gather_table_stats(ownname=>'BING', tabname=>'MYTAB', cascade=>true, estimate_percent=>null)

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_index_stats(ownname=>user, indname=>'MYTAB_IDX', estimate_percent=> null);

PL/SQL procedure successfully completed.

SQL> 
SQL> select table_name, index_name, clustering_factor
  2  from user_indexes
  3  where table_name = 'MYTAB';

TABLE_NAME                     INDEX_NAME                     CLUSTERING_FACTOR                                                     
------------------------------ ------------------------------ -----------------                                                     
MYTAB                          MYTAB_IDX                                 987551                                                     

SQL> 

The clustering factor has decreased somewhat (again it’s not a dramatic decrease due to the random nature of the MYID values). Let’s set the parameter to its maximum value (255) and see what Oracle provides as a clustering factor:


SQL> exec dbms_stats.set_table_prefs(ownname=>'BING', tabname=>'MYTAB', pname=>'TABLE_CACHED_BLOCKS', pvalue=>255);

PL/SQL procedure successfully completed.

SQL> 
SQL> commit;

Commit complete.

SQL> 
SQL> exec dbms_stats.gather_table_stats(ownname=>'BING', tabname=>'MYTAB', cascade=>true, estimate_percent=>null)

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_index_stats(ownname=>user, indname=>'MYTAB_IDX', estimate_percent=> null);

PL/SQL procedure successfully completed.

SQL> 
SQL> select table_name, index_name, clustering_factor
  2  from user_indexes
  3  where table_name = 'MYTAB';

TABLE_NAME                     INDEX_NAME                     CLUSTERING_FACTOR                                                     
------------------------------ ------------------------------ -----------------                                                     
MYTAB                          MYTAB_IDX                                 939451                                                     

SQL> 

It’s reduced again, although not dramatically, but it is less than it was originally. Using this new parameter to DBMS_STATS.SET_TABLE_PREFS, DBMS_STATS.SET_SCHEMA_PREFS and DBMS_STATS.SET_DATABASE_PREFS procedures can potentially fix a pessimistic clustering factor and improve index selection and, in turn, improve query performance.

The clustering factor determines which index Oracle will choose for a query against a given table. Changing the clustering factor will influence the CBO calculations sometimes for the better, sometimes for the worse. Plan carefully and test, test, test before second-guessing Oracle.

Which just makes sense.

January 13, 2012

My, How You’ve Grown

Filed under: Indexes — dfitzjarrell @ 19:21
Tags: , ,

Indexes are interesting objects — they can dramatically improve performance but their management can be, well, tricky. Depending upon how data is inserted into and deleted from a table the size an index can attain could be surprising to the DBA. How can the size be surprising? Let’s take an example through a number of iterations and see what Oracle does with the index, and explain why the results shouldn’t be unexpected.

Setting the stage we’ll create a table and a primary key index, load 200000 rows, delete the existing rows and insert new keys then see how the index responds. We’ll do this several times, under differing conditions, to see if the behaviour changes and, if so, why. By the end of the example we should know how index leaf blocks are used and re-used and why some dead space can remain in an index even though general wisdom says otherwise. We begin:

  
SQL> 
SQL> --
SQL> -- Create our test table and primary key index
SQL> --
SQL> 
SQL> create table biggy (id number constraint biggy_pk primary key, name varchar2(100));

Table created.

SQL> 
SQL> --
SQL> -- Insert 200,000 rows
SQL> --
SQL> 
SQL> insert into biggy select rownum, 'BIGGY' from dual connect by level  
SQL> commit;

Commit complete.

SQL> 
SQL> --
SQL> -- Validate the index structure
SQL> --
SQL> -- Report on the current index configuration
SQL> --
SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;

NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS    BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     200000           0        374          1

SQL> 
SQL> --
SQL> -- Create a procedure to delete the existing rows and add
SQL> -- new rows with increasing PK values
SQL> --
SQL> -- Note that we add a new record with an increasing PK at the
SQL> -- end of the index right after we delete the index entry for
SQL> -- the lowest PK value
SQL> --
SQL> --
SQL> -- This can do some strange things to the index structure
SQL> -- as the deleted leaf block cannot be reused since the new
SQL> -- key value is outside of the key range the deleted leaf block
SQL> -- is found in
SQL> --
SQL> 
SQL> create or replace procedure delete_insert_rows(p_commit_after in number)
  2  as
  3       n number;
  4       m number;
  5  begin
  6       select min(id),max(id) into n,m from biggy;
  7       for i in 1..200000 loop
  8    delete from biggy where id=n+i-1;
  9    insert into biggy values(m+i,'Big index test');
 10    if mod(i,p_commit_after)=0 then
 11         commit;
 12    end if;
 13       end loop;
 14       commit;
 15  end;
 16  /

Procedure created.

SQL> 
SQL> --
SQL> -- Replace the 200,000 existing rows with 200,000 new rows
SQL> --
SQL> 
SQL> exec delete_insert_rows(1000)

PL/SQL procedure successfully completed.

SQL> 
SQL> --
SQL> -- Check current index structure
SQL> --
SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;

NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS    BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     245378       45378        495          1

SQL> 
SQL> SELECT name,
  2       del_lf_rows,
  3       lf_rows - del_lf_rows lf_rows_used,
  4       to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
  5  FROM index_stats
  6  where name = upper('biggy_pk');

NAME                           DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK                             45378       200000   18.49310

SQL> 
SQL> --
SQL> -- Do it again
SQL> --
SQL> 
SQL> exec delete_insert_rows(2000)

PL/SQL procedure successfully completed.

SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;

NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS    BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     293295       93295        587          1

SQL> 
SQL> SELECT name,
  2       del_lf_rows,
  3       lf_rows - del_lf_rows lf_rows_used,
  4       to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
  5  FROM index_stats
  6  where name = upper('biggy_pk');

NAME                           DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK                             93295       200000   31.80927

SQL> 
SQL> --
SQL> -- Get the object_id for the PK index so we can perform a treedump
SQL> --
SQL> 
SQL> column object_id new_value objid
SQL> 
SQL> select object_id from dba_objects where object_name = 'BIGGY_PK';

 OBJECT_ID
----------
     69706

SQL> 
SQL> --
SQL> -- Execute the treedump for analysis
SQL> --
SQL> 
SQL> alter session set events 'immediate trace name treedump level &objid';

Session altered.

SQL> 
SQL> --
SQL> -- Go through 8 more runs of the delete/replace procedure
SQL> --
SQL> 
SQL> exec delete_insert_rows(3000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(4000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(5000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(6000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(7000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(8000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(9000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(10000)

PL/SQL procedure successfully completed.

SQL> 
SQL> --
SQL> -- Check the index structure and report on it
SQL> --
SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;

NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS    BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                3     484820      284820       1011          5

SQL> 
SQL> SELECT name,
  2       del_lf_rows,
  3       lf_rows - del_lf_rows lf_rows_used,
  4       to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
  5  FROM index_stats
  6  where name = upper('biggy_pk');

NAME                           DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK                            284820       200000   58.74758

We find that the way we’ve deleted and added the rows has affected how the leaf blocks are managed using a standard primary key index as the index has more than doubled in size; a good portion of that storage is empty leaf blocks that could not be reused by the new keys because they are out of the key range for the associated branch block. Let’s reverse the index and try this exercise again:

  
SQL> 
SQL> --
SQL> -- Drop the existing table and index then recreate
SQL> -- 
SQL> 
SQL> drop table biggy purge;

Table dropped.

SQL> 
SQL> --
SQL> -- Recreate our test table and primary key index
SQL> -- This time the PK is a reverse-key index
SQL> --
SQL> 
SQL> create table biggy (id number, name varchar2(100));

Table created.

SQL> create unique index biggy_pk on biggy(id) reverse;

Index created.

SQL> alter table biggy add constraint biggy_pk primary key(id) using index biggy_pk;

Table altered.

SQL> 
SQL> --
SQL> -- Insert 200,000 rows
SQL> --
SQL> 
SQL> insert into biggy select rownum, 'BIGGY' from dual connect by level  
SQL> commit;

Commit complete.

SQL> 
SQL> --
SQL> -- Validate the index structure
SQL> --
SQL> -- Report on the current index configuration
SQL> --
SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;

NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS    BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     200000           0        512          1

SQL> 
SQL> --
SQL> -- The reverse-key index greatly improves the performance and
SQL> -- decreases the instance of unused empty leaf blocks
SQL> -- compared to our first run
SQL> --
SQL> 
SQL> --
SQL> -- Replace the 200,000 existing rows with 200,000 new rows
SQL> --
SQL> 
SQL> exec delete_insert_rows(1000)

PL/SQL procedure successfully completed.

SQL> 
SQL> --
SQL> -- Check current index structure
SQL> --
SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;

NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS    BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     202645        2645        512          1

SQL> 
SQL> SELECT name,
  2       del_lf_rows,
  3       lf_rows - del_lf_rows lf_rows_used,
  4       to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
  5  FROM index_stats
  6  where name = upper('biggy_pk');

NAME                           DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK                              2645       200000    1.30524

SQL> 
SQL> --
SQL> -- Do it again
SQL> --
SQL> 
SQL> exec delete_insert_rows(2000)

PL/SQL procedure successfully completed.

SQL> 
SQL> select count(*), min(id), max(id) from biggy;

  COUNT(*)    MIN(ID)    MAX(ID)
---------- ---------- ----------
    200000     400001     600000

SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;

NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS    BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     205095        5095        512          1

SQL> 
SQL> SELECT name,
  2       del_lf_rows,
  3       lf_rows - del_lf_rows lf_rows_used,
  4       to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
  5  FROM index_stats
  6  where name = upper('biggy_pk');

NAME                           DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK                              5095       200000    2.48421

SQL> 
SQL> --
SQL> -- Get the object_id for the PK index so we can perform a treedump
SQL> --
SQL> 
SQL> column object_id new_value objid
SQL> 
SQL> select object_id from dba_objects where object_name = 'BIGGY_PK';

 OBJECT_ID
----------
     69709

SQL> 
SQL> --
SQL> -- Execute the treedump for analysis
SQL> --
SQL> 
SQL> alter session set events 'immediate trace name treedump level &objid';

Session altered.

SQL> 
SQL> --
SQL> -- Go through 8 more runs of the delete/replace procedure
SQL> --
SQL> 
SQL> exec delete_insert_rows(3000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(4000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(5000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(6000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(7000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(8000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(9000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(10000)

PL/SQL procedure successfully completed.

SQL> 
SQL> --
SQL> -- Check the index structure and report on it
SQL> --
SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;

NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS    BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     210249       10249        523          1

SQL> 
SQL> SELECT name,
  2       del_lf_rows,
  3       lf_rows - del_lf_rows lf_rows_used,
  4       to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
  5  FROM index_stats
  6  where name = upper('biggy_pk');

NAME                           DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK                             10249       200000    4.87470

SQL> 

The reverse-key index made a substantial impact on the number of empty leaf blocks left unused in the index. Let’t see if manual segment space management causes a change in behaviour over the prior tests using ASSM:

  
SQL> 
SQL> --
SQL> -- Create our test table and primary key index
SQL> -- Use a tablespace with manual segment management
SQL> --
SQL> 
SQL> create table biggy (id number, name varchar2(100));

Table created.

SQL> create unique index biggy_pk on biggy(id) tablespace bing_idx;

Index created.

SQL> alter table biggy add constraint biggy_pk primary key(id) using index biggy_pk;

Table altered.

SQL> 
SQL> --
SQL> -- Insert 200,000 rows
SQL> --
SQL> 
SQL> insert into biggy select rownum, 'BIGGY' from dual connect by level  
SQL> commit;

Commit complete.

SQL> 
SQL> --
SQL> -- Validate the index structure
SQL> --
SQL> -- Report on the current index configuration
SQL> --
SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;

NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS    BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     200000           0        374          1

SQL> 
SQL> --
SQL> -- Create a procedure to delete the existing rows and add
SQL> -- new rows with increasing PK values
SQL> --
SQL> -- Note that we add a new record with an increasing PK at the
SQL> -- end of the index right after we delete the index entry for
SQL> -- the lowest PK value
SQL> --
SQL> --
SQL> -- This can do some strange things to the index structure
SQL> -- as the deleted leaf block cannot be reused since the new
SQL> -- key value is outside of the key range the deleted leaf block
SQL> -- is found in when ASSM is used
SQL> --
SQL> -- MSSM may eliminate the behaviour
SQL> --
SQL> 
SQL> create or replace procedure delete_insert_rows(p_commit_after in number)
  2  as
  3       n number;
  4       m number;
  5  begin
  6       select min(id),max(id) into n,m from biggy;
  7       for i in 1..200000 loop
  8    delete from biggy where id=n+i-1;
  9    insert into biggy values(m+i,'Big index test');
 10    if mod(i,p_commit_after)=0 then
 11         commit;
 12    end if;
 13       end loop;
 14       commit;
 15  end;
 16  /

Procedure created.

SQL> 
SQL> --
SQL> -- Replace the 200,000 existing rows with 200,000 new rows
SQL> --
SQL> 
SQL> exec delete_insert_rows(1000)

PL/SQL procedure successfully completed.

SQL> 
SQL> --
SQL> -- Check current index structure
SQL> --
SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;

NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS    BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     201000        1000        379          1

SQL> 
SQL> SELECT name,
  2       del_lf_rows,
  3       lf_rows - del_lf_rows lf_rows_used,
  4       to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
  5  FROM index_stats
  6  where name = upper('biggy_pk');

NAME                           DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK                              1000       200000     .49751

SQL> 
SQL> --
SQL> -- Do it again
SQL> --
SQL> 
SQL> exec delete_insert_rows(2000)

PL/SQL procedure successfully completed.

SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;

NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS    BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     202000        2000        382          1

SQL> 
SQL> SELECT name,
  2       del_lf_rows,
  3       lf_rows - del_lf_rows lf_rows_used,
  4       to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
  5  FROM index_stats
  6  where name = upper('biggy_pk');

NAME                           DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK                              2000       200000     .99010

SQL> 
SQL> --
SQL> -- Get the object_id for the PK index so we can perform a treedump
SQL> --
SQL> 
SQL> column object_id new_value objid
SQL> 
SQL> select object_id from dba_objects where object_name = 'BIGGY_PK';

 OBJECT_ID
----------
     69711

SQL> 
SQL> --
SQL> -- Execute the treedump for analysis
SQL> --
SQL> 
SQL> alter session set events 'immediate trace name treedump level &objid';

Session altered.

SQL> 
SQL> --
SQL> -- Go through 8 more runs of the delete/replace procedure
SQL> --
SQL> 
SQL> exec delete_insert_rows(3000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(4000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(5000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(6000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(7000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(8000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(9000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(10000)

PL/SQL procedure successfully completed.

SQL> --
SQL> -- Check the index structure and report on it
SQL> --
SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;

NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS    BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     210000       10000        422          1

SQL> 
SQL> SELECT name,
  2       del_lf_rows,
  3       lf_rows - del_lf_rows lf_rows_used,
  4       to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
  5  FROM index_stats
  6  where name = upper('biggy_pk');

NAME                           DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK                             10000       200000    4.76190

SQL> 

We note that manual segment space management didn’t really make much of a difference in the storage with the standard primary key index; the index is still much larger than it ‘should’ be. Will it make a difference with the reverse-key version? Let’s test it and see:

  

SQL> drop table biggy purge;

Table dropped.

SQL> 
SQL> --
SQL> -- Recreate our test table and primary key index
SQL> -- This time the PK is a reverse-key index
SQL> -- Again we use a tablespace with manual extent management
SQL> --
SQL> 
SQL> create table biggy (id number, name varchar2(100));

Table created.

SQL> create unique index biggy_pk on biggy(id) reverse tablespace bing_idx;

Index created.

SQL> alter table biggy add constraint biggy_pk primary key(id) using index biggy_pk;

Table altered.

SQL> 
SQL> --
SQL> -- Insert 200,000 rows
SQL> --
SQL> 
SQL> insert into biggy select rownum, 'BIGGY' from dual connect by level  
SQL> commit;

Commit complete.

SQL> 
SQL> --
SQL> -- Validate the index structure
SQL> --
SQL> -- Report on the current index configuration
SQL> --
SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;

NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS    BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     200000           0        512          1

SQL> 
SQL> --
SQL> -- The reverse-key index greatly improves the performance and
SQL> -- decreases the instance of unused empty leaf blocks
SQL> -- compared to our first run
SQL> --
SQL> 
SQL> --
SQL> -- Replace the 200,000 existing rows with 200,000 new rows
SQL> --
SQL> 
SQL> exec delete_insert_rows(1000)

PL/SQL procedure successfully completed.

SQL> 
SQL> --
SQL> -- Check current index structure
SQL> --
SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;

NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS    BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     202645        2645        512          1

SQL> 
SQL> SELECT name,
  2       del_lf_rows,
  3       lf_rows - del_lf_rows lf_rows_used,
  4       to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
  5  FROM index_stats
  6  where name = upper('biggy_pk');

NAME                           DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK                              2645       200000    1.30524

SQL> 
SQL> --
SQL> -- Do it again
SQL> --
SQL> 
SQL> exec delete_insert_rows(2000)

PL/SQL procedure successfully completed.

SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;

NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS    BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     205095        5095        512          1

SQL> 
SQL> SELECT name,
  2       del_lf_rows,
  3       lf_rows - del_lf_rows lf_rows_used,
  4       to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
  5  FROM index_stats
  6  where name = upper('biggy_pk');

NAME                           DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK                              5095       200000    2.48421

SQL> 
SQL> --
SQL> -- Get the object_id for the PK index so we can perform a treedump
SQL> --
SQL> 
SQL> column object_id new_value objid
SQL> 
SQL> select object_id from dba_objects where object_name = 'BIGGY_PK';

 OBJECT_ID
----------
     69714

SQL> 
SQL> --
SQL> -- Execute the treedump for analysis
SQL> --
SQL> 
SQL> alter session set events 'immediate trace name treedump level &objid';

Session altered.

SQL> 
SQL> --
SQL> -- Go through 8 more runs of the delete/replace procedure
SQL> --
SQL> 
SQL> exec delete_insert_rows(3000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(4000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(5000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(6000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(7000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(8000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(9000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(10000)

PL/SQL procedure successfully completed.

SQL> --
SQL> -- Check the index structure and report on it
SQL> --
SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;

NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS    BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     210249       10249        523          1

SQL> 
SQL> SELECT name,
  2       del_lf_rows,
  3       lf_rows - del_lf_rows lf_rows_used,
  4       to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
  5  FROM index_stats
  6  where name = upper('biggy_pk');

NAME                           DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK                             10249       200000    4.87470

SQL> 

No real difference seen with the reverse-key index so the segment space management (at least in 11gR2) isn’t a factor. One thought on mitigating this behaviour is to set session_cached_cursors to 0; let’s see what that does:

  
SQL> 
SQL> --
SQL> -- Set session_cached_cursors to 0
SQL> --
SQL> -- May improve the situation further
SQL> --
SQL> 
SQL> alter session set session_cached_cursors = 0;

Session altered.

SQL> 
SQL> --
SQL> -- Go through the whole process one more time
SQL> -- with and without a reverse-key index
SQL> -- and using ASSM then MSSM to see which is better
SQL> --
SQL> 
SQL> --
SQL> -- Create our test table and primary key index
SQL> --
SQL> 
SQL> create table biggy (id number constraint biggy_pk primary key, name varchar2(100));

Table created.

SQL> 
SQL> --
SQL> -- Insert 200,000 rows
SQL> --
SQL> 
SQL> insert into biggy select rownum, 'BIGGY' from dual connect by level  
SQL> commit;

Commit complete.

SQL> 
SQL> --
SQL> -- Validate the index structure
SQL> --
SQL> -- Report on the current index configuration
SQL> --
SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;

NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS    BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     200000           0        374          1

SQL> 
SQL> --
SQL> -- Create a procedure to delete the existing rows and add
SQL> -- new rows with increasing PK values
SQL> --
SQL> -- Note that we add a new record with an increasing PK at the
SQL> -- end of the index right after we delete the index entry for
SQL> -- the lowest PK value
SQL> --
SQL> --
SQL> -- This can do some strange things to the index structure
SQL> -- as the deleted leaf block cannot be reused since the new
SQL> -- key value is outside of the key range the deleted leaf block
SQL> -- is found in
SQL> --
SQL> 
SQL> create or replace procedure delete_insert_rows(p_commit_after in number)
  2  as
  3       n number;
  4       m number;
  5  begin
  6       select min(id),max(id) into n,m from biggy;
  7       for i in 1..200000 loop
  8    delete from biggy where id=n+i-1;
  9    insert into biggy values(m+i,'Big index test');
 10    if mod(i,p_commit_after)=0 then
 11         commit;
 12    end if;
 13       end loop;
 14       commit;
 15  end;
 16  /

Procedure created.

SQL> 
SQL> --
SQL> -- Replace the 200,000 existing rows with 200,000 new rows
SQL> --
SQL> 
SQL> exec delete_insert_rows(1000)

PL/SQL procedure successfully completed.

SQL> 
SQL> --
SQL> -- Check current index structure
SQL> --
SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;

NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS    BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     225031       25031        440          1

SQL> 
SQL> SELECT name,
  2       del_lf_rows,
  3       lf_rows - del_lf_rows lf_rows_used,
  4       to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
  5  FROM index_stats
  6  where name = upper('biggy_pk');

NAME                           DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK                             25031       200000   11.12336

SQL> 
SQL> --
SQL> -- Do it again
SQL> --
SQL> 
SQL> exec delete_insert_rows(2000)

PL/SQL procedure successfully completed.

SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;

NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS    BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     228120       28120        446          1

SQL> 
SQL> SELECT name,
  2       del_lf_rows,
  3       lf_rows - del_lf_rows lf_rows_used,
  4       to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
  5  FROM index_stats
  6  where name = upper('biggy_pk');

NAME                           DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK                             28120       200000   12.32685

SQL> 
SQL> --
SQL> -- Get the object_id for the PK index so we can perform a treedump
SQL> --
SQL> 
SQL> column object_id new_value objid
SQL> 
SQL> select object_id from dba_objects where object_name = 'BIGGY_PK';

 OBJECT_ID
----------
     69716

SQL> 
SQL> --
SQL> -- Execute the treedump for analysis
SQL> --
SQL> 
SQL> alter session set events 'immediate trace name treedump level &objid';

Session altered.

SQL> 
SQL> --
SQL> -- Go through 8 more runs of the delete/replace procedure
SQL> --
SQL> 
SQL> exec delete_insert_rows(3000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(4000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(5000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(6000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(7000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(8000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(9000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(10000)

PL/SQL procedure successfully completed.

SQL> --
SQL> -- Check the index structure and report on it
SQL> --
SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;

NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS    BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     237132       37132        495          1

SQL> 
SQL> SELECT name,
  2       del_lf_rows,
  3       lf_rows - del_lf_rows lf_rows_used,
  4       to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
  5  FROM index_stats
  6  where name = upper('biggy_pk');

NAME                           DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK                             37132       200000   15.65879

SQL> 

Nothing changed for the standard index; let’s again test the reverse-key index and see what that produces:

  

SQL> drop table biggy purge;

Table dropped.

SQL> 
SQL> --
SQL> -- Recreate our test table and primary key index
SQL> -- This time the PK is a reverse-key index
SQL> --
SQL> 
SQL> create table biggy (id number, name varchar2(100));

Table created.

SQL> create unique index biggy_pk on biggy(id) reverse;

Index created.

SQL> alter table biggy add constraint biggy_pk primary key(id) using index biggy_pk;

Table altered.

SQL> 
SQL> --
SQL> -- Insert 200,000 rows
SQL> --
SQL> 
SQL> insert into biggy select rownum, 'BIGGY' from dual connect by level  
SQL> commit;

Commit complete.

SQL> 
SQL> --
SQL> -- Validate the index structure
SQL> --
SQL> -- Report on the current index configuration
SQL> --
SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;

NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS    BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     200000           0        512          1

SQL> 
SQL> --
SQL> -- The reverse-key index greatly improves the performance and
SQL> -- decreases the instance of unused empty leaf blocks
SQL> -- compared to our first run
SQL> --
SQL> 
SQL> --
SQL> -- Replace the 200,000 existing rows with 200,000 new rows
SQL> --
SQL> 
SQL> exec delete_insert_rows(1000)

PL/SQL procedure successfully completed.

SQL> 
SQL> --
SQL> -- Check current index structure
SQL> --
SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;

NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS    BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     202645        2645        512          1

SQL> 
SQL> SELECT name,
  2       del_lf_rows,
  3       lf_rows - del_lf_rows lf_rows_used,
  4       to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
  5  FROM index_stats
  6  where name = upper('biggy_pk');

NAME                           DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK                              2645       200000    1.30524

SQL> 
SQL> --
SQL> -- Do it again
SQL> --
SQL> 
SQL> exec delete_insert_rows(2000)

PL/SQL procedure successfully completed.

SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;

NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS    BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     205095        5095        512          1

SQL> 
SQL> SELECT name,
  2       del_lf_rows,
  3       lf_rows - del_lf_rows lf_rows_used,
  4       to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
  5  FROM index_stats
  6  where name = upper('biggy_pk');

NAME                           DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK                              5095       200000    2.48421

SQL> 
SQL> --
SQL> -- Get the object_id for the PK index so we can perform a treedump
SQL> --
SQL> 
SQL> column object_id new_value objid
SQL> 
SQL> select object_id from dba_objects where object_name = 'BIGGY_PK';

 OBJECT_ID
----------
     69718

SQL> 
SQL> --
SQL> -- Execute the treedump for analysis
SQL> --
SQL> 
SQL> alter session set events 'immediate trace name treedump level &objid';

Session altered.

SQL> 
SQL> --
SQL> -- Go through 8 more runs of the delete/replace procedure
SQL> --
SQL> 
SQL> exec delete_insert_rows(3000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(4000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(5000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(6000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(7000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(8000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(9000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(10000)

PL/SQL procedure successfully completed.

SQL> --
SQL> -- Check the index structure and report on it
SQL> --
SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;

NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS    BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     210249       10249        523          1

SQL> 
SQL> SELECT name,
  2       del_lf_rows,
  3       lf_rows - del_lf_rows lf_rows_used,
  4       to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
  5  FROM index_stats
  6  where name = upper('biggy_pk');

NAME                           DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK                             10249       200000    4.87470

SQL> 

Again no change is realized; we try again with manual segment space management and collect the results:

  
SQL> drop table biggy purge;

Table dropped.

SQL> 
SQL> 
SQL> --
SQL> -- Create our test table and primary key index
SQL> -- Use a tablespace with manual segment management
SQL> --
SQL> 
SQL> create table biggy (id number, name varchar2(100));

Table created.

SQL> create unique index biggy_pk on biggy(id) tablespace bing_idx;

Index created.

SQL> alter table biggy add constraint biggy_pk primary key(id) using index biggy_pk;

Table altered.

SQL> 
SQL> --
SQL> -- Insert 200,000 rows
SQL> --
SQL> 
SQL> insert into biggy select rownum, 'BIGGY' from dual connect by level  
SQL> commit;

Commit complete.

SQL> 
SQL> --
SQL> -- Validate the index structure
SQL> --
SQL> -- Report on the current index configuration
SQL> --
SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;

NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS    BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     200000           0        374          1

SQL> 
SQL> --
SQL> -- Create a procedure to delete the existing rows and add
SQL> -- new rows with increasing PK values
SQL> --
SQL> -- Note that we add a new record with an increasing PK at the
SQL> -- end of the index right after we delete the index entry for
SQL> -- the lowest PK value
SQL> --
SQL> --
SQL> -- This can do some strange things to the index structure
SQL> -- as the deleted leaf block cannot be reused since the new
SQL> -- key value is outside of the key range the deleted leaf block
SQL> -- is found in when ASSM is used
SQL> --
SQL> -- MSSM may eliminate the behaviour
SQL> --
SQL> 
SQL> create or replace procedure delete_insert_rows(p_commit_after in number)
  2  as
  3       n number;
  4       m number;
  5  begin
  6       select min(id),max(id) into n,m from biggy;
  7       for i in 1..200000 loop
  8    delete from biggy where id=n+i-1;
  9    insert into biggy values(m+i,'Big index test');
 10    if mod(i,p_commit_after)=0 then
 11         commit;
 12    end if;
 13       end loop;
 14       commit;
 15  end;
 16  /

Procedure created.

SQL> 
SQL> --
SQL> -- Replace the 200,000 existing rows with 200,000 new rows
SQL> --
SQL> 
SQL> exec delete_insert_rows(1000)

PL/SQL procedure successfully completed.

SQL> 
SQL> --
SQL> -- Check current index structure
SQL> --
SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;

NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS    BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     201000        1000        379          1

SQL> 
SQL> SELECT name,
  2       del_lf_rows,
  3       lf_rows - del_lf_rows lf_rows_used,
  4       to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
  5  FROM index_stats
  6  where name = upper('biggy_pk');

NAME                           DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK                              1000       200000     .49751

SQL> 
SQL> --
SQL> -- Do it again
SQL> --
SQL> 
SQL> exec delete_insert_rows(2000)

PL/SQL procedure successfully completed.

SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;

NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS    BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     202000        2000        382          1

SQL> 
SQL> SELECT name,
  2       del_lf_rows,
  3       lf_rows - del_lf_rows lf_rows_used,
  4       to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
  5  FROM index_stats
  6  where name = upper('biggy_pk');

NAME                           DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK                              2000       200000     .99010

SQL> 
SQL> --
SQL> -- Get the object_id for the PK index so we can perform a treedump
SQL> --
SQL> 
SQL> column object_id new_value objid
SQL> 
SQL> select object_id from dba_objects where object_name = 'BIGGY_PK';

 OBJECT_ID
----------
     69720

SQL> 
SQL> --
SQL> -- Execute the treedump for analysis
SQL> --
SQL> 
SQL> alter session set events 'immediate trace name treedump level &objid';

Session altered.

SQL> 
SQL> --
SQL> -- Go through 8 more runs of the delete/replace procedure
SQL> --
SQL> 
SQL> exec delete_insert_rows(3000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(4000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(5000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(6000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(7000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(8000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(9000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(10000)

PL/SQL procedure successfully completed.

SQL> --
SQL> -- Check the index structure and report on it
SQL> --
SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;

NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS    BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     210000       10000        422          1

SQL> 
SQL> SELECT name,
  2       del_lf_rows,
  3       lf_rows - del_lf_rows lf_rows_used,
  4       to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
  5  FROM index_stats
  6  where name = upper('biggy_pk');

NAME                           DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK                             10000       200000    4.76190

SQL> 

Again, no change. One more time with the reverse-key index, using manual segment space management:

  

SQL> drop table biggy purge;

Table dropped.

SQL> 
SQL> --
SQL> -- Recreate our test table and primary key index
SQL> -- This time the PK is a reverse-key index
SQL> -- Again we use a tablespace with manual extent management
SQL> --
SQL> 
SQL> create table biggy (id number, name varchar2(100));

Table created.

SQL> create unique index biggy_pk on biggy(id) reverse tablespace bing_idx;

Index created.

SQL> alter table biggy add constraint biggy_pk primary key(id) using index biggy_pk;

Table altered.

SQL> 
SQL> --
SQL> -- Insert 200,000 rows
SQL> --
SQL> 
SQL> insert into biggy select rownum, 'BIGGY' from dual connect by level  
SQL> commit;

Commit complete.

SQL> 
SQL> --
SQL> -- Validate the index structure
SQL> --
SQL> -- Report on the current index configuration
SQL> --
SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;

NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS    BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     200000           0        512          1

SQL> 
SQL> --
SQL> -- The reverse-key index greatly improves the performance and
SQL> -- decreases the instance of unused empty leaf blocks
SQL> -- compared to our first run
SQL> --
SQL> 
SQL> --
SQL> -- Replace the 200,000 existing rows with 200,000 new rows
SQL> --
SQL> 
SQL> exec delete_insert_rows(1000)

PL/SQL procedure successfully completed.

SQL> 
SQL> --
SQL> -- Check current index structure
SQL> --
SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;

NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS    BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     202645        2645        512          1

SQL> 
SQL> SELECT name,
  2       del_lf_rows,
  3       lf_rows - del_lf_rows lf_rows_used,
  4       to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
  5  FROM index_stats
  6  where name = upper('biggy_pk');

NAME                           DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK                              2645       200000    1.30524

SQL> 
SQL> --
SQL> -- Do it again
SQL> --
SQL> 
SQL> exec delete_insert_rows(2000)

PL/SQL procedure successfully completed.

SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;

NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS    BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     205061        5061        512          1

SQL> 
SQL> SELECT name,
  2       del_lf_rows,
  3       lf_rows - del_lf_rows lf_rows_used,
  4       to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
  5  FROM index_stats
  6  where name = upper('biggy_pk');

NAME                           DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK                              5061       200000    2.46805

SQL> 
SQL> --
SQL> -- Get the object_id for the PK index so we can perform a treedump
SQL> --
SQL> 
SQL> column object_id new_value objid
SQL> 
SQL> select object_id from dba_objects where object_name = 'BIGGY_PK';

 OBJECT_ID
----------
     69722

SQL> 
SQL> --
SQL> -- Execute the treedump for analysis
SQL> --
SQL> 
SQL> alter session set events 'immediate trace name treedump level &objid';

Session altered.

SQL> 
SQL> --
SQL> -- Go through 8 more runs of the delete/replace procedure
SQL> --
SQL> 
SQL> exec delete_insert_rows(3000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(4000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(5000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(6000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(7000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(8000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(9000)

PL/SQL procedure successfully completed.

SQL> exec delete_insert_rows(10000)

PL/SQL procedure successfully completed.

SQL> --
SQL> -- Check the index structure and report on it
SQL> --
SQL> 
SQL> analyze index biggy_pk validate structure;

Index analyzed.

SQL> 
SQL> select name, height, lf_rows, del_lf_rows, lf_blks, br_blks from index_stats;

NAME                               HEIGHT    LF_ROWS DEL_LF_ROWS    LF_BLKS    BR_BLKS
------------------------------ ---------- ---------- ----------- ---------- ----------
BIGGY_PK                                2     210249       10249        523          1

SQL> 
SQL> SELECT name,
  2       del_lf_rows,
  3       lf_rows - del_lf_rows lf_rows_used,
  4       to_char(del_lf_rows / (lf_rows)*100,'999.99999') ibadness
  5  FROM index_stats
  6  where name = upper('biggy_pk');

NAME                           DEL_LF_ROWS LF_ROWS_USED IBADNESS
------------------------------ ----------- ------------ ----------
BIGGY_PK                             10249       200000    4.87470SQL> 

Notice the setting didn’t do much of anything to improve the situation.

So, what happened? The initial pass, with the standard index, causes Oracle to wait to reuse empty leaf blocks until the branch block they are attached to is empty; since we deleted the smallest available key then inserted a new largest key the leaf block released could not immediately be reused as the branch block still had leaf blocks attached to it. Somewhere around the middle of the whole delete/insert process the leaf blocks we released at the beginning of the process were finally available for reuse. Reversing the key on the primary key index allowed reuse of the leaf blocks by the new keys since, in reverse order, they could ‘fit in’ to the key order of the index. Manual segment space management didn’t do much to improve this nor did setting session_cached_cursors to 0.

Of course the ideal method is to delete the rows in batches with the intent of freeing the branch block so the empty leaf blocks can be reused but piecemeal deletes and inserts can and will happen in OLTP systems so such a scenario can be repeated in a running production database. An interesting side note on this is that primary key indexes aren’t usually rebuilt as reverse-key indexes unless block contention is high for the index yet that action can also dramatically reduce the number of empty leaf blocks in the index after rows are deleted. It may be worth considering the use of a reverse-key primary key index to keep the index size ‘reasonable’.

It may be a rare occurrence to have an ever-increasing index even though volumes of data have been deleted but knowing what to do to help correct the situation may prove invaluable should the situation arise. In my opinion it’s better to know something you may not need rather than need something you do not know.

My two cents.

November 19, 2008

‘Magical’ Indexes

Filed under: Indexes,Performance — dfitzjarrell @ 15:32

In a forum I frequent the following question was presented (I have reposted the question in its entirety here):

Suppose I have a table structure like T1(C1,C2,C3,C4....). 
I  creates a index indx1 on (C1,C2,C3). I issue three select statements like : 

1.select * from T1 where C1=<> 
2.select * from T1 where C2=<> 
3.select * from T1 where C3=<> 
4.select * from T1 where C1= <> and  C2= <> and C3 =<> 


How the optimizer will use the index for each statements. 


Regards, 
Sanjoy 

Of course the only way to begin to answer that, since the original poster did not provide any DDL or sample data, is to create an example and execute it (we’ll ignore the fact that his three questions are numbered 1 through 4). I’ve posted the results below, this running on an Oracle 11.1.0.6 instance; the statistics reported by autotrace have been removed as they provided no additional value to the example. Notice how the plans change as the data ‘topography’ changes, illustrating that the original question was ambiguous, at best:

SQL>
SQL> --
SQL> -- Suppose I have a table structure like T1(C1,C2,C3,C4....).
SQL> -- I  creates a index indx1 on (C1,C2,C3). I issue three select
SQL> -- statements like :
SQL> --
SQL> -- 1.select * from T1 where C1=
SQL> -- 2.select * from T1 where C2=
SQL> -- 3.select * from T1 where C3=
SQL> -- 4.select * from T1 where C1=  and  C2=  and C3 =
SQL> --
SQL> --
SQL> -- How the optimizer will use the index for each statements.
SQL> --
SQL> --
SQL> -- Regards,
SQL> -- Sanjoy
SQL> --
SQL>
SQL> --
SQL> -- Create the table in question
SQL> --
SQL> create table t1(
  2        c1 number,
  3        c2 varchar2(20),
  4        c3 date,
  5        c4 varchar2(10),
  6        c5 number,
  7        c6 number
  8  );

Table created.

SQL>
SQL> --
SQL> -- Create the index specified
SQL> --
SQL> create index indx1
  2  on t1(c1,c2,c3);

Index created.

SQL>
SQL> --
SQL> -- Load test data
SQL> --
SQL>
SQL> --
SQL> -- Data with unique C1, C2 and C3 values
SQL> --
SQL> begin
  2        for i in 1..10101 loop
  3         insert into t1
  4         values (i, 'Testing record '||i, trunc(sysdate+i), 'Filler', mod(i, 734), mod(i, 963));
  5        end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> --
SQL> -- 'Standard' statistics
SQL> --
SQL> exec dbms_stats.gather_schema_stats(ownname=>'BING');

PL/SQL procedure successfully completed.

SQL>
SQL> set autotrace on linesize 132
SQL>
SQL> select * From t1 where c1 =433;

        C1 C2                   C3        C4                 C5         C6
---------- -------------------- --------- ---------- ---------- ----------
       433 Testing record 433   26-JAN-10 Filler            433        433


Execution Plan
----------------------------------------------------------
Plan hash value: 552572096

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    46 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |    46 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | INDX1 |     1 |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C1"=433)

SQL>
SQL> select * From t1 where c2 = 'Testing record 7748';

        C1 C2                   C3        C4                 C5         C6
---------- -------------------- --------- ---------- ---------- ----------
      7748 Testing record 7748  05-FEB-30 Filler            408         44


Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    46 |    22   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |    46 |    22   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("C2"='Testing record 7748')

SQL>
SQL> select * from t1 where c3 = trunc(sysdate+9);

        C1 C2                   C3        C4                 C5         C6
---------- -------------------- --------- ---------- ---------- ----------
         9 Testing record 9     28-NOV-08 Filler              9          9


Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    46 |    22   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |    46 |    22   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("C3"=TRUNC(SYSDATE@!+9))

SQL>
SQL> select * From t1 where c1 = 9993 and c2 = 'Testing record 9993' and c3 = trunc(sysdate+9993);

        C1 C2                   C3        C4                 C5         C6
---------- -------------------- --------- ---------- ---------- ----------
      9993 Testing record 9993  30-MAR-36 Filler            451        363


Execution Plan
----------------------------------------------------------
Plan hash value: 552572096

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    46 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |    46 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | INDX1 |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C1"=9993 AND "C2"='Testing record 9993' AND
              "C3"=TRUNC(SYSDATE@!+9993))

SQL>
SQL> set autotrace off
SQL>
SQL> --
SQL> -- Statistics with auto-sized histograms on indexed columns
SQL> --
SQL> exec dbms_stats.gather_schema_stats(ownname=>'BING', method_opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO');

PL/SQL procedure successfully completed.

SQL>
SQL> set autotrace on linesize 132
SQL>
SQL> select * From t1 where c1 =433;

        C1 C2                   C3        C4                 C5         C6
---------- -------------------- --------- ---------- ---------- ----------
       433 Testing record 433   26-JAN-10 Filler            433        433


Execution Plan
----------------------------------------------------------
Plan hash value: 552572096

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    46 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |    46 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | INDX1 |     1 |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C1"=433)

SQL>
SQL> select * From t1 where c2 = 'Testing record 7748';

        C1 C2                   C3        C4                 C5         C6
---------- -------------------- --------- ---------- ---------- ----------
      7748 Testing record 7748  05-FEB-30 Filler            408         44


Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    46 |    22   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |    46 |    22   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("C2"='Testing record 7748')

SQL>
SQL> select * from t1 where c3 = trunc(sysdate+9);

        C1 C2                   C3        C4                 C5         C6
---------- -------------------- --------- ---------- ---------- ----------
         9 Testing record 9     28-NOV-08 Filler              9          9


Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    46 |    22   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |    46 |    22   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("C3"=TRUNC(SYSDATE@!+9))

SQL>
SQL> select * From t1 where c1 = 9993 and c2 = 'Testing record 9993' and c3 = trunc(sysdate+9993);

        C1 C2                   C3        C4                 C5         C6
---------- -------------------- --------- ---------- ---------- ----------
      9993 Testing record 9993  30-MAR-36 Filler            451        363


Execution Plan
----------------------------------------------------------
Plan hash value: 552572096

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    46 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |    46 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | INDX1 |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C1"=9993 AND "C2"='Testing record 9993' AND
              "C3"=TRUNC(SYSDATE@!+9993))

SQL>
SQL> set autotrace off
SQL>
SQL> truncate table t1;

Table truncated.

SQL>
SQL> --
SQL> -- Data with unique C2 and C3 values, and a reasonably selective C1
SQL> --
SQL> begin
  2        for i in 1..10101 loop
  3         insert into t1
  4         values (mod(i, 43), 'Testing record '||i, trunc(sysdate+i), 'Filler', mod(i, 734), mod(i, 963));
  5        end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> --
SQL> -- 'Standard' statistics
SQL> --
SQL> exec dbms_stats.gather_schema_stats(ownname=>'BING');

PL/SQL procedure successfully completed.

SQL>
SQL> set autotrace on linesize 132
SQL>
SQL> select * From t1 where c1 =433;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 552572096

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    45 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |    45 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | INDX1 |     1 |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C1"=433)

SQL>
SQL> select * From t1 where c2 = 'Testing record 7748';

        C1 C2                   C3        C4                 C5         C6
---------- -------------------- --------- ---------- ---------- ----------
         8 Testing record 7748  05-FEB-30 Filler            408         44


Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    45 |    22   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |    45 |    22   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("C2"='Testing record 7748')

SQL>
SQL> select * from t1 where c3 = trunc(sysdate+9);

        C1 C2                   C3        C4                 C5         C6
---------- -------------------- --------- ---------- ---------- ----------
         9 Testing record 9     28-NOV-08 Filler              9          9


Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    45 |    22   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |    45 |    22   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("C3"=TRUNC(SYSDATE@!+9))

SQL>
SQL> select * From t1 where c1 = 9993 and c2 = 'Testing record 9993' and c3 = trunc(sysdate+9993);

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 552572096

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    45 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |    45 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | INDX1 |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C1"=9993 AND "C2"='Testing record 9993' AND
              "C3"=TRUNC(SYSDATE@!+9993))

SQL>
SQL> set autotrace off
SQL>
SQL> --
SQL> -- Statistics with auto-sized histograms on indexed columns
SQL> --
SQL> exec dbms_stats.gather_schema_stats(ownname=>'BING', method_opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO');

PL/SQL procedure successfully completed.

SQL>
SQL> set autotrace on linesize 132
SQL>
SQL> select * From t1 where c1 =433;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 552572096

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    45 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |    45 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | INDX1 |     1 |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C1"=433)

SQL>
SQL> select * From t1 where c2 = 'Testing record 7748';

        C1 C2                   C3        C4                 C5         C6
---------- -------------------- --------- ---------- ---------- ----------
         8 Testing record 7748  05-FEB-30 Filler            408         44


Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    45 |    22   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |    45 |    22   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("C2"='Testing record 7748')

SQL>
SQL> select * from t1 where c3 = trunc(sysdate+9);

        C1 C2                   C3        C4                 C5         C6
---------- -------------------- --------- ---------- ---------- ----------
         9 Testing record 9     28-NOV-08 Filler              9          9


Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    45 |    22   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |    45 |    22   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("C3"=TRUNC(SYSDATE@!+9))

SQL>
SQL> select * From t1 where c1 = 9993 and c2 = 'Testing record 9993' and c3 = trunc(sysdate+9993);

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 552572096

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    45 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |    45 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | INDX1 |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C1"=9993 AND "C2"='Testing record 9993' AND
              "C3"=TRUNC(SYSDATE@!+9993))

SQL>
SQL> set autotrace off
SQL>
SQL> truncate table t1;

Table truncated.

SQL>
SQL> --
SQL> -- Data with unique C2 and C3 values, with a cycling C1 set of values
SQL> --
SQL> begin
  2        for i in 1..10101 loop
  3         insert into t1
  4         values (mod(i, 3), 'Testing record '||i, trunc(sysdate+i), 'Filler', mod(i, 734), mod(i, 963));
  5        end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> --
SQL> -- 'Standard' statistics
SQL> --
SQL> exec dbms_stats.gather_schema_stats(ownname=>'BING');

PL/SQL procedure successfully completed.

SQL>
SQL> set autotrace on linesize 132
SQL>
SQL> select * From t1 where c1 =433;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 552572096

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    45 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |    45 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | INDX1 |     1 |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C1"=433)

SQL>
SQL> select * From t1 where c2 = 'Testing record 7748';

        C1 C2                   C3        C4                 C5         C6
---------- -------------------- --------- ---------- ---------- ----------
         2 Testing record 7748  05-FEB-30 Filler            408         44


Execution Plan
----------------------------------------------------------
Plan hash value: 3325179317

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    45 |     5   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |    45 |     5   (0)| 00:00:01 |
|*  2 |   INDEX SKIP SCAN           | INDX1 |     1 |       |     4   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C2"='Testing record 7748')
       filter("C2"='Testing record 7748')

SQL>
SQL> select * from t1 where c3 = trunc(sysdate+9);

        C1 C2                   C3        C4                 C5         C6
---------- -------------------- --------- ---------- ---------- ----------
         0 Testing record 9     28-NOV-08 Filler              9          9


Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    45 |    22   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |    45 |    22   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("C3"=TRUNC(SYSDATE@!+9))

SQL>
SQL> select * From t1 where c1 = 9993 and c2 = 'Testing record 9993' and c3 = trunc(sysdate+9993);

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 552572096

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    45 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |    45 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | INDX1 |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C1"=9993 AND "C2"='Testing record 9993' AND
              "C3"=TRUNC(SYSDATE@!+9993))

SQL>
SQL> set autotrace off
SQL>
SQL> --
SQL> -- Statistics with auto-sized histograms on indexed columns
SQL> --
SQL> exec dbms_stats.gather_schema_stats(ownname=>'BING', method_opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO');

PL/SQL procedure successfully completed.

SQL>
SQL> set autotrace on linesize 132
SQL>
SQL> select * From t1 where c1 =433;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 552572096

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    45 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |    45 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | INDX1 |     1 |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C1"=433)

SQL>
SQL> select * From t1 where c2 = 'Testing record 7748';

        C1 C2                   C3        C4                 C5         C6
---------- -------------------- --------- ---------- ---------- ----------
         2 Testing record 7748  05-FEB-30 Filler            408         44


Execution Plan
----------------------------------------------------------
Plan hash value: 3325179317

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    45 |     5   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |    45 |     5   (0)| 00:00:01 |
|*  2 |   INDEX SKIP SCAN           | INDX1 |     1 |       |     4   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C2"='Testing record 7748')
       filter("C2"='Testing record 7748')

SQL>
SQL> select * from t1 where c3 = trunc(sysdate+9);

        C1 C2                   C3        C4                 C5         C6
---------- -------------------- --------- ---------- ---------- ----------
         0 Testing record 9     28-NOV-08 Filler              9          9


Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    45 |    22   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T1   |     1 |    45 |    22   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("C3"=TRUNC(SYSDATE@!+9))

SQL>
SQL> select * From t1 where c1 = 9993 and c2 = 'Testing record 9993' and c3 = trunc(sysdate+9993);

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 552572096

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    45 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |    45 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | INDX1 |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C1"=9993 AND "C2"='Testing record 9993' AND
              "C3"=TRUNC(SYSDATE@!+9993))

SQL>
SQL> set autotrace off
SQL>
SQL> truncate table t1;

Table truncated.

SQL>
SQL> --
SQL> -- Data with cycling C1 and C2 values and unique C3 values
SQL> --
SQL> begin
  2        for i in 1..10101 loop
  3         insert into t1
  4         values (mod(i, 3), 'Testing record '||mod(i,3), trunc(sysdate+i), 'Filler', mod(i, 734), mod(i, 963));
  5        end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> --
SQL> -- 'Standard' statistics
SQL> --
SQL> exec dbms_stats.gather_schema_stats(ownname=>'BING');

PL/SQL procedure successfully completed.

SQL>
SQL> set autotrace on linesize 132
SQL>
SQL> select * From t1 where c1 =433;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 552572096

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    42 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |    42 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | INDX1 |     1 |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C1"=433)

SQL>
SQL> select * From t1 where c2 = 'Testing record 7748';

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 3325179317

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    42 |     5   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |    42 |     5   (0)| 00:00:01 |
|*  2 |   INDEX SKIP SCAN           | INDX1 |     1 |       |     4   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C2"='Testing record 7748')
       filter("C2"='Testing record 7748')

SQL>
SQL> select * from t1 where c3 = trunc(sysdate+9);

        C1 C2                   C3        C4                 C5         C6
---------- -------------------- --------- ---------- ---------- ----------
         0 Testing record 0     28-NOV-08 Filler              9          9


Execution Plan
----------------------------------------------------------
Plan hash value: 3325179317

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    42 |     5   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |    42 |     5   (0)| 00:00:01 |
|*  2 |   INDEX SKIP SCAN           | INDX1 |     1 |       |     4   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C3"=TRUNC(SYSDATE@!+9))
       filter("C3"=TRUNC(SYSDATE@!+9))

SQL>
SQL> select * From t1 where c1 = 9993 and c2 = 'Testing record 9993' and c3 = trunc(sysdate+9993);

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 552572096

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    42 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |    42 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | INDX1 |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C1"=9993 AND "C2"='Testing record 9993' AND
              "C3"=TRUNC(SYSDATE@!+9993))

SQL>
SQL> set autotrace off
SQL>
SQL> --
SQL> -- Statistics with auto-sized histograms on indexed columns
SQL> --
SQL> exec dbms_stats.gather_schema_stats(ownname=>'BING', method_opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO');

PL/SQL procedure successfully completed.

SQL>
SQL> set autotrace on linesize 132
SQL>
SQL> select * From t1 where c1 =433;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 552572096

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    42 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |    42 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | INDX1 |     1 |       |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C1"=433)

SQL>
SQL> select * From t1 where c2 = 'Testing record 7748';

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 3325179317

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    42 |     5   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |    42 |     5   (0)| 00:00:01 |
|*  2 |   INDEX SKIP SCAN           | INDX1 |     1 |       |     4   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C2"='Testing record 7748')
       filter("C2"='Testing record 7748')

SQL>
SQL> select * from t1 where c3 = trunc(sysdate+9);

        C1 C2                   C3        C4                 C5         C6
---------- -------------------- --------- ---------- ---------- ----------
         0 Testing record 0     28-NOV-08 Filler              9          9


Execution Plan
----------------------------------------------------------
Plan hash value: 3325179317

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    42 |     5   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |    42 |     5   (0)| 00:00:01 |
|*  2 |   INDEX SKIP SCAN           | INDX1 |     1 |       |     4   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C3"=TRUNC(SYSDATE@!+9))
       filter("C3"=TRUNC(SYSDATE@!+9))

SQL>
SQL> select * From t1 where c1 = 9993 and c2 = 'Testing record 9993' and c3 = trunc(sysdate+9993);

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 552572096

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    42 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |    42 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | INDX1 |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C1"=9993 AND "C2"='Testing record 9993' AND
              "C3"=TRUNC(SYSDATE@!+9993))

SQL>
SQL> set autotrace off
SQL>

Note that as the selectivity of the leading columns (C1 and C2) decreases the plans change from TABLE ACCESS FULL to INDEX SKIP SCAN when column C1 is not included in the where clause. Note also that the queries did NOT change and that histograms made no difference in the plans.

This changes for 11.2.0.2:

SQL>
SQL> 
SQL> --
SQL> -- I  creates a index indx1 on (C1,C2,C3). I issue three select
SQL> -- statements like :
SQL> --
SQL> -- 1.select * from T1 where C1=
SQL> -- 2.select * from T1 where C2=
SQL> -- 3.select * from T1 where C3=
SQL> -- 4.select * from T1 where C1=  and  C2=  and C3 =
SQL> --
SQL> --
SQL> --
SQL> --
SQL> -- Regards,
SQL> -- Sanjoy
SQL> --
SQL> 
SQL> --
SQL> -- Create the table in question
SQL> --
SQL> create table t1(
  2      c1 number,
  3      c2 varchar2(20),
  4      c3 date,
  5      c4 varchar2(10),
  6      c5 number,
  7      c6 number
  8  );

Table created.

SQL> 
SQL> 
SQL> --
SQL> -- Create the index specified
SQL> --
SQL> create index indx1
  2  on t1(c1,c2,c3);

Index created.

SQL> 
SQL> 
SQL> --
SQL> -- Load test data
SQL> --
SQL> 
SQL> --
SQL> -- Data with unique C1, C2 and C3 values
SQL> --
SQL> begin
  2      for i in 1..10101 loop
  3       insert into t1
  4       values (i, 'Testing record '||i, trunc(sysdate+i), 'Filler', mod(i, 734), mod(i, 963));
  5      end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> 
SQL> 
SQL> commit;

Commit complete.

SQL> 
SQL> 
SQL> --
SQL> -- 'Standard' statistics
SQL> --
SQL> exec dbms_stats.gather_schema_stats(ownname=>'BING');

PL/SQL procedure successfully completed.

SQL> 
SQL> 
SQL> 
SQL> set autotrace on linesize 132
SQL> 
SQL> select * From t1 where c1 =433;

        C1 C2                   C3        C4                 C5         C6
---------- -------------------- --------- ---------- ---------- ----------
       433 Testing record 433   07-MAY-13 Filler            433        433


Execution Plan
----------------------------------------------------------
Plan hash value: 552572096

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    47 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |    47 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | INDX1 |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C1"=433)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        739  bytes sent via SQL*Net to client
        420  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> 
SQL> select * From t1 where c2 = 'Testing record 7748';

        C1 C2                   C3        C4                 C5         C6
---------- -------------------- --------- ---------- ---------- ----------
      7748 Testing record 7748  17-MAY-33 Filler            408         44


Execution Plan
----------------------------------------------------------
Plan hash value: 3325179317

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    47 |    11   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |    47 |    11   (0)| 00:00:01 |
|*  2 |   INDEX SKIP SCAN           | INDX1 |     1 |       |    11   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C2"='Testing record 7748')
       filter("C2"='Testing record 7748')


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         59  consistent gets
          0  physical reads
          0  redo size
        739  bytes sent via SQL*Net to client
        420  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> 
SQL> select * from t1 where c3 = trunc(sysdate+9);

        C1 C2                   C3        C4                 C5         C6
---------- -------------------- --------- ---------- ---------- ----------
         9 Testing record 9     09-MAR-12 Filler              9          9


Execution Plan
----------------------------------------------------------
Plan hash value: 3325179317

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    47 |    11   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |    47 |    11   (0)| 00:00:01 |
|*  2 |   INDEX SKIP SCAN           | INDX1 |     1 |       |    11   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C3"=TRUNC(SYSDATE@!+9))
       filter("C3"=TRUNC(SYSDATE@!+9))


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         59  consistent gets
          0  physical reads
          0  redo size
        734  bytes sent via SQL*Net to client
        420  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> 
SQL> select * From t1 where c1 = 9993 and c2 = 'Testing record 9993' and c3 = trunc(sysdate+9993);

        C1 C2                   C3        C4                 C5         C6
---------- -------------------- --------- ---------- ---------- ----------
      9993 Testing record 9993  10-JUL-39 Filler            451        363


Execution Plan
----------------------------------------------------------
Plan hash value: 552572096

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    47 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |    47 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | INDX1 |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C1"=9993 AND "C2"='Testing record 9993' AND
              "C3"=TRUNC(SYSDATE@!+9993))


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        740  bytes sent via SQL*Net to client
        420  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> 
SQL> set autotrace off
SQL> 
SQL> --
SQL> -- Statistics with auto-sized histograms on indexed columns
SQL> --
SQL> exec dbms_stats.gather_schema_stats(ownname=>'BING', method_opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO');

PL/SQL procedure successfully completed.

SQL> 
SQL> set autotrace on linesize 132
SQL> 
SQL> select * From t1 where c1 =433;

        C1 C2                   C3        C4                 C5         C6
---------- -------------------- --------- ---------- ---------- ----------
       433 Testing record 433   07-MAY-13 Filler            433        433


Execution Plan
----------------------------------------------------------
Plan hash value: 552572096

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    47 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |    47 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | INDX1 |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C1"=433)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        739  bytes sent via SQL*Net to client
        420  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> 
SQL> select * From t1 where c2 = 'Testing record 7748';

        C1 C2                   C3        C4                 C5         C6
---------- -------------------- --------- ---------- ---------- ----------
      7748 Testing record 7748  17-MAY-33 Filler            408         44


Execution Plan
----------------------------------------------------------
Plan hash value: 3325179317

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    47 |    11   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |    47 |    11   (0)| 00:00:01 |
|*  2 |   INDEX SKIP SCAN           | INDX1 |     1 |       |    11   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C2"='Testing record 7748')
       filter("C2"='Testing record 7748')


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         59  consistent gets
          0  physical reads
          0  redo size
        739  bytes sent via SQL*Net to client
        420  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> 
SQL> select * from t1 where c3 = trunc(sysdate+9);

        C1 C2                   C3        C4                 C5         C6
---------- -------------------- --------- ---------- ---------- ----------
         9 Testing record 9     09-MAR-12 Filler              9          9


Execution Plan
----------------------------------------------------------
Plan hash value: 3325179317

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    47 |    11   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |    47 |    11   (0)| 00:00:01 |
|*  2 |   INDEX SKIP SCAN           | INDX1 |     1 |       |    11   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C3"=TRUNC(SYSDATE@!+9))
       filter("C3"=TRUNC(SYSDATE@!+9))


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         59  consistent gets
          0  physical reads
          0  redo size
        734  bytes sent via SQL*Net to client
        420  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> 
SQL> select * From t1 where c1 = 9993 and c2 = 'Testing record 9993' and c3 = trunc(sysdate+9993);

        C1 C2                   C3        C4                 C5         C6
---------- -------------------- --------- ---------- ---------- ----------
      9993 Testing record 9993  10-JUL-39 Filler            451        363


Execution Plan
----------------------------------------------------------
Plan hash value: 552572096

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    47 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |    47 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | INDX1 |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C1"=9993 AND "C2"='Testing record 9993' AND
              "C3"=TRUNC(SYSDATE@!+9993))


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        740  bytes sent via SQL*Net to client
        420  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> 
SQL> set autotrace off
SQL> 
SQL> truncate table t1;

Table truncated.

SQL> 
SQL> --
SQL> -- Data with unique C2 and C3 values, and a reasonably selective C1
SQL> --
SQL> begin
  2      for i in 1..10101 loop
  3       insert into t1
  4       values (mod(i, 43), 'Testing record '||i, trunc(sysdate+i), 'Filler', mod(i, 734), mod(i, 963));
  5      end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> 
SQL> commit;

Commit complete.

SQL> 
SQL> --
SQL> -- 'Standard' statistics
SQL> --
SQL> exec dbms_stats.gather_schema_stats(ownname=>'BING');

PL/SQL procedure successfully completed.

SQL> 
SQL> set autotrace on linesize 132
SQL> 
SQL> select * From t1 where c1 =433;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 552572096

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    46 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |    46 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | INDX1 |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C1"=433)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        562  bytes sent via SQL*Net to client
        409  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

SQL> 
SQL> select * From t1 where c2 = 'Testing record 7748';

        C1 C2                   C3        C4                 C5         C6
---------- -------------------- --------- ---------- ---------- ----------
         8 Testing record 7748  17-MAY-33 Filler            408         44


Execution Plan
----------------------------------------------------------
Plan hash value: 3325179317

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    46 |     9   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |    46 |     9   (0)| 00:00:01 |
|*  2 |   INDEX SKIP SCAN           | INDX1 |     1 |       |     9   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C2"='Testing record 7748')
       filter("C2"='Testing record 7748')


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         54  consistent gets
          0  physical reads
          0  redo size
        738  bytes sent via SQL*Net to client
        420  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> 
SQL> select * from t1 where c3 = trunc(sysdate+9);

        C1 C2                   C3        C4                 C5         C6
---------- -------------------- --------- ---------- ---------- ----------
         9 Testing record 9     09-MAR-12 Filler              9          9


Execution Plan
----------------------------------------------------------
Plan hash value: 3325179317

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    46 |    16   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |    46 |    16   (0)| 00:00:01 |
|*  2 |   INDEX SKIP SCAN           | INDX1 |     1 |       |    16   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C3"=TRUNC(SYSDATE@!+9))
       filter("C3"=TRUNC(SYSDATE@!+9))


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         84  consistent gets
          0  physical reads
          0  redo size
        734  bytes sent via SQL*Net to client
        420  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> 
SQL> select * From t1 where c1 = 9993 and c2 = 'Testing record 9993' and c3 = trunc(sysdate+9993);

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 552572096

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    46 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |    46 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | INDX1 |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C1"=9993 AND "C2"='Testing record 9993' AND
              "C3"=TRUNC(SYSDATE@!+9993))


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        562  bytes sent via SQL*Net to client
        409  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

SQL> 
SQL> set autotrace off
SQL> 
SQL> --
SQL> -- Statistics with auto-sized histograms on indexed columns
SQL> --
SQL> exec dbms_stats.gather_schema_stats(ownname=>'BING', method_opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO');

PL/SQL procedure successfully completed.

SQL> 
SQL> set autotrace on linesize 132
SQL> 
SQL> select * From t1 where c1 =433;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 552572096

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    46 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |    46 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | INDX1 |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C1"=433)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        562  bytes sent via SQL*Net to client
        409  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

SQL> 
SQL> select * From t1 where c2 = 'Testing record 7748';

        C1 C2                   C3        C4                 C5         C6
---------- -------------------- --------- ---------- ---------- ----------
         8 Testing record 7748  17-MAY-33 Filler            408         44


Execution Plan
----------------------------------------------------------
Plan hash value: 3325179317

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    46 |     9   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |    46 |     9   (0)| 00:00:01 |
|*  2 |   INDEX SKIP SCAN           | INDX1 |     1 |       |     9   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C2"='Testing record 7748')
       filter("C2"='Testing record 7748')


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         54  consistent gets
          0  physical reads
          0  redo size
        738  bytes sent via SQL*Net to client
        420  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> 
SQL> select * from t1 where c3 = trunc(sysdate+9);

        C1 C2                   C3        C4                 C5         C6
---------- -------------------- --------- ---------- ---------- ----------
         9 Testing record 9     09-MAR-12 Filler              9          9


Execution Plan
----------------------------------------------------------
Plan hash value: 3325179317

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    46 |    16   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |    46 |    16   (0)| 00:00:01 |
|*  2 |   INDEX SKIP SCAN           | INDX1 |     1 |       |    16   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C3"=TRUNC(SYSDATE@!+9))
       filter("C3"=TRUNC(SYSDATE@!+9))


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         84  consistent gets
          0  physical reads
          0  redo size
        734  bytes sent via SQL*Net to client
        420  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> 
SQL> select * From t1 where c1 = 9993 and c2 = 'Testing record 9993' and c3 = trunc(sysdate+9993);

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 552572096

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    46 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |    46 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | INDX1 |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C1"=9993 AND "C2"='Testing record 9993' AND
              "C3"=TRUNC(SYSDATE@!+9993))


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        562  bytes sent via SQL*Net to client
        409  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

SQL> 
SQL> set autotrace off
SQL> 
SQL> truncate table t1;

Table truncated.

SQL> 
SQL> --
SQL> -- Data with unique C2 and C3 values, with a cycling C1 set of values
SQL> --
SQL> begin
  2      for i in 1..10101 loop
  3       insert into t1
  4       values (mod(i, 3), 'Testing record '||i, trunc(sysdate+i), 'Filler', mod(i, 734), mod(i, 963));
  5      end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> 
SQL> commit;

Commit complete.

SQL> 
SQL> --
SQL> -- 'Standard' statistics
SQL> --
SQL> exec dbms_stats.gather_schema_stats(ownname=>'BING');

PL/SQL procedure successfully completed.

SQL> 
SQL> set autotrace on linesize 132
SQL> 
SQL> select * From t1 where c1 =433;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 552572096

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    46 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |    46 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | INDX1 |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C1"=433)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        562  bytes sent via SQL*Net to client
        409  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

SQL> 
SQL> select * From t1 where c2 = 'Testing record 7748';

        C1 C2                   C3        C4                 C5         C6
---------- -------------------- --------- ---------- ---------- ----------
         2 Testing record 7748  17-MAY-33 Filler            408         44


Execution Plan
----------------------------------------------------------
Plan hash value: 3325179317

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    46 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |    46 |     1   (0)| 00:00:01 |
|*  2 |   INDEX SKIP SCAN           | INDX1 |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C2"='Testing record 7748')
       filter("C2"='Testing record 7748')


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         11  consistent gets
          0  physical reads
          0  redo size
        738  bytes sent via SQL*Net to client
        420  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> 
SQL> select * from t1 where c3 = trunc(sysdate+9);

        C1 C2                   C3        C4                 C5         C6
---------- -------------------- --------- ---------- ---------- ----------
         0 Testing record 9     09-MAR-12 Filler              9          9


Execution Plan
----------------------------------------------------------
Plan hash value: 3325179317

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    46 |    20   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |    46 |    20   (0)| 00:00:01 |
|*  2 |   INDEX SKIP SCAN           | INDX1 |     1 |       |    19   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C3"=TRUNC(SYSDATE@!+9))
       filter("C3"=TRUNC(SYSDATE@!+9))


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        100  consistent gets
          0  physical reads
          0  redo size
        733  bytes sent via SQL*Net to client
        420  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> 
SQL> select * From t1 where c1 = 9993 and c2 = 'Testing record 9993' and c3 = trunc(sysdate+9993);

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 552572096

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    46 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |    46 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | INDX1 |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C1"=9993 AND "C2"='Testing record 9993' AND
              "C3"=TRUNC(SYSDATE@!+9993))


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        562  bytes sent via SQL*Net to client
        409  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

SQL> 
SQL> set autotrace off
SQL> 
SQL> --
SQL> -- Statistics with auto-sized histograms on indexed columns
SQL> --
SQL> exec dbms_stats.gather_schema_stats(ownname=>'BING', method_opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO');

PL/SQL procedure successfully completed.

SQL> 
SQL> set autotrace on linesize 132
SQL> 
SQL> select * From t1 where c1 =433;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 552572096

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    46 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |    46 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | INDX1 |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C1"=433)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        562  bytes sent via SQL*Net to client
        409  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

SQL> 
SQL> select * From t1 where c2 = 'Testing record 7748';

        C1 C2                   C3        C4                 C5         C6
---------- -------------------- --------- ---------- ---------- ----------
         2 Testing record 7748  17-MAY-33 Filler            408         44


Execution Plan
----------------------------------------------------------
Plan hash value: 3325179317

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    46 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |    46 |     1   (0)| 00:00:01 |
|*  2 |   INDEX SKIP SCAN           | INDX1 |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C2"='Testing record 7748')
       filter("C2"='Testing record 7748')


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         11  consistent gets
          0  physical reads
          0  redo size
        738  bytes sent via SQL*Net to client
        420  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> 
SQL> select * from t1 where c3 = trunc(sysdate+9);

        C1 C2                   C3        C4                 C5         C6
---------- -------------------- --------- ---------- ---------- ----------
         0 Testing record 9     09-MAR-12 Filler              9          9


Execution Plan
----------------------------------------------------------
Plan hash value: 3325179317

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    46 |    20   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |    46 |    20   (0)| 00:00:01 |
|*  2 |   INDEX SKIP SCAN           | INDX1 |     1 |       |    19   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C3"=TRUNC(SYSDATE@!+9))
       filter("C3"=TRUNC(SYSDATE@!+9))


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        100  consistent gets
          0  physical reads
          0  redo size
        733  bytes sent via SQL*Net to client
        420  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> 
SQL> select * From t1 where c1 = 9993 and c2 = 'Testing record 9993' and c3 = trunc(sysdate+9993);

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 552572096

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    46 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |    46 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | INDX1 |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C1"=9993 AND "C2"='Testing record 9993' AND
              "C3"=TRUNC(SYSDATE@!+9993))


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        562  bytes sent via SQL*Net to client
        409  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

SQL> 
SQL> set autotrace off
SQL> 
SQL> truncate table t1;

Table truncated.

SQL> 
SQL> --
SQL> -- Data with cycling C1 and C2 values and unique C3 values
SQL> --
SQL> begin
  2      for i in 1..10101 loop
  3       insert into t1
  4       values (mod(i, 3), 'Testing record '||mod(i,3), trunc(sysdate+i), 'Filler', mod(i, 734), mod(i, 963));
  5      end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> 
SQL> commit;

Commit complete.

SQL> 
SQL> --
SQL> -- 'Standard' statistics
SQL> --
SQL> exec dbms_stats.gather_schema_stats(ownname=>'BING');

PL/SQL procedure successfully completed.

SQL> 
SQL> set autotrace on linesize 132
SQL> 
SQL> select * From t1 where c1 =433;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 552572096

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    43 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |    43 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | INDX1 |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C1"=433)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        562  bytes sent via SQL*Net to client
        409  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

SQL> 
SQL> select * From t1 where c2 = 'Testing record 7748';

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 3325179317

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    43 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |    43 |     1   (0)| 00:00:01 |
|*  2 |   INDEX SKIP SCAN           | INDX1 |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C2"='Testing record 7748')
       filter("C2"='Testing record 7748')


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          5  consistent gets
          0  physical reads
          0  redo size
        562  bytes sent via SQL*Net to client
        409  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

SQL> 
SQL> select * from t1 where c3 = trunc(sysdate+9);

        C1 C2                   C3        C4                 C5         C6
---------- -------------------- --------- ---------- ---------- ----------
         0 Testing record 0     09-MAR-12 Filler              9          9


Execution Plan
----------------------------------------------------------
Plan hash value: 3325179317

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    43 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |    43 |     1   (0)| 00:00:01 |
|*  2 |   INDEX SKIP SCAN           | INDX1 |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C3"=TRUNC(SYSDATE@!+9))
       filter("C3"=TRUNC(SYSDATE@!+9))


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size
        733  bytes sent via SQL*Net to client
        420  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> 
SQL> select * From t1 where c1 = 9993 and c2 = 'Testing record 9993' and c3 = trunc(sysdate+9993);

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 552572096

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    43 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |    43 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | INDX1 |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C1"=9993 AND "C2"='Testing record 9993' AND
              "C3"=TRUNC(SYSDATE@!+9993))


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        562  bytes sent via SQL*Net to client
        409  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

SQL> 
SQL> set autotrace off
SQL> 
SQL> --
SQL> -- Statistics with auto-sized histograms on indexed columns
SQL> --
SQL> exec dbms_stats.gather_schema_stats(ownname=>'BING', method_opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO');

PL/SQL procedure successfully completed.

SQL> 
SQL> set autotrace on linesize 132
SQL> 
SQL> select * From t1 where c1 =433;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 552572096

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    43 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |    43 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | INDX1 |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C1"=433)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        562  bytes sent via SQL*Net to client
        409  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

SQL> 
SQL> select * From t1 where c2 = 'Testing record 7748';

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 3325179317

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    43 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |    43 |     1   (0)| 00:00:01 |
|*  2 |   INDEX SKIP SCAN           | INDX1 |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C2"='Testing record 7748')
       filter("C2"='Testing record 7748')


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          5  consistent gets
          0  physical reads
          0  redo size
        562  bytes sent via SQL*Net to client
        409  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

SQL> 
SQL> select * from t1 where c3 = trunc(sysdate+9);

        C1 C2                   C3        C4                 C5         C6
---------- -------------------- --------- ---------- ---------- ----------
         0 Testing record 0     09-MAR-12 Filler              9          9


Execution Plan
----------------------------------------------------------
Plan hash value: 3325179317

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    43 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |    43 |     1   (0)| 00:00:01 |
|*  2 |   INDEX SKIP SCAN           | INDX1 |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C3"=TRUNC(SYSDATE@!+9))
       filter("C3"=TRUNC(SYSDATE@!+9))


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size
        733  bytes sent via SQL*Net to client
        420  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> 
SQL> select * From t1 where c1 = 9993 and c2 = 'Testing record 9993' and c3 = trunc(sysdate+9993);

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 552572096

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    43 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |    43 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | INDX1 |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("C1"=9993 AND "C2"='Testing record 9993' AND
              "C3"=TRUNC(SYSDATE@!+9993))


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        562  bytes sent via SQL*Net to client
        409  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

SQL> 
SQL> set autotrace off
SQL> 
SQL>

Notice that in the later release INDEX SKIP SCAN is chosen by the optimizer when any indexed column other than the leading column is used in the where clause.

So how does Oracle treat a ‘select * from t1 where c3 = trunc(sysdate+9)’ query when the index is built on columns (c1,c2,c3)? That depends entirely upon the data and how skewed (or not) it may be. [It also depends upon the Oracle version in use, as releases after 8.1.7.4 implemented changes in the available query plans and how indexes could be used. Again note the differences between how 11.1.0.6 and 11.2.0.2 behave.] Because of deletes/inserts a plan can change even though the query has not, so there is no ‘definitive’ answer to the question as written. The conditions are simply too vague to produce repeatable results.

And that’s a definite maybe.

April 17, 2008

To "b" or not to "b"

Filed under: Indexes,Performance — dfitzjarrell @ 13:12

With the plethora of database-centric applications available today, and with the performance problems they can generate it can be a worthy effort to determine which vendor-created indexes are and are not being used. (This is especially helpful if you’re working closely with the application vendor to improve their product.) Of course one way to do this is to set event 10046 at level 8 or 12 and let the trace files fly so they can be analyzed later for which indexes are being used by the application queries. And that could be a long and tedious process. One would think there is a better way to accomplish this.

There is.

Oh, I suppose you’d like to know this better way … it’s really rather simple:

Let Oracle do the work for you.

So let’s see how we tell Oracle to do this task for us so our job is much easier.

Oracle has provided a mechanism (since at least Oracle 8.1.6) to monitor an index for usage using

alter index <index_name> monitoring usage;

The results of that monitoring are found in the V$OBJECT_USAGE view,in a column, strangely enough, named USED. This isn’t a long, boring thesis on how, when, where, who and why the index in question was used, only that it either is or is not used. The ‘window’ spans the time period starting with the execution of the above-listed command and ends when the following is issued:

alter index <index_name> nomonitoring usage;

The data remains in the V$OBJECT_USAGE view until another monitoring ‘window’ is started.

So, let’s see an example of how this works. We’ll use the EMP table from the SCOTT/TIGER demonstration schema:

SQL>
SQL> --
SQL> -- Create an index on the EMPNO column
SQL> -- of the EMP table
SQL> --
SQL> create index emp_eno_idx
  2  on emp(empno);

Index created.

SQL>
SQL> --
SQL> -- Let's monitor the index to see if
SQL> -- it's being used
SQL> --
SQL> alter index emp_eno_idx monitoring usage;

Index altered.

SQL>
SQL> --
SQL> -- Now, let's run some queries
SQL> --
SQL> -- First, let's get everything from the
SQL> -- EMP table
SQL> --
SQL> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

14 rows selected.

SQL>
SQL> --
SQL> -- Obviously the index hasn't yet been
SQL> -- used
SQL> --
SQL> select index_name, table_name, used from v$object_usage;

INDEX_NAME                     TABLE_NAME                     USE
------------------------------ ------------------------------ ---
EMP_ENO_IDX                    EMP                            NO

1 row selected.

SQL>
SQL> --
SQL> -- So let's run a qualified query and
SQL> -- see if things change
SQL> --
SQL> -- Since the DEPTNO column isn't indexed
SQL> -- the monitored index still shouldn't be
SQL> -- used
SQL> --
SQL> select * from emp where deptno = 30;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30

6 rows selected.

SQL>
SQL> --
SQL> -- And we see it isn't
SQL> --
SQL> select index_name, table_name, used from v$object_usage;

INDEX_NAME                     TABLE_NAME                     USE
------------------------------ ------------------------------ ---
EMP_ENO_IDX                    EMP                            NO

1 row selected.

SQL>
SQL> --
SQL> -- Yet another qualified query, this time
SQL> -- using the indexed column
SQL> --
SQL> select * from emp where empno  --
SQL> -- We see the index is now being used, or at
SQL> -- least it was for that last query
SQL> --
SQL> select index_name, table_name, used from v$object_usage;

INDEX_NAME                     TABLE_NAME                     USE
------------------------------ ------------------------------ ---
EMP_ENO_IDX                    EMP                            YES

1 row selected.

SQL>
SQL> --
SQL> -- We'll try another query using that column
SQL> --
SQL> -- Let's set autotrace on to see if the index
SQL> -- is being used in this example
SQL> --
SQL> set autotrace on
SQL> select * From emp where empno is null;

no rows selected


Execution Plan
----------------------------------------------------------
Plan hash value: 3712041407

---------------------------------------------------------------------------
 Id   Operation           Name  Rows   Bytes  Cost (%CPU) Time 
---------------------------------------------------------------------------
   0  SELECT STATEMENT              1     87      0   (0)      
*  1   FILTER                                                  
   2    TABLE ACCESS FULL EMP      14   1218      3   (0) 00:00:01
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(NULL IS NOT NULL)

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
          4  recursive calls
          0  db block gets
          8  consistent gets
          0  physical reads
          0  redo size
        353  bytes sent via SQL*Net to client
        239  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

SQL> set autotrace off
SQL>
SQL> --
SQL> -- Since the index has been marked as used
SQL> -- it remains in the USED state for this
SQL> -- monitoring window even though the last
SQL> -- query didn't use the index at all
SQL> --
SQL> select index_name, table_name, used from v$object_usage;

INDEX_NAME                     TABLE_NAME                     USE
------------------------------ ------------------------------ ---
EMP_ENO_IDX                    EMP                            YES

1 row selected.

SQL>
SQL> --
SQL> -- Turn off the usage monitoring
SQL> --
SQL> alter index emp_eno_idx nomonitoring usage;

Index altered.

SQL>
SQL> --
SQL> -- And the last generated data remains
SQL> --
SQL> select index_name, table_name, used from v$object_usage;

INDEX_NAME                     TABLE_NAME                     USE
------------------------------ ------------------------------ ---
EMP_ENO_IDX                    EMP                            YES

1 row selected.

SQL> 

Letting Oracle monitor index usage is much easier than traipsing through piles of event 10046 trace files looking for index scans. I’m happy they’ve provided such a tool. But, you may run across an index which is used but won’t be marked as such in V$OBJECT_USAGE. How can this be? Oracle can use the statistics from the index in determining the best query plan, and when those statistics are gone (as when the index has been dropped) performance can suffer; the optimizer generates a decision tree when each query is hard parsed, and missing index statistics may direct the optimizer down a path it might not have taken when the statistics existed. Oracle, in one respect,is correct in that the index in question hasn’t been read however it did use the statistics to perform path elimination. So, before heading straight for the ‘drop index’ command it would be prudent to verify the index in question really isn’t being used in any way — this is why we have test systems, correct? Dropping the index on a test database then verifying that no performance degradation occurs is, in my mind, a good idea. If, after the tests indicate an index may truly be unused, performance problems arise because that index is missing it can be recreated to restore the application to its original lustre.

Some suggest that simply setting an index to UNUSABLE would provide the same conditions as dropping it, but disabling an index in that fashion doesn’t remove the statistics generated on that index and if a query or process is using those statistics but is not actually accessing the index the same conditions don’t exist and one could be led into a false sense of security that the index in question is truly unused. Yes, actual access to the index is not allowed but since the index wasn’t being read to begin with (only the statistics were used by the CBO for cost calculations) I can’t accept that the same run-time conditions exist. Eventually the statistics will be outdated and no longer will be used but it could take a week, a month or longer for this to occur (depending upon system activity). For those DBAs in a hurry (and, face it, sometimes management IS in a hurry for results) setting an index to UNUSABLE may not be a valid course of action to discover whether it’s actually used or not.

There is a way in Oracle 11.2 and later releases: set the index to INVISIBLE. This will prevent the optimizer from using the index and the associated statistics without dropping it:


SQL> create index emp_empno_idx
  2  on emp(empno)
  3  invisible;

Index created.

SQL> 
SQL> set autotrace on
SQL> 
SQL> select
  2  	     *
  3  from
  4  	     emp
  5  where
  6  	     empno < 7400;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                               
---------- ---------- --------- ---------- --------- ---------- ---------- ----------                                               
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20                                               


Execution Plan
----------------------------------------------------------                                                                          
Plan hash value: 3956160932                                                                                                         
                                                                                                                                    
--------------------------------------------------------------------------                                                          
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |                                                          
--------------------------------------------------------------------------                                                          
|   0 | SELECT STATEMENT  |      |     1 |    87 |     3   (0)| 00:00:01 |                                                          
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    87 |     3   (0)| 00:00:01 |                                                          
--------------------------------------------------------------------------                                                          
                                                                                                                                    
Predicate Information (identified by operation id):                                                                                 
---------------------------------------------------                                                                                 
                                                                                                                                    
   1 - filter("EMPNO"<7400)                                                                                                         
                                                                                                                                    
Note                                                                                                                                
-----                                                                                                                               
   - dynamic sampling used for this statement (level=2)                                                                             


Statistics
----------------------------------------------------------                                                                          
          5  recursive calls                                                                                                        
          0  db block gets                                                                                                          
         16  consistent gets                                                                                                        
          0  physical reads                                                                                                         
          0  redo size                                                                                                              
       1021  bytes sent via SQL*Net to client                                                                                       
        520  bytes received via SQL*Net from client                                                                                 
          2  SQL*Net roundtrips to/from client                                                                                      
          0  sorts (memory)                                                                                                         
          0  sorts (disk)                                                                                                           
          1  rows processed                                                                                                         

SQL> 
SQL> alter session set optimizer_use_invisible_indexes=true;

Session altered.

SQL> set autotrace off
SQL> select LOADED_VERSIONS,executions,INVALIDATIONS,ROWS_PROCESSED from v$sql where sql_id='axr7gct6p1g3y';

LOADED_VERSIONS EXECUTIONS INVALIDATIONS ROWS_PROCESSED                                                                             
--------------- ---------- ------------- --------------                                                                             
              1          1             2              1                                                                             
              1          1             1              1                                                                             

SQL> set autotrace on
SQL> select
  2  	     *
  3  from
  4  	     emp
  5  where
  6  	     empno < 7400;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                               
---------- ---------- --------- ---------- --------- ---------- ---------- ----------                                               
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20                                               


Execution Plan
----------------------------------------------------------                                                                          
Plan hash value: 576302360                                                                                                          
                                                                                                                                    
---------------------------------------------------------------------------------------------                                       
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |                                       
---------------------------------------------------------------------------------------------                                       
|   0 | SELECT STATEMENT            |               |     1 |    87 |     2   (0)| 00:00:01 |                                       
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP           |     1 |    87 |     2   (0)| 00:00:01 |                                       
|*  2 |   INDEX RANGE SCAN          | EMP_EMPNO_IDX |     1 |       |     1   (0)| 00:00:01 |                                       
---------------------------------------------------------------------------------------------                                       
                                                                                                                                    
Predicate Information (identified by operation id):                                                                                 
---------------------------------------------------                                                                                 
                                                                                                                                    
   2 - access("EMPNO"<7400)                                                                                                         
                                                                                                                                    
Note                                                                                                                                
-----                                                                                                                               
   - dynamic sampling used for this statement (level=2)                                                                             


Statistics
----------------------------------------------------------                                                                          
          9  recursive calls                                                                                                        
          0  db block gets                                                                                                          
         12  consistent gets                                                                                                        
          0  physical reads                                                                                                         
          0  redo size                                                                                                              
       1025  bytes sent via SQL*Net to client                                                                                       
        520  bytes received via SQL*Net from client                                                                                 
          2  SQL*Net roundtrips to/from client                                                                                      
          0  sorts (memory)                                                                                                         
          0  sorts (disk)                                                                                                           
          1  rows processed                                                                                                         

SQL> 
SQL> alter session set optimizer_use_invisible_indexes=false;

Session altered.

SQL> set autotrace off
SQL> select LOADED_VERSIONS,EXECUTIONS,INVALIDATIONS,ROWS_PROCESSED from v$sql where sql_id='axr7gct6p1g3y';

LOADED_VERSIONS EXECUTIONS INVALIDATIONS ROWS_PROCESSED                                                                             
--------------- ---------- ------------- --------------                                                                             
              1          1             2              1                                                                             
              1          1             1              1                                                                             

SQL> set autotrace on 
SQL> select
  2  	     *
  3  from
  4  	     emp
  5  where
  6  	     empno < 7400;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                               
---------- ---------- --------- ---------- --------- ---------- ---------- ----------                                               
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20                                               


Execution Plan
----------------------------------------------------------                                                                          
Plan hash value: 3956160932                                                                                                         
                                                                                                                                    
--------------------------------------------------------------------------                                                          
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |                                                          
--------------------------------------------------------------------------                                                          
|   0 | SELECT STATEMENT  |      |     1 |    87 |     3   (0)| 00:00:01 |                                                          
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    87 |     3   (0)| 00:00:01 |                                                          
--------------------------------------------------------------------------                                                          
                                                                                                                                    
Predicate Information (identified by operation id):                                                                                 
---------------------------------------------------                                                                                 
                                                                                                                                    
   1 - filter("EMPNO"<7400)                                                                                                         
                                                                                                                                    
Note                                                                                                                                
-----                                                                                                                               
   - dynamic sampling used for this statement (level=2)                                                                             


Statistics
----------------------------------------------------------                                                                          
          0  recursive calls                                                                                                        
          0  db block gets                                                                                                          
          8  consistent gets                                                                                                        
          0  physical reads                                                                                                         
          0  redo size                                                                                                              
       1021  bytes sent via SQL*Net to client                                                                                       
        520  bytes received via SQL*Net from client                                                                                 
          2  SQL*Net roundtrips to/from client                                                                                      
          0  sorts (memory)                                                                                                         
          0  sorts (disk)                                                                                                           
          1  rows processed                                                                                                         

SQL> 
SQL> alter index emp_empno_idx visible;

Index altered.

SQL> set autotrace off
SQL> select LOADED_VERSIONS,EXECUTIONS,INVALIDATIONS,ROWS_PROCESSED from v$sql where sql_id='axr7gct6p1g3y';

LOADED_VERSIONS EXECUTIONS INVALIDATIONS ROWS_PROCESSED                                                                             
--------------- ---------- ------------- --------------                                                                             
              1          2             3              2                                                                             
              1          1             2              1                                                                             


SQL> set autotrace on
SQL> select
  2  	     *
  3  from
  4  	     emp
  5  where
  6  	     empno < 7400;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO                                               
---------- ---------- --------- ---------- --------- ---------- ---------- ----------                                               
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20                                               


Execution Plan
----------------------------------------------------------                                                                          
Plan hash value: 576302360                                                                                                          
                                                                                                                                    
---------------------------------------------------------------------------------------------                                       
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |                                       
---------------------------------------------------------------------------------------------                                       
|   0 | SELECT STATEMENT            |               |     1 |    87 |     2   (0)| 00:00:01 |                                       
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP           |     1 |    87 |     2   (0)| 00:00:01 |                                       
|*  2 |   INDEX RANGE SCAN          | EMP_EMPNO_IDX |     1 |       |     1   (0)| 00:00:01 |                                       
---------------------------------------------------------------------------------------------                                       
                                                                                                                                    
Predicate Information (identified by operation id):                                                                                 
---------------------------------------------------                                                                                 
                                                                                                                                    
   2 - access("EMPNO"<7400)                                                                                                         
                                                                                                                                    
Note                                                                                                                                
-----                                                                                                                               
   - dynamic sampling used for this statement (level=2)                                                                             


Statistics
----------------------------------------------------------                                                                          
         42  recursive calls                                                                                                        
          0  db block gets                                                                                                          
         44  consistent gets                                                                                                        
          0  physical reads                                                                                                         
          0  redo size                                                                                                              
       1025  bytes sent via SQL*Net to client                                                                                       
        520  bytes received via SQL*Net from client                                                                                 
          2  SQL*Net roundtrips to/from client                                                                                      
          5  sorts (memory)                                                                                                         
          0  sorts (disk)                                                                                                           
          1  rows processed                                                                                                         

SQL> 

Note the change in LOADED_VERSIONS and INVALIDATIONS when the index is made visible or invisible. There is also a setting, optimizer_use_invisible_indexes, which can be set at the session level. It’s FALSE by default; setting it to TRUE will show the same results as the example shown above without making the index visible to every user who has access the objects in the affected schema, thus making the effect local to the current session. By setting the index to INVISIBLE you can assess whether that index is truly used or not, regardless of what Oracle’s index monitoring tells you. You will then be better prepared to assess whether the index can be safely dropped.

Of course database administration cannot be ruled by rash acts, and relying upon a sole source of information (such as V$OBJECT_USAGE) can result in problems down the line. So, careful attention to detail is necessary, especially when managing the existence (or not) of an index [or table, or view, or …]. I like to follow a few simple rules:

1 — Test, test, test.
2 — Keep testing.
3 — Never do anything you can’t undo.

Keeping to that methodology usually ensures I’m not in trouble later. And, it keeps the end-users happier.

I like happy end-users.

April 9, 2008

A Tale Of Two Indexes

Filed under: Indexes,Performance — dfitzjarrell @ 13:11

A fairly common question recently appeared in an Oracle-related newsgroup; the question was titled ‘index didn’t work’ and was presented thus:

“a table TEST,includes two columns:c_1(number not null),c_2(varchar2(255));10 rows inserted;create independent index on each column,then i query:select c_1 from TEST,explain plan report:index full scan,that’s right.next,i query:select c_2 from TEST,explain plan report:table access full.why?”

Okay, that’s a bit difficult to read so let’s re-word it a bit:

A table, TEST, contains two columns, defined as follows:

c_1 number not null
c_2 varchar2(255)

10 rows are inserted into this table and single-column indexes are created. Querying the data with the following statement:

select c_1 from test;

produces an index full scan path. Changing the query to:

select c_2 from test;

produces a full table scan path. Both columns have an index; why isn’t the index for the second column used for the above query?

So why isn’t that index used? It all comes down to … [ominous musical interlude here] … NULLs.

You read correctly, NULLs.

In an Oracle database entirely NULL keys are not indexed in conventional index structures (b-tree indexes); I consider a function-based index a special case of this which can index NULL keys provided the function used ‘translates’ NULLs to a non-null value. Bitmap indexes, on the other hand, do index NULLs by default; unfortunately for the questioner the indexes created aren’t bitmap indexes. So, why are NULLs at fault here? Let’s look at the table definition again and see.

SQL> desc test
 Name                                      Null?    Type
 ----------------------------------------- -------- ---------------------------
 C_1                                       NOT NULL NUMBER
 C_2                                                VARCHAR2(255)

SQL>

Column c_1 is declared not null, thus there can never be a NULL value in that field, THUS an index keyed on that column is guaranteed to contain all values of that column. Oracle can, and will, use only an index created against not null columns to return query results if that index satisfies the select list as all values will be present; the associated table will never be accessed in such cases. Contrast that to column c_2, which is nullable. It doesn’t matter that the column doesn’t contain any NULL values when the index is created, the fact remains that the column CAN contain NULLs. This, then, prevents Oracle from using the index to retrieve the data from that column since it’s possible to miss any NULL values stored there. The only way to ensure Oracle returns all values from c_2 is to perform a full table scan.

Let’s look at an example of this in action:

SQL> create table test1(
  2        c_1 number not null,
  3        c_2 varchar2(255)
  4  );

Table created.

SQL>
SQL> begin
  2        for i in 1..10 loop
  3         insert into test1
  4         values (i, 'Test '¦¦i);
  5        end loop;
  6
  7        commit;
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL>
SQL> create index test1_c_1
  2  on test1(c_1);

Index created.

SQL>
SQL> create index test1_c_2
  2  on test1(c_2);

Index created.

SQL>
SQL> set autotrace on
SQL>
SQL> select c_1 from test1;

       C_1
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

10 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2941126162

------------------------------------------------------------------------------
 Id   Operation         Name       Rows   Bytes  Cost (%CPU) Time
------------------------------------------------------------------------------
   0  SELECT STATEMENT                10    130      1   (0) 00:00:01
   1   INDEX FULL SCAN  TEST1_C_1     10    130      1   (0) 00:00:01
------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
          5  recursive calls
          0  db block gets
         11  consistent gets
          0  physical reads
          0  redo size
        284  bytes sent via SQL*Net to client
        246  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         10  rows processed

SQL>
SQL> select c_2 from test1;

C_2
--------------------------------------------------------------------------------
Test 1
Test 2
Test 3
Test 4
Test 5
Test 6
Test 7
Test 8
Test 9
Test 10

10 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3852271815

---------------------------------------------------------------------------
 Id   Operation          Name   Rows   Bytes  Cost (%CPU) Time
---------------------------------------------------------------------------
   0  SELECT STATEMENT             10   1290      3   (0) 00:00:01
   1   TABLE ACCESS FULL TEST1     10   1290      3   (0) 00:00:01
---------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
          4  recursive calls
          0  db block gets
         17  consistent gets
          0  physical reads
          0  redo size
        344  bytes sent via SQL*Net to client
        246  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         10  rows processed

SQL>

Notice that even though no NULL values occur in c_2 the path is a full table scan; this is due, as explained earlier, to the column definition at table creation, allowing NULL values to be stored. Notice, too, that the query

select c_1 from test1;

accesses only the index on that column; at no time is the table ‘touched’ to return any data. There is no need, since all data values for that column are present in that index.

Let’s see if we can get an index full scan of the index for c_2; we’ll change the query and see what the optimizer does:

SQL> select c_2 from test1 where c_2 is not null;

C_2
------------------------------------------------------------------------------
Test 1
Test 10
Test 2
Test 3
Test 4
Test 5
Test 6
Test 7
Test 8
Test 9

10 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 950550661

------------------------------------------------------------------------------
 Id   Operation         Name       Rows   Bytes  Cost (%CPU) Time
------------------------------------------------------------------------------
   0  SELECT STATEMENT                10     80      1   (0) 00:00:01
*  1   INDEX FULL SCAN  TEST1_C_2     10     80      1   (0) 00:00:01
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("C_2" IS NOT NULL)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        343  bytes sent via SQL*Net to client
        246  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         10  rows processed

SQL>

Since Oracle need not return any NULLs stored in c_2, and since the index contains only non-NULL key values, Oracle can perform an index full scan to return the desired data. Of course in this case there are no NULLs stored in the table, making this possibly a bit confusing. We’ll now store a few NULL values in that column and run the queries again:

SQL> begin
  2     for i in 11..20 loop
  3             if mod(i,2) = 0 then
  4                     insert into test1 values (i, 'Test '¦¦i);
  5             else
  6                     insert into test1 values(i, null);
  7             end if;
  8     end loop;
  9  end;
 10  /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

SQL> select c_2 from test1;

C_2
--------------------------------------------------------------------------------

Test 12

Test 14

Test 16

Test 18

Test 20
Test 1

C_2
--------------------------------------------------------------------------------
Test 2
Test 3
Test 4
Test 5
Test 6
Test 7
Test 8
Test 9
Test 10

20 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 3852271815

---------------------------------------------------------------------------
 Id   Operation          Name   Rows   Bytes  Cost (%CPU) Time
---------------------------------------------------------------------------
   0  SELECT STATEMENT             10     80      3   (0) 00:00:01
   1   TABLE ACCESS FULL TEST1     10     80      3   (0) 00:00:01
---------------------------------------------------------------------------


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          9  consistent gets
          4  physical reads
          0  redo size
        474  bytes sent via SQL*Net to client
        253  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         20  rows processed

SQL> select c_2 from test1 where c_2 is not null;

C_2
--------------------------------------------------------------------------------
Test 1
Test 10
Test 12
Test 14
Test 16
Test 18
Test 2
Test 20
Test 3
Test 4
Test 5

C_2
--------------------------------------------------------------------------------
Test 6
Test 7
Test 8
Test 9

15 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 950550661

------------------------------------------------------------------------------
 Id   Operation         Name       Rows   Bytes  Cost (%CPU) Time
------------------------------------------------------------------------------
   0  SELECT STATEMENT                10     80      1   (0) 00:00:01
*  1   INDEX FULL SCAN  TEST1_C_2     10     80      1   (0) 00:00:01
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("C_2" IS NOT NULL)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        408  bytes sent via SQL*Net to client
        246  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         15  rows processed

SQL>

The original query

select c_2 from test1;

returns all values for the column, including the NULLs, requiring the table to be scanned. The second query, with the ‘is not null’ qualifier, returns 5 fewer rows but doesn’t touch the table at all, instead relying solely on the index to produce the desired result set.

Even

select count(*) from test1;

will use the index on the NOT NULL column, thus avoiding any table access:

SQL> select count(*) from test1;

  COUNT(*)
----------
        10


Execution Plan
----------------------------------------------------------
Plan hash value: 2402158148

----------------------------------------------------------------------
 Id   Operation         Name       Rows   Cost (%CPU) Time    
----------------------------------------------------------------------
   0  SELECT STATEMENT                 1      1   (0) 00:00:01
   1   SORT AGGREGATE                  1                      
   2    INDEX FULL SCAN TEST1_C_1     10      1   (0) 00:00:01
----------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
          4  recursive calls
          0  db block gets
         11  consistent gets
          0  physical reads
          0  redo size
        225  bytes sent via SQL*Net to client
        246  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>

Since, as explained before, the index on the NOT NULL column will list every value in that column it will also provide the count for every row in that table.

Let’s make the index on c_2 contain all of the values of c_2, NULL or not, by including a non-null component (in this case a 0):

SQL> drop index test1_c_2;

Index dropped.

SQL> 
SQL> create index test1_c_2
  2  on test1(c_2,0);

Index created.

SQL>

Now the plan accesses the index without the need for the “where c_2 is not null” condition:

SQL> select c_2 from test1;

C_2
-------------------------------------------------------------------------
Test 1
Test 10
Test 12
Test 14
Test 16
Test 18
Test 2
Test 20
Test 3
Test 4
Test 5
Test 6
Test 7
Test 8
Test 9






20 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2529630288

------------------------------------------------------------------------------
| Id  | Operation        | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |           |    20 |  2580 |     1   (0)| 00:00:01 |
|   1 |  INDEX FULL SCAN | TEST1_C_2 |    20 |  2580 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement


Statistics
----------------------------------------------------------
         48  recursive calls
          0  db block gets
         20  consistent gets
          0  physical reads
          0  redo size
        766  bytes sent via SQL*Net to client
        427  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         20  rows processed

SQL> 

Since the index key was not null (there is always a 0 in each index entry) the index could contain NULL column values for c_2. So Oracle can index NULL values in a b-tree index as long as the entire index key is not null.

So, the optimizer is doing the ‘right thing’, really. It just may be confusing when situations like this arise. Just remember that entirely NULL keys, in a traditional b-tree index, aren’t included and, for a key composed of nullable columns, Oracle must scan the entire table to return all of the possible values. It isn’t a problem, it’s just ‘good business’.

June 29, 2007

Table Scans, Histograms and Scarlett O’Hara

Filed under: General,histograms,Indexes,Performance,stats — dfitzjarrell @ 16:43

Table scans aren’t necessarily bad.

Now that I have your attention, let me repeat that.

Table scans aren’t necessarily bad.

Why?

Well, it depends upon the data and how much of it you need to return. Sometimes we WANT a table scan over an index scan [and that may fly in the face of some tuning “experts” recommendations, but I’m not dealing in myth, here, but fact]; let’s look at a situation where this is the case.

Oracle, by default, expects your data to be uniformly distributed in your tables. Yes, that’s very likely wishful thinking on Oracle’s part, but that’s how the biscuit is buttered. We can influence that behaviour, however, with a neat aspect of statistics gathering known as the histogram, which is basically a ‘graph’ of how your data adheres to (or ignores) Oracle’s initial premise.

Computing histograms can help query execution plans by eliminating the ‘guesswork’ the optimizer is doing with respect to data distribution. In one running shot let’s state how Oracle ‘optimizes’ a query path (this could get deep, but it will be ‘sliced and diced’ into more manageable pieces as we go on):

Optimizer plans, in the “absence” of histograms [which aren’t really absent — that will be discussed in a bit], are based upon the assumption of data being distributed evenly across the desired keys. Such assumptions may work well for many installations, however these same assumptions may wreak havoc on data distributed such that clusters and gaps appear as the optimizer will assume, wrongly, that a given range of values will be located in close proximity to each other. Should the data returned exceed 30% of the total data in the table a full-table scan may be chosen, when, in actuality, an index scan would be more appropriate. Histograms will illustrate the relative distribution of the data keys, and will provide the optimizer with better information with which to formulate an access plan. Histograms are created using ‘buckets’, or ranges, to group key values. Such groupings can be seen in the USER_TAB_HISTOGRAMS view; the endpoint number for each group is listed, as well as the normalized value for that group. By default DBMS_STATS computes histograms with one ‘bucket’ for each indexed column if the SIZE parameter is not specified; with fairly evenly distributed data such histograms are acceptable. With skewed data a better histogram is necessary, and with 9i and later releases Oracle provides the SIZE AUTO specification, allowing Oracle to determine how many ‘buckets’ will be necessary. [Oracle release 10.2 in all of its forms has problems generating proper histograms on partitioned tables where single-valued non-null columns are present. Rather than skip the histogram altogether it chooses in some cases to create a frequency histogram using the sample size as an endpoint value. Another issue with 10.2 histograms: the endpoints on frequency histograms ONLY reflect the sample size, NOT an adjusted value for the entire table. 11.2 fixes this issue]

Okay, let’s take a DEEP breath. Now another. Good, now we’re ready to run an example which may explain how this works a bit more clearly and helps explain all of the glorious text in the paragraph above.

Let’s create a table, TEST_TAB, with the following structure

Table TEST_TAB
Name                            Null? Type
------------------------------- ----- ---------
A                                     NUMBER(6)
B                                     NUMBER(6)

I realise the column names aren’t exceptionally descriptive, but they serve the intended purpose. Let’s also load the table with skewed data; Column A contains unique values from 1 to 10000 (yes, I can count that high), while Column B contains 10 distinct values: 1, 2, 3, 4, 5, 9996, 9997, 9998, 9999 and 10000. The value 5 occurs 9991 times in the data; all other values occur only once. [Okay, so I like the number 5.]

We’ll run the following queries against this data:

(1) select * from test_tab where b=5;
(2) select * from test_tab where b=3;

As expected both of the above queries execute a full table scan (no surprise there) as no other access method is available. So, let’s be generous and provide another execution path possibility.

We’ll create an index on column B and see how that changes the execution plans. As we (hopefully) expected both queries now execute an index range scan in place of the full table scan. That’s good, right? I mean, any time we can use an index we should, right? Well, not really. With an index present it would be preferable to see an index range scan for query (2), where a small percentage of rows satisfy the condition BUT a full table scan for query (1), where the majority of the table data would be returned. Why? It’s a matter of I/O. For a small subset of the data an index scan, even though each row generates two I/O operations, generates less I/O than a full table scan. But, if we’re returning most of the table data doubling that I/O is foolish and wasteful, thus a full table scan (one read per row) is preferable. So, what do we do now? Well, we could try MySQL and see if it’s any better (it isn’t) or we could create a better set of histograms. Better? We have histograms now? Yes, but as we will see there is only one ‘bucket’ available; we really need more.

The table is analyzed using the dbms_stats.gather_table_stats() procedure:

SQL> exec dbms_stats.gather_table_stats(ownname=>NULL, tabname=>’TEST_TAB’, method_opt => ‘for all columns’, estimate_percent => null);

The computed statistics from dba_tables are shown below:

NUM_ROWS   BLOCKS     EMPTY_BLOCKS AVG_SPACE  CHAIN_CNT  AVG_ROW_LEN
---------- ---------- ------------ ---------- ---------- -----------
     10000         64            0         86          0          10

And the statistics from dba_tab_columns :

NUM_DISTINCT  LOW HIGH   DENSITY  NUM_NULLS NUM_BUCKETS LAST_ANALYZ SAMPLE_SIZE
------------ ---- ---- --------- ---------- ----------- ----------- -----------
       10000 Full Full     .0001          0           1 30-JUN-1999       10000
          10 Full Full        .1          0           1 30-JUN-1999       10000

From the USER_TAB_HISTOGRAMS view the following information is available:

TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE
---------- ----------- --------------- --------------
TEST_TAB   A                         0              1
TEST_TAB   A                         1          10000
TEST_TAB   B                         0              1
TEST_TAB   B                         1          10000

So, DBMS_STATS.GATHER_TABLE_STATS has created 1 bucket histograms for each column, so all values for the column are in the same bucket. The ENDPOINT_NUMBER represents the bucket number and ENDPOINT_VALUE represents the last column value in that bucket.

Now, unfortunately, both query (1) and (2) again execute a full table scan. Drat, drat and double drat. Simply having statistics about the table and columns does not help the optimizer distinguish how many rows have each value. The reason both queries execute a full table scan is the 1 bucket histogram; any value selected should be in that one bucket. (Yes, this reflects the general data layout if all we consider is whether the data is in the table or not, but that doesn’t help matters any.) This, of course, is not the case. To quote Scarlett O’Hara, “Where shall I go? What shall I do?” I can’t answer the first question, but in response to the second we create a more accurate histogram so the Optimizer knows how many values occur for each column. Let’s look at the test queries again:

Query (1): select * from test_tab where b=5;

which should execute a full table scan as the preponderance of rows in the table have b=5 (it’s far cheaper to ‘throw away’ 9 rows than it is to double the I/O with an index scan for that much data).

Query (2): select * from test_tab where b=3;

which should execute an index range scan, as only 1 of the 10,000 values has b=3 (2 I/O operations are cheaper than 10,000). But, hope springs eternal; we’ll try to correct this situation by executing dbms_stats.gather_table_stats() again, this time specifying ten buckets for the histogram and restricting the action to only column B:

SQL> exec dbms_stats.gather_table_stats(ownname=>NULL, tabname=>’TEST_TAB’, method_opt => ‘for all indexed columns size 10’, estimate_percent => null);

Querying USER_TAB_HISTOGRAMS the new, improved histogram is:

TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE
---------- ----------- --------------- --------------
TEST_TAB   B                         1              1
TEST_TAB   B                         2              2
TEST_TAB   B                         3              3
TEST_TAB   B                         4              4
TEST_TAB   B                      9995              5
TEST_TAB   B                      9996           9996
TEST_TAB   B                      9997           9997
TEST_TAB   B                      9998           9998
TEST_TAB   B                      9999           9999
TEST_TAB   B                     10000          10000

The ENDPOINT_VALUE shows the column value and the ENDPOINT_NUMBER shows the cumulative number of rows up to and including that value (yeah, that can be confusing). To explain it better, for ENDPOINT_VALUE 2 there is an ENDPOINT_NUMBER of 2; the previous ENDPOINT_NUMBER is 1, hence the number of rows with value 2 is the current ENDPOINT_NUMBER minus the previous ENDPOINT_NUMBER: 2 – 1 = 1 row having b=2; for ENDPOINT_VALUE 5, the ENDPOINT_NUMBER is 9995. The previous ENDPOINT_NUMBER is 4, so 9995 – 4 = 9991 rows where b=5. Boy, that’s a lot of work. But, that work is worth the effort as this now accurately reflects the data distribution in the table, and is more likely to provide the correct (read that ‘desired’) execution plans for the two example queries. This is proven (Hallelujah!) by the execution plans for both queries, shown below:

SQL> select * from test_tab where b=5
SQL> /

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS     (Cost=10 Card=9991 Bytes=99910)
1 0 TABLE ACCESS (FULL) OF 'TEST_TAB'     (Cost=10 Card=9991 Bytes=99910)

SQL> select * from test_tab where b=3
SQL> /

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS      (Cost=6 Card=500 Bytes=5000)
1 0 TABLE ACCESS (BY ROWID) OF 'TEST_TAB'  (Cost=6 Card=500 Bytes=5000)
2 1 INDEX (RANGE SCAN) OF 'TEST_TAB_B'     (NON-UNIQUE)

Woo-hoo, we’re cooking with gas now! So, we’ve learned something, but what? We’ve learned that for low cardinality data, histograms having one bucket for each distinct value are just peachy; however there may be a need to create histograms on data with a larger number of distinct values so using one bucket per value in such cases creates far too much overhead. So, Scarlett haunts us again with the “What shall I do?” question. [Rhett told her where she could go, so she’s stopped asking that one.] Well, Scarlett, we create histograms with fewer buckets, resulting in height-balanced buckets with the exception of the last one, which may have fewer values than the rest. If the histogram created in the example above used eight buckets instead of ten:

TABLE_NAME COLUMN_NAME ENDPOINT_NUMBER ENDPOINT_VALUE
---------- ----------- --------------- --------------
TEST_TAB   B                         0              1
TEST_TAB   B                         1              5
TEST_TAB   B                         2              5
TEST_TAB   B                         3              5
TEST_TAB   B                         4              5
TEST_TAB   B                         5              5
TEST_TAB   B                         6              5
TEST_TAB   B                         7              5
TEST_TAB   B                         8          10000

Oracle creates the requested number of buckets by placing the same number of values into each bucket. The ENDPOINT_NUMBER is the actual bucket number and ENDPOINT_VALUE is the endpoint value of the bucket determined by the column value. Bucket 0 holds the lowest value for the column, which is 1. Buckets 1 through 7 contain values up to and including 5, with Bucket 8 containing values from 5 to 10000. Buckets 1 through 7 are height-balanced, so all have the same number of values, leaving Bucket 8 to contain fewer values (it gets the leftovers, so to speak). For this particular data set such a histogram would be a poor choice, but for tables with a large number of distinct values, spread over an even larger dataset, such a histogram could prove quite useful as it could provide a better distribution map than Oracle would normally assume exists. Don’t, however, go overboard with histograms as you could end up with ‘too much of a good thing’ and send Oracle off on query plans it has no business choosing. Moderation in all things is good.

I think Scarlett would approve. Wherever she may be.

Create a free website or blog at WordPress.com.