Re: Index Rang Scan - How to Improve?

From: Tim Gorman <>
Date: Tue, 21 Jun 2011 07:16:58 -0600
Message-ID: <>


Does the execution plan show TABLE ACCESS BY ROWID to TABLE_Y or not?  You've implied that it shouldn't, but the big question is whether or not it is happening.

The "clustering factor" is a value that is clearly much closer to the number of rows in the table rather than the number of blocks, which indicates that each index entry scanned will mean visiting a new different table block.  Thus, scanning 1.5m-3.0m million rows from TABLE_X during the join to TABLE_Y would clearly imply visiting every block in TABLE_Y, so there is little benefit to a range scans and instead chooses another plan.  If the execution plan shows access to the table, then clustering factor is important, but if as you've implied the query is entirely satisfied within INDEX_02 then clustering factor would not be an important consideration as there should be no access to TABLE_Y.

I can only conclude that the query is not entirely satisfied within INDEX_02, and that TABLE_Y is being accessed, and thus that the cluster factor is steering the optimizer away from using index range scan, which is a wise decision on its part -- as you've proven with your own testing of response times.

So, can you post the execution plan chosen by the optimizer?

Hope this helps...
Tim Gorman
consultant -> Evergreen Database Technologies, Inc.
postal     => 13087 West Cedar Drive #225, Lakewood CO 80228
website    =>
email      =>
mobile     => +1-303-885-4526
fax        => +1-303-484-3608
Lost Data? => for info about DUDE...

On 6/20/2011 8:54 PM, Thiago Maciel wrote:
Folks, i need some help/advice. ETL/DW environment, Oracle, AIX 5.3; 28 cpus; 164gb ram. 

Its a simple query that is part of an ETL routine:

SELECT    blablabla           ,
     FROM TABLE_X PARTITION (T1)              X,
          TABLE_Y         Y
 WHERE X.COLUMN_A                =   'N'
      AND  NVL(to_number(TRIM(X.COLUMN_B)),1) = Y.COLUMN_A

I need to run this query with 8 different sessions at the same time (each one will choose a different partition of the table_x, like: session 1 partition (t1), session 2 partition (t3),and so on). Table_X is partitioned by hash with partitions (each partition will vary between 1,5MM and 3MM) , and table_y has around 300MM. There is an index on table_y with the following stats:

INDEX_NAME                                        : INDEX_02
COLUMNS                                            : 1 ASC   COLUMN_A                         NOT NULL --    299.094.710 NDV
                                                               2 ASC   COLUMN_B                         NOT NULL   --  PK of the table_y
INDEX_TYPE                                         : NORM                           
DEGREE                                               : 1                          
BLEVEL                                                : 3                              
LEAF_BLOCKS                                     : 1.416.590                          
DISTINCT_KEYS                                    : 295.327.520                             
AVG_LEAF_BLOCKS_PER_KEY            : 1                         
AVG_DATA_BLOCKS_PER_KEY            : 1                            
CLUSTERING_FACTOR                          : 265.964.260                                
BLOCKS_IN_TABLE                               : 1.289.581                                                                 
ROWS_IN_TABLE                                  : 300.017.100
SIZE                                                      : 11gb

Tests showed to me that full scan on index_02 or on table_y is the best scenario regarding response time (even with lots of time spent on read by other session). When i force Oracle to choose index range scan on index_02 the response time is higher than full scan, either: index or table.

See that the selectivity is "only" between 0,5% - 1,1% (range scan between 1,5MM and 3,5MM), and Oracle does not need to touch the table_y.

So my question is: how can i improve the access to the index via range scan? Or is there a better solution to this issue? For example, create the index with 32kb block size (It seems not so good idea: 

Yes,yes i need to improve a lot more the response time of this query running with different sessions at the same time.

** response time of the disks are normals, most of the time between: 4ms and 16ms.
** Index and Tables in ASSM with 8kb block size (i know its a small block size to DW environment).

If you need more information, please let me know it.

Thanks in advance.

Thiago Maciel
-- Received on Tue Jun 21 2011 - 08:16:58 CDT

Original text of this message