Index Rang Scan - How to Improve?

From: Thiago Maciel <thiagomaciel_at_gmail.com>
Date: Mon, 20 Jun 2011 23:54:28 -0300
Message-ID: <BANLkTimb_FNuk6TgZ=X8gzZCfU3AMpdRaw_at_mail.gmail.com>

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

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Jun 20 2011 - 21:54:28 CDT