Re: UNIQUE scan is always costlier than NON-UNIUQE scan?
- From: J.Velikanovs@xxxxxxxx
- To: oracle-l@xxxxxxxxxxxxx
- Date: Tue, 10 Aug 2004 16:15:22 +0300
Try to turnon 10053 event and check TRC file.
It can show you the reason, why Oracle think it is better to scan FK
index.
Jurijs
+371 9268222 (+2 GMT)
============================================
Thank you for teaching me.
http://otn.oracle.com/ocm/jvelikanovs.html
jaysingh1@xxxxxxxxxxxxx
Sent by: oracle-l-bounce@xxxxxxxxxxxxx
10.08.2004 15:57
Please respond to oracle-l
To: oracle-l@xxxxxxxxxxxxx
cc:
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
-----------------------------------------------------------------
----------------------------------------------------------------
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:
