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

〇〇

〇〇 发表于 2013-9-9 12:04
那就把中间结果连接来求

这种还是比较快的，虽然扫描了2次

select x.b,x.x-y.y from (
select b.b,
       max(b.id)x, /*或,*/
       min(b.id)y
  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)x,
(
select b.b,
       max(b.id)x, /*或,*/
       min(b.id)y
  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)y
where y.b=x.b;
执行计划
----------------------------------------------------------
Plan hash value: 3195090795

-------------------------------------------------------------------------------------
| Id  | Operation               | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |           |    13 |   676 |  1288   (4)| 00:00:01 |
|*  1 |  HASH JOIN              |           |    13 |   676 |  1288   (4)| 00:00:01 |
|   2 |   VIEW                  |           |    13 |   338 |   644   (4)| 00:00:01 |
|   3 |    HASH GROUP BY        |           |    13 |   182 |   644   (4)| 00:00:01 |
|*  4 |     HASH JOIN           |           |   128K|  1763K|   638   (3)| 00:00:01 |
|   5 |      VIEW               | VW_GBF_12 |    13 |    39 |     8  (13)| 00:00:01 |
|   6 |       HASH GROUP BY     |           |    13 |    39 |     8  (13)| 00:00:01 |
|*  7 |        TABLE ACCESS FULL| E         |   500 |  1500 |     7   (0)| 00:00:01 |
|*  8 |      TABLE ACCESS FULL  | BIG       |   280K|  3012K|   628   (3)| 00:00:01 |
|   9 |   VIEW                  |           |    13 |   338 |   644   (4)| 00:00:01 |
|  10 |    HASH GROUP BY        |           |    13 |   182 |   644   (4)| 00:00:01 |
|* 11 |     HASH JOIN           |           |   128K|  1763K|   638   (3)| 00:00:01 |
|  12 |      VIEW               | VW_GBF_6  |    13 |    39 |     8  (13)| 00:00:01 |
|  13 |       HASH GROUP BY     |           |    13 |    39 |     8  (13)| 00:00:01 |
|* 14 |        TABLE ACCESS FULL| E         |   500 |  1500 |     7   (0)| 00:00:01 |
|* 15 |      TABLE ACCESS FULL  | BIG       |   280K|  3012K|   628   (3)| 00:00:01 |
-------------------------------------------------------------------------------------

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

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


统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       4460  consistent gets
          0  physical reads
          0  redo size
        679  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

暗纹河豚毒

〇〇 发表于 2013-9-9 15:26
这种还是比较快的，虽然扫描了2次

