| 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?
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:37:19 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
-----------------------------------------------------------------
 |
 |