讨论：2个几乎相同的sql，为啥效率相差10倍？

Question

SELECT E.DATA_DATE,'01',P.METER_ID,P.COLL_OBJ_ID,
       
        E.R,
         '3010',
       TO_CHAR(E.GET_DATE, 'yyyymm') as GET_DATE
       FROM E_MR_DAY_READ@testdb E,
     
       C_CONS@testdb CC ,P_MR_MPED@testdb P
WHERE  
E.DATA_DATE = TRUNC(SYSDATE-1,'DD') 
 AND P.MPED_ID=E.ID
AND CC.CONS_ID=P.CONS_ID
AND 
EXISTS (select 1 from R_PLAN_DAY@testdb  where MR_DAY=SUBSTR(TO_CHAR(SYSDATE,'YYYY-MM-DD'),9,10) 
                 and MR_SECT_NO=CC.MR_SECT_NO)SELECT E.DATA_DATE,'01',P.METER_ID,P.COLL_OBJ_ID,
      E.PAP_R,
       '3010',
                        TO_CHAR(E.GET_DATE,'yyyymm') as GET_DATE
FROM      E_MP_DAY_READ@testdb E,
         C_CONS@testdb CC ,P_MR_MPED@testdb P
WHERE  
 E.DATA_DATE = TRUNC(SYSDATE-1,'DD') 
 AND P.MPED_ID=E.ID
AND CC.CONS_ID=P.CONS_IDAND 
EXISTS (select 1 from R_PLAN_DAY@testdb  where MR_DAY=SUBSTR(TO_CHAR(SYSDATE,'YYYY-MM-DD'),9,10) 
                 and MR_SECT_NO=CC.MR_SECT_NO)上面一个sql比下面一个sql快了10倍，我想知道有哪些原因导致的？

Answer 1

看了下:
E.R,
E.PAP_R,
就这2个字段的区别,索引的区别?
有执行计划么,看看,或许能看出点问题出来

Answer 2

晕  我怎么看不出这两个sql有什么区别

Answer 3

是不是E.R中的R是唯一键什么的？

Answer 4

