What are the common execution plans in Oracle 03/28 Update SLTechnology News&Howtos

What are the common execution plans in Oracle

2025-03-28 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Shulou(Shulou.com)05/31 Report--

Editor to share with you what are the common implementation plans in Oracle, I believe most people do not know much about it, so share this article for your reference, I hope you can learn a lot after reading this article, let's go to know it!

Execution plan related to table access

There are two methods related to table access in Oracle database: full table scan and ROWID scan. As reflected in the execution plan, the key word in the execution plan corresponding to the full table scan is "TABLE ACCESS FULL", and the key word in the execution plan corresponding to the ROWID scan is "TABLE ACCESS BY USER ROWID" or "TABLE ACCESS BY INDEX ROWID".

Let's take a look at the execution plan related to table access, first execute the following SQL:

SQL > select empno,ename,rowid from emp where ename='TURNER'

EMPNO ENAME ROWID

7844 TURNER AAAVREAAEAAAACXAAJ

SQL > select * from table (dbms_xplan.display_cursor (null,null,'ALL'))

PLAN_TABLE_OUTPUT

SQL_ID3bjd8ps607cau, child number 0

Select empno,ename,rowid from emp where ename='TURNER'

Plan hash value: 3956160932

| | 0 | SELECT STATEMENT | 3 (100) | |

| | * 1 | TABLE ACCESS FULL | EMP | 1 | 22 | 3 (0) | 00:00:01 |

As can be seen from the above display, the execution plan of the target sql is a full table scan of the table EMP, and the corresponding keyword in the execution plan is "TABLE ACCESS FULL".

Rewrite the above sql to execute in the specified ROWID:

SQL > select empno,ename from emp where rowid='AAAVREAAEAAAACXAAJ'

EMPNO ENAME

7844 TURNER

SQL > select * from table (dbms_xplan.display_cursor (null,null,'ALL'))

PLAN_TABLE_OUTPUT

SQL_ID8n08pmh26ud05, child number 0

Select empno,ename from emp where rowid='AAAVREAAEAAAACXAAJ'

Plan hash value: 1116584662

| | 0 | SELECT STATEMENT | 1 (100) | | |

| | 1 | TABLE ACCESS BY USER ROWID | EMP | 1 | 22 | 1 (0) | 00:00:01 |

As can be seen from the above display, the execution plan of the sql now goes to the rowid scan of the table emp, corresponding to the execution plan keyword "TABLE ACCESS BY USER ROWID".

Note that the keyword for the execution plan corresponding to the ROWID scan may also be "TABLE ACCESS BY INDEX ROWID", depending on the ROWID source when accessing the table. If the ROWID is manually specified by the user (for example, "select empno,ename from emp where rowid='AAAVREAAEAAAACXAAJ'" specified above), the corresponding execution plan keyword is "TABLE ACCESS BY USER ROWID"; if the ROWID is derived from the index, the corresponding execution plan keyword is "TABLE ACCESS BY INDEX ROWID"

The primary key of the table EMP is the column EMPNO (that is, there is a primary key index on the column EMPNO). We rewrite the target sql into the following form:

SQL > select empno,ename from emp where empno=7369

EMPNO ENAME

7369 SMITH

SQL > select * from table (dbms_xplan.display_cursor (null,null,'ALL'))

PLAN_TABLE_OUTPUT

SQL_ID6yzqcfbz5xz3c, child number 0

Select empno,ename from emp where empno=7369

Plan hash value: 2949544139

| | 0 | SELECT STATEMENT | 1 (100) | | |

| | 1 | TABLE ACCESS BY INDEX ROWID | EMP | 1 | 10 | 1 (0) | 00:00:01 |

PLAN_TABLE_OUTPUT

| | * 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0) |

As can be seen from the above display, the execution plan of the target sql accesses the table emp by scanning the rowid of the table emp. Because the rowid here comes from the index PK_emp, the corresponding execution plan keyword is "TABLE ACCESS BY INDEX ROWID".

two。 Execution plan related to B-tree index

Common methods related to B-tree index access are: index unique scan, index range scan, index full scan, index fast full scan and index skip scan, which are reflected in the execution plan. The keyword corresponding to the index unique scan is "INDEX UNIQUE SCAN", the keyword corresponding to the index range scan is "INDEX RANGE SCAN", the keyword corresponding to the index full scan is "INDEX FULL SCAN", the keyword corresponding to the index fast full scan is "INDEX FAST FULL SCAN", and the keyword corresponding to the index skip scan is "INDEX SKIP SCAN".

