e.g.
prepare example table:
SQL>
create table ep_employees as select * from employees
observe costs values:
SQL>
explain plan for select * from ep_employees where job_id = 'SA_MAN';
-- Explained.
SQL>
select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------
-------------------------
Plan hash value: 745119821
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 675 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EP_EMPLOYEES | 5 | 675 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("JOB_ID"='SA_MAN')
Note
-----
- dynamic sampling used for this statement (level=2)
Fix full access issue on ep_employees table:
SQL>
create index ep_emp_job_ix on ep_employees (job_id);
SQL>
select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
-------------------------
Plan hash value: 3994861717
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 675 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EP_EMPLOYEES | 5 | 675 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EP_EMP_JOB_IX | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("JOB_ID"='SA_MAN')
Note
-----
- dynamic sampling used for this statement (level=2)
Results:
Costs are reduced so sql query performance got increased.
prepare example table:
SQL>
create table ep_employees as select * from employees
observe costs values:
SQL>
explain plan for select * from ep_employees where job_id = 'SA_MAN';
-- Explained.
SQL>
select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------
-------------------------
Plan hash value: 745119821
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 675 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EP_EMPLOYEES | 5 | 675 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("JOB_ID"='SA_MAN')
Note
-----
- dynamic sampling used for this statement (level=2)
Fix full access issue on ep_employees table:
SQL>
create index ep_emp_job_ix on ep_employees (job_id);
SQL>
select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
-------------------------
Plan hash value: 3994861717
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 675 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| EP_EMPLOYEES | 5 | 675 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EP_EMP_JOB_IX | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("JOB_ID"='SA_MAN')
Note
-----
- dynamic sampling used for this statement (level=2)
Results:
Costs are reduced so sql query performance got increased.