[高手求助]，SQL优化，一直困扰着的一个问题,无能为力了

Question

本帖最后由 real_yqdt 于 2009-10-26 12:42:17 编辑

Answer 1

item_id 和 user_id 有索引吧。 
楼主先用EXPLAIN PLAN 分析下SQL 看看. 执行计划是什么，cost 是多少?
补充一点，如果返回的结果集超过记录总数的20% 是不建议用索引的，因为这种情况下用全表扫描更快.

Answer 2

需求不太对
with temp as
(
select 1 ID,           '13500000003' USER_ID,  '601803000000000074' ITEM_ID   from dual
union all
select 2,          '13500000003',   '601803000000000075' from dual
union all 
select 3,          '13500000004',     '601803000000000074' from dual
union all 
select 4,          '13500000004',    '601803000000000075' from dual
union all 
select 5,          '13500000003',        '601803000000000078' from dual
union all 
select 6,          '13500000003',      '601803000000000079' from dual
)
select ITEM_ID, count(user_id) as  出现次数
FROM TEMP
group by item_id--获取不同的ITEM_ID
结果
601803000000000074 2
601803000000000075 2
601803000000000078 1
601803000000000079 1
在做行列转换就是结果了
但是总感觉哪不对劲

Answer 3

with temp as
(
select 1 ID,           '13500000003' USER_ID,  '601803000000000074' ITEM_ID   from dual
union all
select 2,          '13500000003',   '601803000000000075' from dual
union all 
select 3,          '13500000004',     '601803000000000074' from dual
union all 
select 4,          '13500000004',    '601803000000000075' from dual
union all 
select 5,          '13500000003',        '601803000000000078' from dual
union all 
select 6,          '13500000003',      '601803000000000079' from dual
)
SELECT counts,LTRIM(MAX(SYS_CONNECT_BY_PATH(Item_id,',')),',') Item_id
 FROM
 (
 SELECT counts,Item_id,MIN(Item_id) OVER(PARTITION BY counts) Item_id_MIN,
 (ROW_NUMBER() OVER(ORDER BY counts,Item_id))+(DENSE_RANK() OVER (ORDER BY counts)) NUMID
 FROM 
 (select ITEM_ID, count(user_id) as  counts
FROM TEMP
group by item_id) T2
 )
 START WITH Item_id=Item_id_MIN CONNECT BY NUMID-1=PRIOR NUMID
 GROUP BY  counts;
1    601803000000000078,601803000000000079
2    601803000000000074,601803000000000075

Answer 4

查出所有USER_ID相同但ITEM不同的所有记录的一一对应的ITEM_ID 出现的次数?
还是
查出所有USER_ID不同但ITEM相同的所有记录的一一对应的ITEM_ID 出现的次数用"<>"用不上index，试下这个select item_id,max(rank) as count
from
(
select user_id,item_id,rank() over(partition by item_id order by user_id) as rank
from b
)a group by item_id

Answer 5

这种也算，我手写了一下结果，以我写的sql查出来的为准

Answer 6

那如果原始表里面有100W的记录，你的第一种写法甚至可以1亿以上的记录。用<>来做，可以产生太多的记录了。

Answer 7

这个效率应该可以了：
select min(item_id) aid, max(item_id) bid, count(1) cnt
  from T_RATINGS
 group by user_id
 having count(1) > 1但不知道结果是否完全符合

Answer 8

谢谢各位的热心帮助，
首先报歉，我把查询出来的结果有问题，当时只为了理解所以就自已手写了个，实际比这个结果要多很多
结果如下：
ITEM_ID ITEM_ID COUNT(*)
601803000000000000 601803000000005000 1
601803000000000000 601803000000010000 1
601803000000000074  601803000000000075  2
601803000000000074  601803000000000078  1
601803000000000074  601803000000000079  1
601803000000000075  601803000000000074  2
601803000000000075  601803000000000078  1
601803000000000075  601803000000000079  1
601803000000000078  601803000000000074  1
601803000000000078  601803000000000075  1
601803000000000078  601803000000000079  1
601803000000000079  601803000000000074  1
601803000000000079  601803000000000078  1
601803000000000079  601803000000000075  1
601803000000005000 601803000000000000 1
601803000000005000 601803000000010000 1
601803000000010000 601803000000000000 1
601803000000010000 601803000000005000 1
601803000000035000 601803000000040000 1
601803000000035000 601803000000045000 1
601803000000040000 601803000000035000 1
601803000000040000 601803000000045000 1
601803000000045000 601803000000035000 1
601803000000045000 601803000000040000 1经过java3344520的提示，我把改成
insert /*+ append */ into T_TMP_ITEM_OVERLAP
select item_aid, item_bid, count from (
select
 /*+ use_hash( a b) leading(a) */
a.item_id item_aid,
b.item_id item_bid,
count(*) count,
row_number() OVER(PARTITION BY  a.item_id ORDER BY count(*) DESC)  rank
from (select * from t_ratings where ctime >LASTTIME) a,
  t_sys_ratings b
where  a.item_id != b.item_id
  and a.user_id = b.user_id
group by a.item_id, b.item_id 
 ) where rank<=MAXCOUNT;
 
 使12小时跑不完的过程，只需要10几分钟就跑完了
 在这里表示感谢。。