先看一下我的SQL 语句吧:
create or replace view view_dat_sms1182308466773 as
( select * from DAT_SMS_20070413
union all select * from DAT_SMS_20070415
union all select * from DAT_SMS_20070422
union all select * from DAT_SMS_20070423
union all select * from DAT_SMS_20070424
union all select * from DAT_SMS_20070510
union all select * from DAT_SMS_20070511
union all select * from DAT_SMS_20070615 );
需求:
select distinct t.called_id from view_dat_sms1182308466773 t where t.task_id>'0';
问题:
效率太低
我的解决方法:
create or replace view ed_view_called13 as
select CALLED_ID from DAT_SMS_20070413 where task_id>'0' ;create or replace view ed_view_called15 as
select CALLED_ID from DAT_SMS_20070415 where task_id>'0' ;create or replace view ed_view_called22 as
select CALLED_ID from DAT_SMS_20070422 where task_id>'0' ;create or replace view ed_view_called23 as
select CALLED_ID from DAT_SMS_20070423 where task_id>'0' ;create or replace view ed_view_called24 as
select CALLED_ID from DAT_SMS_20070424 where task_id>'0' ;create or replace view ed_view_called510 as
select CALLED_ID from DAT_SMS_20070510 where task_id>'0' ;create or replace view ed_view_called511 as
select CALLED_ID from DAT_SMS_20070511 where task_id>'0' ;create or replace view ed_view_called615 as
select CALLED_ID from DAT_SMS_20070615 where task_id>'0' ; create or replace view ED_VIEW_CALLED_TEST as
((((((( select * from ed_view_called13
) union all select * from ed_view_called15 where not exists (select * from ed_view_called13)
) union all select * from ed_view_called22 where not exists (select * from ed_view_called13)
and not exists (select * from ed_view_called15)
) union all select * from ed_view_called23 where not exists (select * from ed_view_called13)
and not exists (select * from ed_view_called15)
and not exists (select * from ed_view_called22)
) union all select * from ed_view_called24 where not exists (select * from ed_view_called13)
and not exists (select * from ed_view_called15)
and not exists (select * from ed_view_called22)
and not exists (select * from ed_view_called23)
) union all select * from ed_view_called510 where not exists (select * from ed_view_called13)
and not exists (select * from ed_view_called15)
and not exists (select * from ed_view_called22)
and not exists (select * from ed_view_called23)
and not exists (select * from ed_view_called24)
) union all select * from ed_view_called511 where not exists (select * from ed_view_called13)
and not exists (select * from ed_view_called15)
and not exists (select * from ed_view_called22)
and not exists (select * from ed_view_called23)
and not exists (select * from ed_view_called24)
and not exists (select * from ed_view_called510)
) union all select * from ed_view_called615 where not exists (select * from ed_view_called13)
and not exists (select * from ed_view_called15)
and not exists (select * from ed_view_called22)
and not exists (select * from ed_view_called23)
and not exists (select * from ed_view_called24)
and not exists (select * from ed_view_called510)
and not exists (select * from ed_view_called511) ;
select * from ED_VIEW_CALLED_TEST ;结果:
输出结果不一直我通过查数据 看到
create or replace view ED_VIEW_CALLED_TEST as
((((((( select * from ed_view_called13
) union all select * from ed_view_called15 where not exists (select * from ed_view_called13)
) union all select * from ed_view_called22 where not exists (select * from ed_view_called13)
and not exists (select * from ed_view_called15)
) union all select * from ed_view_called23 where not exists (select * from ed_view_called13)
and not exists (select * from ed_view_called15)
and not exists (select * from ed_view_called22)
) union all select * from ed_view_called24 where not exists (select * from ed_view_called13)
and not exists (select * from ed_view_called15)
and not exists (select * from ed_view_called22)
and not exists (select * from ed_view_called23)
) union all select * from ed_view_called510 where not exists (select * from ed_view_called13)
and not exists (select * from ed_view_called15)
and not exists (select * from ed_view_called22)
and not exists (select * from ed_view_called23)
and not exists (select * from ed_view_called24)
) union all select * from ed_view_called511 where not exists (select * from ed_view_called13)
and not exists (select * from ed_view_called15)
and not exists (select * from ed_view_called22)
and not exists (select * from ed_view_called23)
and not exists (select * from ed_view_called24)
and not exists (select * from ed_view_called510)
) union all select * from ed_view_called615 where not exists (select * from ed_view_called13)
and not exists (select * from ed_view_called15)
and not exists (select * from ed_view_called22)
and not exists (select * from ed_view_called23)
and not exists (select * from ed_view_called24)
and not exists (select * from ed_view_called510)
and not exists (select * from ed_view_called511) ;
跟
create or replace view ED_VIEW_CALLED_TEST as
( select * from ed_view_called13)
效果是一样的
说白了 就是 not exists 的问题。
谁能给我讲明白了,先谢过了。
create or replace view view_dat_sms1182308466773 as
( select * from DAT_SMS_20070413
union all select * from DAT_SMS_20070415
union all select * from DAT_SMS_20070422
union all select * from DAT_SMS_20070423
union all select * from DAT_SMS_20070424
union all select * from DAT_SMS_20070510
union all select * from DAT_SMS_20070511
union all select * from DAT_SMS_20070615 );
需求:
select distinct t.called_id from view_dat_sms1182308466773 t where t.task_id>'0';
问题:
效率太低
我的解决方法:
create or replace view ed_view_called13 as
select CALLED_ID from DAT_SMS_20070413 where task_id>'0' ;create or replace view ed_view_called15 as
select CALLED_ID from DAT_SMS_20070415 where task_id>'0' ;create or replace view ed_view_called22 as
select CALLED_ID from DAT_SMS_20070422 where task_id>'0' ;create or replace view ed_view_called23 as
select CALLED_ID from DAT_SMS_20070423 where task_id>'0' ;create or replace view ed_view_called24 as
select CALLED_ID from DAT_SMS_20070424 where task_id>'0' ;create or replace view ed_view_called510 as
select CALLED_ID from DAT_SMS_20070510 where task_id>'0' ;create or replace view ed_view_called511 as
select CALLED_ID from DAT_SMS_20070511 where task_id>'0' ;create or replace view ed_view_called615 as
select CALLED_ID from DAT_SMS_20070615 where task_id>'0' ; create or replace view ED_VIEW_CALLED_TEST as
((((((( select * from ed_view_called13
) union all select * from ed_view_called15 where not exists (select * from ed_view_called13)
) union all select * from ed_view_called22 where not exists (select * from ed_view_called13)
and not exists (select * from ed_view_called15)
) union all select * from ed_view_called23 where not exists (select * from ed_view_called13)
and not exists (select * from ed_view_called15)
and not exists (select * from ed_view_called22)
) union all select * from ed_view_called24 where not exists (select * from ed_view_called13)
and not exists (select * from ed_view_called15)
and not exists (select * from ed_view_called22)
and not exists (select * from ed_view_called23)
) union all select * from ed_view_called510 where not exists (select * from ed_view_called13)
and not exists (select * from ed_view_called15)
and not exists (select * from ed_view_called22)
and not exists (select * from ed_view_called23)
and not exists (select * from ed_view_called24)
) union all select * from ed_view_called511 where not exists (select * from ed_view_called13)
and not exists (select * from ed_view_called15)
and not exists (select * from ed_view_called22)
and not exists (select * from ed_view_called23)
and not exists (select * from ed_view_called24)
and not exists (select * from ed_view_called510)
) union all select * from ed_view_called615 where not exists (select * from ed_view_called13)
and not exists (select * from ed_view_called15)
and not exists (select * from ed_view_called22)
and not exists (select * from ed_view_called23)
and not exists (select * from ed_view_called24)
and not exists (select * from ed_view_called510)
and not exists (select * from ed_view_called511) ;
select * from ED_VIEW_CALLED_TEST ;结果:
输出结果不一直我通过查数据 看到
create or replace view ED_VIEW_CALLED_TEST as
((((((( select * from ed_view_called13
) union all select * from ed_view_called15 where not exists (select * from ed_view_called13)
) union all select * from ed_view_called22 where not exists (select * from ed_view_called13)
and not exists (select * from ed_view_called15)
) union all select * from ed_view_called23 where not exists (select * from ed_view_called13)
and not exists (select * from ed_view_called15)
and not exists (select * from ed_view_called22)
) union all select * from ed_view_called24 where not exists (select * from ed_view_called13)
and not exists (select * from ed_view_called15)
and not exists (select * from ed_view_called22)
and not exists (select * from ed_view_called23)
) union all select * from ed_view_called510 where not exists (select * from ed_view_called13)
and not exists (select * from ed_view_called15)
and not exists (select * from ed_view_called22)
and not exists (select * from ed_view_called23)
and not exists (select * from ed_view_called24)
) union all select * from ed_view_called511 where not exists (select * from ed_view_called13)
and not exists (select * from ed_view_called15)
and not exists (select * from ed_view_called22)
and not exists (select * from ed_view_called23)
and not exists (select * from ed_view_called24)
and not exists (select * from ed_view_called510)
) union all select * from ed_view_called615 where not exists (select * from ed_view_called13)
and not exists (select * from ed_view_called15)
and not exists (select * from ed_view_called22)
and not exists (select * from ed_view_called23)
and not exists (select * from ed_view_called24)
and not exists (select * from ed_view_called510)
and not exists (select * from ed_view_called511) ;
跟
create or replace view ED_VIEW_CALLED_TEST as
( select * from ed_view_called13)
效果是一样的
说白了 就是 not exists 的问题。
谁能给我讲明白了,先谢过了。
select * from ed_view_called15 where not exists (select * from ed_view_called13)子查询无任何条件,说明只要视图ed_view_called13有满足条件的数据,该语句就不会得到任何结果。