Oracle how to partition data and get records at every 10%

I have a giant table that has billions of records like this:

ID   |  H  |  N   |  Q  | other
-----+-----+------+-----+--------
AAAA |  0  |  7   |  Y  | ...
BBBB |  1  |  5   |  Y  | ...
CCCC |  0  |  11  |  N  | ...
DDDD |  3  |  123 |  N  | ...
EEEE |  6  |  4   |  Y  | ...

These four columns are part of an index. What I want to do is construct a query that gives me the 1st row, followed by the row at 10%, 20%, 30%, 40%, ..开发者_如何学Python. so that the query will always give me 10 rows regardless of how big the table is (as long as # rows >= 10).

Is this even possible with SQL? If so, how would I do it? What kind of performance characteristics does it have?

One option would be

SELECT id,
       h,
       n,
       q
  FROM (
    SELECT id, 
           h, 
           n, 
           q,
           row_number() over (partition by decile order by id, n) rn
      FROM (
        SELECT id, 
               h, 
               n, 
               q,
               ntile(10) over (order by id, n) decile
          FROM your_table
            )
        )
   WHERE rn = 1

There is probably a more efficient approach using PERCENTILE_DISC or CUME_DIST that isn't clicking for me at the moment. But this should work.

You can use a histogram to get this information. The huge downside is that the results will only be approximate, and it's very difficult to say how approximate they will be. And you'll need to gather table statistics to refresh the results, but you're probably already doing that. On the positive side, the query to get the results will be very fast. And using statistics instead of a query would be so cool.

Here's a quick demo:

--Create a table with the IDs AA - ZZ.
create table test(id varchar2(100), h number, n number, q varchar2(100)
    ,other varchar2(100));

insert into test 
select letter1||letter2 letters, row_number() over (order by letter1||letter2), 1, 1, 1
from
    (select chr(65+level-1) letter1 from dual connect by level <= 26) letters1
    cross join
    (select chr(65+level-1) letter2 from dual connect by level <= 26) letters2
;
commit;

--Gather stats, create a histogram with 11 buckets (we'll only use the first 10)
begin
    dbms_stats.gather_table_stats(user, 'TEST', cascade=>true,
        method_opt=>'FOR ALL COLUMNS SIZE AUTO, FOR COLUMNS SIZE 10 ID');
end;
/

--Getting the values from user_histograms is kinda tricky, especially for varchars.
--There are problems with rounding, so some of the values may not actually exist.
--
--This query is from Jonathan Lewis:
-- http://jonathanlewis.wordpress.com/2010/10/05/frequency-histogram-4/
select
        endpoint_number,
        endpoint_number - nvl(prev_endpoint,0)  frequency,
        hex_val,
        chr(to_number(substr(hex_val, 2,2),'XX')) ||
        chr(to_number(substr(hex_val, 4,2),'XX')) ||
        chr(to_number(substr(hex_val, 6,2),'XX')) ||
        chr(to_number(substr(hex_val, 8,2),'XX')) ||
        chr(to_number(substr(hex_val,10,2),'XX')) ||
        chr(to_number(substr(hex_val,12,2),'XX')),
        endpoint_actual_value
from    (
        select
                endpoint_number,
                lag(endpoint_number,1) over(
                        order by endpoint_number
                )                                                       prev_endpoint,
                to_char(endpoint_value,'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX')hex_val,
                endpoint_actual_value
        from
                user_histograms
        where   table_name = 'TEST'
        and     column_name = 'ID'
        )
where
        endpoint_number < 10
order by
        endpoint_number
;

Here's a comparison of the histogram results with the real results from @Justin Cave's query:

Histogram:    Real results:
A@            AA
CP            CQ
FF            FG
HV            HW
KL            KM
NB            NC
PR            PS
SG            SH
UU            UW
XK            XL