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

From: Charlie Mengler <charliem_at_mwh.com>
Date: Tue, 20 Nov 2001 12:06:33 -0800
Message-ID: <F001.003C9D11.20011120115021@fatcity.com>

This could occur IF MSO_OR_INDEPENDENT_ID is really a VARCHAR2. The implicit conversion [ TO_NUMBER(mso_or_independent_id) ] invalidates the use of the index!

"Jamadagni, Rajendra" wrote:
>
> 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 = 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!
>
>

>-----------------------------------------------------------------------------------------------------------------------------------


>                           Name: ESPN_Disclaimer.txt

>    ESPN_Disclaimer.txt    Type: Plain Text (text/plain)

>                       Encoding: 7bit


-- 
Charlie Mengler                         Maintenance Warehouse  
charliem_at_mwh.com                        10641 Scripps Summit Ct.
858-831-2229                            San Diego, CA 92131    

Extraordinary DBA - Design on the Fly & Just In Time Programming

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Charlie Mengler
  INET: charliem_at_mwh.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).

Received on Tue Nov 20 2001 - 14:06:33 CST