请sql高手帮我优化一下这个oracle的sql语句。

xieyunchao 2009-12-09 11:20:32
这里有两个sql。他们大体相同。但执行效率却是天壤之别。一个1秒就可以查询出结果，而一个需要10多分钟。查看他们的执行计划，发现果然不通。

sql1：



SELECT distinct t.CARD_ID AS CARD_ID,

                t.ZONECODE AS ZONECODE,

                t.flag as FLAG,

                (select zonename from zonecode where zonecode = t.zonecode) as zonename,

                (select orgname

                   from aidszh_sgra_organise

                  where orgcode = t.orgcode) as orgname,

                t.PID_ORG AS PID_ORG,

                t.ORGCODE AS ORGCODE,

                t.CARD_CODE AS CARD_CODE,

                t.EMPID_CREATE_ORG as EMPID_CREATE_ORG,

                t.PID AS PID,

                t.ANTIN AS ANTIN,

                t.PATIENT_NAME AS PATIENT_NAME,

                t.ID AS ID,

                (select name from aidszh_sgra_dd_sex where id = t.SEX) AS SEX,

                to_char(t.BIRTHDAY, 'yyyy-mm-dd') AS BIRTHDAY,

                (select name

                   from aidszh_sgra_dd_chargesrc

                  where id_dic = t.CHARGESRC) AS CHARGESRC,

                to_char(t.DT_ANTIVIRUS, 'yyyy-mm-dd') AS DT_ANTIVIRUS,

                (select name from aidszh_sgra_dd_flag where id = t.FLAG) AS FLAGSTATUS,

                to_char(t.DT_REPORT, 'yyyy-mm-dd') AS DT_REPORT,

                to_char(t.TM_CREATE, 'yyyy-mm-dd') AS TM_CREATE,

                f.cure_org as CURE_ORG,

                (decode(f.LAST_CLINIC_TREATMENT,

                        '3',

                        '停药',

                        decode(f.FLW_STATUS,

                               '1',

                               '在治',

                               (decode(f.end_cause,

                                       '1',

                                       '失访',

                                       '2',

                                       '死亡',

                                       '3',

                                       '转出'))))) as flwstat

  FROM aidszh_sgra_adult_newstatus f,

       AIDSZH_SGRA_ADULT_INFO t,

       (select /*+index(h,IDX_SGRA_ADULT_FLW_IDTRANS) +index(p,IDX_AIDSZH_SGRA_TRANS_IDCARD)*/

         p.card_id, p.tzonecode, p.yorgcode, p.torgcode, p.dt_last_aduit

          from aidszh_sgra_trans p, aidszh_sgra_adult_flw h

         where p.card_id = h.card_id

           and p.id_record = h.id_trans

           and p.dt_last_aduit is not null) g

 where t.card_id = f.CARD_ID

   and t.card_id = g.card_id(+)

   and ((g.TZONECODE like '%') or (t.zonecode like '%'))

   and t.TM_CREATE >=

       to_date('2009-12-09 00:00:00', 'yyyy-mm-dd hh24:mi:ss')

   and t.TM_CREATE <=

       to_date('2009-12-09 23:59:59', 'yyyy-mm-dd hh24:mi:ss')

   and 1 = 1

   and 1 = 1

   and 1 = 1　 　and 1 = 1 　and

   1 = 1

   and 1 = 1

   and 1 = 1

   AND 1 = 1

   and (t.id in (select b.id_no from aidszh_mst_patients b) and

       t.pid in (select s.cure_no from aidszh_mst_hivchech s))

 ORDER BY T.ANTIN

那么它的执行计划为：



这条sql只需要几秒就能查询出数据。

下面来看这条sql，和上面类似，就换了两张表。



SELECT  distinct t.CARD_ID AS CARD_ID,

                t.ZONECODE AS ZONECODE,

                t.ORGCODE AS ORGCODE1,

                t.ANTIN AS ANTIN,

                PID_ORG as PID_ORG,

                (select b.CNNAME

                   from zonecode b

                  where b.ZONECODE = t.ZONECODE) as ZONENAME,

                (select ORGNAME

                   from aidszh_sgra_organise

                  where ORGCODE = t.orgcode) as ORGCODE,

                t.EMPID_CREATE_ORG as EMPID_CREATE_ORG,

                f.CURE_ORG as CURE_ORG,

                (select name from aidszh_sgra_dd_flag where id = t.FLAG) AS FLAGSTATUS,

                (decode(f.LAST_CLINIC_TREATMENT,

                        '4',

                        '停药',

                        decode(f.FLW_STATUS,

                               '1',

                               '在治',

                               (decode(f.end_cause,

                                       '1',

                                       '失访',

                                       '2',

                                       '死亡',

                                       '3',

                                       '转出'))))) as flwstat

  FROM aidszh_sgra_child_newstatus f,

       AIDSZH_SGRA_CHILD_INFO t,

       (select /*+index(h,IDX_SGRA_CHILD_FLW_IDTRANS) +index(p,IDX_AIDSZH_SGRA_TRANS_IDCARD)*/

         p.card_id, p.tzonecode, p.torgcode, p.dt_last_aduit

          from aidszh_sgra_trans p, aidszh_sgra_child_flw h

         where p.card_id = h.card_id

           and p.id_record = h.id_trans

           and p.DT_LAST_ADUIT is not null) g

 where t.card_id = f.CARD_ID

   and t.card_id = g.card_id(+)

   and (t.id in (select b.id_no from aidszh_mst_patients b) and

       t.pid in (select s.cure_no from aidszh_mst_hivchech s))

   and ((g.TZONECODE like '%') or (t.zonecode like '%'))

   and t.TM_CREATE >=

       to_date('2009-12-09 00:00:00', 'yyyy-mm-dd hh24:mi:ss')

   and t.TM_CREATE <=

       to_date('2009-12-09 23:59:59', 'yyyy-mm-dd hh24:mi:ss')

   and 1 = 1

   and 1 = 1

   and 1 = 1

   and 1 = 1

   and 1 = 1

   and 1 = 1

   AND 1 = 1

 ORDER BY T.ANTIN

