the columns that remain in the select list likely are being read from the
index -- is there any other type table access for 'big_table' in the
execution plan replacing the FTS?
does anything else in the plan change?
have you looked into hints to force the index range scan (which may not be
so cheap with the additional columns included -- the optimizer may be making
a correct choice in this case)
--
Mark C. Stock
email mcstock -> enquery(dot)com
www.enquery.com
(888) 512-2048
"Thomas Kellerer" <spam_eater_at_gmx.net> wrote in message
news:bodc47$1dagp3$1_at_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 -- remove
> , big_table.discount -- remon
> 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 - 06:38:48 CST
