有TA,TB两个表
TA表字段 a,b,c,d,e
TB表字段 a,m我本来的目的是这么查询 select TA.a ,TA.b, TA.c, TA.d, TA.e from TA,TB where TA.a=TB.a,
结果发现速度太慢了(TA表数万条记录,TB表数百条记录),请问如何改进呢
TA表字段 a,b,c,d,e
TB表字段 a,m我本来的目的是这么查询 select TA.a ,TA.b, TA.c, TA.d, TA.e from TA,TB where TA.a=TB.a,
结果发现速度太慢了(TA表数万条记录,TB表数百条记录),请问如何改进呢
CREATE OR REPLACE VIEW ICCUSTOMER_INFO AS
SELECT
c.ciid as customer_id,
max(c.ciowner) as onwer,
max(c.ciuser) as customer_name,
max(c.cihtel) as customer_tel,
max(nvl(a.ainame,' ')||c.cidoorplate) as customer_address,
max(c.cisubmitdate) as now_time,
max(c.cicusttype) as user_type,
max(c.cistatus) as user_flag,
max(p.szyfprice) * min(decode(cfl_piid,null,1,'1',cfl_yhdj*cfl_yhbl,1)) as szyf_price,
max(p.sfprice) * min(decode(cfl_piid,null,1,'1',cfl_yhdj*cfl_yhbl,1)) as unit_price,
max(p.psfprice) * min(decode(cfl_piid,null,1,'2',cfl_yhdj*cfl_yhbl,1)) as wu_price,
max(c.cisbzs) as meter_num,
'Y' as yhzt
FROM WMIS_CUSTINFO c , view_current_price p , wmis_cust_favourable_list l , wmis_meterinfo m , wmis_addressinfo a
WHERE c.cipfid = p.pfid and c.ciid = cfl_cid(+) and c.ciid = m.miciid and
m.mitid = '2' and c.ciaddrid = a.aiid(+)
GROUP BY c.ciidunion allSELECT
cd.cird_cid as customer_id,
max(cd.cird_name) as onwer,
max(cd.cird_name) as customer_name,
max(cd.cird_htel) as customer_tel,
max(cd.cird_doorplate) as customer_address,
max(cd.CIRD_YLHDATE) as now_time,
max(cd.CIRD_CUSTTYPE) as user_type,
max('1') as user_flag,
max(p.szyfprice) * min(decode(cfl_piid,null,1,'1',cfl_yhdj*cfl_yhbl,1)) as szyf_price,
max(p.sfprice) * min(decode(cfl_piid,null,1,'1',cfl_yhdj*cfl_yhbl,1)) as unit_price,
max(p.psfprice) * min(decode(cfl_piid,null,1,'2',cfl_yhdj*cfl_yhbl,1)) as wu_price,
count(*) as meter_num,
'N' as yhzt
FROM wmis_cust_inst_req_detail cd , view_current_price p , wmis_cust_favourable_list l
WHERE cd.cird_pfid = p.pfid and cd.cird_cid = cfl_cid(+) and
cd.CIRD_MBRAND = '27' and cd.CIRD_FLAGD = 'Y' and CIRD_FLAGDREDGE = 'N'
GROUP BY cd.cird_cid
表
-- Create table
create table
(
CUSTOMER_ID VARCHAR2(10) not null,
WATERMETER_INFO VARCHAR2(12) not null
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
-- Create/Recreate primary, unique and foreign key constraints
alter table
add constraint CUSTOMER_ID2 primary key (CUSTOMER_ID)
using index
tablespace USERS
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
select * from 视图, 本身是比较慢
找出ta中a字段存在于tb表中的记录?
试试
select TA.a ,TA.b, TA.c, TA.d, TA.e from TA
where exists(select 1 from tb where a=ta.a)
直接查询ta快吗