Could you show us the tkprof-outputs of both scenarios: the full table scan and the index range scan. With wait events (so from a 10046 level 12 sqltrace). On Tue, Jun 21, 2011 at 4:54 AM, Thiago Maciel <thiagomaciel@xxxxxxxxx>wrote: > 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 > -- Toon Koppelaars RuleGen BV Toon.Koppelaars@xxxxxxxxxxx www.RuleGen.com TheHelsinkiDeclaration.blogspot.com (co)Author: "Applied Mathematics for Database Professionals" www.rulegen.com/am4dp-backcover-text