index questions

[marist89]

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?

[pravin]

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.

[jmodic]

Originally posted by marist89
But can he juggle too?

Sure I can....

[elaine3839]

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

[marist89]

Originally posted by jmodic

Originally posted by marist89
But can he juggle too?

Sure I can....

I bow to the master....

[elaine3839]

Juggle masters ---

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

[jmodic]

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

[elaine3839]

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