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

dingjun123

newkid 发表于 2013-9-9 22:31
这个以前有过定论的吧，必须分开来写。

他这个不是index min/max那计划

angu204

with a as (
select b.elec_meter_id elec_meter_id,
       max(b.total_used) max_used,
       min(b.total_used) min_used
  from bigdata b,
       (select distinct e.meter_id
          from elec_meter_info e,
               (select org_id
                  from org_info
                 where super_org_id = '52ac306a38755fcf01388e69195c007e') o
         where e.is_delete = 'N'
           and e.is_disable = 'N'
           and e.is_init > 0
           and e.org_stat_area = 0
           and e.org_id = o.org_id) m
where b.treate_date >= '2013-01-01 00:00:00'
   and b.treate_date <= '2014-01-01 00:00:00'
   and b.elec_meter_id = m.meter_id
group by b.elec_meter_id)
select a.elec_meter_id,max_used-min_used from a;
这样呢？

lastwinner

b.treate_date >= '2013-01-01 00:00:00'   and b.treate_date <= '2014-01-01 00:00:00'

这地方感觉有两个问题
一是隐式转换，二是后一个条件应为b.treate_date <'2014-01-01 00:00:00'

〇〇

lastwinner 发表于 2013-9-10 00:23
b.treate_date >= '2013-01-01 00:00:00'   and b.treate_date

看看我的例子

〇〇

我找了另一台windows机器，内存也很大，同样的计划，执行都慢了，全表扫描估计用了8秒

SQL> select b.b,
  2         max(b.id) , /*很快*/
  3         min(b.id) min_used
  4    from big b,
  5         (select distinct e.a
  6            from e
  7          where e.a like '1%') m
  8  where b.a>=3
  9     and b.a<=4
10     and b.b= m.a
11  group by b.b;

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

已用时间:  00: 00: 09.62
SQL> select b.b,
  2         max(b.id) - /*很慢*/
  3         min(b.id) min_used
  4    from big b,
  5         (select distinct e.a
  6            from e
  7          where e.a like '1%') m
  8  where b.a>=3
  9     and b.a<=4
10     and b.b= m.a
11  group by b.b;

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

已用时间:  00: 00: 09.62
SQL> exec dbms_stats.gather_table_stats(ownname => 'scott',tabname => 'big',estimate_percent => 100,method_opt=> 'for all columns') ;

PL/SQL 过程已成功完成。

已用时间:  00: 00: 06.19
SQL>
SQL>
SQL> exec dbms_stats.gather_table_stats(ownname => 'scott',tabname => 'e',estimate_percent => 100,method_opt=> 'for all columns') ;

PL/SQL 过程已成功完成。

已用时间:  00: 00: 00.04
SQL>
SQL>
SQL> select b.b,
  2         max(b.id) , /*很快*/
  3         min(b.id) min_used
  4    from big b,
  5         (select distinct e.a
  6            from e
  7          where e.a like '1%') m
  8  where b.a>=3
  9     and b.a<=4
10     and b.b= m.a
11  group by b.b;

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

已用时间:  00: 00: 09.47
SQL> set lines 132 pages 50000
SQL> set autot trace
SP2-0618: 无法找到会话标识符。启用检查 PLUSTRACE 角色
SP2-0611: 启用 STATISTICS 报告时出错
SQL> /
已用时间:  00: 00: 00.00

执行计划
----------------------------------------------------------
Plan hash value: 69571800

----------------------------------------------------------------------------------
| Id  | Operation             | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |          |    13 |   182 |   644   (4)| 00:00:08 |
|   1 |  HASH GROUP BY        |          |    13 |   182 |   644   (4)| 00:00:08 |
|*  2 |   HASH JOIN           |          |   128K|  1751K|   638   (3)| 00:00:08 |
|   3 |    VIEW               | VW_GBF_5 |    13 |    39 |     8  (13)| 00:00:01 |
|   4 |     HASH GROUP BY     |          |    13 |    39 |     8  (13)| 00:00:01 |
|*  5 |      TABLE ACCESS FULL| E        |   500 |  1500 |     7   (0)| 00:00:01 |
|*  6 |    TABLE ACCESS FULL  | BIG      |   285K|  3069K|   628   (3)| 00:00:08 |
----------------------------------------------------------------------------------

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)

