Oracle Live SQL - Script: #18c - optimizer_ignore

Statement 1

alter session set OPTIMIZER_IGNORE_HINTS=false

Statement processed.

Statement 2

create table DEMO(n primary key,v) as select rownum,rownum from xmltable('1 to 100000')

Table created.

Statement 3

explain plan for 
select /*+ index(DEMO) */ sum(n) from DEMO

Statement processed.

Statement 4

select * from dbms_xplan.display()


PLAN_TABLE_OUTPUT
Plan hash value: 2389417001
 
---------------------------------------------------------------------------------
| Id  | Operation        | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |              |     1 |     5 |   210   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE  |              |     1 |     5 |            |          |
|   2 |   INDEX FULL SCAN| SYS_C0065417 |   100K|   488K|   210   (1)| 00:00:01 |
---------------------------------------------------------------------------------

9 rows selected.

PLAN_TABLE_OUTPUT
Plan hash value: 2389417001

---------------------------------------------------------------------------------
\| Id \| Operation \| Name \| Rows \| Bytes \| Cost (%CPU)\| Time \|
---------------------------------------------------------------------------------
\| 0 \| SELECT STATEMENT \| \| 1 \| 5 \| 210 (1)\| 00:00:01 \|
\| 1 \| SORT AGGREGATE \| \| 1 \| 5 \| \| \|
\| 2 \| INDEX FULL SCAN\| SYS_C0065417 \| 100K\| 488K\| 210 (1)\| 00:00:01 \|
---------------------------------------------------------------------------------

Statement 5

alter session set OPTIMIZER_IGNORE_HINTS=true

Statement processed.

Statement 6

explain plan for 
select /*+ index(DEMO) */ sum(n) from DEMO

Statement processed.

Statement 7

select * from dbms_xplan.display()


PLAN_TABLE_OUTPUT
Plan hash value: 3274068422
 
----------------------------------------------------------------------------------------------
| Id  | Operation                     | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |              |     1 |     5 |    58   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE               |              |     1 |     5 |            |          |
|   2 |   INDEX STORAGE FAST FULL SCAN| SYS_C0065417 |   100K|   488K|    58   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

9 rows selected.

PLAN_TABLE_OUTPUT
Plan hash value: 3274068422

----------------------------------------------------------------------------------------------
\| Id \| Operation \| Name \| Rows \| Bytes \| Cost (%CPU)\| Time \|
----------------------------------------------------------------------------------------------
\| 0 \| SELECT STATEMENT \| \| 1 \| 5 \| 58 (0)\| 00:00:01 \|
\| 1 \| SORT AGGREGATE \| \| 1 \| 5 \| \| \|
\| 2 \| INDEX STORAGE FAST FULL SCAN\| SYS_C0065417 \| 100K\| 488K\| 58 (0)\| 00:00:01 \|
----------------------------------------------------------------------------------------------

Statement 8

alter session set OPTIMIZER_IGNORE_HINTS=false

Statement processed.

Statement 9

create table DEMO(n primary key,v) as select rownum,rownum from xmltable('1 to 100000')

ORA-00955: name is already used by an existing object

More Details: https://docs.oracle.com/error-help/db/ora-00955

Statement 10

explain plan for 
select /*+ index(DEMO) */ sum(n) from DEMO

Statement processed.

Statement 11

select * from dbms_xplan.display()


PLAN_TABLE_OUTPUT
Plan hash value: 2389417001
 
---------------------------------------------------------------------------------
| Id  | Operation        | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |              |     1 |     5 |   210   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE  |              |     1 |     5 |            |          |
|   2 |   INDEX FULL SCAN| SYS_C0065417 |   100K|   488K|   210   (1)| 00:00:01 |
---------------------------------------------------------------------------------

9 rows selected.

PLAN_TABLE_OUTPUT
Plan hash value: 2389417001

---------------------------------------------------------------------------------
\| Id \| Operation \| Name \| Rows \| Bytes \| Cost (%CPU)\| Time \|
---------------------------------------------------------------------------------
\| 0 \| SELECT STATEMENT \| \| 1 \| 5 \| 210 (1)\| 00:00:01 \|
\| 1 \| SORT AGGREGATE \| \| 1 \| 5 \| \| \|
\| 2 \| INDEX FULL SCAN\| SYS_C0065417 \| 100K\| 488K\| 210 (1)\| 00:00:01 \|
---------------------------------------------------------------------------------

Statement 12

alter session set OPTIMIZER_IGNORE_HINTS=true

Statement processed.

Statement 13

explain plan for 
select /*+ index(DEMO) */ sum(n) from DEMO

Statement processed.

Statement 14

select * from dbms_xplan.display()


PLAN_TABLE_OUTPUT
Plan hash value: 3274068422
 
----------------------------------------------------------------------------------------------
| Id  | Operation                     | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |              |     1 |     5 |    58   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE               |              |     1 |     5 |            |          |
|   2 |   INDEX STORAGE FAST FULL SCAN| SYS_C0065417 |   100K|   488K|    58   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

9 rows selected.

PLAN_TABLE_OUTPUT
Plan hash value: 3274068422

----------------------------------------------------------------------------------------------
\| Id \| Operation \| Name \| Rows \| Bytes \| Cost (%CPU)\| Time \|
----------------------------------------------------------------------------------------------
\| 0 \| SELECT STATEMENT \| \| 1 \| 5 \| 58 (0)\| 00:00:01 \|
\| 1 \| SORT AGGREGATE \| \| 1 \| 5 \| \| \|
\| 2 \| INDEX STORAGE FAST FULL SCAN\| SYS_C0065417 \| 100K\| 488K\| 58 (0)\| 00:00:01 \|
----------------------------------------------------------------------------------------------

