ID IDNAME TYPE
1 q 1
2 w 1
3 e 1
4 r 1
5 t 1
6 y 2
7 u 2
8 i 2
9 o 2
10 p 2
11 a 3
12 s 3
13 d 3
14 f 3
15 g 3
16 h 3 从中随机选择3个TYPE 为1 ,2个TYPE为2,3个TYPE为3 的数据,
结果类似如下所示:
2 w 1
3 e 1
4 r 1
9 o 2
7 u 2
13 d 3
14 f 3
15 g 3 类似这种情况, 数据每天高达数千万, select * from
(
select * from T_20081028 where TYPE=1
order by dbms_random.value
)
where rownum <= 3
union all
select * from
(
select * from T_20081028 where TYPE=2
order by dbms_random.value
)
where rownum <= 2
union all
select * from
(
select * from T_20081028 where TYPE=3
order by dbms_random.value
)
where rownum <= 3;使用如上语句进行查询, 非常慢,不知道有没有快速查询的方法呢??请大家多多赐教,谢谢!
1 q 1
2 w 1
3 e 1
4 r 1
5 t 1
6 y 2
7 u 2
8 i 2
9 o 2
10 p 2
11 a 3
12 s 3
13 d 3
14 f 3
15 g 3
16 h 3 从中随机选择3个TYPE 为1 ,2个TYPE为2,3个TYPE为3 的数据,
结果类似如下所示:
2 w 1
3 e 1
4 r 1
9 o 2
7 u 2
13 d 3
14 f 3
15 g 3 类似这种情况, 数据每天高达数千万, select * from
(
select * from T_20081028 where TYPE=1
order by dbms_random.value
)
where rownum <= 3
union all
select * from
(
select * from T_20081028 where TYPE=2
order by dbms_random.value
)
where rownum <= 2
union all
select * from
(
select * from T_20081028 where TYPE=3
order by dbms_random.value
)
where rownum <= 3;使用如上语句进行查询, 非常慢,不知道有没有快速查询的方法呢??请大家多多赐教,谢谢!
SELECT *
FROM (SELECT S.*,
ROW_NUMBER() OVER(PARTITION BY TYPE ORDER BY DBMS_RANDOM.RANDOM)
FROM T_20081028 S
WHERE ROWID IN (SELECT ROWID
FROM (SELECT ROWID,
ROW_NUMBER() OVER(PARTITION BY TYPE ORDER BY DBMS_RANDOM.RANDOM) RN
FROM T_20081028
WHERE TYPE IN (1, 2, 3))
WHERE RN <= 3))
WHERE TYPE IN (1, 3)
AND RN <= 3
OR TYPE = 2
AND RN <= 2;
(
select * from rtype sample(10) where TYPE=1
order by dbms_random.value
)
where rownum <= 3
union all
select * from
(
select * from rtype sample(10) where TYPE=2
order by dbms_random.value
)
where rownum <= 2
union all
select * from
(
select * from rtype sample(10) where TYPE=3
order by dbms_random.value
)
where rownum <= 3;
select * from
(
select * from rtype sample(10) where TYPE=1
--order by dbms_random.value
)
where rownum <= 3
union all
select * from
(
select * from rtype sample(10) where TYPE=2
--order by dbms_random.value
)
where rownum <= 2
union all
select * from
(
select * from rtype sample(10) where TYPE=3
--order by dbms_random.value
)
where rownum <= 3;
你又没试过,我试了,快很多。 可以把sample(10) 改成 sample(5),更快
如果对于多个表联合查询,怎么使用sample()例如, select * from table1, table2 where table1.TYPE=1, and table1.TYPE = table2.TYPE
sample只对单表生效,不能用于表连接和远程表
对于多表查询用可以先用create tabel as select * from table_name1,table_2 where ......
然后在用sample
SELECT *
FROM (
select emp.*
from emp, dept
where emp.deptno = dept.deptno
) sample(10)
--下面的用with,就可以了 。with也相当于临时表,不过用起来方便
with x as (
select emp.*
from emp, dept
where emp.deptno = dept.deptno
)
select *
from x sample(90)