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 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@xxxxxxxxxxxxx
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
-----------------------------------------------------------------

Other related posts: