UNIQUE scan is always costlier than NON-UNIUQE scan?

From: <jaysingh1_at_optonline.net>
Date: Tue, 10 Aug 2004 08:57:58 -0400
Message-id: <434622243416e0.43416e04346222@optonline.net>

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_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 - 07:56:35 CDT