index questions

I got a couple of index questions when I was tuning some sql statements.

1. Composite index
Suppose I have a composite index idx_comp(col1, col2, col3, col4), and my query's WHERE clause is like

WHERE col1=xx and col3=xx;

In this case, is col3 still being checked during the index scan OR col1 is the only one being checked since col2 is not in the where clause?

2. composite index v.s. individual column index
How do I tell which one is performed better than the other? By execution time or what? I looked at explain plan, but the cost over there seems irrelevant to the execution time for the query to execute.

3. logical read?
Since the physical read etc could be changed when we repeatly execute the same query, does Oracle have something like 'logical read' which provide objective measurable result which won't change for the same query, but will differ between different queries?

Thanks a lot!

1) only col1
to use an index, you need data to be ordered in this index (else the index is not useful). as far as the index is concerned, if you set col1, data will be ordered in col2, if you set col1 and col2, col3 will be ordered, but if you only set col1, col3 will NOT be ordered (was I clear ???)

2) consider that none is better as far as execution time is concerned ... one will be bigger that the other, anyway you shouldn't choose according to the performances, but according to what your needs will be, and to what your queries will be like ...

3) you mean a kind of performance index that would be comparable between queries ??

Thanks Pipo!

1) Very clear now.

2) Confused... If I shouldn't choose based on the performance, what should I based on? Can you elaborate a littel bit?

3) Yes, I am looking for a performance index if there is one available.

1)
Pipo's answer was not so clear to me as it was to you ;), but the answer is definitively: your index (col1,col2,col3,col4) will be used even if you choose only col1 and col3 in your WHERE condition. Of course, optimizer can decide that it would rather not used your index because it could execute the query more efficiently without using it, but this is not because you are not referencing col2.

To perform a query your index will be partly scanned ("range scan") based on col1 condition. Then for each key (c1, c2, c3, c4) retrieved it will check the condition on col3. Only for those keys that mach conditions on both col1 and col3 it will fetch the row from the table (if all wanted data can not be retrieved from the index itself).

2)
Of course, the ultimate goal will be to execute query as fast as possible (but of course, the cost of maintaining indexes during DML should also not be neglected). If you are in doubt if composite index will perform better than single column index then you should think of their selectivity for a particular query - the index that is more selective will generally result in faster execution of a query.

3)
You are correct, logical reads are exactly the measure you are looking for. In Oracle, the equation for logical reads is:
logical reads = db block gets + consistent gets

BTW, estimated logical reads is exactly the measure on which cost based optimizer decides which execution plan is the most efficient.

J.Modic absolutly right in point 2,3.
In point 1 he is absolutly right in case, when optimazer choose (or not choose) index and choose NESTED LOOP execution plan.
but, if optimazer choose HASH JOIN, as main operation , then execution plan may be another.
u only can see it, if u get execution plan, especialy for ur query.

Thanks everybody!

Jurij --

suppose the index is like ix_composite(col1,col2,col3,col4) and query like WHERE col1=xx and col3=xx, then

1) Now I know col3 will be checked by CBO anyway even if col2 is missing from the query's where clause. But will the query performs better if the index was like (col1, col3, ....) or they'll pretty much the same?

2) How to comparing the selectivity of ix_composite(col1,col2,col3,col4) with 4 separate indexes ix_col1, ix_col2, ix_col3, ix_col4? I would think if you combine all separate column indexes, its selectivity will be the same as the composite index, right? Or do you mean each of the individual index needs to be selective enough in order to be used by CBO? Also, do I need to worry about the merge process (for different index result) consumes too much time?

3) Where can I find more explanation on what db block gets and consistent gets really means? I do get them when I set autotrace on, while they are not available if I just do explain plan.

4) Talking about the cost column in explain plan, I've found 2 queries' explain plan have very different cost, but the execution time for them are roughly the same. So what does cost really tell us?

Thanks again...

1. Sure, query would perform better if index was created like (col1, col3,...) instead of (col1, col2, col3). Suppose your condition is:
... WHERE col1=1 AND col3=3....
Now suppose you have 10.000.000 records in that table. 1.000 records have col1=1, out of those only 1 record have also value col3=3. Now with index (col1, col2, col3) oracle will have to search all 1.000 index keys with col1=1 to find that single record that satisfies also col3=3. On the other hand with index (col1, col3) it will immediately find that single record that sattisfies both criteria.

2. Oracle can't combine multiple B*tree indexes in a single operation. With the above example, if you have separate single column indexes for each column c1 and c3, and your condition is again:
... WHERE col1=1 AND col3=3....
then oracle can use either index on col1 or col2, but not both together. So if you have 1.000 records that sattisfies col1=1 and 1.000 records that sattisfies condition col3=3, but only one record sattisfies both conditions col1=1 AND col3=3, the combined index on (col1, col3) or (col3,col1) will be much more selective and more efficient for that query.

3. For start, I think the explanation in the following url is sufficient: http://www.ixora.com.au/tips/tuning/cache_miss.htm

4. IMHO the cost does not give us any meaningfull information. Generaly you can't compare costs of two different queries (even if they produce the same result). Cost is meaningfull only to the cost based optimizer to choose the best from various possible execution plans *for the same* query. To find out more how CBO actually works, you can check the following document: http://www.evdbt.com/SearchIntelligenceCBO.doc

Thanks again Jurij!

1) Wonderful explanation, no question anymore :-)

2) I did some tests when I was tuning the queries running against the same big table. The original table has composite index on it while the 2nd table has separate column indexes on it. Both tables have exactly same sturcture and data.

Here is the explain plan for one of my queries running on the 2nd table(w/ seperate indexes):

COST Execution Plan
----- --------------------------------------------------
5029 SELECT STATEMENT
5029 TABLE ACCESS BY INDEX ROWID CALLDETAIL_BCP
BITMAP CONVERSION TO ROWIDS
BITMAP AND
BITMAP CONVERSION FROM ROWIDS
SORT ORDER BY
543 INDEX RANGE SCAN IDX_CONNECTED DATE
BITMAP CONVERSION FROM ROWIDS
28310 INDEX RANGE SCAN IDX_CALLDIRECTION

As you may find, both B*tree indexes idx_calldirection and idx_connecteddate are being used in this query.

The WHERE clause of the query looks like:

WHERE ConnectedDate >= to_date('2001-09-20 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
AND ConnectedDate < to_date('2001-09-21 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
AND CallDirection = 'Inbound'
AND RemoteNumber >= '+'
AND RemoteNumber <= '+99999999999999'
AND SiteId >= 0
AND SiteId <= 9999;

So does that mean Oracle does combine B*tree index? FYI -- I am using Oracle 9i, not sure if that's a new feature.

3) 95/5 rule question
Does Oracle's CBO always follow 95/5 rule that if the number of rows returned is less than 5% of the total number of rows then index will be used? During my test, I found many occasions that full table scan was performed even if the number of rows returned is less than 5%. Of course, I've analyzed the table to help CBO to make the right choice. Any idea why would that happen?

Thanks again!

2.)
I must correct myself. My statement: "Oracle can't combine multiple B*tree indexes in a single operation" was wrong. It can, your query proves it. BTW, do you also have any bitmap indexes on that table (eventhough they were not used in this particular execution path)? Namely, Oracle documentation states:

"If there is at least one bitmap index on the table, then the optimizer considers using a bitmap access path using regular B*-tree indexes for that table. This access path may involve combinations of B*-tree and bitmap indexes, but may not involve any bitmap indexes at all. However, the optimizer will not generate a bitmap access path using a single B*-tree index unless instructed to do so by a hint."
http://otn.oracle.com/docs/products/..._acce.htm#2506

Anyway, it is obvious that by converting rowdis from b*tree indexes into bitmaps and combining them with binary operations (AND, OR, ...) and then resulting bitmaps back to rowids, CBO can combine multiple single-column b*tree indexes. This "BITMAP CONVERSION FROM ROWIDS" operation is not new in 9i, it was also available in 8i (at least from release 2).

But to take back my initial claim even further: oracle was able to combine two single-column b*tree indexes even before "BITMAP CONVERSION FROM ROWIDS" was introduced. This was the case when it was able to use AND-EQUAL operation, like in the following example:

Code:

---

SELECT * FROM emp
WHERE job='SALESMAN' AND sal=1250;

Explain plan (with b*tree indexes on JOB and on SAL):
TABLE ACCESS BY ROWID EMP
    AND-EQUAL
        INDEX RANGE SCAN NONUNIQUE EMP_JOB
        INDEX RANGE SCAN NONUNIQUE EMP_SAL

---

3.)
No, 95/5 rule is not something fixed for CBO. It is more like some kind of rulle of thumb to predict if CBO will use index (or even to suggest to CBO with hint not to use one). This "general" ratio limit was very different in different releases of Oracle, drifting from 80/20 (or something like that) in the begining of CBO back in Oracle7, to 90/10, then 95/5 etc. In adition, this breaking ratio for CBO should also be very dependant on the setting of DB_FILE_MULTIPLE_READ_COUNT.

Besides, even CBO figures out it would have to retrieve all the rows from the table, it will stil use index if it can perform "fast full index scan". So 95/5 is nothing but a rule of thumb.

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

Quote:

---

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

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.

Quote:

---

Originally posted by marist89
But can he juggle too?

---

Sure I can....
http://www.theunholytrinity.org/crac...rol_wkiss2.gif

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

Quote:

---

Originally posted by jmodic

Quote:

---

Originally posted by marist89
But can he juggle too?

---

Sure I can....
http://www.dbasupport.com/forums/

---

I bow to the master....

Juggle masters ---

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

Quote:

---

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.

Quote:

---

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.

Quote:

---

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.

Quote:

---

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