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

From: Jeffery Stevenson <jeff_at_mpv.com>
Date: Tue, 20 Nov 2001 12:20:13 -0800
Message-ID: <F001.003C9D9F.20011120120035@fatcity.com>

I'm gonna hazard a guess that the mso_or_independent_id field may be a CHAR or VARCHAR. Try enclosing the value in single quotes and see if that helps. The implicit conversion from numeric to char would suppress index usage and it would also drop off the leading zero (which might be why you aren't getting any data returned). Anyways, just a thought.

Jeffery Stevenson
Databeast Slayer
Medical Present Value, Inc.
Austin, TX

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

Returns null or no rows? If it is null then something weird is going on...

-----Original Message-----
Sent: Tuesday, November 20, 2001 1: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: Norrell, Brian
INET: BNorrell_at_QuadraMed.com

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

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

Author: Jeffery Stevenson
INET: jeff_at_mpv.com

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