RE: autotrace issue

Sanjay

>any input that i give will exist ONLY in one of the partitions. 

This information is available to the CBO?

>the table is list-partitioned on field2 and field2 is also an  
>input in the query, and its constant for both the queries . 
>So there is no-way it should be doing a FTS of the entire table.

Are you sure that it's not a partition full scan? (in fact if you have a 
restriction on the partition key the CBO should use partition pruning...)

Please notice that:
- this is a partition full scan

SELECT STATEMENT
  TABLE ACCESS (FULL) OF '...'

- this is a table full scan

SELECT STATEMENT
  PARTITION RANGE (ALL)
    TABLE ACCESS (FULL) OF '...'


HTH
Chris

New Features Oracle Database 10g Release 2 seminars @ www.trivadis.com
Italiano: Lugano (24-Nov)
Français: Genève (17-Nov)
Deutsch: München (20-Oct), Basel (25-Oct), Frankfurt (27-Oct),
         Düsseldorf (23-Nov), Zürich (29-Nov), Stuttgart (13-Dec)
--
http://www.freelists.org/webpage/oracle-l

Other related posts: