Index Rang Scan - How to Improve?

From: Thiago Maciel <>
Date: Mon, 20 Jun 2011 23:54:28 -0300
Message-ID: <>

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 *8 *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

*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 Mon Jun 20 2011 - 21:54:28 CDT

Original text of this message