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

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

Paging query execution Plan based on UNION ALL: http://yangtingkun.itpub.net/post/468/303221

This article continues to discuss this problem and gives a relatively simple solution.

The first problem found is that it is caused by UNION ALL and has nothing to do with the view.

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)

SQL > SELECT *

2 FROM

3 (

4 SELECT ROWNUM RN, A.*

5 FROM

6 (

7 SELECT / * + FIRST_ROWS * / * FROM

8 (

9 SELECT * FROM T1

10 UNION ALL

11 SELECT * FROM T2

12)

13 WHERE CREATE_DATE = SYSDATE-2

14) A

15 WHERE ROWNUM 1

18

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 (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)

A search on Metalink found no description of the problem, but it seems to be somewhat similar to the following question: Note:2281909.8.

Description

Suboptimal plan possible from INLINE non-correlated UNION ALL subquery. When this problem occurs the execution plan indicates that the subquery has been unnested to a view, and a join predicate was pushed into the view.

The problem in this bug is that Oracle incorrectly pushes the query condition of the join column into the UNION all subquery, resulting in performance degradation. The current problem is that Oracle does not push constraints into the UNION all subquery.

The solution to this problem is to avoid nesting another layer of queries on the outer layer after the emergence of ROWNUM.

Of course, upgrading to 10g is also an option, but it is more expensive.

For paging operations, because three-tier nested queries cannot be avoided, you can use MINUS to solve this problem:

SQL > SELECT ROWNUM, A.*

2 FROM

3 (

4 SELECT / * + FIRST_ROWS * / * FROM vault

5 WHERE CREATE_DATE = SYSDATE-2

6 ORDER BY NAME

7) A

8 WHERE 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.