视图 order by 导致执行计划改变

最新推荐文章于 2021-04-04 21:23:29 发布

cuichenkai7892

最新推荐文章于 2021-04-04 21:23:29 发布

阅读量141

点赞数

文章标签：数据库

CREATE TABLE test_aaa AS SELECT * FROM Dba_Objects;

CREATE TABLE test_bbbb AS SELECT * FROM Dba_Objects WHERE ROWNUM<=10000;
INSERT INTO test_bbbb SELECT * FROM test_bbbb; --执行 2 次

CREATE INDEX idx_a_name ON test_aaa(object_name);
CREATE INDEX idx_b_name ON test_bbbb(object_name);
CREATE INDEX idx_a_id ON test_aaa(object_id);
CREATE INDEX idx_b_id ON test_bbbb(object_id);

exec dbms_stats.gather_table_stats(null,'test_aaa',cascade => true);

exec dbms_stats.gather_table_stats(null,'test_bbbb',cascade => true);

CREATE VIEW aaa AS
SELECT a.*,b.object_id object_id1,b.object_name object_name1,b.data_object_id data_object_id1
FROM test_aaa a,test_bbbb b
WHERE a.object_name=b.object_name
ORDER BY b.data_object_id; --- 这里先定义 order by 的操作

EXPLAIN PLAN FOR
SELECT * FROM
(SELECT a.*,ROWNUM rn FROM aaa a
WHERE a.object_id=100479)
WHERE rn>0
AND rn<=5;

SELECT * FROM TABLE(dbms_xplan.display);

在视图 aaa 有 order by 的情况下，执行计划走的是全表扫描：

-----------------------------------------------------------------------------------
| Id | Operation              | Name       | Rows | Bytes |TempSpc| Cost (%CPU)|
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |             | 59717 |    16M|       | 1590   (1)|
|* 1 | VIEW                  |             | 59717 |    16M|       |            |
|   2 |   COUNT                |             |       |       |       |            |
|* 3 |    VIEW                | AAA         | 59717 |    15M|       |            |
|   4 |     SORT ORDER BY      |             | 59717 | 7173K|    15M| 1590   (1)|
|* 5 |      HASH JOIN         |             | 59717 | 7173K| 1648K|   189   (3)|
|   6 |       TABLE ACCESS FULL| TEST_BBBB   | 40000 | 1171K|       |    55   (4)|
|   7 |       TABLE ACCESS FULL| TEST_AAA    | 35575 | 3230K|       |    50   (4)|
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("from$_subquery$_001"."RN"<=5 AND "from$_subquery$_001"."RN">0)
   3 - filter("A"."OBJECT_ID"=100479)   --- 这里谓词没有推荐到视图里面执行
   5 - access("A"."OBJECT_NAME"="B"."OBJECT_NAME")

可以看到，这个执行计划不是很好，没有有效利用 test_aaa.object_id 的索引（后面有 10053 的报告），之后采用了很多 hint，index(a.a ...) 、push_pred(a) 。。（看的是 10g 的文档，用到 9i的库，囧），都没有效果。
没辙，最后把视图的 order by 给去掉，发现执行计划正确了。

------------------------------------------------------------------------------------
| Id | Operation                       | Name       | Rows | Bytes | Cost (%CPU)|
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |             |     4 | 1128 |     5   (0)|
|* 1 | VIEW                           |             |     4 | 1128 |            |
|   2 |   COUNT                         |             |       |       |            |
|   3 |    TABLE ACCESS BY INDEX ROWID | TEST_BBBB   |     4 |   120 |     4   (0)|
|   4 |     NESTED LOOPS                |             |     4 |   492 |     5   (0)|
|   5 |      TABLE ACCESS BY INDEX ROWID| TEST_AAA    |     1 |    93 |     2   (0)|
|* 6 |       INDEX RANGE SCAN          | IDX_A_ID    |     1 |       |     2 (50)|
|* 7 |      INDEX RANGE SCAN           | IDX_B_NAME |     4 |       |            |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("from$_subquery$_001"."RN"<=5 AND "from$_subquery$_001"."RN">0)
   6 - access("A"."OBJECT_ID"=100479)
   7 - access("A"."OBJECT_NAME"="B"."OBJECT_NAME")

