середа, 17 травня 2017 р.

How to check what indexes are used in query for Oracle

Here I'm going to describe how to get execution plan result for specific query. For example we have a table with test data:

CREATE TABLE indexed_data (
  id NUMBER(19,0) NOT NULL CONSTRAINT indexed_data_pk PRIMARY KEY,
  data NUMBER(19,0) NOT NULL
);
CREATE INDEX indexed_data_data_inx ON indexed_data (
  data
) NOLOGGING;

insert into indexed_data(id, data) values (1, 1);
insert into indexed_data(id, data) values (2, 2);
insert into indexed_data(id, data) values (3, 3);

And want to know is indexed_data_data_inx index used in query:

SELECT * from indexed_data WHERE data = 0;

This two queries will helps you:

EXPLAIN PLAN
SET statement_id = 'indexed_data_ex_plan' FOR
SELECT * from indexed_data WHERE data = 0;
SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, 'indexed_data_ex_plan','BASIC'));

As result you will get:

Plan hash value: 1246571312

-------------------------------------------------------------
| Id  | Operation                                                | Name                                           |
-------------------------------------------------------------
|   0 | SELECT STATEMENT                          |                                                      |
|   1 |  TABLE ACCESS BY INDEX ROWID | INDEXED_DATA                      |
|   2 |   INDEX RANGE SCAN                        | INDEXED_DATA_DATA_INX |
-------------------------------------------------------------