Misses in library cache during parse: 0
Parsing user id: 56 (USER_ID) (recursive depth: 1)
i have a index on IN_FLAG checked the status its valid also
Is there any thing like this index wont get used as we are checking "IS NULL"
If yes how to solve this.......
I dont know how to add tags so i am attaching txt file having stats. Please have a lok at it. And Some how tkprof is giving only this much o/p not explain plan....
SQL> @out_explain.sql
Q_PLAN
-----------------------------
SELECT STATEMENT
TABLE ACCESS FULL UTXREJTAB
As we are using is null it is not taking index . Actually we are building one cursor based on this condition....
like this
==========
declare cursor c1
is
select rowid,imsi from utxrejtab where in_flag is null ;
cnt number:=0;
begin
for i in c1 loop
.........
This is causing problem . How to build cursor which will hold all rows which are having null values in in_flag column. but doesnt do full table scan. Is there any way......
Oracle cannot index null values and hence queries with is and is not null cannot use an index.
How about doing something like altering the values of in_flag column and insert NULL into this column when no value is supplied (alter the table and make the default value to NULL)
and then you can write your query something like
select rowid,imsi from utxrejtab where in_flag ='NULL'
and create a index on in_flag
watch out for data skewness too you might have to create histograms
regards
Hrishy
P.s use tages like square bracket code and /code square bracket to format query plans etc to post here
How about creating an function based index on NVL(in_flag, 'NULL') ?
"The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman