Index Rang Scan - How to Improve?

Folks, i need some help/advice. ETL/DW environment, Oracle 10.2.0.4, AIX
5.3; 28 cpus; 164gb ram.

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

SELECT    blablabla           ,
          *Y.COLUMN_B*,
          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
*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:
http://richardfoote.wordpress.com/category/index-block-size/)?

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

Other related posts: