[book Review: Oracle query Optimization rewriting] Chapter 3 02/24 Update SLTechnology News&Howtos

[book Review: Oracle query Optimization rewriting] Chapter 3

2025-02-24 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

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

[book Review: Oracle query Optimization rewriting] Chapter 3

BLOG document structure diagram

I. 1 introduction

Technical enthusiasts, after reading this article, you can master the following skills, and you can also learn some other knowledge that you do not know, ~ O (∩ _ ∩) Olympiad:

Introduction to ① implicit parameter _ b_tree_bitmap_plans

② 11g New feature Native Full Outer Join

If there are any mistakes or imperfections in this article, please correct them as much as you can, ITPUB or QQ. Your criticism is the biggest motivation of my writing.

I. 2 introduction to the experimental environment

Oracle:11.2.0.3 、 8.1.7.0.0

OS: RHEL6.5

I. 3 preface

The links in the first 2 chapters refer to related links:

[book Review: Oracle query Optimization rewriting] Chapter 1 http://blog.itpub.net/26736162/viewspace-1652985/

[book Review: Oracle query Optimization rewriting] Chapter 2 http://blog.itpub.net/26736162/viewspace-1654252/

Last night, after reading the third chapter of "Oracle query Optimization rewriting", I have to say that there is a big problem with the typesetting of the code in this book, and the format is always out of alignment, especially the format of the execution plan, which may be a problem in printing, not to mention this. This third chapter is mainly about the association of multiple tables, including the writing of various connections, such as left and right couplets, and what will happen if the filter conditions are mistakenly placed in the WHERE; when the data has duplicate values, it should be directly associated or grouped and summarized.

Chapter 3 manipulating multiple tables

3.1 UNION ALL and empty string

3.2 UNION and OR

3.3 combine related rows

3.4 IN, EXISTS and INNER JOIN

3.5 parsing of INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN

3.6 self-correlation

3.7 NOT IN, NOT EXISTS and LEFT JOIN

3.8 do not misplace the conditions in the external connection

3.9 check whether the number of data in the two tables and the corresponding data are the same

3.10 aggregation and internal connection

3.11 aggregation and external connection

3.12 return lost data from multiple tables

3.13 Null value processing in multi-table query

Next, I will do some research on some key points, or parts that I do not quite understand.

I. 4 implied parameter _ b_tree_bitmap_plans experiment 1. 4.1 introduction

