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

This article will explain in detail what are the paging query execution problems related to UNION ALL, and the content of the article is of high quality, so the editor will share it with you for reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

Today I found another problem on 9204. However, this problem will not cause data errors, but will seriously affect the performance of the query.

Or show the problem through a simple example:

SQL > CREATE TABLE T1 (ID NUMBER, NAME VARCHAR2 (30), CREATE_DATE DATE)

The table has been created.

SQL > CREATE INDEX IND_T1_CREATE_DATE ON T1 (CREATE_DATE)

The index has been created.

SQL > CREATE TABLE T2 (ID NUMBER, NAME VARCHAR2 (30), CREATE_DATE DATE)

The table has been created.

SQL > CREATE INDEX IND_T2_CREATE_DATE ON T2 (CREATE_DATE)

The index has been created.

SQL > CREATE VIEW vault AS SELECT ID, NAME, CREATE_DATE FROM T1 UNION ALL SELECT ID, NAME, CREATE_DATE

FROM T2

The view has been created.

SQL > INSERT INTO T1 SELECT ROWNUM, OBJECT_NAME, CREATED FROM DBA_OBJECTS WHERE OWNER = 'SYS'

13727 rows were created.

SQL > INSERT INTO T2 SELECT ROWNUM, OBJECT_NAME, CREATED FROM DBA_OBJECTS WHERE OWNER = 'NDMAIN'

1158 rows were created.

SQL > COMMIT

The submission is complete.

SQL > EXEC DBMS_STATS.GATHER_TABLE_STATS (USER, 'T1')

The PL/SQL process completed successfully.

SQL > EXEC DBMS_STATS.GATHER_TABLE_STATS (USER, 'T2')

The PL/SQL process completed successfully.

Let's take a look at a simple query based on the VroomT view:

SQL > SELECT / * + FIRST_ROWS * / * FROM vault WHERE CREATE_DATE = SYSDATE-2

No rows selected

Carry out the plan

0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=4 Card=21 Bytes=777)

1 0 VIEW OF 'Venture T' (Cost=4 Card=21 Bytes=777)

2 1 UNION-ALL (PARTITION)

3 2 TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=4 Card=35 Bytes=1225)

4 3 INDEX (RANGE SCAN) OF 'IND_T1_CREATE_DATE' (NON-UNIQUE) (Cost=1 Card=35)

5 2 TABLE ACCESS (BY INDEX ROWID) OF 'T2' (Cost=3 Card=2 Bytes=62)

6 5 INDEX (RANGE SCAN) OF 'IND_T2_CREATE_DATE' (NON-UNIQUE) (Cost=1 Card=2)

There is no problem with this execution plan, but if you add pagination, the execution plan will become:

SQL > SELECT *

2 FROM

3 (

4 SELECT ROWNUM RN, A.*

5 FROM

6 (

7 SELECT / * + FIRST_ROWS * / * FROM vault

8 WHERE CREATE_DATE = SYSDATE-2

9) A

10 WHERE ROWNUM 1

13

No rows selected

Carry out the plan

0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=11 Card=10 Bytes=520)

10 VIEW (Cost=11 Card=10 Bytes=520)

2 1 COUNT (STOPKEY)

3 2 VIEW OF 'Venture T' (Cost=11 Card=14885 Bytes=580515)

4 3 UNION-ALL

5 4 TABLE ACCESS (FULL) OF 'T1' (Cost=9 Card=13727 Bytes=480445)

6 4 TABLE ACCESS (FULL) OF 'T2' (Cost=2 Card=1158 Bytes=35898)

Although the FIRST_ROWS hint is still included, Oracle does not select the index here. Here's an attempt to use the INDEX prompt to force the query to use the index:

SQL > SELECT *

2 FROM

3 (

4 SELECT ROWNUM RN, A.*

5 FROM

6 (

7 SELECT / * + INDEX (V_T.T1) INDEX (V_T.T2) * / * FROM vault

8 WHERE CREATE_DATE = SYSDATE-2

9) A

10 WHERE ROWNUM 1

13

No rows selected

Carry out the plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=11 Card=10 Bytes=520)

