有个查询语句
select msisdn,'1006000001','11' from gsm_user_nt a where to_char(a.apply_date,'yyyymm')<='200507' and brand_id=1 and state>=1 and state<=19
and exists (select 1 from custcare.user_product_info_nt@TO_ZWDB11_WX b where a.gsm_user_id=to_char(b.user_id) and product_id in(100000,100004,100002,100006) and end_date is null)
and not exists
(select 1 from user_package_nt@TO_ZWDB11_WX c
where a.gsm_user_id=to_char(c.user_id) and (package_code=1801
and end_date is null ) or ( package_code=1983 and end_date is null ) or ( package_code in (2686,2687,2688,2689,2759,4525,4625,4521,4624,1205,1208,1209,1184,4516,4894) and end_date is null ))需要70秒左右(查询到的数据为0条)但是我在查询里,加了sequense,查询长时间出不来了。cm_credit_user_seq.nextval
select msisdn,'1006000001','11',cm_credit_user_seq.nextval from gsm_user_nt a where to_char(a.apply_date,'yyyymm')<='200507' and brand_id=1 and state>=1 and state<=19
and exists (select 1 from custcare.user_product_info_nt@TO_ZWDB11_WX b where a.gsm_user_id=to_char(b.user_id) and product_id in(100000,100004,100002,100006) and end_date is null)
and not exists
(select 1 from user_package_nt@TO_ZWDB11_WX c
where a.gsm_user_id=to_char(c.user_id) and (package_code=1801
and end_date is null ) or ( package_code=1983 and end_date is null ) or ( package_code in (2686,2687,2688,2689,2759,4525,4625,4521,4624,1205,1208,1209,1184,4516,4894) and end_date is null ))
select msisdn,'1006000001','11' from gsm_user_nt a where to_char(a.apply_date,'yyyymm')<='200507' and brand_id=1 and state>=1 and state<=19
and exists (select 1 from custcare.user_product_info_nt@TO_ZWDB11_WX b where a.gsm_user_id=to_char(b.user_id) and product_id in(100000,100004,100002,100006) and end_date is null)
and not exists
(select 1 from user_package_nt@TO_ZWDB11_WX c
where a.gsm_user_id=to_char(c.user_id) and (package_code=1801
and end_date is null ) or ( package_code=1983 and end_date is null ) or ( package_code in (2686,2687,2688,2689,2759,4525,4625,4521,4624,1205,1208,1209,1184,4516,4894) and end_date is null ))需要70秒左右(查询到的数据为0条)但是我在查询里,加了sequense,查询长时间出不来了。cm_credit_user_seq.nextval
select msisdn,'1006000001','11',cm_credit_user_seq.nextval from gsm_user_nt a where to_char(a.apply_date,'yyyymm')<='200507' and brand_id=1 and state>=1 and state<=19
and exists (select 1 from custcare.user_product_info_nt@TO_ZWDB11_WX b where a.gsm_user_id=to_char(b.user_id) and product_id in(100000,100004,100002,100006) and end_date is null)
and not exists
(select 1 from user_package_nt@TO_ZWDB11_WX c
where a.gsm_user_id=to_char(c.user_id) and (package_code=1801
and end_date is null ) or ( package_code=1983 and end_date is null ) or ( package_code in (2686,2687,2688,2689,2759,4525,4625,4521,4624,1205,1208,1209,1184,4516,4894) and end_date is null ))
解决方案 »
- toplink 到底是什么东东啊?
- linux下的Oracle安装包?
- Oracle 数据库,Session Pooling的问题 - 高手帮忙
- 高手求救,我想测下sql语句的性能,存储过程测试时点概览图,说调试模式下不支持
- 查询以汉字打头的记录
- ORACLE服务器IP改变问题
- 请问如何在SQLPLUS中执行一个存储过程
- 大家oracle10g用过了没有?我怎么enterprise manager登陆不了??
- 如何连接其他机的数据库。
- 如何在一个procedure中把出错信息导入到一个文本文件中记录下来?
- pl/sql 建表时报错不知是何原因,请帮忙解决!
- 怎样将oracle数据库的system用户的sysdba权限更改为普通权限或dba权限
2.一般插入的时候用到序列吧。