SQL> set autot on
SP2-0618: 无法找到会话标识符。启用检查 PLUSTRACE 角色
SP2-0611: 启用 STATISTICS 报告时出错
SQL> /

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

已用时间:  00: 00: 09.65

执行计划
----------------------------------------------------------
Plan hash value: 69571800

----------------------------------------------------------------------------------
| Id  | Operation             | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |          |    13 |   182 |   644   (4)| 00:00:08 |
|   1 |  HASH GROUP BY        |          |    13 |   182 |   644   (4)| 00:00:08 |
|*  2 |   HASH JOIN           |          |   128K|  1751K|   638   (3)| 00:00:08 |
|   3 |    VIEW               | VW_GBF_5 |    13 |    39 |     8  (13)| 00:00:01 |
|   4 |     HASH GROUP BY     |          |    13 |    39 |     8  (13)| 00:00:01 |
|*  5 |      TABLE ACCESS FULL| E        |   500 |  1500 |     7   (0)| 00:00:01 |
|*  6 |    TABLE ACCESS FULL  | BIG      |   285K|  3069K|   628   (3)| 00:00:08 |
----------------------------------------------------------------------------------

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)

SQL> show sga
ORA-00942: 表或视图不存在


SQL>

〇〇

在另一个磁盘新建表空间测试

SQL> conn / as sysdba
已连接。
SQL> create tablespace ts datafile 'e:\temp\ts.dbf' size 100M;

表空间已创建。

已用时间:  00: 00: 00.50
SQL> alter user scott default tablespace ts quota unlimited on ts;

用户已更改。

已用时间:  00: 00: 00.01
SQL> conn scott/tiger
已连接。
SQL> create table big(id int,a int,b int);

表已创建。

已用时间:  00: 00: 00.02
SQL> create table e(a int);

表已创建。

已用时间:  00: 00: 00.01
SQL> insert into big select level l,mod(level,7),mod(level,29) from dual connect by level<=1e6;

已创建 1000000 行。

已用时间:  00: 00: 02.96
SQL> insert into e select mod(level,13)from dual connect by level<=1e4;

已创建 10000 行。

已用时间:  00: 00: 00.04
SQL> commit;

提交完成。

已用时间:  00: 00: 00.00
SQL>
SQL> set autot on
SP2-0618: 无法找到会话标识符。启用检查 PLUSTRACE 角色
SP2-0611: 启用 STATISTICS 报告时出错
SQL>
SQL> select b.b,
  2         max(b.id) , /*很快*/
  3         min(b.id) min_used
  4    from big b,
  5         (select distinct e.a
  6            from e
  7          where e.a like '1%') m
  8  where b.a>=3
  9     and b.a<=4
10     and b.b= m.a
11  group by b.b;

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

已用时间:  00: 00: 00.41

执行计划
----------------------------------------------------------
Plan hash value: 375847392

-----------------------------------------------------------------------------------
| Id  | Operation              | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |          |    70M|  3482M|   991  (38)| 00:00:12 |
|   1 |  SORT GROUP BY NOSORT  |          |    70M|  3482M|   991  (38)| 00:00:12 |
|   2 |   MERGE JOIN           |          |    70M|  3482M|   991  (38)| 00:00:12 |
|   3 |    SORT JOIN           |          |   296K|    11M|   639   (4)| 00:00:08 |
|   4 |     VIEW               | VW_GBC_5 |   296K|    11M|   639   (4)| 00:00:08 |
|   5 |      HASH GROUP BY     |          |   296K|    11M|   639   (4)| 00:00:08 |
|*  6 |       TABLE ACCESS FULL| BIG      |   296K|    11M|   626   (2)| 00:00:08 |
|*  7 |    SORT JOIN           |          |  3077 | 40001 |     8  (13)| 00:00:01 |
|*  8 |     TABLE ACCESS FULL  | E        |  3077 | 40001 |     7   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

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

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

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

