Query tunning (WHERE IN_FLAG IS NULL)
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 3 123 LastLast
Results 1 to 10 of 27

Thread: Query tunning (WHERE IN_FLAG IS NULL)

Hybrid View

  1. #1
    Join Date
    Apr 2003
    Location
    Pune,Maharashtra. India.
    Posts
    245

    Query tunning (WHERE IN_FLAG IS NULL)

    Hi,
    one mf my processes is going slow. So after tracing the session i found that
    in one of the queries this condition is there (WHERE IN_FLAG IS NULL)

    SELECT ROWID,XYZ FROM TABLE_NAME WHERE IN_FLAG IS NULL

    Some how i tried but tkprof is not giving explain plan for this statement
    following are stats

    SELECT ROWID,XYZ FROM TABLE_NAME WHERE IN_FLAG IS NULL

    call count cpu elapsed disk query current rows
    ------- ------ -------- ---------- ---------- ---------- ---------- -------
    Parse 0 0.00 0.00 0 0 0 0
    Execute 0 0.00 0.00 0 0 0 0
    Fetch 110922 2.32 2.03 2738 116800 0 110921
    ------- ------ -------- ---------- ---------- ---------- ---------- -------
    total 110922 2.32 2.03 2738 116800 0 110921

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

    Thanks in advance.......

    Rgds
    Parag

  2. #2
    Join Date
    Apr 2003
    Location
    Pune,Maharashtra. India.
    Posts
    245
    As stats is not in readble way i am again pasting the stats


    SELECT xyz,ABC FROM table_name WHERE IN_FLAG IS NULL


    call count cpu elapsed disk query current rows
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    Parse 0 0.00 0.00 0 0 0 0
    Execute 0 0.00 0.00 0 0 0 0
    Fetch 110922 2.32 2.03 2738 116800 0 110921
    ------- ------ -------- ---------- ---------- ---------- ---------- ----------
    total 110922 2.32 2.03 2738 116800 0 110921

    Misses in library cache during parse: 0
    Parsing user id: 56 (USER_ID) (recursive depth: 1)

  3. #3
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    learn how to post properly using the code tags, it will format the text properly. Also post the explain plan

  4. #4
    Join Date
    Apr 2003
    Location
    Pune,Maharashtra. India.
    Posts
    245
    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....
    Attached Files Attached Files

  5. #5
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    you havent closed the session before running tkprof

  6. #6
    Join Date
    Apr 2003
    Location
    Pune,Maharashtra. India.
    Posts
    245
    This is 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......


    Rgds
    Parag

  7. #7
    Join Date
    Jan 2001
    Posts
    2,828
    Hi Parag

    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

    regards
    Hrishy
    Last edited by hrishy; 07-15-2005 at 06:33 AM.

  8. #8
    Join Date
    Apr 2003
    Location
    Pune,Maharashtra. India.
    Posts
    245
    Thanks hrishy,
    Good idea I will try it. I think it will solve my problem.
    Also next time i will try those tags to put thread info on forum..

    Thanks once again......

    Rgds
    Parag

  9. #9
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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

  10. #10
    Join Date
    May 2005
    Location
    AZ, USA
    Posts
    131
    Quote Originally Posted by hrishy
    Hi Parag

    Oracle cannot index null values and hence queries with is and is not null cannot use an index.
    Hrishy - how would you explain the following:

    "Indexing Null Values
    Bitmap indexes index nulls, whereas all other index types do not.
    "
    Found in the propaganda at:
    http://download-west.oracle.com/docs..._acc.htm#18779


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width