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

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

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

Original text of this message

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