Shulou(Shulou.com)06/01 Report--

This article introduces the common execution plans in the Oracle database, using the Oracle database version 11.2.0.1.

1. 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 keyword 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".

Scott@MYDB > select empno,ename,rowid from emp where ename='SCOTT'; EMPNO ENAME ROWID--7788 SCOTT AAAR3xAAEAAAACXAAH

Scott@MYDB > select empno,ename,rowid from emp where rowid='AAAR3xAAEAAAACXAAH'; EMPNO ENAME ROWID--7788 SCOTT AAAR3xAAEAAAACXAAH

Scott@MYDB > select empno,ename,rowid from emp where empno=7788; EMPNO ENAME ROWID--7788 SCOTT AAAR3xAAEAAAACXAAH

As can be seen from the experiment, the first SQL execution plan takes the full table scan of the table EMP, and the corresponding keyword is "TABLE ACCESS FULL"; the second SQL execution plan takes the ROWID scan of the table EMP, with the corresponding keyword "TABLE ACCESS BY USER ROWID"; and the third SQL execution plan takes the ROWID scan of the table EMP, and the corresponding keyword is "TABLE ACCESS BY INDEX ROWID". Note that if the ROWID is manually specified by the user, it corresponds to "TABLE ACCESS BY USER ROWID"; if the ROWID is derived from the index, it corresponds to "TABLE ACCESS BY INDEX ROWID".

2 execution plan related to B*Tree index

The common methods related to B*Tree index access in Oracle database include index unique scan, index range scan, index full scan, index fast full scan and index skip scan, which are reflected in the execution plan corresponding to INDEX UNIQUE SCAN, INDEX RANGE SCAN, INDEX FULL SCAN, INDEX FAST FULL SCAN and INDEX SKIP SCAN, respectively.

Use the experiment to view the relevant implementation plan

Zx@MYDB > create table employee (gender varchar2 (1), employee_id number); Table created.zx@MYDB > insert into employee values; 1 row created.zx@MYDB > insert into employee values; 1 row created.zx@MYDB > insert into employee values; 1 row created.zx@MYDB > insert into employee values; 1 row created.zx@MYDB > insert into employee values; 1 row created.zx@MYDB > insert into employee values 1 row created.zx@MYDB > insert into employee values; 1 row created.zx@MYDB > create unique index idx_uni_emp on employee (employee_id); Index created.zx@MYDB > select * from employee where employee_id=100;GEN EMPLOYEE_ID----F 100

The first SQL execution plan takes a unique scan of the index IDX_UNI_EMP, with the keyword "INDEX UNIQUE SCAN".

Zx@MYDB > drop index idx_uni_emp;Index dropped.zx@MYDB > create index idx_emp_1 on employee (employee_id); Index created.zx@MYDB > select * from employee where employee_id=100;GEN EMPLOYEE_ID----F 100

Now SQL's execution plan is to scan the index range of the index IDX_EMP_1 with the keyword "INDEX RANGE SCAN".