create table E_MR_DAY_READ  (
   ID                   NUMBER(16)                      not null,
   DATA_DATE            DATE                            not null,
   DATA_TYPE            NUMBER(2)                       not null,
   GET_DATE             DATE                            not null,
   ORG_NO               VARCHAR2(16)                    not null,
   COL_TIME             DATE,
   R                    NUMBER(12,4),
   R1                   NUMBER(12,4),
   R2                   NUMBER(12,4),
   R3                   NUMBER(12,4),
   R4                   NUMBER(12,4),
   R5                   NUMBER(12,4),
   R6                   NUMBER(12,4),
   R9                   NUMBER(12,4),
   R7                   NUMBER(12,4),
   R8                   NUMBER(12,4),
   R10                  NUMBER(12,4),
   R11                  NUMBER(12,4),
   R12                  NUMBER(12,4),
   R13                  NUMBER(12,4),
   R14                  NUMBER(12,4)
);create table E_MP_DAY_READ  (
   ID                   NUMBER(16)                      not null,
   DATA_DATE            DATE                            not null,
   ORG_NO               VARCHAR2(16)                    not null,
   GET_DATE             DATE                           default SYSDATE not null,
   COL_TIME             DATE,
   PAP_R                NUMBER(11,4),
   PAP_R1               NUMBER(11,4),
   PAP_R2               NUMBER(11,4),
   PAP_R4               NUMBER(11,4),
   PAP_R3               NUMBER(11,4),
   PAP_R5               NUMBER(11,4),
   PAP_R6               NUMBER(11,4),
   PAP_R7               NUMBER(11,4),
   PAP_R8               NUMBER(11,4),
   PAP_R9               NUMBER(11,4),
   PAP_R10              NUMBER(11,4),
   PAP_R11              NUMBER(11,4),
   PAP_R12              NUMBER(11,4),
   PAP_R13              NUMBER(11,4),
   PAP_R14              NUMBER(11,4),
   PRP_R                NUMBER(11,4),
   PRP_R1               NUMBER(11,4),
   PRP_R2               NUMBER(11,4),
   PRP_R3               NUMBER(11,4),
   PRP_R4               NUMBER(11,4),
   PRP_R5               NUMBER(11,4),
   PRP_R6               NUMBER(11,4),
   PRP_R7               NUMBER(11,4),
   PRP_R8               NUMBER(11,4),
   PRP_R9               NUMBER(11,4),
   PRP_R10              NUMBER(11,4),
   PRP_R11              NUMBER(11,4),
   PRP_R12              NUMBER(11,4),
   PRP_R13              NUMBER(11,4),
   PRP_R14              NUMBER(11,4),
   RAP_R                NUMBER(11,4),
   RAP_R1               NUMBER(11,4),
   RAP_R2               NUMBER(11,4),
   RAP_R3               NUMBER(11,4),
   RAP_R4               NUMBER(11,4),
   RAP_R5               NUMBER(11,4),
   RAP_R6               NUMBER(11,4),
   RAP_R7               NUMBER(11,4),
   RAP_R8               NUMBER(11,4),
   RAP_R9               NUMBER(11,4),
   RAP_R10              NUMBER(11,4),
   RAP_R11              NUMBER(11,4),
   RAP_R12              NUMBER(11,4),
   RAP_R13              NUMBER(11,4),
   RAP_R14              NUMBER(11,4),
   RRP_R                NUMBER(11,4),
   RRP_R1               NUMBER(11,4),
   RRP_R2               NUMBER(11,4),
   RRP_R3               NUMBER(11,4),
   RRP_R4               NUMBER(11,4),
   RRP_R5               NUMBER(11,4),
   RRP_R6               NUMBER(11,4),
   RRP_R7               NUMBER(11,4),
   RRP_R8               NUMBER(11,4),
   RRP_R9               NUMBER(11,4),
   RRP_R10              NUMBER(11,4),
   RRP_R11              NUMBER(11,4),
   RRP_R12              NUMBER(11,4),
   RRP_R13              NUMBER(11,4),
   RRP_R14              NUMBER(11,4),
   RP1_R                NUMBER(11,4),
   RP1_R1               NUMBER(11,4),
   RP1_R2               NUMBER(11,4),
   RP1_R3               NUMBER(11,4),
   RP1_R4               NUMBER(11,4),
   RP3_R                NUMBER(11,4),
   RP3_R1               NUMBER(11,4),
   RP3_R2               NUMBER(11,4),
   RP3_R3               NUMBER(11,4),
   RP3_R4               NUMBER(11,4),
   RP2_R                NUMBER(11,4),
   RP2_R1               NUMBER(11,4),
   RP2_R2               NUMBER(11,4),
   RP2_R3               NUMBER(11,4),
   RP2_R4               NUMBER(11,4),
   RP4_R                NUMBER(11,4),
   RP4_R1               NUMBER(11,4),
   RP4_R2               NUMBER(11,4),
   RP4_R3               NUMBER(11,4),
   RP4_R4               NUMBER(11,4)
);
这是两种表的结构

Answer 5

两个地方不相同
1、E.R -------E.PAP_R select字段不同
2、E_MR_DAY_READ---------E_MP_DAY_READ  取得表不同

Answer 6

E_MR_DAY_READ的数据量比E_MP_DAY_READ大很多

Answer 7

字段E.R,
E.PAP_R,
不一样 估计数据量不一样 是主要原因 还有就是索引
set autotrace on  看执行计划就会，明白

Answer 8

这不能说明问题,因为在索引可以满足要求的情况下,执行计划可能根本不会考虑表的扫描,这个时候所比较的就是完成所需逻辑操作需要访问的数据块数了.所以归根结底要看你的实际执行计划以及相关scheme对象的统计特性.

Answer 9

对于数据库sql执行效率的统计，要采用随即样本做多次试验后汇总，仅凭某一次或几次的数据是不能证明的。还要考虑操作系统的因素是否会对效率评估造成影响。在测试数据库sql执行效率时应该采用专业的分析工具，pl/sql计划分析器显示的数据不一定准确、可信。

Answer 10

既然你两个sql里面查询的表不同，比效率高低，没有任何意义。
如果你的sql不同，只是因为写法上不同，比较效率还有意义。

Answer 11

E_MR_DAY_READ
E_MP_DAY_READ
楼主没发现这是两个不同的表吧，太不细心了，哈哈，害我看半天

Answer 12

E_MR_DAY_READ
E_MP_DAY_READ
这是两个不同的表吧，害我看半天才找到，晕！索引和数据量的问题