它的执行计划为：

这个执行时间需要十几分钟。

说明：表aidszh_mst_patients和aidszh_mst_hivchech 都有很多的数据量大约10w。 AIDSZH_SGRA_CHILD_INFO 和aidszh_sgra_child_flw 是一对多的关系，数据量也很多。我不想大篇幅改变sql的结构，很多地方都用这个sql。在第二个sql中。我如果删除条件 and (t.id in (select b.id_no from aidszh_mst_patients b) and
t.pid in (select s.cure_no from aidszh_mst_hivchech s))则执行效率超快。这个条件有时候有，有时候没有，完全取决于用户的操作。

请高手分析这两个sql的异同，并提出第二个sql的优化建议，达到和第一个sql同样的效果。
多谢。
...全文
179 7 打赏 收藏 转发到动态 举报
写回复
用AI写文章
7 条回复
切换为时间正序
请发表友善的回复…
发表回复
mantisXF 2009-12-09
  • 打赏
  • 举报
 回复 
-- 下面SQL的执行计划和相关表的数据量及有无索引情况贴上来：

select t.*

  from AIDSZH_SGRA_ADULT_INFO t,

       aidszh_mst_patients b,

       aidszh_mst_hivchech s

 where t.id = b.id_no

   and t.pid = s.cure_no;
[Quote=引用楼主 xieyunchao 的回复:]
这里有两个sql。他们大体相同。但执行效率却是天壤之别。一个1秒就可以查询出结果，而一个需要10多分钟。查看他们的执行计划，发现果然不通。

sql1：

SQL codeSELECTdistinct t.CARD_IDAS CARD_ID,
t.ZONECODEAS ZONECODE,
t.flagas FLAG,
(select zonenamefrom zonecodewhere zonecode= t.zonecode)as zonename,
(select orgnamefrom aidszh_sgra_organisewhere orgcode= t.orgcode)as orgname,
t.PID_ORGAS PID_ORG,
t.ORGCODEAS ORGCODE,
t.CARD_CODEAS CARD_CODE,
t.EMPID_CREATE_ORGas EMPID_CREATE_ORG,
t.PIDAS PID,
t.ANTINAS ANTIN,
t.PATIENT_NAMEAS PATIENT_NAME,
t.IDAS ID,
(select namefrom aidszh_sgra_dd_sexwhere id= t.SEX)AS SEX,
to_char(t.BIRTHDAY,'yyyy-mm-dd')AS BIRTHDAY,
(select namefrom aidszh_sgra_dd_chargesrcwhere id_dic= t.CHARGESRC)AS CHARGESRC,
to_char(t.DT_ANTIVIRUS,'yyyy-mm-dd')AS DT_ANTIVIRUS,
(select namefrom aidszh_sgra_dd_flagwhere id= t.FLAG)AS FLAGSTATUS,
to_char(t.DT_REPORT,'yyyy-mm-dd')AS DT_REPORT,
to_char(t.TM_CREATE,'yyyy-mm-dd')AS TM_CREATE,
f.cure_orgas CURE_ORG,
(decode(f.LAST_CLINIC_TREATMENT,'3','停药',
decode(f.FLW_STATUS,'1','在治',
(decode(f.end_cause,'1','失访','2','死亡','3','转出')))))as flwstatFROM aidszh_sgra_adult_newstatus f,
AIDSZH_SGRA_ADULT_INFO t,
(select/*+index(h,IDX_SGRA_ADULT_FLW_IDTRANS) +index(p,IDX_AIDSZH_SGRA_TRANS_IDCARD)*/
p.card_id, p.tzonecode, p.yorgcode, p.torgcode, p.dt_last_aduitfrom aidszh_sgra_trans p, aidszh_sgra_adult_flw hwhere p.card_id= h.card_idand p.id_record= h.id_transand p.dt_last_aduitisnotnull) gwhere t.card_id= f.CARD_IDand t.card_id= g.card_id(+)and ((g.TZONECODElike'%')or (t.zonecodelike'%'))and t.TM_CREATE>=
to_date('2009-12-09 00:00:00','yyyy-mm-dd hh24:mi:ss')and t.TM_CREATE<=
to_date('2009-12-09 23:59:59','yyyy-mm-dd hh24:mi:ss')and1=1and1=1and1=1　 　and1=1 　and1=1and1=1and1=1AND1=1and (t.idin (select b.id_nofrom aidszh_mst_patients b)and
t.pidin (select s.cure_nofrom aidszh_mst_hivchech s))ORDERBY T.ANTIN
那么它的执行计划为：



这条sql只需要几秒就能查询出数据。

下面来看这条sql，和上面类似，就换了两张表。

