| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: UNIQUE scan is always costlier than NON-UNIUQE scan?
note that "SORT" in an execution plan is not always what you think it is ...
the "(AGGREGATE)" suffix tells you that this is the actual counting at work.
there is no "real" sorting here, as you would need for a DISTINCT or an
ORDER BY.
hope this helps, additions/corrections welcome,
Lex.
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Mark W. Farnham
Sent: Tuesday, August 10, 2004 14:41
To: oracle-l_at_freelists.org
Subject: RE: UNIQUE scan is always costlier than NON-UNIUQE scan?
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
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Lex de Haan
Sent: Tuesday, August 10, 2004 9:12 AM
To: oracle-l_at_freelists.org
Subject: RE: UNIQUE scan is always costlier than NON-UNIUQE scan?
no, not always -- but for a COUNT(*) any index on a NOT NULL column is good
enough for a fast full scan. probably, scanning the non-unique index seems
cheaper -- less blocks?
Kind regards,
Lex.
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of
jaysingh1_at_optonline.net
Sent: Tuesday, August 10, 2004 13:58
To: oracle-l_at_freelists.org
Subject: UNIQUE scan is always costlier than NON-UNIUQE scan?
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
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.htmlReceived on Tue Aug 10 2004 - 08:44:14 CDT
-----------------------------------------------------------------
-- Binary/unsupported file stripped by Ecartis -- -- Type: text/x-vcard -- File: Lex de Haan.vcf
----------------------------------------------------------------
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
-----------------------------------------------------------------
-- Binary/unsupported file stripped by Ecartis -- -- Type: text/x-vcard -- File: Lex de Haan.vcf
----------------------------------------------------------------
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
-----------------------------------------------------------------
 |
 |