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

[eeff]

就是一个行转列，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

[mahanso]

cmd

sqlplus 登陆

set autotrace on
set timing on

执行你这个sql

看一下执行计划，最好把执行计划贴出来，然后让大家帮你看看。

[iqlife]

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做笛卡尔集?

[eeff]

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

[iqlife]

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

[westneverwin]

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

[eeff]

楼上答案稍微改了下，是对的

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