Re: UNIQUE scan is always costlier than NON-UNIUQE scan?
- From: Daniel Fink <Daniel.Fink@xxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Tue, 10 Aug 2004 07:31:28 -0600
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@xxxxxxxxxxxxx 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@xxxxxxxxxxxxx
> put 'unsubscribe' in the subject line.
> --
> Archives are at //www.freelists.org/archives/oracle-l/
> FAQ is at //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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
