Help on execution plan

From: Thomas Kellerer <spam_eater_at_gmx.net>
Date: Thu, 06 Nov 2003 12:42:57 +0100
Message-ID: <bodc47$1dagp3$1@ID-13919.news.uni-berlin.de>

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

WHERE big_table.id = table2.id

AND   table2.partner_id = table1.partner_id
AND   table2.ind = '1'
AND   table2.start_date < sysdate

AND (table2.end_date IS NULL OR table2.end_date > sysdate) AND table1.loc_id = 'xxxx'
;

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