Wednesday, August 8, 2018

Explain Plan for knowing the Query performance

SELECT * FROM DEPT_TEST1;

explain plan for
select * from dept_test1 where deptno = 10;

select * from table(dbms_xplan.display());

Plan hash value: 4123923436

--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |     1 |    14 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| DEPT_TEST1 |     1 |    14 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("DEPTNO"=10)

create index idx on dept_test1(deptno);

explain plan for
select * from dept_test1 where deptno = 10;

select * from table(dbms_xplan.display());

Plan hash value: 3021753262

--------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |            |     1 |    14 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| DEPT_TEST1 |     1 |    14 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | IDX        |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("DEPTNO"=10)

No comments:

Post a Comment