This parameter is implied and refers to whether to convert the index to a bitmap index and then execute it. The default value is false before oracle9i and true after that. You can think of it this way, if there are two fields btree B has an index, it is possible for oracle to convert these two indexes to bitmap index and then do and operation to get the result set. If you change it to false, you will choose one of the indexes and take the index of btree. You can set this parameter to false at the session or system level, or you can disable it by adding hint / * + opt_param ('_ bounded treepercent mapping plansmanship, 'false') * /.

Symptom: Execution plan operation shows bitmap conversion from rowids

Symptom: No bitmap indexes

Symptom: Execution plan shows BITMAP CONVERSION

Cause: In 7.3.4 and in 8.1.7 default value of _ b_tree_bitmap_plans is FALSE

Whereas as of 9.0.1 (and 9.2) the default value is TRUE When _ b_tree_bitmap_plans set to true (advice not to change the default setting

Yourself) the optimizer is allowed to produce bitmap plans for normal b*tree

Indexes even if no bitmap indexes set.

The relevant implementation plan may be converted to the following form:

(1) BITMAP CONVERSION FROM ROWIDS

Map the ROWID of a batch of data records to bitmaps.

For ordinary B* tree indexes, Oracle can also map the ROWID of data records to a bitmap, and then perform bitmap operations. Making such a conversion requires setting the system parameter _ b_tree_bitmap_plans to TRUE.

(2) BITMAP CONVERSION TO ROWIDS

Map the bitmap to ROWID. A bitmap key contains the start and end addresses of a batch of data records, and the records are continuous, so each bit in the bitmap corresponds to a data record in order.

(3) BITMAP OR

OR the bitmap. In the filter condition of the query, if the direct relationship of the bitmap index field is "or", you can use BITMAP OR to determine whether a batch of data records mapped by the bitmap satisfy the condition.

An example of Master eygle:

Http://www.eygle.com/archives/2011/12/bitmap_conversion_cpu.html

I. 4.2 11g

[root@rhel6_lhr ~] # su-oracle

[oracle@rhel6_lhr ~] $sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Fri May 10:16:10 2015

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit Production

With the Partitioning, Automatic Storage Management, OLAP, Data Mining

And Real Application Testing options

10:16:10 SQL >

10:16:10 SQL > conn lhr/lhr

Connected.

10:16:10 SQL > create table emp_bk as select * from scott.emp

Table created.

Elapsed: 00:00:03.43

10:16:15 SQL > create index idx_emp_empno on emp_bk (empno)

Index created.

Elapsed: 00:00:00.05

10:19:26 SQL > create index idx_emp_ename on emp_bk (ename)

Index created.

Elapsed: 00:00:00.04

10:20:48 SQL > explain plan for select empno,ename from emp_bk where empno=7788 or ename='SCOTT'

Explained.

Elapsed: 00:00:00.09

10:20:56 SQL > select * from table (dbms_xplan.display)

PLAN_TABLE_OUTPUT

- -

Plan hash value: 4193090541

| | 0 | SELECT STATEMENT | | 1 | 20 | 2 (0) | 00:00:01 |

| | 1 | TABLE ACCESS BY INDEX ROWID | EMP_BK | 1 | 20 | 2 (0) | 00:00:01 |

| | 2 | BITMAP CONVERSION TO ROWIDS | | |

| | 3 | BITMAP OR | | |

| | 4 | BITMAP CONVERSION FROM ROWIDS | | |

| | * 5 | INDEX RANGE SCAN | IDX_EMP_EMPNO | 1 (0) | 00:00:01 |

| | 6 | BITMAP CONVERSION FROM ROWIDS | | |

| | * 7 | INDEX RANGE SCAN | IDX_EMP_ENAME | 1 (0) | 00:00:01 |

Predicate Information (identified by operation id):

5-access ("EMPNO" = 7788)

7-access ("ENAME" = 'SCOTT')

Note

-dynamic sampling used for this statement (level=2)

24 rows selected.

Elapsed: 00:00:00.52

10:24:06 SQL > conn / as sysdba

Connected.

Elapsed: 00:00:00.03

10:24:34 SQL > set pagesize 9999

10:24:41 SQL > set line 9999

10:24:41 SQL > col NAME format A30

10:24:41 SQL > col KSPPDESC format A50

10:24:41 SQL > col KSPPSTVL format A20

10:24:42 SQL > SELECT a.INDX

10:24:42 2 a.KSPPINM NAME

10:24:42 3 a.KSPPDESC

10:24:42 4 b.KSPPSTVL

10:24:42 5 FROM x$ksppi a

10:24:42 6 x$ksppcv b

10:24:42 7 WHERE a.INDX = b.INDX

10:24:42 8 and lower (a.KSPPINM) like lower ('%? meter%')

Enter value for parameter: _ b_tree_bitmap_plans

Old 8: and lower (a.KSPPINM) like lower ('%? meter%')

New 8: and lower (a.KSPPINM) like lower ('% _ bounded trees bitmapped plans%')

INDX NAME KSPPDESC KSPPSTVL

1910 _ b_tree_bitmap_plans enable the use of bitmap plans for tables w. Only TRUE

B-tree indexes

Elapsed: 00:00:00.01

10:25:44 SQL > conn lhr/lhr

Connected.

10:26:56 SQL > alter session set "_ b_tree_bitmap_plans" = false

Session altered.

Elapsed: 00:00:00.00

10:27:01 SQL > show parameter _ b_tree_bitmap_plans

NAME TYPE VALUE

_ b_tree_bitmap_plans boolean FALSE

10:27:05 SQL > explain plan for select empno,ename from emp_bk where empno=7788 or ename='SCOTT'

Explained.

Elapsed: 00:00:00.01

10:27:14 SQL > select * from table (dbms_xplan.display)

PLAN_TABLE_OUTPUT

- -

Plan hash value: 370270337

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

| | * 1 | TABLE ACCESS FULL | EMP_BK | 1 | 20 | 3 (0) | 00:00:01 |

Predicate Information (identified by operation id):

1-filter ("EMPNO" = 7788 OR "ENAME" = 'SCOTT')

Note

-dynamic sampling used for this statement (level=2)

17 rows selected.

Elapsed: 00:00:00.04

10:27:18 SQL > explain plan for select empno,ename from emp_bk where empno=7788

10:27:49 2 union

10:27:55 3 select empno,ename from emp_bk where ename='SCOTT'

Explained.

Elapsed: 00:00:00.00

10:28:07 SQL > select * from table (dbms_xplan.display)

PLAN_TABLE_OUTPUT

- -

Plan hash value: 3014579657

| | 0 | SELECT STATEMENT | | 2 | 40 | 6 (67) | 00:00:01 |

| | 1 | SORT UNIQUE | | 2 | 40 | 6 (67) | 00:00:01 |

| | 2 | UNION-ALL | | |

| | 3 | TABLE ACCESS BY INDEX ROWID | EMP_BK | 1 | 20 | 2 (0) | 00:00:01 |

| | * 4 | INDEX RANGE SCAN | IDX_EMP_EMPNO | 1 | | 1 (0) | 00:00:01 |

| | 5 | TABLE ACCESS BY INDEX ROWID | EMP_BK | 1 | 20 | 2 (0) | 00:00:01 |

| | * 6 | INDEX RANGE SCAN | IDX_EMP_ENAME | 1 | | 1 (0) | 00:00:01 |

Predicate Information (identified by operation id):

4-access ("EMPNO" = 7788)

6-access ("ENAME" = 'SCOTT')

Note

-dynamic sampling used for this statement (level=2)

23 rows selected.

Elapsed: 00:00:00.01

10:28:13 SQL > Select Name, Value From v$parameter Where Name ='_ baked trees bitmapped plans'

NAME VALUE

_ b_tree_bitmap_plans FALSE

Elapsed: 00:00:00.02

10:34:06 SQL > alter session set "_ b_tree_bitmap_plans" = true

Session altered.

Elapsed: 00:00:00.00

11:19:04 SQL > explain plan for select / * + opt_param ('_ bounded treeworthy bitmappings plansy, 'false') * / empno,ename from emp_bk where empno=7788 or ename='SCOTT'

Explained.

Elapsed: 00:00:00.08

11:19:22 SQL > select * from table (dbms_xplan.display)

PLAN_TABLE_OUTPUT

- -

Plan hash value: 370270337

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

| | * 1 | TABLE ACCESS FULL | EMP_BK | 1 | 20 | 3 (0) | 00:00:01 |

Predicate Information (identified by operation id):

1-filter ("EMPNO" = 7788 OR "ENAME" = 'SCOTT')

Note

-dynamic sampling used for this statement (level=2)

17 rows selected.

Elapsed: 00:00:00.24

As can be seen from the experiment, when _ b_tree_bitmap_plans is set to false, emp_bk does a full table scan and does not go to bitmap index conversion.

I. in the case of 4.3 8i

C:\ Users\ Administrator > sqlplus "lhr/lhr@orcl8i as sysdba"

SQL*Plus: Release 8.1.7.0.0-Production on Mon May 18 10:44:28 2015

Connected to:

Oracle8i Enterprise Edition Release 8.1.7.0.0-Production

With the Partitioning option

JServer Release 8.1.7.0.0-Production

SQL > set pagesize 9999

SQL > set line 9999

SQL > col NAME format A30

SQL > col KSPPDESC format A50

SQL > col KSPPSTVL format A20

SQL > SELECT a.INDX

2 a.KSPPINM NAME

3 a.KSPPDESC

4 b.KSPPSTVL

5 FROM x$ksppi a

6 x$ksppcv b

7 WHERE a.INDX = b.INDX

8 and lower (a.KSPPINM) like lower ('%? meter%')

Enter value for parameter: _ b_tree_bitmap_plans

Old 8: and lower (a.KSPPINM) like lower ('%? meter%')

New 8: and lower (a.KSPPINM) like lower ('% _ bounded trees bitmapped plans%')

INDX NAME KSPPDESC KSPPSTVL

348 _ b_tree_bitmap_plans enable the use of bitmap plans for tables w. Only FALSE

B-tree indexes

SQL >

SQL > create table lhr.emp_bk as select * from scott.emp

Table created.

SQL > create index lhr.idx_emp_empno on lhr.emp_bk (empno)

Index created.

SQL > create index lhr.idx_emp_ename on lhr.emp_bk (ename)

Index created.

SQL > set line 9999 pagesize 9999

SQL > set autot on

SQL > select empno,ename from lhr.emp_bk where empno=7788 or ename='SCOTT'

EMPNO ENAME

7788 SCOTT

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE

1 0 CONCATENATION

2 1 TABLE ACCESS (BY INDEX ROWID) OF 'EMP_BK'

3 2 INDEX (RANGE SCAN) OF 'IDX_EMP_ENAME' (NON-UNIQUE)

4 1 TABLE ACCESS (BY INDEX ROWID) OF 'EMP_BK'

5 4 INDEX (RANGE SCAN) OF 'IDX_EMP_EMPNO' (NON-UNIQUE)

Statistics

0 recursive calls

0 db block gets

0 consistent gets

0 physical reads

0 redo size

0 bytes sent via SQL*Net to client

0 bytes received via SQL*Net from client

0 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

SQL >

8i defaults to false, and the execution plan is completely different.

One. 5 Native Full Outer Join

For this feature, please refer to the following article:

Http://blog.itpub.net/26736162/viewspace-1660038/

Let's test it under 10.2.0.4:

[oracle@rhel6_lhr ~] $sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0-Production on Mon May 18 11:41:13 2015

Connected to an idle instance.

SQL > startup

ORACLE instance started.

Total System Global Area 448790528 bytes

Fixed Size 2084616 bytes

Variable Size 121635064 bytes

Database Buffers 318767104 bytes

Redo Buffers 6303744 bytes

Database mounted.

Database opened.

SQL > create table lhr.emp_bk as select * from scott.emp

Table created.

SQL > create table lhr.emp_bk as select * from scott.emp

Table created.

SQL > set autot on

SQL > set line 9999 pagesize 9999

SQL > select * from lhr.emp_bk a full outer join lhr.emp_bk2 b on a.empno=b.empno

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20

7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30 7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30

7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 7698 30 7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 7698 30

7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20 7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20

7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30 7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30

7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30 7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30

7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10 7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10

7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20 7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20

7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10 7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10

7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30 7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30

7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20 7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20

7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30 7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30

7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20 7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20

7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10 7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10

14 rows selected.

Execution Plan

Plan hash value: 914601651

| | 0 | SELECT STATEMENT | | 15 | 2610 | 13 (8) | 00:00:01 |

| | 1 | VIEW | | 15 | 2610 | 13 (8) | 00:00:01 |

| | 2 | UNION-ALL | | |

| | * 3 | HASH JOIN OUTER | | 14 | 2436 | 7 (15) | 00:00:01 |

| | 4 | TABLE ACCESS FULL | EMP_BK | 14 | 1218 | 3 (0) | 00:00:01 |

| | 5 | TABLE ACCESS FULL | EMP_BK2 | 14 | 1218 | 3 (0) | 00:00:01 |

| | * 6 | HASH JOIN ANTI | | 1 | 100 | 7 (15) | 00:00:01 |

| | 7 | TABLE ACCESS FULL | EMP_BK2 | 14 | 1218 | 3 (0) | 00:00:01 |

| | 8 | TABLE ACCESS FULL | EMP_BK | 14 | 182 | 3 (0) | 00:00:01 |

Predicate Information (identified by operation id):

3-access ("A". "EMPNO" = "B". "EMPNO" (+))

6-access ("A". "EMPNO" = "B". "EMPNO")

Note

-dynamic sampling used for this statement

Statistics

338 recursive calls

0 db block gets

61 consistent gets

6 physical reads

0 redo size

2521 bytes sent via SQL*Net to client

492 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

4 sorts (memory)

0 sorts (disk)

14 rows processed

SQL > select / * + NATIVE_FULL_OUTER_JOIN * / * from lhr.emp_bk a full outer join lhr.emp_bk2 b on a.empno=b.empno