select x.b,x.x-y.y from (

好的，我改写一下试试

〇〇

〇〇 发表于 2013-9-9 15:26
这种还是比较快的，虽然扫描了2次

select x.b,x.x-y.y from (

改用with反而慢多了

with x as(
select b.b,
       max(b.id)x, /*或,*/
       min(b.id)y
  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)
select x.b,x.x-y.y from x,
x y
where y.b=x.b;
执行计划
----------------------------------------------------------
Plan hash value: 990172467

----------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                             |     2 |   104 |   906  (32)| 00:00:01 |
|   1 |  TEMP TABLE TRANSFORMATION |                             |       |       |            |          |
|   2 |   LOAD AS SELECT           | SYS_TEMP_0FD9D70E7_42BAC073 |       |       |            |          |
|   3 |    HASH GROUP BY           |                             |    13 |   182 |   901  (32)| 00:00:01 |
|*  4 |     HASH JOIN              |                             |  4961K|    66M|   659   (6)| 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 |
|*  7 |   HASH JOIN                |                             |     2 |   104 |     5  (20)| 00:00:01 |
|   8 |    VIEW                    |                             |    13 |   338 |     2   (0)| 00:00:01 |
|   9 |     TABLE ACCESS FULL      | SYS_TEMP_0FD9D70E7_42BAC073 |    13 |   182 |     2   (0)| 00:00:01 |
|  10 |    VIEW                    |                             |    13 |   338 |     2   (0)| 00:00:01 |
|  11 |     TABLE ACCESS FULL      | SYS_TEMP_0FD9D70E7_42BAC073 |    13 |   182 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------

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

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


统计信息
----------------------------------------------------------
         55  recursive calls
          8  db block gets
       2272  consistent gets
          1  physical reads
        852  redo size
        679  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

阿吉2009

暗纹河豚毒 发表于 2013-9-9 15:20
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------- ...

好难交流。

1）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
至少最后一条没有设置收集级别为ALL。
2）第一次ELEC_METER_INFO表的实际记录为34条，而第二次则是2782条！建议收集相关表的统计信息。

bell6248

lz, 你目前第二个代码效率低是由于对大表的访问使用了index skip scan, 导致产生了很大的一致读， 我前面给的语句你测试一下， 按要求提供详细信息！

暗纹河豚毒

阿吉2009 发表于 2013-9-9 15:31
好难交流。

1）Note

呵呵抱歉，LZ是新手

刚刚看到版主执行了DBMS_STATS.GATHER_TABLE_STATS，我再试一下

阿吉2009

暗纹河豚毒 发表于 2013-9-9 15:36
呵呵抱歉，LZ是新手

刚刚看到版主执行了DBMS_STATS.GATHER_TABLE_STATS，我再试一下

如是表很大，可能要些时间，建议DB空闲时再做，且最好用100%的估算率，最好是也加上直方图。

〇〇

bell6248 发表于 2013-9-9 15:32
lz, 你目前第二个代码效率低是由于对大表的访问使用了index skip scan, 导致产生了很大的一致读， 我前面给 ...

我的表没有索引，都是全表扫描，慢的全是cpu高

暗纹河豚毒

bell6248 发表于 2013-9-9 15:32
lz, 你目前第二个代码效率低是由于对大表的访问使用了index skip scan, 导致产生了很大的一致读， 我前面给 ...

看到回复之前，已经执行过DBMS_STATS.GATHER_TABLE_STATS了。


下面是详细信息
15:42:14 ecms0219@ECMS> set autotrace on
15:42:22 ecms0219@ECMS> select /*+ no_merge(m) */ b.elec_meter_id elec_meter_id
15:43:18   2         max(b.total_used) - min(b.total_used) x
15:43:18   3    from BIGDATA b,
15:43:18   4         (select distinct e.meter_id
15:43:18   5            from ELEC_METER_INFO e,
15:43:18   6                 (select u.org_id
15:43:18   7                    from ORG_INFO u
15:43:18   8                   where u.super_org_id = '52ac306a38755fcf01388e69
95c007e') org
15:43:18   9           where e.is_delete = 'N'
15:43:18  10             and e.is_disable = 'N'
15:43:18  11             and e.is_init > 0
15:43:18  12             and e.org_stat_area = 0
15:43:18  13             and e.org_id = org.org_id) m
15:43:18  14   where b.treate_date >= '2013-01-01 00:00:00'
15:43:18  15     and b.treate_date <= '2014-01-01 00:00:00'
15:43:18  16     and b.elec_meter_id = m.meter_id
15:43:18  17   group by b.elec_meter_id
15:43:18  18   order by 1;

ELEC_METER_ID                                                             X
---------------------------------------------------------------- ----------
52ac306a38755fcf01388f0e2fbe0132                                      180.2
...
已选择37行。
已用时间:  00: 00: 00.13
执行计划
----------------------------------------------------------
Plan hash value: 1145997452
-------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                  | Rows  | Bytes |Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                       |   823 | 74070 |  813   (1)| 00:00:10 |
|   1 |  SORT GROUP BY                |                       |   823 | 74070 |  813   (1)| 00:00:10 |
|   2 |   NESTED LOOPS                |                       |       |       |           |          |
|   3 |    NESTED LOOPS               |                       |   823 | 74070 |  813   (1)| 00:00:10 |
|   4 |     VIEW                      |                       |     1 |    33 |   93   (3)| 00:00:02 |
|   5 |      HASH UNIQUE              |                       |     1 |   135 |   93   (3)| 00:00:02 |
|*  6 |       HASH JOIN               |                       |     1 |   135 |   92   (2)| 00:00:02 |
|*  7 |        TABLE ACCESS FULL      | US_ELECMETER_INFO     |    34 |  2346 |   69   (0)| 00:00:01 |
|*  8 |        TABLE ACCESS FULL      | US_ORGAN_INFOR        |    55 |  3630 |   22   (0)| 00:00:01 |
|*  9 |     INDEX RANGE SCAN          | UIDX_BS_TRE_ELEC_DATE |   823 |       |   10   (0)| 00:00:01 |
|  10 |    TABLE ACCESS BY INDEX ROWID| BS_TREATEDDATA        |   823 | 46911 |  720   (0)| 00:00:09 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   6 - access("E"."ORG_ID"="U"."ORG_ID")
   7 - 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')
   8 - filter("U"."SUPER_ORG_ID"='52ac306a38755fcf01388e69195c007e')
   9 - access("B"."ELEC_METER_ID"="M"."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)

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

15:43:35 ecms0219@ECMS>

bell6248

暗纹河豚毒 发表于 2013-9-9 15:51
看到回复之前，已经执行过DBMS_STATS.GATHER_TABLE_STATS了。

逻辑读目前的数目大大下降了， 改过语句的执行计划正常了.