SQL codeSELECTdistinct t.CARD_IDAS CARD_ID,
t.ZONECODEAS ZONECODE,
t.ORGCODEAS ORGCODE1,
t.ANTINAS ANTIN,
PID_ORGas PID_ORG,
(select b.CNNAMEfrom zonecode bwhere b.ZONECODE= t.ZONECODE)as ZONENAME,
(select ORGNAMEfrom aidszh_sgra_organisewhere ORGCODE= t.orgcode)as ORGCODE,
t.EMPID_CREATE_ORGas EMPID_CREATE_ORG,
f.CURE_ORGas CURE_ORG,
(select namefrom aidszh_sgra_dd_flagwhere id= t.FLAG)AS FLAGSTATUS,
(decode(f.LAST_CLINIC_TREATMENT,'4','停药',
decode(f.FLW_STATUS,'1','在治',
(decode(f.end_cause,'1','失访','2','死亡','3','转出')))))as flwstatFROM aidszh_sgra_child_newstatus f,
AIDSZH_SGRA_CHILD_INFO t,
(select/*+index(h,IDX_SGRA_CHILD_FLW_IDTRANS) +index(p,IDX_AIDSZH_SGRA_TRANS_IDCARD)*/
p.card_id, p.tzonecode, p.torgcode, p.dt_last_aduitfrom aidszh_sgra_trans p, aidszh_sgra_child_flw hwhere p.card_id= h.card_idand p.id_record= h.id_transand p.DT_LAST_ADUITisnotnull) gwhere t.card_id= f.CARD_IDand t.card_id= g.card_id(+)and (t.idin (select b.id_nofrom aidszh_mst_patients b)and
t.pidin (select s.cure_nofrom aidszh_mst_hivchech s))and ((g.TZONECODElike'%')or (t.zonecodelike'%'))and t.TM_CREATE>=
to_date('2009-12-09 00:00:00','yyyy-mm-dd hh24:mi:ss')and t.TM_CREATE<=
to_date('2009-12-09 23:59:59','yyyy-mm-dd hh24:mi:ss')and1=1and1=1and1=1and1=1and1=1and1=1AND1=1ORDERBY T.ANTIN
它的执行计划为：

这个执行时间需要十几分钟。

说明：表aidszh_mst_patients和aidszh_mst_hivchech 都有很多的数据量大约10w。 AIDSZH_SGRA_CHILD_INFO 和aidszh_sgra_child_flw 是一对多的关系，数据量也很多。我不想大篇幅改变sql的结构，很多地方都用这个sql。在第二个sql中。我如果删除条件 and (t.id in (select b.id_no from aidszh_mst_patients b) and
      t.pid in (select s.cure_no from aidszh_mst_hivchech s))则执行效率超快。这个条件有时候有，有时候没有，完全取决于用户的操作。

请高手分析这两个sql的异同，并提出第二个sql的优化建议，达到和第一个sql同样的效果。
多谢。
[/Quote]
qurihong 2009-12-09
  • 打赏
  • 举报
 回复
问题应该就在in上，in后面要匹配的内容太多，效率当然会慢
1. 可以使用exist试试
2. 另外，如果aidszh_mst_patients和aidszh_mst_hivchech中内容不多的话，可以全写出来，改用or .. or .. or
3. 最后就是试试 inner join的用法了，麻烦一些，也许有提高
sxq129601 2009-12-09
  • 打赏
  • 举报
 回复
