现有一table 和 一 view:table(数据量非常大):Big_table (userid,订购物品,物品数量,订购时间) view(存用户信息): userinfo_vw (userid,name,address)其中Big_table中的 userid 和 订购时间建有 索引;用户可以在Client 输入:用户查询时间,用户输入的姓名(对应view中的name),
通过这2个查询条件(不考虑name重名的情况)
查询出 小于用户查询时间的最近的一笔用户的信息;
SQL如下:
select name,address,订购物品,物品数量,订购时间
from Big_table t,userinfo_vw e
where 订购时间 =
( select max(订购时间) from Big_table A,userinfo_vw B
where A.订购时间<to_date(用户查询时间,‘YYYY-MM-DD HH24:MI:SS’)
and A.userid = B.userid
and B.name = 用户输入的姓名
)
and t.useid =e.userid
and e.name = 用户输入的姓名请各位帮忙看下,此语句是否还有方法进行优化,提升查询效率?
通过这2个查询条件(不考虑name重名的情况)
查询出 小于用户查询时间的最近的一笔用户的信息;
SQL如下:
select name,address,订购物品,物品数量,订购时间
from Big_table t,userinfo_vw e
where 订购时间 =
( select max(订购时间) from Big_table A,userinfo_vw B
where A.订购时间<to_date(用户查询时间,‘YYYY-MM-DD HH24:MI:SS’)
and A.userid = B.userid
and B.name = 用户输入的姓名
)
and t.useid =e.userid
and e.name = 用户输入的姓名请各位帮忙看下,此语句是否还有方法进行优化,提升查询效率?
select name,address,订购物品,物品数量,订购时间
from Big_table t,userinfo_vw e
where 订购时间 = (select max(订购时间)
from Big_table A
where exists(select 1 from userinfo_vw B where A.userid = B.userid
and B.name = 用户输入的姓名
and A.订购时间<to_date(用户查询时间,'YYYY-MM-DD HH24:MI:SS’) ))
and t.useid =e.userid
and e.name = 用户输入的姓名
select 1 as userid,'001' as item,20 as qty ,'2010/06/01' as d_date from dual
union all select 1 as userid,'002' as item,22 as qty ,'2010/06/02' as d_date from dual
union all select 1 as userid,'003' as item,33 as qty ,'2010/06/03' as d_date from dual
union all select 1 as userid,'002' as item,11 as qty ,'2010/06/04' as d_date from dual
union all select 1 as userid,'001' as item,55 as qty ,'2010/06/05' as d_date from dual
),
userinfo_vw as(
select 1 as userid,'usename' as name,'address' as address from dual
)
select b.name,b.address,
max(a.item) keep( dense_rank first order by d_date desc ) as item,
max(a.qty) keep( dense_rank first order by d_date desc ) as qty,
max(a.d_date) keep( dense_rank first order by d_date desc ) as d_date
from big_table a , userinfo_vw b
where a.userid= b.userid
and b.name = 'usename'
and a.d_date < '2010/06/05'
group by b.name,b.address