10 VIEW (Cost=11 Card=10 Bytes=520)

2 1 COUNT (STOPKEY)

3 2 VIEW OF 'Venture T' (Cost=11 Card=14885 Bytes=580515)

4 3 UNION-ALL

5 4 TABLE ACCESS (FULL) OF 'T1' (Cost=9 Card=13727 Bytes=480445)

6 4 TABLE ACCESS (FULL) OF 'T2' (Cost=2 Card=1158 Bytes=35898)

The prompt is still invalid, try adding a NOT NULL constraint to CREATE_DATE:

SQL > ALTER TABLE T1 MODIFY CREATE_DATE NOT NULL

The table has changed.

SQL > ALTER TABLE T2 MODIFY CREATE_DATE NOT NULL

The table has changed.

SQL > SELECT *

2 FROM

3 (

4 SELECT ROWNUM RN, A.*

5 FROM

6 (

7 SELECT / * + INDEX (V_T.T1) INDEX (V_T.T2) * / * FROM vault

8 WHERE CREATE_DATE = SYSDATE-2

9) A

10 WHERE ROWNUM 1

13

No rows selected

Carry out the plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1652 Card=10 Bytes=520)

10 VIEW (Cost=1652 Card=10 Bytes=520)

2 1 COUNT (STOPKEY)

3 2 VIEW OF 'Venture T' (Cost=1652 Card=14885 Bytes=580515)

4 3 UNION-ALL

5 4 TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=826 Card=13727 Bytes=480445)

6 5 INDEX (FULL SCAN) OF 'IND_T1_CREATE_DATE' (NON-UNIQUE) (Cost=26 Card=13727)

7 4 TABLE ACCESS (BY INDEX ROWID) OF 'T2' (Cost=826 Card=1158 Bytes=35898)

8 7 INDEX (FULL SCAN) OF 'IND_T2_CREATE_DATE' (NON-UNIQUE) (Cost=26 Card=1158)

Although the index is used this time, it is not the initial index range scan, but the index full scan. In fact, from the changes in the execution plan after the addition of NOT NULL constraints, we can see that Oracle's considerations here and before have changed a lot.

If you remove the outermost layer of the query, the query will return to normal:

SQL > SELECT ROWNUM, A.*

2 FROM

3 (

4 SELECT / * + FIRST_ROWS * / * FROM vault

5 WHERE CREATE_DATE = SYSDATE-2

6) A

7 WHERE ROWNUM SELECT *

2 FROM

3 (

4 SELECT ROWNUM, A.*

5 FROM

6 (

7 SELECT / * + FIRST_ROWS * / * FROM vault

8 WHERE CREATE_DATE = SYSDATE-2

9) A

10)

11

No rows selected

Carry out the plan

0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=11 Card=14885 Bytes=774020)

1 0 VIEW (Cost=11 Card=14885 Bytes=774020)

2 1 COUNT

3 2 VIEW OF 'Venture T' (Cost=11 Card=14885 Bytes=580515)

4 3 UNION-ALL

5 4 TABLE ACCESS (FULL) OF 'T1' (Cost=9 Card=13727 Bytes=480445)

6 4 TABLE ACCESS (FULL) OF 'T2' (Cost=2 Card=1158 Bytes=35898)

SQL > SELECT *

2 FROM

3 (

4 SELECT A.*

5 FROM

6 (

7 SELECT / * + FIRST_ROWS * / * FROM vault

8 WHERE CREATE_DATE = SYSDATE-2

9) A

10)

11

No rows selected

Carry out the plan

0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=4 Card=21 Bytes=777)

1 0 VIEW OF 'Venture T' (Cost=4 Card=21 Bytes=777)

2 1 UNION-ALL (PARTITION)

3 2 TABLE ACCESS (BY INDEX ROWID) OF 'T1' (Cost=4 Card=35 Bytes=1225)