Zx@MYDB > truncate table employee;Table truncated.zx@MYDB > begin 2 for i in 1.. 10000 loop 3 insert into employee select decode (mod, 0 end loop; 5 end; 6 / PL/SQL procedure successfully completed.zx@MYDB > zx@MYDB > commit;Commit complete.zx@MYDB > select gender,count (*) from employee group by gender GEN COUNT (*)-M 5000F 5000zx@MYDB > exec dbms_stats.gather_table_stats (ownname= > USER,tabname= > 'EMPLOYEE',estimate_percent= > 100 description cascade> true,no_invalidate= > false,method_opt= >' FOR ALL COLUMNS SIZE 1'); PL/SQL procedure successfully completed.zx@MYDB > set autotrace traceonlyzx@MYDB > select employee_id from employee 10000 rows selected.Execution Plan---Plan hash value: 2119105728 Murray- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |- -| 0 | SELECT STATEMENT | | 10000 | 40000 | 7 (0) | 00:00:01 | | 1 | TABLE ACCESS FULL | EMPLOYEE | 10000 | 40000 | 7 (0) | 00:00:01 |- -. Omit part of the output

Obviously, you can scan the index IDX_EMP_1 to get the results, but choose the full table scan. Even if you use Hint to force Oracle to scan the index IDX_EMP_1, the result is that Hint is invalid.

Zx@MYDB > select / * + index (employee idx_emp_1) * / employee_id from employee 10000 rows selected.Execution Plan---Plan hash value: 2119105728 Murray- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |- -| 0 | SELECT STATEMENT | | 10000 | 40000 | 7 (0) | 00:00:01 | | 1 | TABLE ACCESS FULL | EMPLOYEE | 10000 | 40000 | 7 (0) | 00:00:01 |- -. Omit part of the output

The reason for this is that Oracle always ensures the correctness of the target SQL results anyway, and Oracle will not consider the execution path that may get the wrong results. For index IDX_EMP_1, it is a B*Tree index with a single key value, so the NULL value will not be stored in it, so a quantity of EMPLOYEE_ID has a null value (although there is actually no null value here), then the result of scanning the index is to leave out those records whose EMPLOYEE_ID is null, which means that if Oracle chooses to scan IDX_EMP_1 when performing the above SQL, then the execution result may be incorrect. In this case, Oracle certainly won't consider scanning the index, even if we use Hint.

If you want Oracle to scan the index IDX_EMP_1 when executing the above SQL, you must change the property of the column EMPLOYEE_ID to NOT NULL. This is equivalent to telling Oracle that there will be no null value on the column EMPLOYEE_ID, so you can safely scan the index IDX_EMP_1.

Zx@MYDB > alter table employee modify employee_id not null;Table altered.zx@MYDB > select employee_id from employee 10000 rows selected.Execution Plan---Plan hash value: 3918702848 Murray- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |- -| 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 |- --. Omit part of the output

As you can see from the output above, SQL's execution plan now follows a fast full scan of the index IDX_EMP_1, corresponding to "INDEX FAST FULL SCAN".

Now add the Hint that forces the index IDX_EMP_1 to go, and execute the SQL again

Zx@MYDB > select / * + index (employee idx_emp_1) * / employee_id from employee 10000 rows selected.Execution Plan---Plan hash value: 438557521 Murray- -| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |- -| 0 | SELECT STATEMENT | | 10000 | 40000 | 20 (0) | 00:00:01 | | 1 | INDEX FULL SCAN | IDX_EMP_1 | 10000 | 40000 | 20 (0) | 00:00:01 |- -. Omitting some of the output, you can see that the execution plan of SQL now follows the fast full scan INDEX FULL SCAN of index IDX_EMP_1 (or INDEX FAST FULL SCAN if it is executed on version 11.2.0.4 with SQL) zx@MYDB > drop index idx_emp_1;Index dropped.zx@MYDB > create index idx_emp_2 on employee (gender,employee_id); Index created.zx@MYDB > select * scan

As you can see from the output above, the execution plan of SQL is an index skip scan of index IDX_EMP_2, corresponding to "INDEXSKIP SCAN".

3. Execution plans related to table joins

Some common methods related to table joins in Oracle databases: sort merge joins, nested loop joins, hash joins, and anti-joins and semi-joins

Zx@MYDB > create table T1 (col1 number,col2 varchar2 (1)); Table created.zx@MYDB > create table T2 (col2 varchar2 (1), col3 varchar2 (2)); Table created.zx@MYDB > insert into T1 values (1 values); 1 row created.zx@MYDB > insert into T1 values (2 values); 1 row created.zx@MYDB > insert into T1 values (3 insert into C'); 1 row created.zx@MYDB > insert into T1 values (4 Lindsay D'); 1 row created.zx@MYDB > insert into T1 values (5 Magazine E') 1 row created.zx@MYDB > insert into T2 values, 1 row created.zx@MYDB > zx@MYDB > commit;Commit complete.zx@MYDB > select * from T1 COL1 COL- 1 A2B 3 C 4 D 5 Ezx@MYDB > select * from T2 where t1.col2=t2.col2 Col COL3----An A2B B2D D2E E2zx@MYDB > select t1.col1 where t1.col2=t2.col2 COL1 COL COL3- 1 An A2 22 B B2 4 D D25 E 2

As you can see from the output above, the execution plan of SQL is a hash join of tables T1 and T2 with the join condition t1.col2=t2.col2 and the corresponding keyword "HASH JOIN".

Execute the SQL again after merging the connected Hint using the forced walk-out sort

Zx@MYDB > select / * + use_merge (t 1grad T2) * / t 1.col1 from t 1.col2 from t 1reel t 2 where t 1.col2 from t 2.col2; COL1 COL COL3- 1 An A22 BB2 24 D D25 E 2 2

As you can see from the output above, SQL's execution plan now follows the sort and merge join of tables T1 and T2, with the corresponding keywords "MERGEJOIN" and "SORT JOIN".

Then execute the SQL again after using the Hint that forces the nested loop connection

Zx@MYDB > select / * + use_nl (t 1grad T2) * / t 1.col1 from t 1.col2 from t 1reel t 2 where t 1.col2 from t 2.col2; COL1 COL COL3- 1 An A22 BB2 24 D D25 E 2 2

As can be seen from the output above, the execution plan of SQL now follows a nested loop join to tables T1 and T2, with the corresponding keyword "NESTEDLOOPS"

The driver table of the nested loop connection can be changed. We use Hint to change the driver table of the above SQL to T1 and then execute SQL.

Zx@MYDB > select / * + ordered use_nl (t 1grad T2) * / t 1.col1 from t 1.col2 from t 1reel t 2 where t 1.col2 from t 2.col2; COL1 COL COL3- 1 An A22 BB2 24 D D25 E 2 2

As you can see from the results, the driver table of the nested loop join has indeed become T1

Let's look at the example of anti-connection. First, change the join column col2 of tables T1 and T2 to NOT NULL, so that we can get out of the execution plan of the disjoin we want

Zx@MYDB > alter table T1 modify col2 not null;Table altered.zx@MYDB > alter table T2 modify col2 not null;Table altered.zx@MYDB > select * from T1 where col2 not in (select col2 from T2 where col3='A2'); COL1 COL--5 E 4 D 2 B 3 C

As can be seen from the output, the SQL execution plan is a hash disjoin of tables T1 and T2, and the corresponding keyword in the execution plan is "ANTI", and the hash disjoin corresponds to "HASH JOIN ANTI".

The specific connection method of anti-join is variable. Here we use Hint to change the anti-join of SQL to sort and merge.

Zx@MYDB > select * from T1 where col2 not in (select / * + MERGE_AJ * / col2 from T2 where col3='A2'); COL1 COL- 2 B 3 C 4 D 5 E

As you can see from the output, the execution plan of SQL is to sort and disjoin tables T1 and T2, with the corresponding keyword "MERGE JOIN ANTI".

Then use Hint to change the anti-connection method of SQL to nested loop anti-connection.

Zx@MYDB > select * from T1 where col2 not in (select / * + NL_AJ * / col2 from T2 where col3='A2'); COL1 COL- 2 B 3 C 4 D 5 E

Let's take a look at the example of a semi-connection.

Zx@MYDB > insert into T2 values ('Eagle commit;Commit complete.zx@MYDB > select * from T1 where exists (select * from T2 where t1.col2=t2.col2 and col3 >' D2'); COL1 COL- 5 E

As can be seen from the output, the execution plan of SQL is a hash semi-join of tables T1 and T2, the corresponding keyword of the semi-join in the execution plan is "SEMI", and the corresponding keyword of the hash semi-join in the execution plan is "HASH JOIN SEMI".

The specific join method of semi-join is variable. Use Hint to change the semi-join method of SQL to sort and merge semi-join:

Zx@MYDB > select * from T1 where exists (select / * + MERGE_SJ * / * from T2 where t1.col2=t2.col2 and col3 > 'D2'); COL1 COL--5 E

As you can see from the output, the execution plan of SQL is to sort and merge semi-joins of tables T1 and T2, with the corresponding keyword "MERGE JOIN SEMI".

Then use Hint to change the semi-join method of SQL to nested loop semi-join:

Zx@MYDB > select * from T1 where exists (select / * + NL_SJ * / * from T2 where t1.col2=t2.col2 and col3 > 'D2'); COL1 COL--5 E

As can be seen from the output, the execution plan of SQL is a nested loop semi-join of tables T1 and T2, and the corresponding keyword is "NESTED LOOPS SEMI".

4. Execution plan related to bitmap index

The common methods related to bitmap index access in Oracle database include the following types: bitmap index single key value scan, bitmap index range scan, bitmap index full scan, bitmap index fast full scan, bitmap bitmap bitwise and, bitmap bitmap bitwise subtraction, and so on.

After using the bitmap index, Oracle usually converts the final bitmap operation result into ROWID, which converts the "BITMAP CONVERSION TO ROWIDS" in the corresponding execution plan.

Zx@MYDB > create table customer 2 (3 customer# number, 4 marital_status varchar2 (10), 5 region varchar2 (10), 6 gender varchar2 (10), 7 income_level varchar2 (10) 8); Table created.zx@MYDB > insert into customer values. 1 row created.zx@MYDB > insert into customer values, 1 row created.zx@MYDB > insert into customer values, 1 row created.zx@MYDB > insert into customer values, 1 row created.zx@MYDB > insert into customer values, 1 row created.zx@MYDB > insert into customer values 1 row created.zx@MYDB > commit;Commit complete.zx@MYDB > create bitmap index idx_b_region on customer (region); Index created.zx@MYDB > create bitmap index idx_b_maritalstatus on customer (marital_status); Index created.zx@MYDB > exec dbms_stats.gather_table_stats (ownname= > USER,tabname= > 'CUSTOMER',estimate_percent= > 100 cascad > true); PL/SQL procedure successfully completed.zx@MYDB > select / * + index (customer idx_b_region) * / customer# from customer where region='east'; CUSTOMER#- 101

As can be seen from the output above, the execution plan of SQL is to scan the bitmap index single key value of the bitmap index IDX_B_REGION, and the corresponding keyword is "BITMAP INDEX SINGLE VALUE".

Rewrite SQL to scope query and execute it again

Zx@MYDB > select / * + index (customer idx_b_region) * / customer# from customer where region between 'east' and' west'; CUSTOMER#- 101 103 104

From the output, we can see that the execution plan of SQL is to scan the bitmap index range of bitmap index IDX_B_REGION, and the corresponding keyword is "BITMAP INDEX RANGE SCAN".

Remove the where condition and query only the key value column of the bitmap index IDX_B_REGION:

Zx@MYDB > select region from customer;REGION--centralcentralcentraleastwestwest

It can be seen from the output that SQL's execution plan is a fast full scan of the bitmap index of the bitmap index IDX_B_REGION, with the corresponding keyword "BIT INDEX FAST FULL SCAN".

Perform the following SQL:

Zx@MYDB > select count (*) from customer where marital_status='married' and region in ('central','west'); COUNT (*)-3

From the output, we can see that the bitmap and operation are used in the SQL execution plan, the corresponding keywords are "BITMAP AND" and bitmap bitmap bitwise or operation, and the corresponding keyword is "BITMAP OR".

Then construct the bitmap execution plan of bitmap subtraction. The SQL is as follows:

Zx@MYDB > select / * + index (customer idx_b_maritalstatus) index (customer idx_b_region) * / customer# from customer where marital_status='married' and

From the output execution plan, the key word for the bitmap subtracted execution plan is "BITMAP MINUX".

Refer to "SQL Optimization based on Oracle"

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.