I want to show the usage of an explain plan in Oracle. This will be shown in Oracle 12c. I will create a table and a sql statement. This sql statement will be analysed in a so-called explained plan that shows how Oracle will process the query. To do so, I first created a big table “Groot” in Oracle. Upon that I created an index “GrootIndex” with create index grootIndex on groot(n).
The explain plan is created with (select * from C##HR.GROOT where n=600000; being the sql statement) :
explain plan for select * from C##HR.GROOT where n=600000;
The results are then shown with:
select object_name, operation, options from plan_table order by id;
The results are:
OBJECT_NAME OPERATION OPTIONS SELECT STATEMENT GROOT TABLE ACCESS BY INDEX ROWID BATCHED GROOTINDEX INDEX RANGE SCAN
One might see that the sql uses the index. Based on the rowid =found herein, the sql accesses table Groot with the rid to retrieve the rows that were requested.