select '4400006009','2007Y0001',
b.* from etl_case5_sryh b
where
b.acct_item_type_id
in (select distinct c.srzllz from etl_case5_cpkmzhgz c where c.srzll='acct_item_type_id' and c.sccpkm = '92030101')
and b.libs_city_village_id in (select c.srzllz from etl_case5_cpkmzhgz c where c.srzll='libs_city_village_id' and c.sccpkm='92030101')
and b.libs_cust_type in (select c.srzllz from etl_case5_cpkmzhgz c where c.srzll='libs_cust_type' and c.sccpkm='92030101')
and b.libs_product_id in (select c.srzllz from etl_case5_cpkmzhgz c where c.srzll='libs_product_id' and c.sccpkm='92030101')
and b.libs_user_type_id in (select c.srzllz from etl_case5_cpkmzhgz c where c.srzll='libs_user_type_id' and c.sccpkm='92030101')查询速度太慢,知道是由于in造成的,in条件中数据量不大,关键是etl_case5_sryh表中数据量很大,几百万条数据,请问如何优化?
从网上搜了一下说是exists貌似可以加快速度,不过在plsql把in改为exists报错。只能在Oracle数据库中优化,不能用程序优化。
b.* from etl_case5_sryh b
where
b.acct_item_type_id
in (select distinct c.srzllz from etl_case5_cpkmzhgz c where c.srzll='acct_item_type_id' and c.sccpkm = '92030101')
and b.libs_city_village_id in (select c.srzllz from etl_case5_cpkmzhgz c where c.srzll='libs_city_village_id' and c.sccpkm='92030101')
and b.libs_cust_type in (select c.srzllz from etl_case5_cpkmzhgz c where c.srzll='libs_cust_type' and c.sccpkm='92030101')
and b.libs_product_id in (select c.srzllz from etl_case5_cpkmzhgz c where c.srzll='libs_product_id' and c.sccpkm='92030101')
and b.libs_user_type_id in (select c.srzllz from etl_case5_cpkmzhgz c where c.srzll='libs_user_type_id' and c.sccpkm='92030101')查询速度太慢,知道是由于in造成的,in条件中数据量不大,关键是etl_case5_sryh表中数据量很大,几百万条数据,请问如何优化?
从网上搜了一下说是exists貌似可以加快速度,不过在plsql把in改为exists报错。只能在Oracle数据库中优化,不能用程序优化。
解决方案 »
- 浏览器进不了sqlplus!
- oracle dblink问题,请教高手
- oracle事务插入多张表
- 问个语句啊
- Oracle 中VARRAY的 NOT NULL之惑,限制为NOT NULL后,还可以为NULL
- CONNECT BY 超难问题!在线等....高手请进!
- 导入DMP文件出错!!!!
- ora-1578 data block corrupted的处理问题
- Oracle卸载卸不干净,再次安装的时候怎么也安装不成功
- 请问有什么oracle书籍适合一个初学者学习?
- 我在sql语句中DECODE返回的值有韩文,发现,导出excel表中,,得到的数据都是乱码
- 一个棘手的Oracle Hint相关的问题。
select /* + use_nl(etl_case5_sryh)*/ '4400006009','2007Y0001',
b.* from etl_case5_sryh b
where exists(select 1 from etl_case5_cpkmzhgz c where c.srzll='acct_item_type_id' and c.sccpkm = '92030101' and b.acct_item_type_id = c.srzllz)
and exists(select 1 from etl_case5_cpkmzhgz c where c.srzll='libs_city_village_id' and c.sccpkm = '92030101' and b.libs_city_village_id = c.srzllz)
and ...
你是做电信的?
From Etl_Case5_Sryh b
Where (b.Acct_Item_Type_Id, b.Libs_City_Village_Id, b.Libs_Cust_Type,
b.Libs_Product_Id, b.Libs_User_Type_Id) In
(Select Decode(c.Srzll, 'acct_item_type_id', c.Srzllz),
Decode(c.Srzll, 'libs_city_village_id', c.Srzllz),
Decode(c.Srzll, 'libs_cust_type', c.Srzllz),
Decode(c.Srzll, 'libs_product_id', c.Srzllz),
Decode(c.Srzll, 'libs_user_type_id', c.Srzllz)
From Etl_Case5_Cpkmzhgz c
Where c.Sccpkm = '92030101')
试试这个,不行的话,自己修改一下,同一个表最好扫面一次,数据量还多。
From Etl_Case5_Sryh b
Where (b.Acct_Item_Type_Id, b.Libs_City_Village_Id, b.Libs_Cust_Type,
b.Libs_Product_Id, b.Libs_User_Type_Id) In
(Select max(Decode(c.Srzll, 'acct_item_type_id', c.Srzllz,null)),
max(Decode(c.Srzll, 'libs_city_village_id', c.Srzllz,null)),
max(Decode(c.Srzll, 'libs_cust_type', c.Srzllz,null)),
max(Decode(c.Srzll, 'libs_product_id', c.Srzllz,null)),
max(Decode(c.Srzll, 'libs_user_type_id', c.Srzllz,null))
From Etl_Case5_Cpkmzhgz c
Where c.Sccpkm = '92030101')