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

Thread: Query tunning (WHERE IN_FLAG IS NULL)

  1. #21
    Join Date
    Mar 2002
    Posts
    534
    Are you sure that an index will speed up your query?
    Which percentage of you rows contain a NULL?

  2. #22
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    mike asked a good Q.. whether index will help..? yes if there is small set of recs having nulls..

    Can u paste the o/p of the query..

    Code:
    select 
      nvl(in_flag, 'NULL') in_flag, 
      count(*)
    FROM 
      table_name
    GROUP BY 
      nvl(in_flag, 'NULL')

    Rgds
    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  3. #23
    Join Date
    Apr 2003
    Location
    Pune,Maharashtra. India.
    Posts
    245
    Problem solved.
    We choose to alter the table with default value as "ABC".That columns now can contain only 3 values Y,N,ABC. So problem solved now.

    Rgds
    Parag

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

    Thats a design change.

    Did you test the other methods provided by other posters here ?

    You may check out the other methods too if you have time :-)

    regards
    Hrishy

  5. #25
    Join Date
    Apr 2003
    Location
    Pune,Maharashtra. India.
    Posts
    245
    Hrishi,
    We were having 3 options.
    1. Bitmap index
    2. Set column value to default
    3. NVL Functional index
    1. I tried bitmap select was running super fast but insertion got affected (so no help)
    2. In test env only (as i dont wanted more to play with PROD) i created table of data around 100mb as mirror copy of table in PROD
    he he index took more space than table. I am not ready to take risk of this on PROD

    3. And final solution given by you worked and normal index solved the issue.

    I think this clears everybodies doubts now.....

    Hrishy thanks for simple solution for which i took long way.

    Rgds
    Parag

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

    Thanks for the feedback i appreciate it very much.

    regards
    Hrishy

  7. #27
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    ===
    Oracle cannot index null values and hence queries with is and is not null cannot use an index.
    ===

    Part of the statement is incorrect. Oracle will use index when "IS NOT NULL" condition is used in the WHERE clause.

    Tamil

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