Statement 15

alter session set OPTIMIZER_IGNORE_HINTS=false

Statement processed.

Statement 16

create table DEMO(n primary key,v) as select rownum,rownum from xmltable('1 to 100000')

ORA-00955: name is already used by an existing object

More Details: https://docs.oracle.com/error-help/db/ora-00955

Statement 17

explain plan for 
select /*+ index(DEMO) */ sum(n) from DEMO

Statement processed.

Statement 18

select * from dbms_xplan.display()


PLAN_TABLE_OUTPUT
Plan hash value: 2389417001
 
---------------------------------------------------------------------------------
| Id  | Operation        | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |              |     1 |     5 |   210   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE  |              |     1 |     5 |            |          |
|   2 |   INDEX FULL SCAN| SYS_C0065417 |   100K|   488K|   210   (1)| 00:00:01 |
---------------------------------------------------------------------------------

9 rows selected.

PLAN_TABLE_OUTPUT
Plan hash value: 2389417001

---------------------------------------------------------------------------------
\| Id \| Operation \| Name \| Rows \| Bytes \| Cost (%CPU)\| Time \|
---------------------------------------------------------------------------------
\| 0 \| SELECT STATEMENT \| \| 1 \| 5 \| 210 (1)\| 00:00:01 \|
\| 1 \| SORT AGGREGATE \| \| 1 \| 5 \| \| \|
\| 2 \| INDEX FULL SCAN\| SYS_C0065417 \| 100K\| 488K\| 210 (1)\| 00:00:01 \|
---------------------------------------------------------------------------------

Statement 19

alter session set OPTIMIZER_IGNORE_HINTS=true

Statement processed.

Statement 20

explain plan for 
select /*+ index(DEMO) */ sum(n) from DEMO

Statement processed.

Statement 21

select * from dbms_xplan.display()


PLAN_TABLE_OUTPUT
Plan hash value: 3274068422
 
----------------------------------------------------------------------------------------------
| Id  | Operation                     | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |              |     1 |     5 |    58   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE               |              |     1 |     5 |            |          |
|   2 |   INDEX STORAGE FAST FULL SCAN| SYS_C0065417 |   100K|   488K|    58   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

9 rows selected.

PLAN_TABLE_OUTPUT
Plan hash value: 3274068422

----------------------------------------------------------------------------------------------
\| Id \| Operation \| Name \| Rows \| Bytes \| Cost (%CPU)\| Time \|
----------------------------------------------------------------------------------------------
\| 0 \| SELECT STATEMENT \| \| 1 \| 5 \| 58 (0)\| 00:00:01 \|
\| 1 \| SORT AGGREGATE \| \| 1 \| 5 \| \| \|
\| 2 \| INDEX STORAGE FAST FULL SCAN\| SYS_C0065417 \| 100K\| 488K\| 58 (0)\| 00:00:01 \|
----------------------------------------------------------------------------------------------

Statement 22

alter session set OPTIMIZER_IGNORE_HINTS=false

Statement processed.

Statement 23

create table DEMO(n primary key,v) as select rownum,rownum from xmltable('1 to 100000')

ORA-00955: name is already used by an existing object

More Details: https://docs.oracle.com/error-help/db/ora-00955

Statement 24

explain plan for 
select /*+ index(DEMO) */ sum(n) from DEMO

Statement processed.

Statement 25

select * from dbms_xplan.display()


PLAN_TABLE_OUTPUT
Plan hash value: 2389417001
 
---------------------------------------------------------------------------------
| Id  | Operation        | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |              |     1 |     5 |   210   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE  |              |     1 |     5 |            |          |
|   2 |   INDEX FULL SCAN| SYS_C0065417 |   100K|   488K|   210   (1)| 00:00:01 |
---------------------------------------------------------------------------------

9 rows selected.

PLAN_TABLE_OUTPUT
Plan hash value: 2389417001

---------------------------------------------------------------------------------
\| Id \| Operation \| Name \| Rows \| Bytes \| Cost (%CPU)\| Time \|
---------------------------------------------------------------------------------
\| 0 \| SELECT STATEMENT \| \| 1 \| 5 \| 210 (1)\| 00:00:01 \|
\| 1 \| SORT AGGREGATE \| \| 1 \| 5 \| \| \|
\| 2 \| INDEX FULL SCAN\| SYS_C0065417 \| 100K\| 488K\| 210 (1)\| 00:00:01 \|
---------------------------------------------------------------------------------

Statement 26

alter session set OPTIMIZER_IGNORE_HINTS=true

Statement processed.

Statement 27

explain plan for 
select /*+ index(DEMO) */ sum(n) from DEMO

Statement processed.

Statement 28

select * from dbms_xplan.display()


PLAN_TABLE_OUTPUT
Plan hash value: 3274068422
 
----------------------------------------------------------------------------------------------
| Id  | Operation                     | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |              |     1 |     5 |    58   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE               |              |     1 |     5 |            |          |
|   2 |   INDEX STORAGE FAST FULL SCAN| SYS_C0065417 |   100K|   488K|    58   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

9 rows selected.

#18c - optimizer_ignore_hints.sql

Additional Information