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