分组求max，min速度很快，但是求max-min速度很慢

阿吉2009

C:\Documents and Settings\10c001>sqlplus mes/MES@XXMES

SQL*Plus: Release 11.2.0.1.0 Production on 星期二 9月 10 08:16:51 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Release 11.2.0.1.0 - 64bit Production

SQL> @c:\plset

Session altered.

SQL> SELECT b.b,  max(b.ID) , /*test*/ min(b.ID) min_used FROM big b, (SELECT DISTINCT E.A FROM E WHERE E.A LIKE '1%') M
  2    WHERE b.A>=3 AND b.A<=4 AND b.b= M.A GROUP BY b.b;

         B  MAX(B.ID)   MIN_USED
---------- ---------- ----------
         1     999863         59
        11     999989         11
        12     999961        157
        10     999988         10

SQL> @c:\pln test
old   6: where upper(sql_text) like '%&1%'
new   6: where upper(sql_text) like '%test%'

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

------------------------

SQL_ID  9kq9byyj13ysw, child number 0
-------------------------------------
SELECT b.b,  max(b.ID) , /*test*/ min(b.ID) min_used FROM big b,
(SELECT DISTINCT E.A FROM E WHERE E.A LIKE '1%') M   WHERE b.A>=3 AND
b.A<=4 AND b.b= M.A GROUP BY b.b

Plan hash value: 69571800

-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |          |      1 |        |      4 |00:00:00.97 |    1147 |       |    |             |
|   1 |  HASH GROUP BY        |          |      1 |     13 |      4 |00:00:00.97 |    1147 |   718K|   718K| 1191K (0)|
|*  2 |   HASH JOIN           |          |      1 |    128K|  39410 |00:00:00.93 |    1147 |  1301K|  1301K|  940K (0)|
|   3 |    VIEW               | VW_GBF_5 |      1 |     13 |      4 |00:00:00.01 |      12 |       |    |             |
|   4 |     HASH GROUP BY     |          |      1 |     13 |      4 |00:00:00.01 |      12 |  1302K|  1302K|  715K (0)|
|*  5 |      TABLE ACCESS FULL| E        |      1 |    500 |   3077 |00:00:00.01 |      12 |       |    |             |
|*  6 |    TABLE ACCESS FULL  | BIG      |      1 |    285K|    285K|00:00:00.26 |    1135 |       |    |             |
-----------------------------------------------------------------------------------------------------------------------

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

   2 - access("B"."B"="ITEM_1")
   5 - filter(TO_CHAR("E"."A") LIKE '1%')
   6 - filter(("B"."A">=3 AND "B"."A"<=4))


27 rows selected.

SQL> SELECT b.b, /*test2*/ max(b.ID) - min(b.ID) min_used FROM big b, (SELECT DISTINCT E.A FROM E WHERE E.A LIKE '1%') M
  2    WHERE b.A>=3 AND b.A<=4 AND b.b= M.A GROUP BY b.b;

         B   MIN_USED
---------- ----------
         1     999804
        11     999978
        12     999804
        10     999978

SQL> @c:\pln test2
old   6: where upper(sql_text) like '%&1%'
new   6: where upper(sql_text) like '%test2%'

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

------------------------

SQL_ID  8w619bv23hwy3, child number 0
-------------------------------------
SELECT b.b, /*test2*/ max(b.ID) - min(b.ID) min_used FROM big b,
(SELECT DISTINCT E.A FROM E WHERE E.A LIKE '1%') M   WHERE b.A>=3 AND
b.A<=4 AND b.b= M.A GROUP BY b.b

Plan hash value: 2033372611

-----------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |        |      4 |00:00:54.02 |    1147 |       |       |          |
|   1 |  HASH GROUP BY      |      |      1 |     13 |      4 |00:00:54.02 |    1147 |   718K|   718K| 3978K (0)|
|*  2 |   HASH JOIN         |      |      1 |   4926K|     30M|00:00:25.76 |    1147 |  1301K|  1301K| 1156K (0)|
|*  3 |    TABLE ACCESS FULL| E    |      1 |    500 |   3077 |00:00:00.01 |      12 |       |       |          |
|*  4 |    TABLE ACCESS FULL| BIG  |      1 |    285K|    285K|00:00:00.26 |    1135 |       |       |          |
-----------------------------------------------------------------------------------------------------------------

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

   2 - access("B"."B"="E"."A")
   3 - filter(TO_CHAR("E"."A") LIKE '1%')
   4 - filter(("B"."A">=3 AND "B"."A"<=4))


25 rows selected.
从test2看，哈希关联行数达285K*3077次，结果为30M行，比TEST的39410多得多，这正是慢的原因，最根本的原因还是关联前两个数据源的数量不同，TEST是将E表的3077行DISTINCT后只剩4行，而TEST2