现在开始怀疑是视图的 order by 语句导致了执行计划的不正确，可是实在想不通 oracle 为什么不把谓词放到里面去。现在贴出视图有 order by 时的 10053 文档

Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
ORACLE_HOME = /home/oracle/oracle92
System name:    Linux
Node name:      localhost
Release:        2.6.23-2smp64G
Version:        #1 SMP Sun Oct 28 14:22:42 JST 2007
Machine:        i686
Instance name: zxin
Redo thread mounted by this instance: 1
Oracle process number: 165
Unix process pid: 13804, image: oracle@localhost (TNS V1-V3)

*** SESSION ID:(23.6381) 2010-04-14 09:01:29.710
QUERY
SELECT * FROM
(SELECT a.*,ROWNUM rn FROM aaa a
WHERE a.object_id=100479)
WHERE rn>0
AND rn<=5
***************************************
PARAMETERS USED BY THE OPTIMIZER
********************************
。。。。。。。。。。。。。。。。。。。。。。。。。。
***************************************
BASE STATISTICAL INFORMATION
***********************
Table stats    Table: TEST_BBBB   Alias: B
TOTAL :: CDN: 40000 NBLKS: 536 AVG_ROW_LEN: 89
Column: OBJECT_NAM Col#: 2      Table: TEST_BBBB   Alias: B
    NDV: 9666      NULLS: 0         DENS: 1.0346e-04
    NO HISTOGRAM: #BKT: 1 #VAL: 2
Column: OBJECT_NAM Col#: 2      Table: TEST_BBBB   Alias: B
    NDV: 9666      NULLS: 0         DENS: 1.0346e-04
    NO HISTOGRAM: #BKT: 1 #VAL: 2
-- Index stats
INDEX NAME: IDX_B_ID COL#: 4
    TOTAL :: LVLS: 1   #LB: 87 #DK: 10000 LB/K: 1 DB/K: 4 CLUF: 40000
INDEX NAME: IDX_B_NAME COL#: 2
    TOTAL :: LVLS: 1   #LB: 189 #DK: 9666 LB/K: 1 DB/K: 4 CLUF: 38680
***********************
Table stats    Table: TEST_AAA   Alias: A
TOTAL :: CDN: 35575 NBLKS: 487 AVG_ROW_LEN: 93
Column: OBJECT_NAM Col#: 2      Table: TEST_AAA   Alias: A
    NDV: 23829     NULLS: 0         DENS: 4.1966e-05
    NO HISTOGRAM: #BKT: 1 #VAL: 2
Column: OBJECT_NAM Col#: 2      Table: TEST_AAA   Alias: A
    NDV: 23829     NULLS: 0         DENS: 4.1966e-05
    NO HISTOGRAM: #BKT: 1 #VAL: 2
-- Index stats
INDEX NAME: IDX_A_ID COL#: 4
    TOTAL :: LVLS: 1   #LB: 79 #DK: 35512 LB/K: 1 DB/K: 1 CLUF: 26932
INDEX NAME: IDX_A_NAME COL#: 2
    TOTAL :: LVLS: 1   #LB: 165 #DK: 23829 LB/K: 1 DB/K: 1 CLUF: 23757
_OPTIMIZER_PERCENT_PARALLEL = 0
***************************************
SINGLE TABLE ACCESS PATH   ---------注意这里，oracle 认为全表扫描的代价是最小的，完全忽略了 IDX_A_ID 的存在
TABLE: TEST_AAA     ORIG CDN: 35575 ROUNDED CDN: 35575 CMPTD CDN: 35575
Access path: tsc Resc: 49 Resp: 49
BEST_CST: 50.00 PATH: 2 Degree: 1
***************************************
SINGLE TABLE ACCESS PATH
TABLE: TEST_BBBB     ORIG CDN: 40000 ROUNDED CDN: 40000 CMPTD CDN: 40000
Access path: tsc Resc: 54 Resp: 54
BEST_CST: 55.00 PATH: 2 Degree: 1
***************************************
OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
***********************
Join order[1]: TEST_AAA [ A] TEST_BBBB [ B]
Now joining: TEST_BBBB [ B] *******
NL Join
Outer table: cost: 50 cdn: 35575 rcz: 93 resp: 49
Inner table: TEST_BBBB
    Access path: tsc Resc: 54
    Join: Resc: 1922144 Resp: 1922144