Let's take a look at an example of an execution plan related to B-tree index access. Create a test table EMPLOYEE:

SQL > create table employee (gender varchar2 (1), employee_id number)

Table created.

SQL > insert into employee values ('Fleming,' 99')

1 row created.

SQL > insert into employee values ('Flying Magazine 100')

1 row created.

SQL > insert into employee values ('Maureen 101')

1 row created.

SQL > insert into employee values ('Maureen 102')

1 row created.

SQL > insert into employee values ('Maureen 103')

1 row created.

SQL > insert into employee values ('Maureen 104')

1 row created.

SQL > insert into employee values ('Maureen 105')

1 row created.

SQL > commit

Commit complete.

SQL > create unique index idx_uni_emp on employee (employee_id)

Index created.

SQL > select * from employee where employee_id=100

G EMPLOYEE_ID

F 100

SQL > select plan_table_output from table (dbms_xplan.display_cursor (null,null,'ALL'))

PLAN_TABLE_OUTPUT

SQL_IDbum8qv24s6tqp, child number 0

Select * from employee where employee_id=100

Plan hash value: 1887894887

| | 0 | SELECT STATEMENT | 1 (100) | | |

| | 1 | TABLE ACCESS BY INDEX ROWID | EMPLOYEE | 1 | 15 | 1 (0) | 00:00:01 |

PLAN_TABLE_OUTPUT

| | * 2 | INDEX UNIQUE SCAN | IDX_UNI_EMP | 1 | | 0 (0) |

From the above display, we can see that the execution plan of this sql takes the unique index scan of the index IDX_UNI_EMP, and the corresponding keyword in the execution plan is "INDEX UNIQUE SCAN".

Drop drops the above unique index IDX_UNI_EMP

SQL > drop index idx_uni_emp

Index dropped.

SQL > create index idx_emp_1 on employee (employee_id)

Index created.

SQL > select * from employee where employee_id=100

G EMPLOYEE_ID

F 100

SQL > select plan_table_output from table (dbms_xplan.display_cursor (null,null,'ALL'))

PLAN_TABLE_OUTPUT

SQL_IDbum8qv24s6tqp, child number 0

Select * from employee where employee_id=100

Plan hash value: 2428325319

| | 0 | SELECT STATEMENT | 2 (100) |

| | 1 | TABLE ACCESS BY INDEX ROWID | EMPLOYEE | 1 | 15 | 2 (0) | 00:00:01 |

PLAN_TABLE_OUTPUT

| | * 2 | INDEX RANGE SCAN | IDX_EMP_1 | 1 | | 1 (0) | 00:00:01 |

As can be seen from the above display, the current sql execution plan is to scan the index range of the index idx_emp_1, and the corresponding keyword in the execution plan is "INDEX RANGE SCAN".

The data in the truncate table EMPLOYEE:

SQL > truncate table employee

Table truncated.

Update insert 10000 records:

SQL > begin

2 for i in 1..5000 loop

3 insert into employee values ('Fairhead I)

4 end loop

5 commit

6 end

7 /

PL/SQL procedure successfully completed.

SQL > begin

2 for i in 5001..10000 loop

3 insert into employee values ('Mauremeni)

4 end loop

5 commit

6 end

7 /

PL/SQL procedure successfully completed.

SQL > select gender,count (*) from employee group by gender

G COUNT (*)

M5000

F5000

Collect statistics from table EMPLOYEE:

SQL > exec dbms_stats.gather_table_stats (ownname= > 'SCOTT',tabname= >' EMPLOYEE',estimate_percent= > 100 TRUE,no_invalidate= > false,method_opt= > 'FOR ALL COLUMNS SIZE 1')

PL/SQL procedure successfully completed.

SQL > set autotrace traceonly

SQL > show user

USER is "SCOTT"

SQL > select employee_id from employee

10000 rows selected.

Execution Plan

Plan hash value: 2119105728

| | 0 | SELECT STATEMENT | | 10000 | 40000 | 7 (0) | 00:00:01 |

| | 1 | TABLE ACCESS FULL | EMPLOYEE | 10000 | 40000 | 7 (0) | 00:00:01 |

Statistics

1 recursive calls

0 db block gets

689 consistent gets

0 physical reads

0 redo size

174308 bytes sent via SQL*Net to client

7850 bytes received via SQL*Net from client

668 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

10000 rows processed

It is clear that the above SQL query field employee_id can be obtained by scanning the index idx_emp_1, but oracle still chooses the full table scan of employee.

At this point, even if we use Hint to force oracle to scan the index idx_emp_1, we can see from the following results that oracle still chooses a full table scan of the table employee (that is, hint is invalid)

SQL > select / * index (employee idx_emp_1) * / employee_id from employee

10000 rows selected.

Execution Plan

Plan hash value: 2119105728

| | 0 | SELECT STATEMENT | | 10000 | 40000 | 7 (0) | 00:00:01 |

| | 1 | TABLE ACCESS FULL | EMPLOYEE | 10000 | 40000 | 7 (0) | 00:00:01 |

Statistics

1 recursive calls

0 db block gets

689 consistent gets

0 physical reads

0 redo size

174308 bytes sent via SQL*Net to client

7850 bytes received via SQL*Net from client

668 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

10000 rows processed

The above phenomenon occurs because oracle always guarantees the correctness of the target sql results anyway, and the execution path that may get the wrong results will not be considered by orale. For idx_emp_1, it is a single-key B-tree index in which the index NULL value is not stored. Once a null value appears in the column employee_id (although there is actually no null value here), the result of scanning the index IDX_EMP_1 will leave out those values whose employee_id is NULL, which means that if orale chooses to scan the index idx_emp_1 when executing the above sql. Then the result of the implementation may be incorrect. In this case, oracle certainly won't consider scanning idx_emp_1, even if we use Hint.

If we want oracle to perform the sql scan index idx_emp_1 described above, we must change the property of the column employee_id to not null. This is tantamount to telling oracle that there will be no null values on the column employee_id here, so you can safely scan idx_emp_1.

SQL > select employee_id from employee

10000 rows selected.

Execution Plan

Plan hash value: 3918702848

| | 0 | SELECT STATEMENT | | 10000 | 40000 | 7 (0) | 00:00:01 |

| | 1 | INDEX FAST FULL SCAN | IDX_EMP_1 | 10000 | 40000 | 7 (0) | 00:00:01 |

As can be seen from the above display, the execution plan of sql now follows idx_emp_1 's index fast full scan, and the corresponding keyword in the execution plan is "INDEX FAST FULL SCAN".

Now let's add the hint that forces the index IDX_EMP_1 to execute the sql again:

SQL > select / * + index (employee idx_emp_1) * / employee_id from employee

10000 rows selected.

Execution Plan

Plan hash value: 438557521

| | 0 | SELECT STATEMENT | | 10000 | 40000 | 20 (0) | 00:00:01 |

| | 1 | INDEX FULL SCAN | IDX_EMP_1 | 10000 | 40000 | 20 (0) | 00:00:01 |

From the above display, we can see that the execution plan of SQL now is the index full scan of index idx_emp_1, and the corresponding keyword in the execution plan is "INDEX FULL SCAN".

DROP drop single key value B-tree index IDX_EMP_1

SQL > drop index idx_emp_1

Index dropped.

SQL > create index index_emp_2 on employee (gender,employee_id)

Index created.

SQL > set autot trace

SQL > select * from employee where employee_id=101

Execution Plan

Plan hash value: 2052968723

| | 0 | SELECT STATEMENT | | 1 | 6 | 3 (0) | 00:00:01 |

| | * 1 | INDEX SKIP SCAN | INDEX_EMP_2 | 1 | 6 | 3 (0) | 00:00:01 |

Predicate Information (identified by operation id):

1-access ("EMPLOYEE_ID" = 101)

Filter ("EMPLOYEE_ID" = 101)

Statistics

0 recursive calls

0 db block gets

6 consistent gets

0 physical reads

0 redo size

600 bytes sent via SQL*Net to client

524 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

From the above display, we can see that now the execution plan of sql is the index jump scan of index IDX_EMP_2, and the corresponding keyword of index jump scan in the execution plan is "INDEX SKIP SCAN".

The above is all the contents of the article "what are the common implementation plans in Oracle". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.