这段sql有没有可以优化的地方

Question

就是一个行转列，t_cp_infoext（pk:object_id,lang,name）(fk:object_id)
                t_cp_info(pk:OBJECT_ID)
select cpid, lang, spshortname, spname, status, credit, contact, contacttel
  from (select distinct a.cpid,
                        c.lang,
                        nvl((select b.value
                              from t_cp_infoext b
                             where b.object_id = a.object_id
                               and lower(b.name) = 'shortname'
                               and c.lang = b.lang),
                            ' ') spshortname,
                        nvl((select b.value
                              from t_cp_infoext b
                             where b.object_id = a.object_id
                               and lower(b.name) = 'name'
                               and c.lang = b.lang),
                            ' ') spname,
                        a.status,
                        a.credit,
                        a.contact,
                        a.contacttel
          from t_cp_info a, t_cp_infoext c)
 where spshortname <> ' '
    or spname <> ' '
 order by 1

Answer 1

cmdsqlplus 登陆set autotrace on 
set timing on执行你这个sql看一下执行计划，最好把执行计划贴出来，然后让大家帮你看看。

Answer 2

select distinct a.cpid,
                        c.lang,
                        nvl((select b.value
                              from t_cp_infoext b
                             where b.object_id = a.object_id
                               and lower(b.name) = 'shortname'
                               and c.lang = b.lang),
                            ' ') spshortname,
                        nvl((select b.value
                              from t_cp_infoext b
                             where b.object_id = a.object_id
                               and lower(b.name) = 'name'
                               and c.lang = b.lang),
                            ' ') spname,
                        a.status,
                        a.credit,
                        a.contact,
                        a.contacttel
          from t_cp_info a, t_cp_infoext c
好怪..怎么会让A,C做笛卡尔集?

Answer 3

不做笛卡尔积，没办法返回唯一行啊 
然后用distinct去重了，错是没错误了，但是效率太低

Answer 4

 select distinct a.cpid,
                        c.lang,
                        c.name ,
                        c.value namevalue,
                        a.status,
                        a.credit,
                        a.contact,
                        a.contacttel
          from t_cp_info a, t_cp_infoext c
          where b.object_id = a.object_id
            and lower(c.name) in('shortname','name')
中间的笛卡尔及改成上面的,然后再判断 name,分解spshortname,spname

Answer 5

select a.cpid, c.lang, 
  nvl(max(decode(lower(b.name),'shortname',b.value,null)),' ') as spshortname, 
  nvl(max(decode(lower(b.name),'name',b.value,null)),' ') as spname, a.status, a.credit, a.contact, a.contacttel
  from t_cp_info a, t_cp_infoext c,t_cp_infoext b
 where lower(b.name) in ('shortname','name')
   and b.object_id = a.object_id
   and c.lang = b.lang
 group by a.cpid, c.lang, spshortname, spname, a.status, a.credit, a.contact, a.contacttel
 order by 1

Answer 6

楼上答案稍微改了下，是对的
select a.cpid,
       b.lang,
       nvl(max(decode(lower(b.name), 'shortname', b.value, null)), ' ') as spshortname,
       nvl(max(decode(lower(b.name), 'name', b.value, null)), ' ') as spname,
       a.status,
       a.credit,
       a.contact,
       a.contacttel
  from t_cp_info@dblink01    a,
       t_cp_infoext@dblink01 b
 where lower(b.name) in ('shortname', 'name')
   and b.object_id = a.object_id
 group by a.cpid,
          b.lang,
          a.status,
          a.credit,
          a.contact,
          a.contacttel
 order by 1,2