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

阿吉2009

暗纹河豚毒 发表于 2013-9-9 14:33
你的意思是，一个是预估的，一个是实际实行的。

可是执行时间是3-4s，SQL能优化嘛？

你应该是执行了两次，一次是预估一次是实际。

〇〇

我的测试，两种计划没有区别
SQL> create table big(id int,a int,b int);

表已创建。

SQL> create table e(a int);

表已创建。

SQL> insert into big select level l,mod(level,7),mod(level,29) from dual connect by level<=1e5;

已创建 100000 行。

SQL> insert into e select mod(level,13)from dual connect by level<=1e3;

已创建 1000 行。


select 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;

暗纹河豚毒

阿吉2009 发表于 2013-9-9 14:38
你应该是执行了两次，一次是预估一次是实际。

执行了多遍，又看了一下trace
有NL的，有HJ的

阿吉2009

暗纹河豚毒 发表于 2013-9-9 14:53
执行了多遍，又看了一下trace
有NL的，有HJ的

你用DBMS_XPLAN.DISPLAY_CURSOR(sql_id, child_number, 'ALLSTATS LAST')要实际的执行计划出来。注意要全部贴上来，不要只贴一部分。

bell6248

lz, 测试一下如下的代码， 贴出执行计划和统计数据。

SELECT  /*+ no_merge(m) */ b.elec_meter_id,
                 MAX(b.total_used)-MIN(b.total_used) res
FROM bigdata b,
     (SELECT e.meter_id
       FROM elec_meter_info e
       WHERE e.is_delete = 'N'
       AND e.is_disable = 'N'
       AND e.is_init > 0
       AND e.org_stat_area = 0
       AND EXISTS
       (SELECT 1 FROM  org_info
              WHERE super_org_id = '52ac306a38755fcf01388e69195c007e'
              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
order by 1;

〇〇

〇〇 发表于 2013-9-9 14:50
我的测试，两种计划没有区别
SQL> create table big(id int,a int,b int);

加大数据就出现lz的现象

SQL> rollback;

回退已完成。

SQL> insert into big select level l,mod(level,7),mod(level,29) from dual connect by level<=1e6;

已创建 1000000 行。

SQL> insert into e select mod(level,13)from dual connect by level<=1e4;

已创建 10000 行。

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;


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

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |   288M|    13G| 19809  (97)| 00:00:01 |
|   1 |  HASH GROUP BY      |      |   288M|    13G| 19809  (97)| 00:00:01 |
|*  2 |   HASH JOIN         |      |   288M|    13G|  2130  (71)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL| E    |  3077 | 40001 |     7   (0)| 00:00:01 |
|*  4 |    TABLE ACCESS FULL| BIG  |  2716K|   101M|   726  (16)| 00:00:01 |
----------------------------------------------------------------------------

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)


统计信息
----------------------------------------------------------
          8  recursive calls
          1  db block gets
       2878  consistent gets
          0  physical reads
        176  redo size
        675  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          4  rows processed

SQL> 2
  2*        max(b.id) - /*或,*/
SQL> c/-/,
  2*        max(b.id) , /*或,*/
SQL> /


执行计划
----------------------------------------------------------
Plan hash value: 375847392
-----------------------------------------------------------------------------------
| Id  | Operation              | Name     | Rows  | Bytes | Cost (%CPU)| Time  |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |          |   642M|    31G|  3957  (85)| 00:00:01 |
|   1 |  SORT GROUP BY NOSORT  |          |   642M|    31G|  3957  (85)| 00:00:01 |
|   2 |   MERGE JOIN           |          |   642M|    31G|  3957  (85)| 00:00:01 |
|   3 |    SORT JOIN           |          |  2716K|   101M|   848  (28)| 00:00:01 |
|   4 |     VIEW               | VW_GBC_5 |  2716K|   101M|   848  (28)| 00:00:01 |
|   5 |      HASH GROUP BY     |          |  2716K|   101M|   848  (28)| 00:00:01 |
|*  6 |       TABLE ACCESS FULL| BIG      |  2716K|   101M|   720  (15)| 00:00:01 |
|*  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)


统计信息
----------------------------------------------------------
         28  recursive calls
          0  db block gets
       4728  consistent gets
          0  physical reads
          0  redo size
        764  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          4  rows processed

阿吉2009

〇〇 发表于 2013-9-9 15:07
加大数据就出现lz的现象

SQL> rollback;

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

可能是动态采样导致不稳定的执行计划。

阿吉2009

阿吉2009 发表于 2013-9-9 15:12
Note
-----
   - dynamic sampling used for this statement (level=2)

不对，他是同一条（即第二条）的执行计划每次不一样。

〇〇

阿吉2009 发表于 2013-9-9 15:12
Note
-----
   - dynamic sampling used for this statement (level=2)

SQL> exec DBMS_STATS.GATHER_TABLE_STATS('SCOTT','BIG');

PL/SQL 过程已成功完成。

SQL> exec DBMS_STATS.GATHER_TABLE_STATS('SCOTT','E');

