Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: UNIQUE scan is always costlier than NON-UNIUQE scan?

Re: UNIQUE scan is always costlier than NON-UNIUQE scan?

From: Daniel Fink <Daniel.Fink_at_Sun.COM>
Date: Tue, 10 Aug 2004 07:31:28 -0600
Message-id: <4118CE30.2080501@sun.com>


There is not an absolute like this in the CBO (not even the lowest cost plan is always used). You need to examine it case by case.

Is the PK Index also using a FAST FULL SCAN? If not, you really can't compare the two. Are both plans doing a SORT? <see above reply for second half>

IIRC, A FFS uses the same algorithm (and perhaps code) as a FULL TABLE SCAN. It will start at the first block in the segment and perform multiblock reads until it hits the 'high water mark'. If the PK index has allocated more blocks (both branch and leaf), the FFS will be more costly.

The first place to start is checking the index stats. Look at BLEVEL, LEAF_BLOCKS, DISTINCT_KEYS, AVG_LEAF_BLOCKS_PER_KEY,

                 AVG_DATA_BLOCKS_PER_KEY, CLUSTERING_FACTOR. I am sure you will find a significant difference in the statistics 
between the two indexes.

The real question is "Is the CBO choosing the proper path to return the correct values within a time that meets the user expectations?"

Regards,
Daniel Fink

jaysingh1_at_optonline.net wrote:
> Hi All,
>
> All stats are upto date.
>
> Is there any specific reason why the optmizer is going through the NON-UNIQUE index instead of PK index?
>
> In otherwords,
> NON-UNIQUE index = (COST=947)
> PK index = (Cost=17825) [using hint to use PK index]
>
> Does it mean that UNIQUE scan is always costlier than NON-UNIUQE scan?
>
>
> SQL>
> 1* SELECT COUNT(*) FROM US_PROFILE_TABLE
> SQL> /
>
> COUNT(*)
> ----------
> 482639
>
>
> EXECUTION PLAN
> ----------------------------------------------------------
> 0 SELECT STATEMENT OPTIMIZER=CHOOSE (COST=947 CARD=1)
> 1 0 SORT (AGGREGATE)
> 2 1 INDEX (FAST FULL SCAN) OF 'US_PROFILE_TABLE_FK10' (NON-UNIQU
> E) (COST=947 CARD=479439)
>
> ***US_PROFILE_TABLE_FK10 is a NON-UNIQUE index on LEVELID column.***
>
>
> SQL> SELECT LEVELID,COUNT(*) FROM US_PROFILE_TABLE GROUP BY LEVELID;
>
> LEVELID COUNT(*)
> ---------- ----------
> 0 477912
> 30 1507
> 60 3223
>
>
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------



Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Received on Tue Aug 10 2004 - 08:27:34 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US