Index Rang Scan - How to Improve?
- From: Thiago Maciel <thiagomaciel@xxxxxxxxx>
- To: Oracle-L List <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 20 Jun 2011 23:54:28 -0300
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