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 ??
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.
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).
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.
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.
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?
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 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?
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."
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:
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
INDEX RANGE SCAN NONUNIQUE EMP_JOB
INDEX RANGE SCAN NONUNIQUE EMP_SAL
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.