SQL>
SQL> select b.b,
  2         max(b.id) - /*很慢*/
  3         min(b.id) min_used
  4    from big b,
  5         (select distinct e.a
  6            from e
  7          where e.a like '1%') m
  8  where b.a>=3
  9     and b.a<=4
10     and b.b= m.a
11  group by b.b;

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

已用时间:  00: 00: 09.63

执行计划
----------------------------------------------------------
Plan hash value: 2033372611

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |    31M|  1561M|  2525  (76)| 00:00:31 |
|   1 |  HASH GROUP BY      |      |    31M|  1561M|  2525  (76)| 00:00:31 |
|*  2 |   HASH JOIN         |      |    31M|  1561M|   789  (22)| 00:00:10 |
|*  3 |    TABLE ACCESS FULL| E    |  3077 | 40001 |     7   (0)| 00:00:01 |
|*  4 |    TABLE ACCESS FULL| BIG  |   296K|    11M|   627   (3)| 00:00:08 |
----------------------------------------------------------------------------

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)

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

SQL>
SQL> exec dbms_stats.gather_table_stats(ownname => 'scott',tabname => 'big',estimate_percent => 100,method_opt=> 'for all columns') ;

PL/SQL 过程已成功完成。

已用时间:  00: 00: 06.22
SQL>
SQL> exec dbms_stats.gather_table_stats(ownname => 'scott',tabname => 'e',estimate_percent => 100,method_opt=> 'for all columns') ;

PL/SQL 过程已成功完成。

已用时间:  00: 00: 00.02
SQL>
SQL>
SQL>
SQL> select b.b,
  2         max(b.id) , /*很快*/
  3         min(b.id) min_used
  4    from big b,
  5         (select distinct e.a
  6            from e
  7          where e.a like '1%') m
  8  where b.a>=3
  9     and b.a<=4
10     and b.b= m.a
11  group by b.b;

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

已用时间:  00: 00: 00.30

执行计划
----------------------------------------------------------
Plan hash value: 69571800

----------------------------------------------------------------------------------
| Id  | Operation             | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |          |    13 |   182 |   644   (4)| 00:00:08 |
|   1 |  HASH GROUP BY        |          |    13 |   182 |   644   (4)| 00:00:08 |
|*  2 |   HASH JOIN           |          |   128K|  1751K|   638   (3)| 00:00:08 |
|   3 |    VIEW               | VW_GBF_5 |    13 |    39 |     8  (13)| 00:00:01 |
|   4 |     HASH GROUP BY     |          |    13 |    39 |     8  (13)| 00:00:01 |
|*  5 |      TABLE ACCESS FULL| E        |   500 |  1500 |     7   (0)| 00:00:01 |
|*  6 |    TABLE ACCESS FULL  | BIG      |   285K|  3069K|   628   (3)| 00:00:08 |
----------------------------------------------------------------------------------

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)

SQL>
SQL> select b.b,
  2         max(b.id) - /*很慢*/
  3         min(b.id) min_used
  4    from big b,
  5         (select distinct e.a
  6            from e
  7          where e.a like '1%') m
  8  where b.a>=3
  9     and b.a<=4
10     and b.b= m.a
11  group by b.b;

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

已用时间:  00: 00: 09.71

执行计划
----------------------------------------------------------
Plan hash value: 2033372611

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |    13 |   182 |   903  (32)| 00:00:11 |
|   1 |  HASH GROUP BY      |      |    13 |   182 |   903  (32)| 00:00:11 |
|*  2 |   HASH JOIN         |      |  4926K|    65M|   660   (6)| 00:00:08 |
|*  3 |    TABLE ACCESS FULL| E    |   500 |  1500 |     7   (0)| 00:00:01 |
|*  4 |    TABLE ACCESS FULL| BIG  |   285K|  3069K|   628   (3)| 00:00:08 |
----------------------------------------------------------------------------

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)

SQL>

暗纹河豚毒

