Oracle: Need to fetch the rows exponentially

Question

I got below query from another post which selects 100 rows from every 2000 rows. Like this: 1-100,2001-2100,4001-4100,6001-6100,8001-8100 and so on.

SELECT * FROM (SELECT t.*,ROWNUM AS rn FROM(SELECT * FROM   your_table ORDER BY your_condition) t)WHERE MOD( rn - 1, 2000 ) < 100;

Now I want to select my data exponentially.Such that it will select 100 rows from first 1000 rows, then from next 2000 rows, then from next 4000 rows. Like this: 1-100,2000-2100,4000-4100,8000-8100,16000-16100 and so on. The idea is to scan rows with a specific pattern.

your "before" and "after" outputs are the same. Are you saying you want to paginate so it would be 100-200, 2100-2200, etc? Also, why the strange selection? — Rogue, May 10 '17 at 10:35
@Rogue the before and after outputs are different. In the first case, it is always 2000 plus next rows. In the second case, I want to scan 2k,4k,8k(2 raised to power) — Raj, May 10 '17 at 10:52
Well in addition to modulo, you'd check if it was a "power of 2" when divided by 1000 as an integer. Here's a nice hint if you want to try the bitwise route: powers of 2 have a pattern in binary: `10`, `100`, `1000`, `10000` (2, 4, 8, 16). You can `AND` whatever check you have with the modulo check, as I would assume it is faster (bitwise converts data types to `BIGINT` when calculating **edit**: that's for mysql, can't speak for oracle). — Rogue, May 10 '17 at 10:59

Gordon Linoff · Answer 1 · 2017-05-10T13:48:03.393

0

From what you describe, you can use logs to define the groups. This is probably close enough to what you want:

select t.*
from (select t.*,
             row_number() over (floor(log(2, floor(1 + (seqnum - 1) / 1000) ))
                                order by col
                               ) as seqnum_2
      from (select t.*, row_number() over (order by col) as seqnum
            from t
           ) t
where seqnum_2 <= 100;

The difference from your description is that the first group is 1-999, 1000-1999, and so on.

edited May 10 '17 at 13:48

answered May 10 '17 at 10:51

Gordon Linoff

1,242,037
58
646
786

`LOG( 2, 4 )` outputs `1.99999999999999999999999999999999999998`. You will need to handle this as it will `FLOOR` to `1` when the value should be `2`. – MT0 May 10 '17 at 12:33
Also `FLOOR( LOG( 2, 1/1000 ) )` is `-10` and `FLOOR( LOG( 2, 10/1000 ) )` is `-7` so this will not put the first 999 rows into the same partition. – MT0 May 10 '17 at 12:37

score 0 · Answer 2 · answered May 10 '17 at 11:29

You could use power function and simple hierarchical query, then join it with your table. Here is example with all_objects view:

with rng as (select 0 num from dual union all
             select 1000 * power(2, level) from dual connect by level < 10 )
select * 
  from (select row_number() over (order by object_name) rn, object_name from all_objects)
  join rng on rn between num + 1 and num + 100

score 0 · Accepted Answer · edited May 23 '17 at 11:54

You asked this in a comment on your previous question and I answered there...

SELECT *
FROM   (
  SELECT t.*,
         ROWNUM AS rn            -- Secondly, assign a row number to the ordered rows
  FROM   (
    SELECT *
    FROM   your_table
    ORDER BY your_condition      -- First, order the data
  ) t
)
WHERE rn - POWER(                -- Finally, filter the top 100.
             2,
             TRUNC( CAST( LOG( 2, CEIL( rn / 1000 ) ) AS NUMBER(20,4) ) )
           ) * 1000 + 1000 <= 100

This will take the first 100 rows from the groups 1-1000, 1001-3000, 3001-7000, 7001-15000, etc.

Or, to get the rows:

1-100,2000-2100,4000-4100,8000-8100,16000-16100, 32000-32100 and so on.

Then:

WHERE CASE                -- Finally, filter the top 100.
        WHEN rn <= 2000 THEN rn
        ELSE rn - POWER(
                    2,
                    TRUNC( CAST( LOG( 2, CEIL( rn / 1000 - 1 ) ) AS NUMBER(20,4) ) )
                  ) * 1000
      END <= 100

Thanks. Your answer somewhat solved my problem statement. I am trying to modify the query to match my exact requirement which is like this: Getting rows: 1-100,2000-2100,4000-4100,8000-8100,16000-16100, 32000-32100 and so on. — Raj, May 10 '17 at 13:19
@Raj Updated for your ranges - they do not grow exponentially as the 1st and 2nd ranges are both 2000 rows so you need to make a special case for the first group. — MT0, May 10 '17 at 13:39

Oracle: Need to fetch the rows exponentially

3 Answers3