sql - Sampling unique set of records in Oracle table -

sql - Sampling unique set of records in Oracle table -

i have oracle table need select given percentage of records each type of given set of unique column combination.

for example,

select distinct column1, column2, column3 tablex;

provides me combination of unique records table. need % of each rows each such combination. using next query accomplish this, lengthy , slow.

select * tablex sample ( 3 ) column1 = ‘value1’ , column2 = ‘value2’ , column3 = ‘value3 union select * tablex sample ( 3 ) column1 = ‘value1’ , column2 = ‘value2’ , column3 = ‘value4 union … … select * tablex sample ( 3 ) column1 = ‘valuep’ , column2 = ‘valueq’ , column3 = ‘valuer’

where combination of suffix in “value” unique table (obtained first query)

how can improve length of query , speed?

here 1 approach:

select t.* (select t.*, row_number() on (partition column1, column2, column3 order dbms_random() ) seqnum, count(*) on (partition column1, column2, column3) totcnt tablex t ) t seqnum / totcnt <= 0.10 -- or whatever threshold

it uses row_number() assign sequential number rows in each group, in random order. where clause chooses proportion want.

sql oracle11g query-performance