本人安装的是oracle11gR2;创建表(create table as方式)与视图的语句:
select t1.start_dt,t1.user_name,
t1.login_type,t1.ua_type,
t2.start_time,t2.node_id,t2.nas_ip,t2.roam_domain,
t2.session_time,t2.input_octets,t2.output_octets
from
(select a.start_dt,a.user_name,a.nas_ip,a.login_type,a.ua_type,
row_number() over(partition by user_name order by start_dt asc) r_num
from temp_monitor_ua_1128 a) t1,
(select b.start_time,b.user_name,b.node_id,
b.nas_ip,b.roam_domain,b.session_time,
b.input_octets,b.output_octets,
row_number() over(partition by user_name order by start_time) r_num
from temp_usage_bppp_1128 b) t2
where t1.user_name=t2.user_name
and t1.r_num=t2.r_num;做sum的语句:
select sum(a.session_time) from temp_table a
union all
select sum(a.session_time) from view_table a;
结果:
1 5087185135
2 5087196832如何解释这个现象?表空间是由3个数据文件组成的,10g,10g,5g,不知与这个有关系否?
select t1.start_dt,t1.user_name,
t1.login_type,t1.ua_type,
t2.start_time,t2.node_id,t2.nas_ip,t2.roam_domain,
t2.session_time,t2.input_octets,t2.output_octets
from
(select a.start_dt,a.user_name,a.nas_ip,a.login_type,a.ua_type,
row_number() over(partition by user_name order by start_dt asc) r_num
from temp_monitor_ua_1128 a) t1,
(select b.start_time,b.user_name,b.node_id,
b.nas_ip,b.roam_domain,b.session_time,
b.input_octets,b.output_octets,
row_number() over(partition by user_name order by start_time) r_num
from temp_usage_bppp_1128 b) t2
where t1.user_name=t2.user_name
and t1.r_num=t2.r_num;做sum的语句:
select sum(a.session_time) from temp_table a
union all
select sum(a.session_time) from view_table a;
结果:
1 5087185135
2 5087196832如何解释这个现象?表空间是由3个数据文件组成的,10g,10g,5g,不知与这个有关系否?
create table temp_a as
with monitor as (
select a.start_dt,a.user_name,a.nas_ip,a.login_type,a.ua_type,
row_number() over(partition by user_name order by start_dt asc) r_num
from temp_starbacks a
),
bppp_1128 as (
select b.start_time,b.user_name,b.node_id,
b.nas_ip,b.roam_domain,b.session_time,
b.input_octets,b.output_octets,
row_number() over(partition by user_name order by start_time asc) r_num
from temp_usage_bppp_1128 b
)
select t1.start_dt,t1.user_name,
case when upper(t1.user_name) like '%.EDU.%' then 1
when upper(t1.user_name) like '%STARBUCKS%' THEN 2
ELSE 3
END user_type,
t1.login_type,t1.ua_type,
t2.start_time,t2.node_id,t2.nas_ip,t2.roam_domain,
t2.session_time,t2.input_octets,t2.output_octets
from monitor t1,bppp_1128 t2
where t1.user_name=t2.user_name
and t1.r_num=t2.r_num
;drop view temp_b;create view temp_b as
with monitor as (
select a.start_dt,a.user_name,a.nas_ip,a.login_type,a.ua_type,
row_number() over(partition by user_name order by start_dt asc) r_num
from temp_starbacks a
),
bppp_1128 as (
select b.start_time,b.user_name,b.node_id,
b.nas_ip,b.roam_domain,b.session_time,
b.input_octets,b.output_octets,
row_number() over(partition by user_name order by start_time asc) r_num
from temp_usage_bppp_1128 b
)
select t1.start_dt,t1.user_name,
case when upper(t1.user_name) like '%.EDU.%' then 1
when upper(t1.user_name) like '%STARBUCKS%' THEN 2
ELSE 3
END user_type,
t1.login_type,t1.ua_type,
t2.start_time,t2.node_id,t2.nas_ip,t2.roam_domain,
t2.session_time,t2.input_octets,t2.output_octets
from monitor t1,bppp_1128 t2
where t1.user_name=t2.user_name
and t1.r_num=t2.r_num
;commit;select count(*) from temp_a
union all
select count(*) from temp_b;select * from temp_a
minus
select * from temp_b;select sum(session_time) from temp_a
union all
select sum(session_time) from temp_b;1 85868454
2 85865901
select a.start_dt,a.user_name,a.nas_ip,a.login_type,a.ua_type,
row_number() over(partition by user_name order by start_dt asc) r_num
from temp_starbacks a;
select count(*) from temp_1;
create table temp_2 as
select b.start_time,b.user_name,b.node_id,
b.nas_ip,b.roam_domain,b.session_time,
b.input_octets,b.output_octets,
row_number() over(partition by user_name order by start_time asc) r_num
from temp_usage_bppp_1128 b ;
create table temp_3 as
select t1.start_dt,t1.user_name,
case when upper(t1.user_name) like '%.EDU.%' then 1
when upper(t1.user_name) like '%STARBUCKS%' THEN 2
ELSE 3
END user_type,
t1.login_type,t1.ua_type,
t2.start_time,t2.node_id,t2.nas_ip,t2.roam_domain,
t2.session_time,t2.input_octets,t2.output_octets
from temp_1 t1,temp_2 t2
where t1.user_name=t2.user_name
and t1.r_num=t2.r_num;
select count(*) from temp_3;
create view temp_4 as
select t1.start_dt,t1.user_name,
case when upper(t1.user_name) like '%.EDU.%' then 1
when upper(t1.user_name) like '%STARBUCKS%' THEN 2
ELSE 3
END user_type,
t1.login_type,t1.ua_type,
t2.start_time,t2.node_id,t2.nas_ip,t2.roam_domain,
t2.session_time,t2.input_octets,t2.output_octets
from temp_1 t1,temp_2 t2
where t1.user_name=t2.user_name
and t1.r_num=t2.r_num;
select count(*) from temp_3
union all
select count(*) from temp_4;select * from temp_3
minus
select * from temp_4;select sum(session_time) from temp_3
union all
select sum(session_time) from temp_4;1 85868454
2 85868454
1 85868454--物理表
2 85865901--视图
固化row_num的结果
1 85868454--物理表
2 85868454--视图结论:对于包含开窗函数row_number()子查询的视图做聚合等操作会出现难以意料的问题,具体原因还不知道。
处理方式:将包含开窗函数的查询创建成临时表,所有的聚合等操作在物理表上完成。请各位大侠批评指正~!