In this blog post, we will put together a
slightly more complex SQL example in relation to the current implementation of
Oracle Automatic Indexing.
We will begin by creating three table, a
larger table “demo013a_t1” and two smaller table “demo013a_t2” and “demo013a_t3”
lookup table, each got created with only a primary key and currently have no
secondary indexes.
ai_demo@PDB19> create
table demo013a_t1
2 nologging as
3 select rownum as id,
4 mod(rownum,1000)+1 as x1,
5 mod(rownum,200000)+1 as x2,
6 'some_really_big_text'||rownum as some_data
7 from all_objects a, all_users
8 where rownum <= 1000000;
Table created.
ai_demo@PDB19> alter table
demo013a_t1
2 add constraint demo013a_t1_pk
3 primary key(id) ;
Table altered.
ai_demo@PDB19> create
table demo013a_t2
2 nologging as
3 select rownum as id,
4 mod(rownum,100)+1 as x1,
5 'some_more_stuff_goes_here'||rownum as name2
6 from all_objects
7 where rownum <= 10000;
Table created.
ai_demo@PDB19> alter table
demo013a_t2
2 add constraint demo013a_t2_pk
3 primary key(id) ;
Table altered.
ai_demo@PDB19>
ai_demo@PDB19> create table demo013a_t3
2 nologging as
3 select rownum as id,
4 mod(rownum,500)+1 as x1,
5 'some_more_stuff_goes_here_again'||rownum as name2
6 from all_objects
7 where rownum <= 1000;
Table created.
ai_demo@PDB19> alter table
demo013a_t3
2 add constraint demo013a_t3_pk
3 primary key(id) ;
Table altered.
We will next run the following “complex”
query a number of times:
ai_demo@PDB19> set
autotrace traceonly exp statistics
ai_demo@PDB19> select t1.x1, t1.x2, t1.some_data, t2.name2
2 from demo013a_t1 t1,
3 demo013a_t2 t2
4 where t1.x1 = t2.id
5 and t1.x2 in ( select t3.id from demo013a_t3 t3 where t3.x1 = 42 );
10 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3852922303
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 780 | 1779 (2)| 00:00:01 |
| 1 | NESTED LOOPS | | 10 | 780 | 1779 (2)| 00:00:01 |
| 2 | NESTED LOOPS | | 10 | 780 | 1779 (2)| 00:00:01 |
|* 3 | HASH JOIN | | 10 | 440 | 1769 (2)| 00:00:01 |
| 4 | JOIN FILTER CREATE | :BF0000 | 2 | 16 | 4 (0)| 00:00:01 |
|* 5 | TABLE ACCESS STORAGE FULL| DEMO013A_T3 | 2 | 16 | 4 (0)| 00:00:01 |
| 6 | JOIN FILTER USE | :BF0000 | 1000K| 34M| 1760 (1)| 00:00:01 |
|* 7 | TABLE ACCESS STORAGE FULL| DEMO013A_T1 | 1000K| 34M| 1760 (1)| 00:00:01 |
|* 8 | INDEX UNIQUE SCAN | DEMO013A_T2_PK | 1 | | 0 (0)| 00:00:01 |
| 9 | TABLE ACCESS BY INDEX ROWID | DEMO013A_T2 | 1 | 34 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information
(identified by operation id):
---------------------------------------------------
3 -
access("T1"."X2"="T3"."ID")
5 - storage("T3"."X1"=42)
filter("T3"."X1"=42)
7 - storage(SYS_OP_BLOOM_FILTER(:BF0000,"T1"."X2"))
filter(SYS_OP_BLOOM_FILTER(:BF0000,"T1"."X2"))
8 - access("T1"."X1"="T2"."ID")
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
6375 consistent gets
0 physical reads
0 redo size
1200 bytes sent via SQL*Net to client
546 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
ai_demo@PDB19> set
autotrace off
we note there are three predicates listed
in which a column access could potentially benefit from an index being created.
t1.x1 = t2.id ( note that
table T2.ID already got a unique index on it)
t1.x2 in ( select … )
t3.x1 = 42
Let’s have a look at the corresponding AI
report to see what indexing would be suggested by AI process.
ai_demo@PDB19> select
dbms_auto_index.report_activity( activity_start=> systimestamp - 1/24 )
report from dual;
REPORT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GENERAL INFORMATION
-------------------------------------------------------------------------------
Activity start : 22-MAR-2022 07:08:04
Activity end : 22-MAR-2022 08:08:04
Executions completed : 4
Executions interrupted : 0
Executions with fatal error : 0
-------------------------------------------------------------------------------
SUMMARY (AUTO INDEXES)
-------------------------------------------------------------------------------
Index candidates : 6
Indexes created (visible / invisible) : 2 (2 / 0)
Space used (visible / invisible) : 15.79 MB (15.79 MB / 0 B)
Indexes dropped : 0
SQL statements verified : 1
SQL statements improved (improvement factor) : 1 (213.3x)
SQL plan baselines created : 0
Overall improvement factor : 213.3x
-------------------------------------------------------------------------------
We note there are 6 index candidates that
were considered, BUT only 2 new indexes were actually created. Overall, the
created indexes resulted in an estimated 213.3x improvement in the above SQL
performance.
INDEX DETAILS
-------------------------------------------------------------------------------
1. The following indexes were created:
-------------------------------------------------------------------------------
----------------------------------------------------------------------------
| Owner | Table | Index | Key | Type | Properties |
----------------------------------------------------------------------------
| AI_DEMO | DEMO013A_T1 | SYS_AI_fpb323xg3yzjy | X2 | B-TREE | NONE |
| AI_DEMO | DEMO013A_T3 | SYS_AI_c114rytxdgvc5 | X1 | B-TREE | NONE |
----------------------------------------------------------------------------
-------------------------------------------------------------------------------
VERIFICATION DETAILS
-------------------------------------------------------------------------------
1. The performance of the following statements improved:
-------------------------------------------------------------------------------
Parsing Schema Name : AI_DEMO
SQL ID : gw9c64b5ardfp
SQL Text : select t1.x1, t1.x2, t1.some_data, t2.name2 from
demo013a_t1 t1, demo013a_t2 t2 where t1.x1 = t2.id and
t1.x2 in ( select t3.id from demo013a_t3 t3 where t3.x1 =
42 )
Improvement Factor : 213.3x
Execution Statistics:
-----------------------------
Original Plan Auto Index Plan
---------------------------- ----------------------------
Elapsed Time (s): 239254 2725
CPU Time (s): 225255 1933
Buffer Gets: 25597 43
Disk Reads: 2 4
Direct Writes: 0 0
Rows Processed: 40 10
Executions: 4 1
We note the report states the two new
indexes are created on the DEMO013A_T1.X2 and DEMO013A_T3.X1 columns.
If we look further down in the report and
compare the before and after execution plans in the plan section
PLANS SECTION
---------------------------------------------------------------------------------------------
- Original
-----------------------------
Plan Hash Value : 3852922303
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1779 | |
| 1 | NESTED LOOPS | | 10 | 780 | 1779 | 00:00:01 |
| 2 | NESTED LOOPS | | 10 | 780 | 1779 | 00:00:01 |
| 3 | HASH JOIN | | 10 | 440 | 1769 | 00:00:01 |
| 4 | JOIN FILTER CREATE | :BF0000 | 2 | 16 | 4 | 00:00:01 |
| 5 | TABLE ACCESS STORAGE FULL | DEMO013A_T3 | 2 | 16 | 4 | 00:00:01 |
| 6 | JOIN FILTER USE | :BF0000 | 1000000 | 36000000 | 1760 | 00:00:01 |
| 7 | TABLE ACCESS STORAGE FULL | DEMO013A_T1 | 1000000 | 36000000 | 1760 | 00:00:01 |
| 8 | INDEX UNIQUE SCAN | DEMO013A_T2_PK | 1 | | 0 | |
| 9 | TABLE ACCESS BY INDEX ROWID | DEMO013A_T2 | 1 | 34 | 1 | 00:00:01 |
------------------------------------------------------------------------------------------------
- With Auto Indexes
-----------------------------
Plan Hash Value : 1751176333
----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 780 | 27 | 00:00:01 |
| 1 | NESTED LOOPS | | 10 | 780 | 27 | 00:00:01 |
| 2 | NESTED LOOPS | | 10 | 780 | 27 | 00:00:01 |
| 3 | NESTED LOOPS | | 10 | 440 | 17 | 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID BATCHED | DEMO013A_T3 | 2 | 16 | 3 | 00:00:01 |
| * 5 | INDEX RANGE SCAN | SYS_AI_c114rytxdgvc5 | 2 | | 1 | 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID BATCHED | DEMO013A_T1 | 5 | 180 | 7 | 00:00:01 |
| * 7 | INDEX RANGE SCAN | SYS_AI_fpb323xg3yzjy | 5 | | 2 | 00:00:01 |
| * 8 | INDEX UNIQUE SCAN | DEMO013A_T2_PK | 1 | | 0 | |
| 9 | TABLE ACCESS BY INDEX ROWID | DEMO013A_T2 | 1 | 34 | 1 | 00:00:01 |
----------------------------------------------------------------------------------------------------------
Predicate Information
(identified by operation id):
------------------------------------------
* 5 - access("T3"."X1"=42)
* 7 - access("T1"."X2"="T3"."ID")
* 8 - access("T1"."X1"="T2"."ID")
We can see the new plan uses both the new
AI and has a new improved cost of 27 (down from 1779)
If we look at all the indexes that got
created on these tables:
ai_demo@PDB19> select
table_name,index_name,tablespace_name,compression,
2 status,visibility,indexing,auto
3 from user_indexes
4 where table_name like 'DEMO013A%'
5 order by 1,2;
TABLE_NAME INDEX_NAME TABLESPACE_NAME COMPRESSION
STATUS VISIBILIT INDEXIN AUT
--------------- -------------------- ---------------- ------------- -------- --------- ------- ---
DEMO013A_T1 DEMO013A_T1_PK TS_INDEX_DEMO DISABLED VALID VISIBLE FULL NO
DEMO013A_T1 SYS_AI_c0byf1thgqjxs TS_INDEX_DEMO ADVANCED LOW UNUSABLE INVISIBLE FULL YES
DEMO013A_T1 SYS_AI_fpb323xg3yzjy TS_INDEX_DEMO ADVANCED LOW VALID VISIBLE FULL YES
DEMO013A_T2 DEMO013A_T2_PK TS_INDEX_DEMO DISABLED VALID VISIBLE FULL NO
DEMO013A_T3 DEMO013A_T3_PK TS_INDEX_DEMO DISABLED VALID VISIBLE FULL NO
DEMO013A_T3 SYS_AI_c114rytxdgvc5 TS_INDEX_DEMO ADVANCED LOW VALID VISIBLE FULL YES
6 rows selected.
ai_demo@PDB19> select
table_name,index_name,column_name,column_position
2 from user_ind_columns
3 where table_name like 'DEMO013A%'
4 order by 1,2,3;
TABLE_NAME INDEX_NAME COLUMN_NAM COLUMN_POSITION
------------ --------------------- ---------- ---------------
DEMO013A_T1 DEMO013A_T1_PK ID 1
DEMO013A_T1 SYS_AI_c0byf1thgqjxs X1 1
DEMO013A_T1 SYS_AI_fpb323xg3yzjy X2 1
DEMO013A_T2 DEMO013A_T2_PK ID 1
DEMO013A_T3 DEMO013A_T3_PK ID 1
DEMO013A_T3 SYS_AI_c114rytxdgvc5 X1 1
However there is also an AI defined on
the other potential indexed column “DEMO013A_T1. X1” called “SYS_AI_c0byf1thgqjxs” that has been left in the INVISIBLE
and UNUSABLE state. This index has been shown to be ineffective in improving
SQL performance and have been converted back to an UNUSABLE state.
If we run the query again and look at the
resultant execution plan:
ai_demo@PDB19> set
autotrace traceonly exp statistics
ai_demo@PDB19> select t1.x1, t1.x2, t1.some_data, t2.name2
2 from demo013a_t1 t1,
3 demo013a_t2 t2
4 where t1.x1 = t2.id
5 and t1.x2 in ( select t3.id from demo013a_t3 t3 where t3.x1 = 42 );
10 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1751176333
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 780 | 27 (0)|
| 1 | NESTED LOOPS | | 10 | 780 | 27 (0)|
| 2 | NESTED LOOPS | | 10 | 780 | 27 (0)|
| 3 | NESTED LOOPS | | 10 | 440 | 17 (0)|
| 4 | TABLE ACCESS BY INDEX ROWID BATCHED| DEMO013A_T3 | 2 | 16 | 3 (0)|
|* 5 | INDEX RANGE SCAN | SYS_AI_c114rytxdgvc5 | 2 | | 1 (0)|
| 6 | TABLE ACCESS BY INDEX ROWID BATCHED| DEMO013A_T1 | 5 | 180 | 7 (0)|
|* 7 | INDEX RANGE SCAN | SYS_AI_fpb323xg3yzjy | 5 | | 2 (0)|
|* 8 | INDEX UNIQUE SCAN | DEMO013A_T2_PK | 1 | | 0 (0)|
| 9 | TABLE ACCESS BY INDEX ROWID | DEMO013A_T2 | 1 | 34 | 1 (0)|
----------------------------------------------------------------------------------------------------
Predicate Information
(identified by operation id):
---------------------------------------------------
5 - access("T3"."X1"=42)
7 - access("T1"."X2"="T3"."ID")
8 - access("T1"."X1"="T2"."ID")
Note
-----
- this is an adaptive plan
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
45 consistent gets
0 physical reads
0 redo size
950 bytes sent via SQL*Net to client
546 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
ai_demo@PDB19> set
autotrace off
we note the new execution plan uses both
the newly created AI and at just 45 consistent gets, is significantly more
efficient than it was previously where it required 6375 consistent gets.
So the Auto Indexing process can create
any number of possible indexes for a particular query and may independently
ultimately determine different states for the various candidate indexes and so
only create and keep the necessary indexes to sufficiently improve an SQL.
We now have an SQL statement that
automatic runs much more efficiently without human intervention thanks to these
automatically created indexes…
2 nologging as
3 select rownum as id,
4 mod(rownum,1000)+1 as x1,
5 mod(rownum,200000)+1 as x2,
6 'some_really_big_text'||rownum as some_data
7 from all_objects a, all_users
8 where rownum <= 1000000;
2 add constraint demo013a_t1_pk
3 primary key(id) ;
2 nologging as
3 select rownum as id,
4 mod(rownum,100)+1 as x1,
5 'some_more_stuff_goes_here'||rownum as name2
6 from all_objects
7 where rownum <= 10000;
2 add constraint demo013a_t2_pk
3 primary key(id) ;
ai_demo@PDB19> create table demo013a_t3
2 nologging as
3 select rownum as id,
4 mod(rownum,500)+1 as x1,
5 'some_more_stuff_goes_here_again'||rownum as name2
6 from all_objects
7 where rownum <= 1000;
2 add constraint demo013a_t3_pk
3 primary key(id) ;
ai_demo@PDB19> select t1.x1, t1.x2, t1.some_data, t2.name2
2 from demo013a_t1 t1,
3 demo013a_t2 t2
4 where t1.x1 = t2.id
5 and t1.x2 in ( select t3.id from demo013a_t3 t3 where t3.x1 = 42 );
----------------------------------------------------------
Plan hash value: 3852922303
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 780 | 1779 (2)| 00:00:01 |
| 1 | NESTED LOOPS | | 10 | 780 | 1779 (2)| 00:00:01 |
| 2 | NESTED LOOPS | | 10 | 780 | 1779 (2)| 00:00:01 |
|* 3 | HASH JOIN | | 10 | 440 | 1769 (2)| 00:00:01 |
| 4 | JOIN FILTER CREATE | :BF0000 | 2 | 16 | 4 (0)| 00:00:01 |
|* 5 | TABLE ACCESS STORAGE FULL| DEMO013A_T3 | 2 | 16 | 4 (0)| 00:00:01 |
| 6 | JOIN FILTER USE | :BF0000 | 1000K| 34M| 1760 (1)| 00:00:01 |
|* 7 | TABLE ACCESS STORAGE FULL| DEMO013A_T1 | 1000K| 34M| 1760 (1)| 00:00:01 |
|* 8 | INDEX UNIQUE SCAN | DEMO013A_T2_PK | 1 | | 0 (0)| 00:00:01 |
| 9 | TABLE ACCESS BY INDEX ROWID | DEMO013A_T2 | 1 | 34 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
---------------------------------------------------
5 - storage("T3"."X1"=42)
filter("T3"."X1"=42)
7 - storage(SYS_OP_BLOOM_FILTER(:BF0000,"T1"."X2"))
filter(SYS_OP_BLOOM_FILTER(:BF0000,"T1"."X2"))
8 - access("T1"."X1"="T2"."ID")
----------------------------------------------------------
0 recursive calls
0 db block gets
6375 consistent gets
0 physical reads
0 redo size
1200 bytes sent via SQL*Net to client
546 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
t1.x2 in ( select … )
t3.x1 = 42
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GENERAL INFORMATION
-------------------------------------------------------------------------------
Activity start : 22-MAR-2022 07:08:04
Activity end : 22-MAR-2022 08:08:04
Executions completed : 4
Executions interrupted : 0
Executions with fatal error : 0
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
Index candidates : 6
Indexes created (visible / invisible) : 2 (2 / 0)
Space used (visible / invisible) : 15.79 MB (15.79 MB / 0 B)
Indexes dropped : 0
SQL statements verified : 1
SQL statements improved (improvement factor) : 1 (213.3x)
SQL plan baselines created : 0
Overall improvement factor : 213.3x
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
1. The following indexes were created:
-------------------------------------------------------------------------------
----------------------------------------------------------------------------
| Owner | Table | Index | Key | Type | Properties |
----------------------------------------------------------------------------
| AI_DEMO | DEMO013A_T1 | SYS_AI_fpb323xg3yzjy | X2 | B-TREE | NONE |
| AI_DEMO | DEMO013A_T3 | SYS_AI_c114rytxdgvc5 | X1 | B-TREE | NONE |
----------------------------------------------------------------------------
-------------------------------------------------------------------------------
-------------------------------------------------------------------------------
1. The performance of the following statements improved:
-------------------------------------------------------------------------------
Parsing Schema Name : AI_DEMO
SQL ID : gw9c64b5ardfp
SQL Text : select t1.x1, t1.x2, t1.some_data, t2.name2 from
demo013a_t1 t1, demo013a_t2 t2 where t1.x1 = t2.id and
t1.x2 in ( select t3.id from demo013a_t3 t3 where t3.x1 =
42 )
Improvement Factor : 213.3x
-----------------------------
Original Plan Auto Index Plan
---------------------------- ----------------------------
Elapsed Time (s): 239254 2725
CPU Time (s): 225255 1933
Buffer Gets: 25597 43
Disk Reads: 2 4
Direct Writes: 0 0
Rows Processed: 40 10
Executions: 4 1
---------------------------------------------------------------------------------------------
-----------------------------
Plan Hash Value : 3852922303
| Id | Operation | Name | Rows | Bytes | Cost | Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1779 | |
| 1 | NESTED LOOPS | | 10 | 780 | 1779 | 00:00:01 |
| 2 | NESTED LOOPS | | 10 | 780 | 1779 | 00:00:01 |
| 3 | HASH JOIN | | 10 | 440 | 1769 | 00:00:01 |
| 4 | JOIN FILTER CREATE | :BF0000 | 2 | 16 | 4 | 00:00:01 |
| 5 | TABLE ACCESS STORAGE FULL | DEMO013A_T3 | 2 | 16 | 4 | 00:00:01 |
| 6 | JOIN FILTER USE | :BF0000 | 1000000 | 36000000 | 1760 | 00:00:01 |
| 7 | TABLE ACCESS STORAGE FULL | DEMO013A_T1 | 1000000 | 36000000 | 1760 | 00:00:01 |
| 8 | INDEX UNIQUE SCAN | DEMO013A_T2_PK | 1 | | 0 | |
| 9 | TABLE ACCESS BY INDEX ROWID | DEMO013A_T2 | 1 | 34 | 1 | 00:00:01 |
------------------------------------------------------------------------------------------------
-----------------------------
Plan Hash Value : 1751176333
| Id | Operation | Name | Rows | Bytes | Cost | Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 780 | 27 | 00:00:01 |
| 1 | NESTED LOOPS | | 10 | 780 | 27 | 00:00:01 |
| 2 | NESTED LOOPS | | 10 | 780 | 27 | 00:00:01 |
| 3 | NESTED LOOPS | | 10 | 440 | 17 | 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID BATCHED | DEMO013A_T3 | 2 | 16 | 3 | 00:00:01 |
| * 5 | INDEX RANGE SCAN | SYS_AI_c114rytxdgvc5 | 2 | | 1 | 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID BATCHED | DEMO013A_T1 | 5 | 180 | 7 | 00:00:01 |
| * 7 | INDEX RANGE SCAN | SYS_AI_fpb323xg3yzjy | 5 | | 2 | 00:00:01 |
| * 8 | INDEX UNIQUE SCAN | DEMO013A_T2_PK | 1 | | 0 | |
| 9 | TABLE ACCESS BY INDEX ROWID | DEMO013A_T2 | 1 | 34 | 1 | 00:00:01 |
----------------------------------------------------------------------------------------------------------
------------------------------------------
* 5 - access("T3"."X1"=42)
* 7 - access("T1"."X2"="T3"."ID")
* 8 - access("T1"."X1"="T2"."ID")
2 status,visibility,indexing,auto
3 from user_indexes
4 where table_name like 'DEMO013A%'
5 order by 1,2;
--------------- -------------------- ---------------- ------------- -------- --------- ------- ---
DEMO013A_T1 DEMO013A_T1_PK TS_INDEX_DEMO DISABLED VALID VISIBLE FULL NO
DEMO013A_T1 SYS_AI_c0byf1thgqjxs TS_INDEX_DEMO ADVANCED LOW UNUSABLE INVISIBLE FULL YES
DEMO013A_T1 SYS_AI_fpb323xg3yzjy TS_INDEX_DEMO ADVANCED LOW VALID VISIBLE FULL YES
DEMO013A_T2 DEMO013A_T2_PK TS_INDEX_DEMO DISABLED VALID VISIBLE FULL NO
DEMO013A_T3 DEMO013A_T3_PK TS_INDEX_DEMO DISABLED VALID VISIBLE FULL NO
DEMO013A_T3 SYS_AI_c114rytxdgvc5 TS_INDEX_DEMO ADVANCED LOW VALID VISIBLE FULL YES
2 from user_ind_columns
3 where table_name like 'DEMO013A%'
4 order by 1,2,3;
------------ --------------------- ---------- ---------------
DEMO013A_T1 DEMO013A_T1_PK ID 1
DEMO013A_T1 SYS_AI_c0byf1thgqjxs X1 1
DEMO013A_T1 SYS_AI_fpb323xg3yzjy X2 1
DEMO013A_T2 DEMO013A_T2_PK ID 1
DEMO013A_T3 DEMO013A_T3_PK ID 1
DEMO013A_T3 SYS_AI_c114rytxdgvc5 X1 1
ai_demo@PDB19> select t1.x1, t1.x2, t1.some_data, t2.name2
2 from demo013a_t1 t1,
3 demo013a_t2 t2
4 where t1.x1 = t2.id
5 and t1.x2 in ( select t3.id from demo013a_t3 t3 where t3.x1 = 42 );
----------------------------------------------------------
Plan hash value: 1751176333
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 780 | 27 (0)|
| 1 | NESTED LOOPS | | 10 | 780 | 27 (0)|
| 2 | NESTED LOOPS | | 10 | 780 | 27 (0)|
| 3 | NESTED LOOPS | | 10 | 440 | 17 (0)|
| 4 | TABLE ACCESS BY INDEX ROWID BATCHED| DEMO013A_T3 | 2 | 16 | 3 (0)|
|* 5 | INDEX RANGE SCAN | SYS_AI_c114rytxdgvc5 | 2 | | 1 (0)|
| 6 | TABLE ACCESS BY INDEX ROWID BATCHED| DEMO013A_T1 | 5 | 180 | 7 (0)|
|* 7 | INDEX RANGE SCAN | SYS_AI_fpb323xg3yzjy | 5 | | 2 (0)|
|* 8 | INDEX UNIQUE SCAN | DEMO013A_T2_PK | 1 | | 0 (0)|
| 9 | TABLE ACCESS BY INDEX ROWID | DEMO013A_T2 | 1 | 34 | 1 (0)|
----------------------------------------------------------------------------------------------------
---------------------------------------------------
7 - access("T1"."X2"="T3"."ID")
8 - access("T1"."X1"="T2"."ID")
-----
- this is an adaptive plan
----------------------------------------------------------
0 recursive calls
0 db block gets
45 consistent gets
0 physical reads
0 redo size
950 bytes sent via SQL*Net to client
546 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