PL/SQL 过程已成功完成。
SQL> set autot trace
SQL> /


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

----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |    13 |   182 |   901  (32)| 00:00:01 |
|   1 |  HASH GROUP BY      |      |    13 |   182 |   901  (32)| 00:00:01 |
|*  2 |   HASH JOIN         |      |  4961K|    66M|   659   (6)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL| E    |   500 |  1500 |     7   (0)| 00:00:01 |
|*  4 |    TABLE ACCESS FULL| BIG  |   280K|  3012K|   628   (3)| 00:00:01 |
----------------------------------------------------------------------------

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)


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       2232  consistent gets
          0  physical reads
          0  redo size
        680  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          4  rows processed
SQL> 2
  2*        max(b.id) - /*或,*/
SQL> c/-/,
  2*        max(b.id) , /*或,*/
SQL> /


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

----------------------------------------------------------------------------------
| Id  | Operation             | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |          |    13 |   182 |   644   (4)| 00:00:01 |
|   1 |  HASH GROUP BY        |          |    13 |   182 |   644   (4)| 00:00:01 |
|*  2 |   HASH JOIN           |          |   128K|  1763K|   638   (3)| 00:00:01 |
|   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      |   280K|  3012K|   628   (3)| 00:00:01 |
----------------------------------------------------------------------------------

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)


统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       2232  consistent gets
          0  physical reads
          0  redo size
        764  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          4  rows processed

暗纹河豚毒

阿吉2009 发表于 2013-9-9 14:58
你用DBMS_XPLAN.DISPLAY_CURSOR(sql_id, child_number, 'ALLSTATS LAST')要实际的执行计划出来。注意要 ...

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------
SQL_ID  ccf2nsqk610f5, child number 0
-------------------------------------
select b.elec_meter_id elec_meter_id,        max(b.total_used) -
min(b.total_used) x   from BIGDATA b,        (select distinct
e.meter_id           from ELEC_METER_INFO e,                (select
u.org_id                   from ORG_INFO u                  where
u.super_org_id = '52ac306a38755fcf01388e69195c007e') org          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 = org.org_id) meter  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 = meter.meter_id  group by b.elec_meter_id

Plan hash value: 321739330

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

| Id  | Operation                     | Name                  | Starts | E-Rows| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |

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

|   0 | SELECT STATEMENT              |                       |      1 |       |     37 |00:00:00.11 |   33746 |       |       |        |
|   1 |  HASH GROUP BY                |                       |      1 |      1|     37 |00:00:00.11 |   33746 |   784K|   784K| 1345K (0)|
|   2 |   NESTED LOOPS                |                       |      1 |       |  34216 |00:00:00.11 |   33746 |       |       |        |
|   3 |    NESTED LOOPS               |                       |      1 |      1|  34216 |00:00:00.02 |     778 |       |       |        |
|*  4 |     HASH JOIN                 |                       |      1 |      1|     48 |00:00:00.02 |     329 |   874K|   874K| 1348K (0)|
|*  5 |      TABLE ACCESS FULL        | ELEC_METER_INFO     |      1 |     34|   2782 |00:00:00.01 |     253 |       |       |        |
|*  6 |      TABLE ACCESS FULL        | ORG_INFO        |      1 |     55|     49 |00:00:00.01 |      76 |       |       |        |
|*  7 |     INDEX RANGE SCAN          | UIDX_BS_TRE_ELEC_DATE |     48 |      1|  34216 |00:00:00.01 |     449 |       |       |        |
|   8 |    TABLE ACCESS BY INDEX ROWID| BIGDATA        |  34216 |      1|  34216 |00:00:00.07 |   32968 |       |       |        |

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


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

   4 - access("E"."ORG_ID"="U"."ORG_ID")
   5 - filter(("E"."ORG_ID" IS NOT NULL AND "E"."ORG_STAT_AREA"=0 AND TO_NUMBER("E"."IS_INIT")>0 AND "E"."IS_DISABLE"='N' AND "E"."IS_DELETE"='N'))
   6 - filter("U"."SUPER_ORG_ID"='52ac306a38755fcf01388e69195c007e')
   7 - access("B"."ELEC_METER_ID"="E"."METER_ID" AND "B"."TREATE_DATE">='2013-01-01 00:00:00' AND "B"."TREATE_DATE"<='2014-01-01 00:00:00')
       filter("B"."ELEC_METER_ID" IS NOT NULL)

SQL_ID  ccf2nsqk610f5, child number 1
-------------------------------------
select b.elec_meter_id elec_meter_id,        max(b.total_used) -
min(b.total_used) x   from BIGDATA b,        (select distinct
e.meter_id           from ELEC_METER_INFO e,                (select
u.org_id                   from ORG_INFO u                  where
u.super_org_id = '52ac306a38755fcf01388e69195c007e') org          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 = org.org_id) meter  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 = meter.meter_id  group by b.elec_meter_id

Plan hash value: 2157596226

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

| Id  | Operation                     | Name                   | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |

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

