-- 统计某一段时间登录过系统,且同时拥有2种物品的用户总数
select count(distinct(i.user_no))
from inventory i
where i.user_no in (select i.user_no
from inventory i, timelog l
where i.user_no = l.user_no
and l.logindate between
to_date('20070101000000', 'yyyymmddhh24miss') and
to_date('20070107235959', 'yyyymmddhh24miss')
and i.itemid = 10002)
and i.itemid = 10001说明:
timelog表中包括user_no和logindate字段,用户每次登录系统都会产生一条记录
inventory表中包括user_no和itemid字段,当用户拥有某种物品时,其中会有一条对应的记录我的问题是:感觉这样查询的效率不高,但是又不知道怎样优化语句,请达人指点一二,谢谢!
select count(distinct(i.user_no))
from inventory i
where i.user_no in (select i.user_no
from inventory i, timelog l
where i.user_no = l.user_no
and l.logindate between
to_date('20070101000000', 'yyyymmddhh24miss') and
to_date('20070107235959', 'yyyymmddhh24miss')
and i.itemid = 10002)
and i.itemid = 10001说明:
timelog表中包括user_no和logindate字段,用户每次登录系统都会产生一条记录
inventory表中包括user_no和itemid字段,当用户拥有某种物品时,其中会有一条对应的记录我的问题是:感觉这样查询的效率不高,但是又不知道怎样优化语句,请达人指点一二,谢谢!
from inventory i
where i.user_no in (select i.user_no
from inventory i, timelog l
where l.logindate between
to_date('20070101000000', 'yyyymmddhh24miss') and
to_date('20070107235959', 'yyyymmddhh24miss')
and i.user_no = l.user_no
and i.itemid = 10002)
and i.itemid = 10001再看这样的执行计划,是否i.itemid 上有索引,是否返回了较小的数据集。请楼主把执行计划贴出来
但是从执行计划上看来,两种方式并没有区别
说明一下,timelog表记录很多,大约有数千万条至1亿条记录
inventor的纪录比较少,可能几百万条吧
SORT GROUP BY Cardinality=1 Bytes=26
FILTER
REMOTE Cost=1553 Cardinality=13694 Bytes=356044
NESTED LOOPS Cost=12 Cardinality=5 Bytes=200
TABLE ACCESS BY GLOBAL INDEX ROWID Object owner=TJYH Object name=TIMELOG Cost=7 Cardinality=5 Bytes=70
INDEX RANGE SCAN Object owner=TJYH Object name=IKEY_TIMELOG_CREDATE Cost=4 Cardinality=5
REMOTE Cost=1 Cardinality=1 Bytes=26