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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).