4 3 INDEX (RANGE SCAN) OF 'IND_T1_CREATE_DATE' (NON-UNIQUE) (Cost=1 Card=35)

5 2 TABLE ACCESS (BY INDEX ROWID) OF 'T2' (Cost=3 Card=2 Bytes=62)

6 5 INDEX (RANGE SCAN) OF 'IND_T2_CREATE_DATE' (NON-UNIQUE) (Cost=1 Card=2)

Comparing the above two queries, we can not find that when the query contains ROWNUM, and another layer is nested outside the query, it will lead to the above problem, that is, the query condition can not be extrapolated from the view to the base table query of the UNION ALL view.

It seems that 9i has a lot of bug in implementing the plan, and it has encountered several in succession recently.

Here's a brief test of 10g:

SQL > SELECT * FROM V$VERSION

BANNER

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0-Prod

PL/SQL Release 10.2.0.1.0-Production

CORE 10.2.0.1.0 Production

TNS for 32-bit Windows: Version 10.2.0.1.0-Production

NLSRTL Version 10.2.0.1.0-Production

SQL > CREATE TABLE T1 (ID NUMBER, NAME VARCHAR2 (30), CREATE_DATE DATE)

The table has been created.

SQL > CREATE INDEX IND_T1_CREATE_DATE ON T1 (CREATE_DATE)

The index has been created.

SQL > CREATE TABLE T2 (ID NUMBER, NAME VARCHAR2 (30), CREATE_DATE DATE)

The table has been created.

SQL > CREATE INDEX IND_T2_CREATE_DATE ON T2 (CREATE_DATE)

The index has been created.

SQL > CREATE VIEW vault AS SELECT ID, NAME, CREATE_DATE FROM T1 UNION ALL SELECT ID, NAME, CREATE_DATE

FROM T2

The view has been created.

SQL > INSERT INTO T1 SELECT ROWNUM, OBJECT_NAME, CREATED FROM DBA_OBJECTS WHERE OWNER = 'SYS'

22988 rows were created.

SQL > INSERT INTO T2 SELECT ROWNUM, OBJECT_NAME, CREATED FROM DBA_OBJECTS WHERE OWNER = 'NDMAIN'

Line 0 has been created.

SQL > COMMIT

The submission is complete.

SQL > EXEC DBMS_STATS.GATHER_TABLE_STATS (USER, 'T1')

The PL/SQL process completed successfully.

SQL > EXEC DBMS_STATS.GATHER_TABLE_STATS (USER, 'T2')

The PL/SQL process completed successfully.

SQL > SET AUTOT ON EXP

SQL > SELECT *

2 FROM

3 (

4 SELECT ROWNUM RN, A.*

5 FROM

6 (

7 SELECT / * + FIRST_ROWS * / * FROM vault

8 WHERE CREATE_DATE = SYSDATE-2

9) A

10 WHERE ROWNUM 1

13

No rows selected

Carry out the plan

Plan hash value: 2589469176

-

| | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | |

-

| | 0 | SELECT STATEMENT | | 10 | 520 | 1 (0) |

| | * 1 | VIEW | | 10 | 520 | 1 (0) |

| | * 2 | COUNT STOPKEY |

| | 3 | VIEW | Vint | 10 | 380 | 1 (0) |

| | 4 | UNION-ALL PARTITION |

| | 5 | TABLE ACCESS BY INDEX ROWID | T1 | 34 | 1224 | 1 (0) |

| | * 6 | INDEX RANGE SCAN | IND_T1_CREATE_DATE | 34 | | 1 (0) |

| | 7 | TABLE ACCESS BY INDEX ROWID | T2 | 1 | 39 | 1 (0) | |

| | * 8 | INDEX RANGE SCAN | IND_T2_CREATE_DATE | 1 | | 1 (0) |

-

Predicate Information (identified by operation id):

1-filter ("RN" > 1)

2-filter (ROWNUM

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.