index questions - Page 2
DBAsupport.com Forums - Powered by vBulletin
Page 2 of 2 FirstFirst 12
Results 11 to 18 of 18

Thread: index questions

  1. #11
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by pravin
    jurij - Complement to u from me

    Ur way of answering the questions and also explaining the concepts and theory behind it , is really great.

    Pravin

    But can he juggle too?
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  2. #12
    Join Date
    Dec 2001
    Location
    SAN FRANCISCO, CA
    Posts
    306

    Talking

    Jeff

    about his juggle I am not sure about that

    By the way I am also ur fan. U and jurij should get together or cordinate and come out with DBA - Trouble shooting Guide asap. I will be first one to buy book.

    Eat , Drink & Enjoy life -

    pravin_kini@hotmail.com

  3. #13
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by marist89
    But can he juggle too?
    Sure I can....
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  4. #14
    Join Date
    May 2001
    Posts
    285
    Back to the original topic (BTW -- I like the little guy you put there;-)), here is my answers to your questions:

    2) No, I don't have any bitmap index created on that table, only B*tree index. That's why I am kind of confused when I saw all the conversions from bitmap to row_id. Does it look normal to you?

    2.1) Actually, CallDirection is an ideal column to create Bitmap index on it since it only has 3 distince values -- inbound, outbound and unknown. So shall I create a bitmap index on it? If I decided to partition this table, do I need to rebuild the bitmap index whenever I drop a partition from the table?

    2.2) Since Oracle does combine B*tree indexes on seperate columns, how can we judge the selectivity of composite index and separate column index?

    In your previous post, you said the one offers better selectivity usually works better.

    3) In my situation, I can't put hint on my queries since they are generated by Crystal report during runtime. So what can I do if CBO didn't use index even if the number of rows returned is <<5% of the total rows in that partition?
    FYI -- they are local partitioned index, and have been analyzed.

    How can I change DB_File_Multiple_read_count to make CBO change its mind?

    Thanks again...

  5. #15
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by jmodic
    Originally posted by marist89
    But can he juggle too?
    Sure I can....
    I bow to the master....
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  6. #16
    Join Date
    May 2001
    Posts
    285

    Red face

    Juggle masters ---

    Don't forget to answer my questions when you are juggling ;-)

  7. #17
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by elaine3839
    2) No, I don't have any bitmap index created on that table, only B*tree index. That's why I am kind of confused when I saw all the conversions from bitmap to row_id. Does it look normal to you?
    Does it look normal to me? Sure, why not. It woldn't be the first time when Oracle documentation is not 100% accurate. Or, it definitely wouldn't be the first time when I don't read/understand Oracle docs 100% correct. I'm actually very excited by the pace of improvements done to CBO in last few releases.

    2.1) Actually, CallDirection is an ideal column to create Bitmap index on it since it only has 3 distince values -- inbound, outbound and unknown. So shall I create a bitmap index on it? If I decided to partition this table, do I need to rebuild the bitmap index whenever I drop a partition from the table?
    Don't be misguided by the fact that this column has very low cardinality - this fact alone does not yet qualify this column to be an ideal candidate for bitmap index. What is the nature of your system? If it is OLTP and this table is modified frequently with many concurent session then forget about bitmap indexes. It's because row locking actually becomes "range of rows locking" when you use bitmap indexes. On the other hand, if concurent modifications are not an isue with this table, then this column probably realy is an ideal candidate for bitmap index.

    2.2) Since Oracle does combine B*tree indexes on seperate columns, how can we judge the selectivity of composite index and separate column index?

    In your previous post, you said the one offers better selectivity usually works better.
    It is hard to explain. Of course, if you combine two single column indexes, their selectivity is exatly the same as the selectivity of the composite index on the same two columns. But it is also obvious that in your example a composite index would be more efficient, simply because oracle would not need to do those "bitmap to/from rowids" conversion and "bitmap and" operations - single "index range scan" would definitely be more efficient.

    But probably you should not judge the justification of an index by a single query. You must get the whole picture of the system and your application and see if a particular index would be efficient in as many queries as possible. And at the same time it should not be too much of a burden for the system to maintain it.

    3) In my situation, I can't put hint on my queries since they are generated by Crystal report during runtime. So what can I do if CBO didn't use index even if the number of rows returned is <<5% of the total rows in that partition?
    FYI -- they are local partitioned index, and have been analyzed.

    How can I change DB_File_Multiple_read_count to make CBO change its mind?
    Well if you set DB_FILE_MULTIPLE_READ_COUNT as low as possible (I'm not sure if 0 is a valid setting, but 1 definitely is) this will definitely bias CBO in favour of using indexes as opposed to full table scans. Moreover, you can very much influence on CBO to favour index usage with OPTIMIZER_INDEX_CACHING and OPTIMIZER_INDEX_COST_ADJ SETTINGS. Check the document about CBO that I recomended to you in one of my previous answers for the details. http://www.evdbt.com/SearchIntelligenceCBO.doc
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  8. #18
    Join Date
    May 2001
    Posts
    285

    Thumbs up

    Jurij -- you are so cool!

    I will definitely check out the CBO artical you recommended, hopefully it will help me understand it more.

    Have a good weekend...

    Elaine

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