Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.misc -> Help on execution plan
Hello,
I've a SQL query here, which joins 3 tables, and retrieves 5 columns from one of those tables. That table has about 4.5 million records, and the join results in about 4000 records from that table. The other two tables which are needed for joining have about 2000 and 4000 rows. Nothing special so far.
The join for the big table is on a column where an index is available (first column of a multi-column index)
Now, as long as I leave the five columns in the select list, Oracle will use a full table scan on the big table. The total costs are then 12519 (as returned by EXPLAIN PLAN) When I remove two specific columns from the select list Oracle uses an INDEX RANGE SCAN on the index which is avaible and the costs drop down to 89.
It's two specific columns I need to remove, not just any two columns. Those two columns are defined as NUMBER(5) and NUMBER(3,1)
What I'd like to understand is, why does Oracle use the index when those two columns are not selected from the table, but uses a FTS when those columns are included.
I have done an ANALYZE on the table and all index columns, but to no avail. I have also tried removing about every other combination of two columns from the list, but removing those specific two columns are the only case where Oracle will use the index. Taking the number of rows returned and the number of rows in the table I really don't understand how those two columns could make a difference in the execution plan.
Here is the query:
SELECT big_table.name
, big_table.id
, big_table.ca_code
, big_table.product_no
, big_table.min_order_qty
, big_table.discount
FROM big_table,
table1, table2
AND table2.partner_id = table1.partner_id AND table2.ind = '1' AND table2.start_date < sysdate
This will use a FTS on big_table. When I remove min_order_qty and discount, it will use the index on the ID column from big_table.
Any ideas how I could convince Oracle to use the INDEX RANGE SCAN even when all 5 columns are selected (big_table has a total of 11 columns)
Any help is greatly appreciated
Thanks in advance
Thomas
Received on Thu Nov 06 2003 - 05:42:57 CST
![]() |
![]() |