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:48:25 -0600
Message-id: <4118D229.5080309@sun.com>


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_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:44:31 CDT

Original text of this message

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