仍是3077行，也就是说TEST2比TEST在关联时多做多285K*3073次比较。如将TEST2不进行视图合并，则与TEST一样了。
SQL> SELECT b.b, /*test3*/ max(b.ID) - min(b.ID) min_used FROM big b, (SELECT /*+no_merge*/ DISTINCT E.A FROM E WHERE E.A LIKE '1%') M
  2    WHERE b.A>=3 AND b.A<=4 AND b.b= M.A GROUP BY b.b;

         B   MIN_USED
---------- ----------
         1     999804
        11     999978
        12     999804
        10     999978

SQL> @c:\pln test3
old   6: where upper(sql_text) like '%&1%'
new   6: where upper(sql_text) like '%test3%'

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

------------------------

SQL_ID  5rwvaxyv2cps0, child number 0
-------------------------------------
SELECT b.b, /*test3*/ max(b.ID) - min(b.ID) min_used FROM big b,
(SELECT /*+no_merge*/ DISTINCT E.A FROM E WHERE E.A LIKE '1%') M
WHERE b.A>=3 AND b.A<=4 AND b.b= M.A GROUP BY b.b

Plan hash value: 781819126

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |      1 |        |      4 |00:00:01.00 |    1147 |       |       |          |
|   1 |  HASH GROUP BY        |      |      1 |     13 |      4 |00:00:01.00 |    1147 |   718K|   718K| 1186K (0)|
|*  2 |   HASH JOIN           |      |      1 |    128K|  39410 |00:00:00.96 |    1147 |  1301K|  1301K|  820K (0)|
|   3 |    VIEW               |      |      1 |     13 |      4 |00:00:00.01 |      12 |       |       |          |
|   4 |     HASH UNIQUE       |      |      1 |     13 |      4 |00:00:00.01 |      12 |  1302K|  1302K|  695K (0)|
|*  5 |      TABLE ACCESS FULL| E    |      1 |    500 |   3077 |00:00:00.01 |      12 |       |       |          |
|*  6 |    TABLE ACCESS FULL  | BIG  |      1 |    285K|    285K|00:00:00.26 |    1135 |       |       |          |
-------------------------------------------------------------------------------------------------------------------

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

   2 - access("B"."B"="M"."A")
   5 - filter(TO_CHAR("E"."A") LIKE '1%')
   6 - filter(("B"."A">=3 AND "B"."A"<=4))


27 rows selected.

阿吉2009

关键是视图内的DISTINCT后有多少行的问题，
一）如果视图内没有相同值，而走视图不合并，则会多了一步DISTINCT操作，如果记录很多，则DISTINCT要多花时间，走视图合并则较快。
二）如果视图内有相同值，DISTINCT后记录行少，走视图不合并，因源表行很少，所以就快。
所以这个CBO是无法做最优选择的，因它无法在执行前计算源表到底有多少行记录。

阿吉2009

〇〇 发表于 2013-9-10 10:53
lz把你的distinct改为group by 试试看
我的改了以后减法和不做减法计划相同了

看来是GROUP BY能禁止视图合并，而DISTINCT则不能。

暗纹河豚毒

阿吉2009 发表于 2013-9-10 09:10
没有使用HINT？

没有用HINT

暗纹河豚毒

〇〇 发表于 2013-9-10 10:53
lz把你的distinct改为group by 试试看
我的改了以后减法和不做减法计划相同了

是的，改过之后计划相同了
多谢大大

dingjun123

阿吉2009 发表于 2013-9-10 11:30
看来是GROUP BY能禁止视图合并，而DISTINCT则不能。

都能视图合并，complex view merge,限制比较多而已

lastwinner

distinct的效率要比group by慢一些，据说在9i上也如是

dingjun123

lastwinner 发表于 2013-9-10 15:04
distinct的效率要比group by慢一些，据说在9i上也如是

一样的啊

http://asktom.oracle.com/pls/ask ... N_ID:32961403234212

〇〇

--目前最快
select /*+ gather_plan_statistics */b.b,
        max(b.id) - /*很慢*/
        min(b.id) min_used
   from big b
where b.a>=3
    and b.a<=4
and exists(select  1
           from e m
         where m.a like '1%'  and b.b= m.a)
group by b.b;

阿吉2009

〇〇 发表于 2013-9-10 16:49
--目前最快
select /*+ gather_plan_statistics */b.b,
        max(b.id) - /*很慢*/

为何说这样是最快的？我觉得跟视图不合并差不多一样快。你这句走的是HJ SEMI，但驱动表E有3077条记录，而视图不合并尽管走的是HJ，比HJ SEMI慢，但他的驱动表只有4条啊，全比较4条与半比较3077条哪个更快呢？往极端想，如果E有5W记录，DISTINCT后也是4条，那么走视图不合并要快些，如E表只有4条且都不相同，那么你这句就快些了。