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

Thread: Query tunning (WHERE IN_FLAG IS NULL)

  1. #11
    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


  2. #12
    Join Date
    Apr 2003
    Location
    Pune,Maharashtra. India.
    Posts
    245
    Jeff,
    Here is the o/p taht you wanted......

    IN_FLAG COUNT(*)
    ======= ==========
    N 123731
    Y 2503067

  3. #13
    Join Date
    Apr 2003
    Location
    Pune,Maharashtra. India.
    Posts
    245
    Well i read the documnet given in.
    http://download-west.oracle.com/doc...a_acc.htm#18779
    About indexing nulls. But one query In the example given "is null" condition is niot used in where condition. Then in that case will the bitmap index get used.

    Rgds
    Parag

  4. #14
    Join Date
    Apr 2003
    Location
    Pune,Maharashtra. India.
    Posts
    245
    No i tried it.

    i created one table parag1 with one column containing null values created .Table has around 2000000 rows .
    created a bitmap index on that column and tried to see the explain plan of following statement

    select a from parag1 where b is null;

    and explian plan is ....

    Q_PLAN
    -------------------------------
    SELECT STATEMENT
    TABLE ACCESS FULL PARAG1
    TABLE ACCESS FULL PARAG1
    SELECT STATEMENT
    TABLE ACCESS FULL PARAG1
    TABLE ACCESS FULL PARAG1

    So i think i have to go by Hrishy's way. bitmap index wont help in null .

  5. #15
    Join Date
    Apr 2003
    Location
    Pune,Maharashtra. India.
    Posts
    245
    I am extremely sorry to give so many replies on my own.
    But i thought i need to share this with all.
    I tried to give hint in the query like..

    select /*+ index (parag1,idx_parag1) +*/ a from parag1 where b is null;

    And bingo what comes the explain plan.....

    here it is....

    Q_PLAN
    ----------------------------------------------
    SELECT STATEMENT
    TABLE ACCESS BY INDEX ROWID PARAG1
    BITMAP CONVERSION TO ROWIDS
    BITMAP INDEX SINGLE VALUE IDX_PARAG1
    BITMAP INDEX SINGLE VALUE IDX_PARAG1
    BITMAP CONVERSION TO ROWIDS
    BITMAP INDEX SINGLE VALUE IDX_PARAG1
    BITMAP INDEX SINGLE VALUE IDX_PARAG1
    TABLE ACCESS BY INDEX ROWID PARAG1
    BITMAP CONVERSION TO ROWIDS
    BITMAP INDEX SINGLE VALUE IDX_PARAG1

    Q_PLAN
    ----------------------------------------------
    BITMAP INDEX SINGLE VALUE IDX_PARAG1
    BITMAP CONVERSION TO ROWIDS
    BITMAP INDEX SINGLE VALUE IDX_PARAG1
    BITMAP INDEX SINGLE VALUE IDX_PARAG1


    "dbtoo" what you have said is correct..

    Once again sorry to give so many replies on my own.....

    Rgds
    Parag

  6. #16
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    Regular B tree indexes will not store null entries in oracle.

    Bit Map indexes on the other hand will indexe null values.

    Parag the idea of using Function Based indexes as suggested by Dapi did not occur to me.I think you could try DApis method too.

    regards
    Hrishy

  7. #17
    Join Date
    Apr 2003
    Location
    Pune,Maharashtra. India.
    Posts
    245
    Hi,
    Ya hrushi i will try both offcourse on test env first.
    Bitmap index is taking around 25MB . I will create function based
    index and lets see which one is resource friendly. As we have 2 solutions now We will choose which is best. But still want to know your opinion what do you think Which one is better Function based index or bitmap index.

    Rgds
    Parag

  8. #18
    Join Date
    Apr 2003
    Location
    Pune,Maharashtra. India.
    Posts
    245
    Hrishi,
    checked index is taking more space than expected . In test env
    it is taking around 60MB for a table having size 40MB. So i think Bitmap is correct solution....

  9. #19
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    Quote Originally Posted by paragp1981
    So i think Bitmap is correct solution....
    You didn't say much about the environment - Bitmap indexes are *ahem* not advised for OLTP systems.
    "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. #20
    Join Date
    Apr 2003
    Location
    Pune,Maharashtra. India.
    Posts
    245
    Hi Dapi,
    Ours is a telecom domain. Though this problem is coming in new server for which we installed new application called FMS(Fraud Mgmt System). This procedure is taking around 40-50% of one CPU out of Dual CPU. Most of the time some scheduled procedure will be running in this. And even if bitmap indexes are not advised. One Rejection procedure is running and doing Full tablescan of table having 20,00000 rows so . I dont think major problem. In any case we will se if this works for some days.
    and if found not satisfactory We can always come back to solution given by hrishi.

    But still help appreciated. Thanks for all help.

    Rgds
    Parag

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