Oracle virtual indexes
One of the areas I am strong at is using
the proper indexes to influence SQL statement access paths. We
know that an index can influence the optimizer to change an
access path from a tablescan to a table lookup using an index
ROWID. But indexes can also influence the type of join used as
well as the order of the tables being joined.
Let's take a look at the benefits virtual indexes have and we'll
finish this blog by reviewing how indexes can affect simple
access paths, join methods and join orders.
I use virtual indexes in both 9i and 10G. I like them because I
can quickly do "what if" scenarios. I can create the index and
present it to the optimizer without waiting for the index to
become populated and stored on disk. This feature allows me to
reduce the amount of time I spend tuning, which is always a good
I also use virtual indexes when I am training myself and others.
Most production environments do have test counterparts, but
there are times when I need to teach many different indexing
concepts and waiting for indexes to build often becomes a
detriment to the learning process.
9I OEM Virtual Index Wizard
The Virtual Index Wizard feature is provided in the Tuning Pack
option of 9I OEM. Although there are a couple of ways to
activate the wizard, let's take a look at the one that I most
often use. Since we are going to be navigating through the SQL
Analyze tool, we might as well review some of the other features
9I OEM provides the SQL Analyze tool to view access path
information. SQL Analyze is only available if you have purchased
the tuning pack option for 9I OEM. This screenshot shows me
navigating through 9I OEM's menus to activate SQL Analyze.
When you activate SQL Analyze, the first step the utility
performs is to display a listing of all of the top resource
consuming SQL statements that are contained in the library
cache. If the statement has been flushed from cache, you won't
find it in this display.
The tool lists all of the databases on the left side of the
panel that you worked with in the past. As you can see, I am a
pretty active user of SQL Analyze. I can double-click on any
statement in the top SQL listing to view its access path
information. I can also use the menu system at the top of the
screen to create a blank worksheet for new queries that I can
enter manually. I found the statement I wanted from the listing
and double-clicked on it to activate the SQL tuning panel.
The panel displays the SQL statement, and like our other
utilities, allows us to step through the access path the query
is taking. I can click on the SQL drop down menu at the top of
the screen to ask Oracle to explain the statement using the
various optimizer modes that are available.
SQL Analyze Tuning Tools
SQL Analyze provide us with tools that facilitate the SQL tuning
process. The tools menu at the top of the screen allows me to
choose from three different wizards. We'll review the
Virtual Index Wizard in just a moment.
The Hint Wizard allows us to choose hints
from a drop-down menu system, while the SQL Tuning Wizard
activates an intelligent advisor that provides us with SQL
tuning recommendations. Once you have run the SQL Tuning Wizard
a few times, it's pretty easy to see that it was the precursor
to some of the more advanced utilities that are now available in
Index Affects on Access Paths
Let's use a nested loop join as a quick and somewhat easy
example. In a nested loop join, one of the tables in the join is
known as the outer table while the other table (the table being
probed) is known as the inner table. Oracle reads a row from the
outer table and uses the columns in the join condition to probe
the inner table.
Take a look at the SQL statement and graphical access path
select a.employee_id, a.last_name, b.department_id,
c.street_address, c.postal_code, c.city, c.state_province
from hr.employees a, hr.departments b, hr.locations c
and a.employee_id = 174
order by a.last_name;
We are joining three tables together HR.EMPLOYEES,
HR.DEPARTMENTS and HR.LOCATIONS. Notice that we have two
different nested loop join operations. Oracle only joins two
tables together at a time. It then creates an intermediate
result set and uses that result set to join to the next table.
It can use any type of join method available for any of the join
The indexes we have available to us are:
EMP_EMP_ID_PK - index on the EMPLOYEE_ID column of the
DEPT_ID_PK - index on the DEPARTMENT_ID column of the
LOC_ID_PK - index on the LOCATION_ID of the HR. LOCATIONS
The optimizer has chosen to join the HR.EMPLOYEES AND
HR.DEPARTMENTS tables first using the nested loop join method.
It is on this join condition - A.DEPARTMENT_ID = B.DEPARTMENT_ID.
HR.EMPLOYEES is known as the outer table and HR.DEPARTMENTS is
classified as the inner table of the join.
The optimizer looked at the local predicate "A.EMPLOYEE_ID=174"
and checked to see if there was an index that it could use to
improve data access performance. It found the EMP_EMP_ID_PK
index that is defined on the EMPLOYEE_ID column. It will access
the index first and retrieve the ROWID for the row that has an
EMPLOYEE_ID of 174. It will use the ROWID to probe the
As stated previously, the optimizer has chosen the nested loop
join operation. It will access the HR.EMPLOYEES table using the
EMP_EMPID_PK index, retrieve the value for DEPARTMENT_ID and use
it to probe the HR.DEPARTMENTS table looking for a match (A.DEPARTMENT_ID
= B.DEPARTMENT_ID). If it finds a DEPARTMENT_ID value in the
HR.DEPARTMENTS table that matches, Oracle will use that row in
future operations. If it does not match, Oracle will discard it.
Notice that the optimizer has chosen to use the DEPT_ID_PK index
that is built on the HR.DEPARTMENTS' DEPARTMENT_ID column. Since
we are probing this table using the values we found for
DEPARTMENT_ID from the HR.EMPLOYEES table, Oracle will use the
index on the HR.DEPARTMENTS' DEPARTMENT_ID column.
The B.LOCATION_ID = C.LOCATION_ID is the join condition that is
used in the second join operation. The intermediate result set
is used as the outer table and the HR.LOCATIONS table is used as
the inner table. The same type of probe occurs. Since we are
looking for matches using the LOCATION_ID contained in the
intermediate result set, the optimizer will choose the LOC_ID_PK
Here's how indexes can influence the type of join used and the
order of the tables being joined:
If we didn't have indexes on either of the first two tables
being joined, the optimizer would most likely have used a
hash operation instead of a nested loop join to join the
tables together. The optimizer knows that the hash join
operation is best used for joins that return larger result
sets. It could also have influenced the join order of the
tables. In the example, the optimizer could have chosen to
join the HR.DEPARTMENTS table to the HR.LOCATIONS table
first. It would have then used the intermediate result set
created to probe the HR.EMPLOYEES table.
The more indexed columns we have on a table, the greater the
chance that the optimizer will use it as the outer table of
the nested loop join. It can filter out more rows before it
sends it to the join operation. The earlier it can filter
rows out, the better.
If we have an index on both join columns (i.e. indexes on
the DEPARTMENT_ID columns for both the HR.DEPARTMENTS and
HR.EMPLOYEES tables), Oracle will use the DEPARTMENT_ID
index on the inner table but not the outer. Why is that?
Oracle doesn't know if it has a DEPARTMENT_ID VALUE in the
inner table that matches a DEPARTMENT_ID value in the outer
table unless it reads each and every row of the outer table
to do the match. Think about it. It knows that since it has
to read every row from the outer table to perform the match,
the only indexes it can use are the indexes built on local
predicates (i.e. "A.EMPLOYEE_ID=174").
If we have an index on the join column for one table and not
the other, the optimizer will often favor the table that has
the index on the join column as the inner table of the join.
It knows it can't use an index on the join column if it is
the outer table but it can use the index if it is the inner.