angu204 发表于 2013-9-9 22:51
with a as (
select b.elec_meter_id elec_meter_id,
       max(b.total_used) max_used,

谢谢

这种方法我在发帖之前就试过了，还是很慢

暗纹河豚毒

〇〇 发表于 2013-9-10 06:53
我找了另一台windows机器，内存也很大，同样的计划，执行都慢了，全表扫描估计用了8秒

SQL> select b.b, ...

晚上执行了
exec dbms_stats.gather_table_stats(ownname => 'ecms0219',tabname => 'bigdata’,estimate_percent => 100,method_opt=> 'for all columns');
之后，两条SQL执行速度差不多，查了多次都在0.3s以内，原先慢SQL的计划现在都变成了NESTED LOOPS。

阿吉2009

暗纹河豚毒 发表于 2013-9-10 08:00
晚上执行了
exec dbms_stats.gather_table_stats(ownname => 'ecms0219',tabname => 'bigdata’,estimat ...

没有使用HINT？

〇〇

lz把你的distinct改为group by 试试看
我的改了以后减法和不做减法计划相同了

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

select * from TABLE(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST'));

SQL> select /*+ gather_plan_statistics */b.b,
  2         max(b.id) - /*很慢*/
  3         min(b.id) min_used
  4    from big b,
  5         (select distinct e.a
  6            from e
  7          where e.a like '1%') m
  8  where b.a>=3
  9     and b.a<=4
10     and b.b= m.a
11  group by b.b;

select * from TABLE(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST'));

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

SQL> SQL>
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  238f8vj22nb8m, child number 0
-------------------------------------
select /*+ gather_plan_statistics */b.b,        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:10.61 |    2230 |       |       |          |
|   1 |  HASH GROUP BY      |      |      1 |     13 |      4 |00:00:10.61 |    2230 |   838K|   838K| 4105K (0)|
|*  2 |   HASH JOIN         |      |      1 |   4926K|     30M|00:00:05.75 |    2230 |  1517K|  1517K| 1004K (0)|
|*  3 |    TABLE ACCESS FULL| E    |      1 |    500 |   3077 |00:00:00.01 |      22 |       |       |          |
|*  4 |    TABLE ACCESS FULL| BIG  |      1 |    285K|    285K|00:00:00.09 |    2208 |       |       |          |
-----------------------------------------------------------------------------------------------------------------

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


已选择26行。

SQL> select /*+ gather_plan_statistics */b.b,
  2         max(b.id) - /*很慢*/
  3         min(b.id) min_used
  4    from big b,
  5         (select e.a
  6            from e
  7          where e.a like '1%' group by e.a) m
  8  where b.a>=3
  9     and b.a<=4
10     and b.b= m.a
11  group by b.b;

select * from TABLE(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST'));
         B   MIN_USED
---------- ----------
         1     999804
        11     999978
        12     999804
        10     999978

SQL> SQL>

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  44x1j8gq0xhrg, child number 0
-------------------------------------
select /*+ gather_plan_statistics */b.b,        max(b.id) - /*很慢*/
  min(b.id) min_used   from big b,        (select e.a           from e
       where e.a like '1%' group by e.a) m where b.a>=3    and b.a<=4
and b.b= m.a group by b.b

Plan hash value: 3990005897

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |      1 |        |      4 |00:00:00.15 |    2230 |       |       |          |
|   1 |  HASH GROUP BY        |      |      1 |     13 |      4 |00:00:00.15 |    2230 |   838K|   838K| 1254K (0)|
|*  2 |   HASH JOIN           |      |      1 |    128K|  39410 |00:00:00.13 |    2230 |  1517K|  1517K|  863K (0)|
|   3 |    VIEW               |      |      1 |     13 |      4 |00:00:00.01 |      22 |       |       |          |
|   4 |     HASH GROUP BY     |      |      1 |     13 |      4 |00:00:00.01 |      22 |  1518K|  1518K|  707K (0)|
|*  5 |      TABLE ACCESS FULL| E    |      1 |    500 |   3077 |00:00:00.01 |      22 |       |       |          |
|*  6 |    TABLE ACCESS FULL  | BIG  |      1 |    285K|    285K|00:00:00.08 |    2208 |       |       |          |
-------------------------------------------------------------------------------------------------------------------

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