SELECT distinct t.CARD_ID AS CARD_ID,
t.ZONECODE AS ZONECODE,
t.ORGCODE AS ORGCODE1,
t.ANTIN AS ANTIN,
PID_ORG as PID_ORG,
(select b.CNNAME
from zonecode b
where b.ZONECODE = t.ZONECODE) as ZONENAME,
(select ORGNAME
from aidszh_sgra_organise
where ORGCODE = t.orgcode) as ORGCODE,
t.EMPID_CREATE_ORG as EMPID_CREATE_ORG,
f.CURE_ORG as CURE_ORG,
(select name from aidszh_sgra_dd_flag where id = t.FLAG) AS FLAGSTATUS,
(decode(f.LAST_CLINIC_TREATMENT,
'4',
'停药',
decode(f.FLW_STATUS,
'1',
'在治',
(decode(f.end_cause,
'1',
'失访',
'2',
'死亡',
'3',
'转出'))))) as flwstat
FROM aidszh_sgra_child_newstatus f,
AIDSZH_SGRA_CHILD_INFO t,
(select /*+index(h,IDX_SGRA_CHILD_FLW_IDTRANS) +index(p,IDX_AIDSZH_SGRA_TRANS_IDCARD)*/
p.card_id, p.tzonecode, p.torgcode, p.dt_last_aduit
from aidszh_sgra_trans p, aidszh_sgra_child_flw h
where p.card_id = h.card_id
and p.id_record = h.id_trans
and p.DT_LAST_ADUIT is not null) g
where
exists (select 1 from aidszh_mst_patients b where b.id_no=t.id)
and
t.card_id = f.CARD_ID
and t.card_id = g.card_id(+)
and t.pid in (select s.cure_no from aidszh_mst_hivchech s)
and ((g.TZONECODE like '%') or (t.zonecode like '%'))
and t.TM_CREATE >=
to_date('2009-12-09 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
and t.TM_CREATE <=
to_date('2009-12-09 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
and 1 = 1
and 1 = 1
and 1 = 1
and 1 = 1
and 1 = 1
and 1 = 1
AND 1 = 1
ORDER BY T.ANTIN
sxq129601 2009-12-09
  • 打赏
  • 举报
 回复
SELECT distinct t.CARD_ID AS CARD_ID,
t.ZONECODE AS ZONECODE,
t.ORGCODE AS ORGCODE1,
t.ANTIN AS ANTIN,
PID_ORG as PID_ORG,
(select b.CNNAME
from zonecode b
where b.ZONECODE = t.ZONECODE) as ZONENAME,
(select ORGNAME
from aidszh_sgra_organise
where ORGCODE = t.orgcode) as ORGCODE,
t.EMPID_CREATE_ORG as EMPID_CREATE_ORG,
f.CURE_ORG as CURE_ORG,
(select name from aidszh_sgra_dd_flag where id = t.FLAG) AS FLAGSTATUS,
(decode(f.LAST_CLINIC_TREATMENT,
'4',
'停药',
decode(f.FLW_STATUS,
'1',
'在治',
(decode(f.end_cause,
'1',
'失访',
'2',
'死亡',
'3',
'转出'))))) as flwstat
FROM aidszh_sgra_child_newstatus f,
AIDSZH_SGRA_CHILD_INFO t,
(select /*+index(h,IDX_SGRA_CHILD_FLW_IDTRANS) +index(p,IDX_AIDSZH_SGRA_TRANS_IDCARD)*/
p.card_id, p.tzonecode, p.torgcode, p.dt_last_aduit
from aidszh_sgra_trans p, aidszh_sgra_child_flw h
where p.card_id = h.card_id
and p.id_record = h.id_trans
and p.DT_LAST_ADUIT is not null) g
where
exists (select 1 from aidszh_mst_patients b where b.id_no=t.id)
t.card_id = f.CARD_ID
and t.card_id = g.card_id(+)
and t.pid in (select s.cure_no from aidszh_mst_hivchech s)
and ((g.TZONECODE like '%') or (t.zonecode like '%'))
and t.TM_CREATE >=
to_date('2009-12-09 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
and t.TM_CREATE <=
to_date('2009-12-09 23:59:59', 'yyyy-mm-dd hh24:mi:ss')
and 1 = 1
and 1 = 1
and 1 = 1
and 1 = 1
and 1 = 1
and 1 = 1
AND 1 = 1
ORDER BY T.ANTIN
mantisXF 2009-12-09
  • 打赏
  • 举报
 回复
"t,"中的逗号去掉一下。[Quote=引用 6 楼 mantisxf 的回复:]
SQL code--相关where条件连接字段有没有索引？如果没有建相应的索引，然后再跑下把上面SQL的执行计划和下面SQL的执行计划贴上来：select t.*from aidszh_mst_patients b,
aidszh_mst_hivchech s,
AIDSZH_SGRA_ADULT_INFO t,where b.id_no= t.idand s.cure_no= t.pid;引用 5 楼 xieyunchao 的回复:
引用 4 楼 mantisxf 的回复:
SQL code-- 下面SQL的执行计划和相关表的数据量及有无索引情况贴上来：select t.*from AIDSZH_SGRA_ADULT_INFO t,
      aidszh_mst_patients b,
      aidszh_mst_hivchech swhere t.id= b.id_noand t.pid= s.cure_no;引用楼主 xieyunchao 的回复:
这里有两个sql。他们大体相同。但执行效率却是天壤之别。一个1秒就可以查询出结果，而一个需要10多分钟。查看他们的执行计划，发现果然不通。

sql1：

SQL codeSELECTdistinct t.CARD_IDAS CARD_ID,
                t.ZONECODEAS ZONECODE,
                t.flagas FLAG,
                (select zonenamefrom zonecodewhere zonecode= t.zonecode)as zonename,
                (select orgnamefrom aidszh_sgra_organisewhere orgcode= t.orgcode)as orgname,
                t.PID_ORGAS PID_ORG,
                t.ORGCODEAS ORGCODE,
                t.CARD_CODEAS CARD_CODE,
                t.EMPID_CREATE_ORGas EMPID_CREATE_ORG,
                t.PIDAS PID,
                t.ANTINAS ANTIN,
                t.PATIENT_NAMEAS PATIENT_NAME,
                t.IDAS ID,
                (select namefrom aidszh_sgra_dd_sexwhere id= t.SEX)AS SEX,
                to_char(t.BIRTHDAY,'yyyy-mm-dd')AS BIRTHDAY,
                (select namefrom aidszh_sgra_dd_chargesrcwhere id_dic= t.CHARGESRC)AS CHARGESRC,
                to_char(t.DT_ANTIVIRUS,'yyyy-mm-dd')AS DT_ANTIVIRUS,
                (select namefrom aidszh_sgra_dd_flagwhere id= t.FLAG)AS FLAGSTATUS,
                to_char(t.DT_REPORT,'yyyy-mm-dd')AS DT_REPORT,
                to_char(t.TM_CREATE,'yyyy-mm-dd')AS TM_CREATE,
                f.cure_orgas CURE_ORG,
                (decode(f.LAST_CLINIC_TREATMENT,'3','停药',
                        decode(f.FLW_STATUS,'1','在治',
                              (decode(f.end_cause,'1','失访','2','死亡','3','转出')))))as flwstatFROM aidszh_sgra_adult_newstatus f,
      AIDSZH_SGRA_ADULT_INFO t,
      (select/*+index(h,IDX_SGRA_ADULT_FLW_IDTRANS) +index(p,IDX_AIDSZH_SGRA_TRANS_IDCARD)*/
        p.card_id, p.tzonecode, p.yorgcode, p.torgcode, p.dt_last_aduitfrom aidszh_sgra_trans p, aidszh_sgra_adult_flw hwhere p.card_id= h.card_idand p.id_record= h.id_transand p.dt_last_aduitisnotnull) gwhere t.card_id= f.CARD_IDand t.card_id= g.card_id(+)and ((g.TZONECODElike'%')or (t.zonecodelike'%'))and t.TM_CREATE>=
      to_date('2009-12-09 00:00:00','yyyy-mm-dd hh24:mi:ss')and t.TM_CREATE <=
      to_date('2009-12-09 23:59:59','yyyy-mm-dd hh24:mi:ss')and1=1and1=1and1=1　 　and1=1 　and1=1and1=1and1=1AND1=1and (t.idin (select b.id_nofrom aidszh_mst_patients b)and
      t.pidin (select s.cure_nofrom aidszh_mst_hivchech s))ORDERBY T.ANTIN
那么它的执行计划为：


这条sql只需要几秒就能查询出数据。

下面来看这条sql，和上面类似，就换了两张表。

SQL codeSELECTdistinct t.CARD_IDAS CARD_ID,
                t.ZONECODEAS ZONECODE,
                t.ORGCODEAS ORGCODE1,
                t.ANTINAS ANTIN,
                PID_ORGas PID_ORG,
                (select b.CNNAMEfrom zonecode bwhere b.ZONECODE= t.ZONECODE)as ZONENAME,
                (select ORGNAMEfrom aidszh_sgra_organisewhere ORGCODE= t.orgcode)as ORGCODE,
                t.EMPID_CREATE_ORGas EMPID_CREATE_ORG,
                f.CURE_ORGas CURE_ORG,
                (select namefrom aidszh_sgra_dd_flagwhere id= t.FLAG)AS FLAGSTATUS,
                (decode(f.LAST_CLINIC_TREATMENT,'4','停药',
                        decode(f.FLW_STATUS,'1','在治',
                              (decode(f.end_cause,'1','失访','2','死亡','3','转出')))))as flwstatFROM aidszh_sgra_child_newstatus f,
      AIDSZH_SGRA_CHILD_INFO t,
      (select/*+index(h,IDX_SGRA_CHILD_FLW_IDTRANS) +index(p,IDX_AIDSZH_SGRA_TRANS_IDCARD)*/
        p.card_id, p.tzonecode, p.torgcode, p.dt_last_aduitfrom aidszh_sgra_trans p, aidszh_sgra_child_flw hwhere p.card_id= h.card_idand p.id_record= h.id_transand p.DT_LAST_ADUITisnotnull) gwhere t.card_id= f.CARD_IDand t.card_id= g.card_id(+)and (t.idin (select b.id_nofrom aidszh_mst_patients b)and
      t.pidin (select s.cure_nofrom aidszh_mst_hivchech s))and ((g.TZONECODElike'%')or (t.zonecodelike'%'))and t.TM_CREATE>=
      to_date('2009-12-09 00:00:00','yyyy-mm-dd hh24:mi:ss')and t.TM_CREATE <=
      to_date('2009-12-09 23:59:59','yyyy-mm-dd hh24:mi:ss')and1=1and1=1and1=1and1=1and1=1and1=1AND1=1ORDERBY T.ANTIN
它的执行计划为：

这个执行时间需要十几分钟。

说明：表aidszh_mst_patients和aidszh_mst_hivchech 都有很多的数据量大约10w。 AIDSZH_SGRA_CHILD_INFO 和aidszh_sgra_child_flw 是一对多的关系，数据量也很多。我不想大篇幅改变sql的结构，很多地方都用这个sql。在第二个sql中。我如果删除条件 and (t.id in (select b.id_no from aidszh_mst_patients b) and
      t.pid in (select s.cure_no from aidszh_mst_hivchech s))则执行效率超快。这个条件有时候有，有时候没有，完全取决于用户的操作。

请高手分析这两个sql的异同，并提出第二个sql的优化建议，达到和第一个sql同样的效果。
多谢。


SQL codeselect t.*from AIDSZH_SGRA_ADULT_INFO t,
      aidszh_mst_patients b,
      aidszh_mst_hivchech swhere t.id= b.id_noand t.pid= s.cure_no;中，AIDSZH_SGRA_ADULT_INFO 的目前数据量很少，也就几百，正式库中会有几十万。aidszh_mst_patients 和aidszh_mst_hivchech 都有十几万的数据。
执行计划如下：



[/Quote]
mantisXF 2009-12-09
  • 打赏
  • 举报
 回复 
--相关where条件连接字段有没有索引？如果没有建相应的索引，然后再跑下把上面SQL的执行计划和下面SQL的执行计划贴上来：

select t.*

  from aidszh_mst_patients b,

       aidszh_mst_hivchech s,

       AIDSZH_SGRA_ADULT_INFO t,

 where b.id_no = t.id

   and s.cure_no = t.pid;
[Quote=引用 5 楼 xieyunchao 的回复:]
引用 4 楼 mantisxf 的回复:
SQL code-- 下面SQL的执行计划和相关表的数据量及有无索引情况贴上来：select t.*from AIDSZH_SGRA_ADULT_INFO t,
      aidszh_mst_patients b,
      aidszh_mst_hivchech swhere t.id= b.id_noand t.pid= s.cure_no;引用楼主 xieyunchao 的回复:
这里有两个sql。他们大体相同。但执行效率却是天壤之别。一个1秒就可以查询出结果，而一个需要10多分钟。查看他们的执行计划，发现果然不通。

sql1：

SQL codeSELECTdistinct t.CARD_IDAS CARD_ID,
                t.ZONECODEAS ZONECODE,
                t.flagas FLAG,
                (select zonenamefrom zonecodewhere zonecode= t.zonecode)as zonename,
                (select orgnamefrom aidszh_sgra_organisewhere orgcode= t.orgcode)as orgname,
                t.PID_ORGAS PID_ORG,
                t.ORGCODEAS ORGCODE,
                t.CARD_CODEAS CARD_CODE,
                t.EMPID_CREATE_ORGas EMPID_CREATE_ORG,
                t.PIDAS PID,
                t.ANTINAS ANTIN,
                t.PATIENT_NAMEAS PATIENT_NAME,
                t.IDAS ID,
                (select namefrom aidszh_sgra_dd_sexwhere id= t.SEX)AS SEX,
                to_char(t.BIRTHDAY,'yyyy-mm-dd')AS BIRTHDAY,
                (select namefrom aidszh_sgra_dd_chargesrcwhere id_dic= t.CHARGESRC)AS CHARGESRC,
                to_char(t.DT_ANTIVIRUS,'yyyy-mm-dd')AS DT_ANTIVIRUS,
                (select namefrom aidszh_sgra_dd_flagwhere id= t.FLAG)AS FLAGSTATUS,
                to_char(t.DT_REPORT,'yyyy-mm-dd')AS DT_REPORT,
                to_char(t.TM_CREATE,'yyyy-mm-dd')AS TM_CREATE,
                f.cure_orgas CURE_ORG,
                (decode(f.LAST_CLINIC_TREATMENT,'3','停药',
                        decode(f.FLW_STATUS,'1','在治',
                              (decode(f.end_cause,'1','失访','2','死亡','3','转出')))))as flwstatFROM aidszh_sgra_adult_newstatus f,
      AIDSZH_SGRA_ADULT_INFO t,
      (select/*+index(h,IDX_SGRA_ADULT_FLW_IDTRANS) +index(p,IDX_AIDSZH_SGRA_TRANS_IDCARD)*/
        p.card_id, p.tzonecode, p.yorgcode, p.torgcode, p.dt_last_aduitfrom aidszh_sgra_trans p, aidszh_sgra_adult_flw hwhere p.card_id= h.card_idand p.id_record= h.id_transand p.dt_last_aduitisnotnull) gwhere t.card_id= f.CARD_IDand t.card_id= g.card_id(+)and ((g.TZONECODElike'%')or (t.zonecodelike'%'))and t.TM_CREATE>=
      to_date('2009-12-09 00:00:00','yyyy-mm-dd hh24:mi:ss')and t.TM_CREATE <=
      to_date('2009-12-09 23:59:59','yyyy-mm-dd hh24:mi:ss')and1=1and1=1and1=1　 　and1=1 　and1=1and1=1and1=1AND1=1and (t.idin (select b.id_nofrom aidszh_mst_patients b)and
      t.pidin (select s.cure_nofrom aidszh_mst_hivchech s))ORDERBY T.ANTIN
那么它的执行计划为：


这条sql只需要几秒就能查询出数据。

下面来看这条sql，和上面类似，就换了两张表。

SQL codeSELECTdistinct t.CARD_IDAS CARD_ID,
                t.ZONECODEAS ZONECODE,
                t.ORGCODEAS ORGCODE1,
                t.ANTINAS ANTIN,
                PID_ORGas PID_ORG,
                (select b.CNNAMEfrom zonecode bwhere b.ZONECODE= t.ZONECODE)as ZONENAME,
                (select ORGNAMEfrom aidszh_sgra_organisewhere ORGCODE= t.orgcode)as ORGCODE,
                t.EMPID_CREATE_ORGas EMPID_CREATE_ORG,
                f.CURE_ORGas CURE_ORG,
                (select namefrom aidszh_sgra_dd_flagwhere id= t.FLAG)AS FLAGSTATUS,
                (decode(f.LAST_CLINIC_TREATMENT,'4','停药',
                        decode(f.FLW_STATUS,'1','在治',
                              (decode(f.end_cause,'1','失访','2','死亡','3','转出')))))as flwstatFROM aidszh_sgra_child_newstatus f,
      AIDSZH_SGRA_CHILD_INFO t,
      (select/*+index(h,IDX_SGRA_CHILD_FLW_IDTRANS) +index(p,IDX_AIDSZH_SGRA_TRANS_IDCARD)*/
        p.card_id, p.tzonecode, p.torgcode, p.dt_last_aduitfrom aidszh_sgra_trans p, aidszh_sgra_child_flw hwhere p.card_id= h.card_idand p.id_record= h.id_transand p.DT_LAST_ADUITisnotnull) gwhere t.card_id= f.CARD_IDand t.card_id= g.card_id(+)and (t.idin (select b.id_nofrom aidszh_mst_patients b)and
      t.pidin (select s.cure_nofrom aidszh_mst_hivchech s))and ((g.TZONECODElike'%')or (t.zonecodelike'%'))and t.TM_CREATE>=
      to_date('2009-12-09 00:00:00','yyyy-mm-dd hh24:mi:ss')and t.TM_CREATE <=
      to_date('2009-12-09 23:59:59','yyyy-mm-dd hh24:mi:ss')and1=1and1=1and1=1and1=1and1=1and1=1AND1=1ORDERBY T.ANTIN
它的执行计划为：

这个执行时间需要十几分钟。

说明：表aidszh_mst_patients和aidszh_mst_hivchech 都有很多的数据量大约10w。 AIDSZH_SGRA_CHILD_INFO 和aidszh_sgra_child_flw 是一对多的关系，数据量也很多。我不想大篇幅改变sql的结构，很多地方都用这个sql。在第二个sql中。我如果删除条件 and (t.id in (select b.id_no from aidszh_mst_patients b) and
      t.pid in (select s.cure_no from aidszh_mst_hivchech s))则执行效率超快。这个条件有时候有，有时候没有，完全取决于用户的操作。

请高手分析这两个sql的异同，并提出第二个sql的优化建议，达到和第一个sql同样的效果。
多谢。



SQL codeselect t.*from AIDSZH_SGRA_ADULT_INFO t,
aidszh_mst_patients b,
aidszh_mst_hivchech swhere t.id= b.id_noand t.pid= s.cure_no;中，AIDSZH_SGRA_ADULT_INFO 的目前数据量很少，也就几百，正式库中会有几十万。aidszh_mst_patients 和aidszh_mst_hivchech 都有十几万的数据。
执行计划如下：




[/Quote]
xieyunchao 2009-12-09
  • 打赏
  • 举报
 回复
[Quote=引用 4 楼 mantisxf 的回复:]
SQL code-- 下面SQL的执行计划和相关表的数据量及有无索引情况贴上来：select t.*from AIDSZH_SGRA_ADULT_INFO t,
aidszh_mst_patients b,
aidszh_mst_hivchech swhere t.id= b.id_noand t.pid= s.cure_no;引用楼主 xieyunchao 的回复:
这里有两个sql。他们大体相同。但执行效率却是天壤之别。一个1秒就可以查询出结果，而一个需要10多分钟。查看他们的执行计划，发现果然不通。

sql1：

SQL codeSELECTdistinct t.CARD_IDAS CARD_ID,
                t.ZONECODEAS ZONECODE,
                t.flagas FLAG,
                (select zonenamefrom zonecodewhere zonecode= t.zonecode)as zonename,
                (select orgnamefrom aidszh_sgra_organisewhere orgcode= t.orgcode)as orgname,
                t.PID_ORGAS PID_ORG,
                t.ORGCODEAS ORGCODE,
                t.CARD_CODEAS CARD_CODE,
                t.EMPID_CREATE_ORGas EMPID_CREATE_ORG,
                t.PIDAS PID,
                t.ANTINAS ANTIN,
                t.PATIENT_NAMEAS PATIENT_NAME,
                t.IDAS ID,
                (select namefrom aidszh_sgra_dd_sexwhere id= t.SEX)AS SEX,
                to_char(t.BIRTHDAY,'yyyy-mm-dd')AS BIRTHDAY,
                (select namefrom aidszh_sgra_dd_chargesrcwhere id_dic= t.CHARGESRC)AS CHARGESRC,
                to_char(t.DT_ANTIVIRUS,'yyyy-mm-dd')AS DT_ANTIVIRUS,
                (select namefrom aidszh_sgra_dd_flagwhere id= t.FLAG)AS FLAGSTATUS,
                to_char(t.DT_REPORT,'yyyy-mm-dd')AS DT_REPORT,
                to_char(t.TM_CREATE,'yyyy-mm-dd')AS TM_CREATE,
                f.cure_orgas CURE_ORG,
                (decode(f.LAST_CLINIC_TREATMENT,'3','停药',
                        decode(f.FLW_STATUS,'1','在治',
                              (decode(f.end_cause,'1','失访','2','死亡','3','转出')))))as flwstatFROM aidszh_sgra_adult_newstatus f,
      AIDSZH_SGRA_ADULT_INFO t,
      (select/*+index(h,IDX_SGRA_ADULT_FLW_IDTRANS) +index(p,IDX_AIDSZH_SGRA_TRANS_IDCARD)*/
        p.card_id, p.tzonecode, p.yorgcode, p.torgcode, p.dt_last_aduitfrom aidszh_sgra_trans p, aidszh_sgra_adult_flw hwhere p.card_id= h.card_idand p.id_record= h.id_transand p.dt_last_aduitisnotnull) gwhere t.card_id= f.CARD_IDand t.card_id= g.card_id(+)and ((g.TZONECODElike'%')or (t.zonecodelike'%'))and t.TM_CREATE>=
      to_date('2009-12-09 00:00:00','yyyy-mm-dd hh24:mi:ss')and t.TM_CREATE <=
      to_date('2009-12-09 23:59:59','yyyy-mm-dd hh24:mi:ss')and1=1and1=1and1=1　 　and1=1 　and1=1and1=1and1=1AND1=1and (t.idin (select b.id_nofrom aidszh_mst_patients b)and
      t.pidin (select s.cure_nofrom aidszh_mst_hivchech s))ORDERBY T.ANTIN
那么它的执行计划为：


这条sql只需要几秒就能查询出数据。

下面来看这条sql，和上面类似，就换了两张表。

SQL codeSELECTdistinct t.CARD_IDAS CARD_ID,
                t.ZONECODEAS ZONECODE,
                t.ORGCODEAS ORGCODE1,
                t.ANTINAS ANTIN,
                PID_ORGas PID_ORG,
                (select b.CNNAMEfrom zonecode bwhere b.ZONECODE= t.ZONECODE)as ZONENAME,
                (select ORGNAMEfrom aidszh_sgra_organisewhere ORGCODE= t.orgcode)as ORGCODE,
                t.EMPID_CREATE_ORGas EMPID_CREATE_ORG,
                f.CURE_ORGas CURE_ORG,
                (select namefrom aidszh_sgra_dd_flagwhere id= t.FLAG)AS FLAGSTATUS,
                (decode(f.LAST_CLINIC_TREATMENT,'4','停药',
                        decode(f.FLW_STATUS,'1','在治',
                              (decode(f.end_cause,'1','失访','2','死亡','3','转出')))))as flwstatFROM aidszh_sgra_child_newstatus f,
      AIDSZH_SGRA_CHILD_INFO t,
      (select/*+index(h,IDX_SGRA_CHILD_FLW_IDTRANS) +index(p,IDX_AIDSZH_SGRA_TRANS_IDCARD)*/
        p.card_id, p.tzonecode, p.torgcode, p.dt_last_aduitfrom aidszh_sgra_trans p, aidszh_sgra_child_flw hwhere p.card_id= h.card_idand p.id_record= h.id_transand p.DT_LAST_ADUITisnotnull) gwhere t.card_id= f.CARD_IDand t.card_id= g.card_id(+)and (t.idin (select b.id_nofrom aidszh_mst_patients b)and
      t.pidin (select s.cure_nofrom aidszh_mst_hivchech s))and ((g.TZONECODElike'%')or (t.zonecodelike'%'))and t.TM_CREATE>=
      to_date('2009-12-09 00:00:00','yyyy-mm-dd hh24:mi:ss')and t.TM_CREATE <=
      to_date('2009-12-09 23:59:59','yyyy-mm-dd hh24:mi:ss')and1=1and1=1and1=1and1=1and1=1and1=1AND1=1ORDERBY T.ANTIN
它的执行计划为：

这个执行时间需要十几分钟。

说明：表aidszh_mst_patients和aidszh_mst_hivchech 都有很多的数据量大约10w。 AIDSZH_SGRA_CHILD_INFO 和aidszh_sgra_child_flw 是一对多的关系，数据量也很多。我不想大篇幅改变sql的结构，很多地方都用这个sql。在第二个sql中。我如果删除条件 and (t.id in (select b.id_no from aidszh_mst_patients b) and
      t.pid in (select s.cure_no from aidszh_mst_hivchech s))则执行效率超快。这个条件有时候有，有时候没有，完全取决于用户的操作。

请高手分析这两个sql的异同，并提出第二个sql的优化建议，达到和第一个sql同样的效果。
多谢。

[/Quote]



select t.*

  from AIDSZH_SGRA_ADULT_INFO t,

       aidszh_mst_patients b,

       aidszh_mst_hivchech s

 where t.id = b.id_no

   and t.pid = s.cure_no;
中，AIDSZH_SGRA_ADULT_INFO 的目前数据量很少，也就几百，正式库中会有几十万。aidszh_mst_patients 和aidszh_mst_hivchech 都有十几万的数据。
执行计划如下：



oracle经典笔记
SQL语句.pdf SQL语法练习.pdf PLSQL基础教程-必看.pdf Oracle_PL-SQL入门教程(经典).doc
Oracle SQL语句之常见优化方法总结
下面列举一些工作中常常会碰到的Oracle的SQL语句优化方法： 1、SQL语句尽量用大写的；  因为oracle总是先解析SQL语句，把小写的字母转换成大写的再执行。 2、使用表的别名：   当在SQL语句中连接多个表时, ...
SQL语句从入门到精通迅速提升篇视频课程(Oracle零基础版)
这是一套体系化的SQL语句教程，按照从浅入深的模式讲解工作中常用的SQL写法、函数、字段处理技术等。使大家快速掌握SQL的基础及部分高阶知识，进而熟练的对数据库表、字段进行各种查询、创建、更新、删除操作。  本课程内容可分为三大块：   （1）条件查询、排序、分组、模糊查询、聚合函数等基础部分讲解；   （2）多表关联、子查询、常用字符、数字、日期处理技术等常用技术讲解；   （3）对表、表内容、表字段的操作讲解。
【转存】Oracle SQL语句之常见优化方法总结
下面列举一些工作中常常会碰到的Oracle的SQL语句优化方法： 1、SQL语句尽量用大写的； 因为oracle总是先解析SQL语句，把小写的字母转换成大写的再执行。 2、使用表的别名： 当在SQL语句中连接多个表时, 尽量使用...
Oracle 的 SQL 语句优化方法：
Oracle 的 SQL 语句优化方法：
Oracle

17,082

社区成员

55,235

社区内容

发帖
与我相关
我的任务
社区描述
Oracle开发相关技术讨论
社区管理员
  • 开发
  • Lucifer三思而后行
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章