Monday, November 16, 2015

You only get one

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

No comments:

Post a Comment