I had an interesting discussion in a class recently. There was a belief by several folks in the
class that the Oracle’s optimizer could use more than one B-Tree index for retrieving
data from a table. Other than converting
them into bit-maps, no it can’t. (Yes
there is a CONCATENATION plan that can do this, but it’s rather inefficient
and unlikely to be used by the optimizer, it’s more or less deprecated since
10.2.)
For each access into a table in a plan it’s either going to
use one index, or a full table scan. If
a table is access multiple times in a plan each time it could be using a
different access method. A query with a
subquery doing a correlated self-join for example.
This is a simple query to illustrate the point. This is a select from the employees
table. To make it slightly more interesting
the employees table has over 27,000 rows in it. It’s just multiple copies of the standard
employees table (which has 107 rows). If you want the code to create this larger
version of employees drop me a line and I’ll send it to you.
First I’m telling the optimizer to use both these indexes on
this one table. The order of the hints doesn’t
matter by the way; it picks the same index even if you switch the order. The optimizer did evaluate both indexes and
pick the one that had the lower cost. The cost for EMP_JOB was 214, for
EMP_DEPT
was 218 in this case. For the plan I’m
just showing the barest of details just for space. I used DBMS_XPLAN.DISPLAY_CURSOR to show
the plan, these are execution plans not explain plans.
select /*+
index(emp emp_dept) index(emp emp_job) */
manager_id
from employees emp
where department_id = 50
and job_id = 'ST_MAN'
/
Plan hash value: 3308778001
---------------------------------------------------------
| Id |
Operation |
Name |
---------------------------------------------------------
| 0 |
SELECT STATEMENT
| |
|* 1
| TABLE ACCESS BY INDEX ROWID BATCHED|
EMPLOYEES |
|* 2
| INDEX RANGE SCAN | EMP_JOB |
---------------------------------------------------------
Predicate Information (identified by operation
id):
---------------------------------------------------
1 -
filter("DEPARTMENT_ID"=50)
2 -
access("JOB_ID"='ST_MAN')
Now if I have one index on both columns (a concatenated index),
it can use that one to scan for the two predicates in the index (BTW the cost of this was 200 for those that might be curious.):
select /*+
index(emp emp_dept_job) */
manager_id
from employees emp
where department_id = 50
and job_id = 'ST_MAN'
/
Plan hash value: 2068448379
------------------------------------------------------------
| Id |
Operation |
Name |
------------------------------------------------------------
| 0 |
SELECT STATEMENT
| |
| 1
| TABLE ACCESS BY INDEX ROWID BATCHED|
EMPLOYEES |
|* 2
| INDEX RANGE SCAN | EMP_DEPT_JOB |
------------------------------------------------------------
Predicate Information (identified by
operation id):
---------------------------------------------------
2 -
access("DEPARTMENT_ID"=50 AND "JOB_ID"='ST_MAN')
It’s a Highlander thing, there
can only be one. The optimizer can’t
use more than one B-Tree index at a time to access a given table. This is why the right multi-column index can
be so important. For a normal B-Tree
index, getting the correct set of columns in the right order is a critical
aspect of performance.
Some notes about this
test. I did turn off bit map
conversions with:
alter session set
"_b_tree_bitmap_plans" = false;
With this set to the default of true it did do the bitmap conversion
of the two single column indexes into a bit map index for each column. Then
did an “AND”
operation between the two bit-map indexes. Lastly it converted the results back
to rowids to find the rows in the table.
This can appear to be a good plan; however it can be problematic with
the memory used and the time it takes to do all the conversions.
Also with no hints the optimizer went with a full table scan
each time and really for this plan it was the best, it did the fewest over all
LIOs and Buffer Pins. I used the hints
to illustrate that the optimizer can only use one index to access a given table
at a time.
Database used for this test:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 -
64bit Production
