Optimizer Access Paths

Overview

    Purpose

    This tutorial shows you the various access paths that the optimizer can use.

    Time to Complete

    Approximately 30 minutes.

    Introduction

    In this tutorial you will use the Optimizer Access Paths for the following cases (scenarios):

    • Case 1: With and Without Index
    • Case 2: Compare Single Column Index Access path
    • Case 3: Concatenated Index
    • Case 4: Bitmap Index Access
    • Case 5: Index Only Access
    • Case 6: Bitmap Index only Access
    • Case 7: B*Tree index only Access
    • Case 8: Function based index

    Hardware and Software Requirements

    The following is a list of hardware and software requirements:

    • Oracle Database 11g Enterprise Edition with access to the Tuning and Diagnostic management packs and with the sample schema installed.
    • Oracle SQL Developer 3.2.

    Prerequisites

    • Download Oracle SQL Developer 3.2 here.

    Note: For best results, use Firefox or Chrome browsers to view this tutorial.

Creating a Database Connection

    The first step to using the optimizer access path is to create a database connection.
    Perform the following steps to create a database connection:

    Click the SQL Developer 3.2 icon on your Desktop to start SQL Developer.


    Your Oracle SQL Developer 3.2 opens up.



    In the Connections navigator, right-click Connections and select New Connection.
    The New / Select Database Connection dialog opens. Enter the connection details as follows and click Test.

    Connection Name: hr_conn
    Username: hr
    Password: <your_password >(Select Save Password)
    Hostname: localhost
    SID: <your_own_SID> 


    Check for the status of the connection on the left-bottom side (above the Help button). It should read Success. Click Save and then click Connect.



Case 1: With and Without Index

    To view the difference in performance when using an index, versus when not, perform the following steps:

    Right-click hr_conn and select Open SQL Worksheet.

    Drop all the indexes on the employees table except  the primary key and unique key indexes ( *_PK, *_UK).

    drop index EMP_JOB_IX;
    drop index EMP_NAME_IX;
    drop index EMP_MANAGER_IX;
    drop index EMP_DEPARTMENT_IX;

    Autotrace the query.

    select * from employees where department_id = 10;

    Observe the output. You will notice there are no indexes on the employees table.

    The only possibility for the optimizer is to use a full scan to retrieve the rows. You can see that the full table scan takes a long time.


    To enhance the performance of the query in Step 1, create an index.

    create index emp_idx_dept_no on hr.employees(department_id) nologging compute statistics;


    Autotrace the query in Step 1 again and observe the output.


    You can see a significant improvement in performance. Note the difference in time, cost and physical reads.

Case 2: Comparing Single Column Index Access

    To compare single column index access, perform the following steps:

    Connect to  the hr schema. Drop all the indexes on the employees table except the primary key and unique key indexes ( *_PK, *_UK).

    drop index EMP_IDX_DEPT_NO;

    Aurotrace the query:

    SELECT /*+ FULL(e)*/e.*
    FROM employees e
    WHERE department_id = 10
    AND salary > 1000;

    Create two B*-tree indexes on department_id and salary column of the employees table.

    CREATE INDEX emp_dept_id_idx ON employees(department_id) NOLOGGING COMPUTE STATISTICS;
    CREATE INDEX emp_sal_idx ON employees(salary) NOLOGGING COMPUTE STATISTICS;

    To start monitoring the use of the employees index, run the following statements:

    ALTER INDEX emp_dept_id_idx MONITORING USAGE;
    ALTER INDEX emp_sal_idx MONITORING USAGE;

    Autotrace the query in Step 2:

    SELECT /*+ FULL(e)*/e.*
    FROM employees e
    WHERE department_id = 10
    AND salary > 1000;

    You will notice there are no indexes on the employees table.

    The only possibility for the optimizer is to use a full scan to retrieve the rows. You can see that the full table scan takes place again.

    Now Autotrace the query:

    SELECT /*+ INDEX_COMBINE(e)*/e.*
    FROM employees e
    WHERE department_id = 10
    AND salary > 1000;

    You will notice that this time the optimizer uses multiple indexes and combines them to access the table. The cost is lower than the full table scan.


Case 3: Concatenated Index

    To view the performance of a query using concatenated index, perform the following statements:

    Connect to  the hr schema. Drop all the indexes on the employees table except the primary key index.

    drop index EMP_DEPT_ID_IDX;
    drop index EMP_SAL_IDX;

    Create a concatenated index on department_id, salary, hire_date column of the employees table.

    CREATE INDEX emp_dept_id_sal_hiredt_idx
    ON employees(department_id,salary,hire_date)
    NOLOGGING COMPUTE STATISTICS;

    Autotrace the query:

    SELECT /*+INDEX(e)*/e.*
    FROM employees e
    WHERE department_id = 10
    AND salary > 1000
    AND hire_date between '13-JAN-07' AND '13-JAN-08';

    You will notice the optimizer uses concatenated index and the resulting cost is quite good.

    Autotrace the query:

    SELECT /*+INDEX(e)*/e.*
    FROM employees e
    WHERE department_id = 10
    AND salary > 1000;

    The query is quite similar to the previous step, but the predicate on HIRE_DATE is removed.

    The optimizer can still use the concatenated index.

    Autotrace the query:

    SELECT /*+INDEX(e)*/e.*
    FROM employees e
    WHERE salary > 1000
    AND hire_date between '13-JAN-07' AND '13-JAN-08';

    The leading part of the concatenated index is no longer a part of the query. However, the optimizer still uses the index by using a full index scan.

Case 4: Bitmap Index

    To view the performance of a query using bitmap index, perform the following statements:

    Connect to  the hr schema. Drop all the indexes on the employees table except the primary key index.

    drop index EMP_DEPT_ID_SAL_HIREDT_IDX;

    Create the following bitmap indexes:

    CREATE BITMAP INDEX emp_dept_id_bidx
    ON employees(department_id)
    NOLOGGING COMPUTE STATISTICS;

    CREATE BITMAP INDEX emp_sal_bidx
    ON EMPLOYEES(salary)
    NOLOGGING COMPUTE STATISTICS;

    CREATE BITMAP INDEX emp_hire_date_bidx
    ON employees(hire_date)
    NOLOGGING COMPUTE STATISTICS;

    Autotrace the query:

    SELECT /*+INDEX_COMBINE(e)*/e.*
    FROM employees e
    WHERE department_id = 10
    AND salary > 1000
    AND hire_date between '13-JAN-07' AND '13-JAN-08';

    You will notice that the query uses all the bitmap indexes to solve this query.

    However the cost is good. The cost is a little lower than the cost of the full table scan.

Case 5: Index Access Only

    To view the performance of a query using only index access, perform the following statements:

    Connect to  the hr schema. Drop all the indexes on the employees table except the primary key index.

    drop index EMP_DEPT_ID_BIDX;
    drop index EMP_SAL_BIDX;
    drop index EMP_HIRE_DATE_BIDX;

    Create an index on the first_name and salary columns of the employees table.

    CREATE INDEX emp_last_first_name_idx
    ON employees(last_name,first_name)
    NOLOGGING COMPUTE STATISTICS;

    Autotrace the query:

    SELECT e.last_name, e.first_name
    FROM employees e;

    You will observe that the optimizer can use the index to retrieve the entire select list without accessing the table itself. The cost is good.

Case 6: Bitmap Index Access Only

    To view the performance of a query using only bitmap index access, perform the following statements:

    In the Connections navigator, right-click Connections and select New Connection.
    The New / Select Database Connection dialog opens. Enter the connection details as follows and click Test.

    Connection Name: sh
    Username: sh
    Password: <your_password >(Select Save Password)
    Hostname: localhost
    SID: <your_own_SID> 


    Check for the status of the connection on the left-bottom side (above the Help button). It should read Success. Click Save and then click Connect.


    Drop all the indexes on the customers table except the primary key index and unique key index(*_PK, *_UK).


    DROP INDEX customers_gender_bix ;
    DROP INDEX cust_cust_credit_limit_idx;
    DROP INDEX cust_cust_postal_code_bidx;
    DROP INDEX emp_first_name_sal_idx;

    Create a bitmap index on the salary column of the employees table.

    CREATE BITMAP INDEX customers_cust_credit_limit ON CUSTOMERS(cust_credit_limit)
    NOLOGGING COMPUTE STATISTICS;

    Autotrace the query:

    SELECT count(*) credit_limit
    FROM CUSTOMERS
    WHERE cust_credit_limit=2000;

    You will notice that though salary is not a selective column, the COUNT operation on its bitmap index is very efficient.

Case 7: B*Tree Index Access Only

    To view the performance of a query using only bitmap index access, perform the following statements:

    Connect to  the hr schema. Drop all the indexes on the employees table except the primary key index.

    DROP INDEX cust_cust_credit_limit_bidx;

    Create a B* Tree index on the salary column of the employees table.

    CREATE  INDEX cust_cust_credit_limit_idx ON CUSTOMERS(cust_credit_limit)
    NOLOGGING COMPUTE STATISTICS;

    Autotrace the query:

    SELECT count(*) credit_limit
    FROM CUSTOMERS
    WHERE cust_credit_limit=2000;

    You will notice that the optimizer uses the B*Tree index; however this is less efficient compared to the corresponding bitmap index from the previous case.

Case 8: Function Based Index

    To view the performance of a query using only function based index, perform the following statements:

    Connect to  the hr schema. Drop all the indexes on the employees table except the primary key index.

    drop index EMP_SAL_BIDX;

    Create a B* Tree index on the first_name column of the employees table.

    CREATE  INDEX emp_fname_idx ON employees(first_name)
    NOLOGGING COMPUTE STATISTICS;

    Autotrace the query:

    SELECT employee_id, department_id
    FROM EMPLOYEES
    WHERE LOWER(first_name) like 's%';

    You will notice that though there is an index, it cannot be used because its column is modified by a function.

    To enhance the performance of this query, you can create a function based index:

    CREATE  INDEX emp_lower_fname_idx ON employees(LOWER(first_name));

    Autotrace the query in Step 3 again. You will notice the performance of the query is much better now.

Summary

    In this tutorial, you have learned how to use the Optimizer Access Paths for the following cases (scenarios):

    • Case 1: With and Without Index
    • Case 2: Compare Single Column Index Access path
    • Case 3: Concatenated Index
    • Case 4: Bitmap Index Access
    • Case 5: Index Only Access
    • Case 6: Bitmap Index only Access
    • Case 7: B*Tree index only Access
    • Case 8: Function based index

    Credits

    • Lead Curriculum Developers: Dimpi Sarmah, Sharon Sophia Stephen
    • Other Contributors: Ashley Chen, Swarnapriya Shridhar, Nancy Greenberg

To help navigate this Oracle by Example, note the following:

Hiding Header Buttons:
Click the Title to hide the buttons in the header. To show the buttons again, simply click the Title again.
Topic List Button:
A list of all the topics. Click one of the topics to navigate to that section.
Expand/Collapse All Topics:
To show/hide all the detail for all the sections. By default, all topics are collapsed
Show/Hide All Images:
To show/hide all the screenshots. By default, all images are displayed.
Print:
To print the content. The content currently displayed or hidden will be printed.

To navigate to a particular section in this tutorial, select the topic from the list.