Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Index not being used .... why?

Index not being used .... why?

From: Jamadagni, Rajendra <Rajendra.Jamadagni_at_espn.com>
Date: Tue, 20 Nov 2001 11:37:55 -0800
Message-ID: <F001.003C9C18.20011120112254@fatcity.com>


This is from my user .... I can't figure why Oracle is not using the index even with a hint. Optimizer mode is RULE. BTW the query always returns NULL value.

I'm running a simple query. It's taking too long. Ran an explain plan on it
and it's doing a full table scan for some reason. I've tried every data base
thinking there was an index missing or something.  I did /*+ first_rows */
as well with no success. Any ideas ? Any suggestions would be appreciated !!!!!!

select mso_or_independent_id
from billing_file_summary
where mso_or_independent_id = 0104167;

INDEXES ON THE TABLE

INDEX_NAME                     INDEX_TYPE                  TABLE_NAME
TABLE UNIQUENES
------------------------------ ---------------------------
------------------------------ ----- ---------
BFD_PARENT_INV_IDX             NORMAL
BILLING_FILE_SUMMARY           TABLE NONUNIQUE
BFS_BILL_TO_ID_IDX             NORMAL
BILLING_FILE_SUMMARY           TABLE NONUNIQUE
BFS_MSO_IND_IDX                NORMAL
BILLING_FILE_SUMMARY           TABLE NONUNIQUE
BFS_ORG_IDX                    NORMAL
BILLING_FILE_SUMMARY           TABLE NONUNIQUE
BFS_PRINTED_INV_IDX            NORMAL
BILLING_FILE_SUMMARY           TABLE NONUNIQUE
BFS_YEAR_MONTH_IDX             NORMAL
BILLING_FILE_SUMMARY           TABLE NONUNIQUE
PK_BILLING_FILE_SUMMARY        NORMAL
BILLING_FILE_SUMMARY           TABLE UNIQUE



Explain shows a full table scan
TO_CHAR(TIMESTAM OBJECT_NAME                     OPERATION
OPTIONS
---------------- ------------------------------
------------------------------ ---------------------
11202001 01:42pm BILLING_FILE_SUMMARY           TABLE ACCESS
FULL
11202001 01:42pm                                                   SELECT
STATEMENT TIA
Raj

Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.

QOTD: Any clod can have facts, but having an opinion is an art!

*********************************************************************2

This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.

*********************************************************************2
Received on Tue Nov 20 2001 - 13:37:55 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US