RE: UNIQUE scan is always costlier than NON-UNIUQE scan?
- From: "Lex de Haan" <lex.de.haan@xxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 10 Aug 2004 15:48:01 +0200
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.
---------------------------------------------
visit my website at http://www.naturaljoin.nl
---------------------------------------------
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Mark W. Farnham
Sent: Tuesday, August 10, 2004 14:41
To: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Lex de Haan
Sent: Tuesday, August 10, 2004 9:12 AM
To: oracle-l@xxxxxxxxxxxxx
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.
---------------------------------------------
visit my website at http://www.naturaljoin.nl
---------------------------------------------
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of
jaysingh1@xxxxxxxxxxxxx
Sent: Tuesday, August 10, 2004 13:58
To: oracle-l@xxxxxxxxxxxxx
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
----------------------------------------------------------------
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
-----------------------------------------------------------------
-- 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@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
-----------------------------------------------------------------
-- 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@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:
