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:48:25 -0600
The SORT is an aggregate SORT. I wonder if this is always 'performed' when you
have an aggregate function like count(). Notice that
the cost of the plan is the exact same as the cost of the access. This tells me
that the cost of the sort is factored as 0.
Daniel Fink
Mark W. Farnham wrote:
> good point lex -- non-unique indexes often have fewer and/or shorter columns
> so you'd expect the CBO to pick the fewest blocks to scan.
>
> BUT -- what the heck is up with doing a SORT of count(*)? I suppose it is
> pretty cheap to sort the guaranteed 1 row, but why do it at all?
>
> mwf
>
> 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.***
>
----------------------------------------------------------------
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:
