- 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
- 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.
- Download Oracle SQL Developer 3.2 here.
Introduction
In this tutorial you will use the Optimizer Access Paths for the following cases (scenarios):
Hardware and Software Requirements
The following is a list of hardware and software requirements:
Prerequisites
Note: For best results, use Firefox or Chrome browsers to view this tutorial.
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.
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.
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.
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.
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.
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.
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.
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 cust_credit_limit column of the customers table.
CREATE BITMAP INDEX cust_cust_credit_limit_bidx 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.
To view the performance of a query using only bitmap index access, perform the following statements:
Connect to the sh schema. Drop all the indexes on the customers table except the primary key index.
DROP INDEX cust_cust_credit_limit_bidx;
Create a B* Tree index on the cust_credit_limit column of the customers 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.
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.
