RE: Index not being used .... why?

From: Mercadante, Thomas F <NDATFM_at_labor.state.ny.us>
Date: Tue, 20 Nov 2001 12:09:39 -0800
Message-ID: <F001.003C9D2A.20011120115022@fatcity.com>

Raj,

I feel like I'm asking a stupid question but, here goes, is there an index on that table that contains the mso_or_independent_id column?

Is this column declare a varchar by any chance?

Tom Mercadante
Oracle Certified Professional

-----Original Message-----
Sent: Tuesday, November 20, 2001 2:23 PM To: Multiple recipients of list ORACLE-L

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

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Mercadante, Thomas F
  INET: NDATFM_at_labor.state.ny.us

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Tue Nov 20 2001 - 14:09:39 CST