Access path: index (join index)
      Index: IDX_B_NAME
TABLE: TEST_BBBB
      RSC_CPU: 43159   RSC_IO: 5
IX_SEL: 0.0000e+00 TB_SEL: 1.0346e-04
    Join: resc: 178046 resp: 178046
Join cardinality: 59717 = outer (35575) * inner (40000) * sel (4.1966e-05) [flag=0]
Best NL cost: 178046 resp: 178046
。。。。。。。。。。。。。。。。。。。。。。。。
。。。。。。。。。。。。。。。。。。。。。。。。
HA Join
Outer table:
    resc: 54 cdn: 40000 rcz: 30 deg: 1 resp: 54
Inner table: TEST_AAA
    resc: 49 cdn: 35575 rcz: 93 deg: 1 resp: 49
    using join:8 distribution:2 #groups:1
Hash join one ptn Resc: 85   Deg: 1
      hash_area: 124 (max=3610) buildfrag: 206                probefrag:   456 ppasses:    1
Hash join   Resc: 189   Resp: 189
ORDER BY sort
    SORT resource      Sort statistics
      Sort width:           82 Area size:      294912 Max Area size:    14784512   Degree: 1
      Blocks to Sort:     1068 Row size:          146 Rows:      59717
      Initial runs:          2 Merge passes:        1 IO Cost / pass:        326
      Total IO sort cost: 1394
      Total CPU sort cost: 81633851
      Total Temp space used: 18834000
    SORT resource      Sort statistics
      Sort width:           82 Area size:      294912 Max Area size:    14784512   Degree: 1
      Blocks to Sort:     1068 Row size:          146 Rows:      59717
      Initial runs:          2 Merge passes:        1 IO Cost / pass:        326
      Total IO sort cost: 1394
      Total CPU sort cost: 81633851
      Total Temp space used: 16327000
Final:
CST: 1590 CDN: 59717 RSC: 1589 RSP: 1589 BYTES: 7345191
IO-RSC: 1578 IO-RSP: 1578 CPU-RSC: 140469986 CPU-RSP: 140469986
***************************************
PARAMETERS USED BY THE OPTIMIZER
********************************
。。。。。。。。。。。。。。。。。。。。。。。。。。。。。。
***************************************
BASE STATISTICAL INFORMATION
***********************
Table stats    Table: AAA   Alias: A
TOTAL :: (NOT ANALYZED)    CDN: 0 NBLKS: 0 AVG_ROW_LEN: 0
_OPTIMIZER_PERCENT_PARALLEL = 0
BEST_CST: 1590.00 PATH: 2 Degree: 1
***************************************
OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
***********************
Join order[1]: AAA [ A]
Best so far: TABLE#: 0 CST:       1590 CDN:      59717 BYTES:   16063873
Final:
CST: 1590 CDN: 59717 RSC: 1589 RSP: 1589 BYTES: 16063873
IO-RSC: 1578 IO-RSP: 1578 CPU-RSC: 140469986 CPU-RSP: 140469986
***************************************
PARAMETERS USED BY THE OPTIMIZER
********************************
。。。。。。。。。。。。。。。。。。。。。。。
***************************************
BASE STATISTICAL INFORMATION
***********************
Table stats    Table: from$_subquery$_001   Alias: from$_subquery$_001
TOTAL :: (NOT ANALYZED)    CDN: 0 NBLKS: 0 AVG_ROW_LEN: 0
_OPTIMIZER_PERCENT_PARALLEL = 0
BEST_CST: 1590.00 PATH: 2 Degree: 1
***************************************
OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
***********************
Join order[1]: from$_subquery$_001 [from$_subquery$_001]
Best so far: TABLE#: 0 CST:       1590 CDN:      59717 BYTES:   16840194
Final:
CST: 1590 CDN: 59717 RSC: 1589 RSP: 1589 BYTES: 16840194
IO-RSC: 1578 IO-RSP: 1578 CPU-RSC: 140469986 CPU-RSP: 140469986
QUERY