|   0 | SELECT STATEMENT              |                        |      1 |        |     37 |00:00:05.75 |     378K|  45619 |       |       |          |
|   1 |  HASH GROUP BY                |                        |      1 |    542 |     37 |00:00:05.75 |     378K|  45619 |   784K|   784K| 1345K (0)|
|*  2 |   HASH JOIN                   |                        |      1 |    542 |  34216 |00:00:05.46 |     378K|  45619 |   915K|   915K| 1244K (0)|
|*  3 |    HASH JOIN                  |                        |      1 |     56 |     48 |00:00:00.02 |     329 |      0 |   915K|   915K| 1260K (0)|
|*  4 |     TABLE ACCESS FULL         | ORG_INFO               |      1 |     55 |     49 |00:00:00.01 |      76 |      0 |       |       |          |
|*  5 |     TABLE ACCESS FULL         | ELEC_METER_INFO        |      1 |   2782 |   2782 |00:00:00.01 |     253 |      0 |       |       |          |
|*  6 |    TABLE ACCESS BY INDEX ROWID| BIGDATA                |      1 |  34216 |   2957K|00:00:04.10 |     378K|  45619 |       |       |          |
|*  7 |     INDEX SKIP SCAN           | UIDX_BS_TRE_WATER_DATE |      1 |      1 |   3163K|00:00:02.22 |   15323 |  15177 |       |       |          |

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


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

   2 - access("B"."ELEC_METER_ID"="E"."METER_ID")
   3 - access("E"."ORG_ID"="U"."ORG_ID")
   4 - filter("U"."SUPER_ORG_ID"='52ac306a38755fcf01388e69195c007e')
   5 - filter(("E"."ORG_ID" IS NOT NULL AND "E"."ORG_STAT_AREA"=0 AND TO_NUMBER("E"."IS_INIT")>0 AND "E"."IS_DISABLE"='N' AND "E"."IS_DELETE"='N'))
   6 - filter("B"."ELEC_METER_ID" IS NOT NULL)
   7 - access("B"."TREATE_DATE">='2013-01-01 00:00:00' AND "B"."TREATE_DATE"<='2014-01-01 00:00:00')
       filter(("B"."TREATE_DATE">='2013-01-01 00:00:00' AND "B"."TREATE_DATE"<='2014-01-01 00:00:00'))

Note
-----   - cardinality feedback used for this statement

SQL_ID  ccf2nsqk610f5, child number 2
-------------------------------------
select b.elec_meter_id elec_meter_id,        max(b.total_used) -
min(b.total_used) x   from BIGDATA b,        (select distinct
e.meter_id           from ELEC_METER_INFO e,                (select
u.org_id                   from ORG_INFO u                  where
u.super_org_id = '52ac306a38755fcf01388e69195c007e') org          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 = org.org_id) meter  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 = meter.meter_id  group by b.elec_meter_id

Plan hash value: 2157596226

----------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                   | E-Rows |  OMem|  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                        |        |       |       |          |
|   1 |  HASH GROUP BY                |                        |    542 |   784K|   784K| 1350K (0)|
|*  2 |   HASH JOIN                   |                        |    542 |   915K|   915K| 1266K (0)|
|*  3 |    HASH JOIN                  |                        |     56 |   915K|   915K| 1266K (0)|
|*  4 |     TABLE ACCESS FULL         | ORG_INFO               |     55 |       |       |          |
|*  5 |     TABLE ACCESS FULL         | ELEC_METER_INFO        |   2782 |       |       |          |
|*  6 |    TABLE ACCESS BY INDEX ROWID| BIGDATA                |  34216 |       |       |          |
|*  7 |     INDEX SKIP SCAN           | UIDX_BS_TRE_WATER_DATE |      1 |       |       |          |
----------------------------------------------------------------------------------------------------

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

   2 - access("B"."ELEC_METER_ID"="E"."METER_ID")
   3 - access("E"."ORG_ID"="U"."ORG_ID")
   4 - filter("U"."SUPER_ORG_ID"='52ac306a38755fcf01388e69195c007e')
   5 - filter(("E"."ORG_ID" IS NOT NULL AND "E"."ORG_STAT_AREA"=0 AND
              TO_NUMBER("E"."IS_INIT")>0 AND "E"."IS_DISABLE"='N' AND "E"."IS_DELETE"='N'))
   6 - filter("B"."ELEC_METER_ID" IS NOT NULL)
   7 - access("B"."TREATE_DATE">='2013-01-01 00:00:00' AND "B"."TREATE_DATE"<='2014-01-01 00:00:00')
       filter(("B"."TREATE_DATE">='2013-01-01 00:00:00' AND "B"."TREATE_DATE"<='2014-01-01 00:00:00'))

Note
-----
   - cardinality feedback used for this statement
   - Warning: basic plan statistics not available. These are only collected when:
       * hint 'gather_plan_statistics' is used for the statement or
       * parameter 'statistics_level' is set to 'ALL', at session or system level


已选择133行。

已用时间:  00: 00: 00.13