split tables from an oracle database into batches and get the maximum and minimum values of a column

As I understand your question, you want ntile() with an order by clause:

select num, min(profile_id) min_id, max(profile_id) max_id
from (
    select profile_id, ntile(4) over(order by profile_id) num
    from dba_cdd_review_ds.review_data_store 
) t
group by nt

I removed the other columns from the subquery, as well as the filtering on rownum, that do not fit well with your problem statement.

That said, I am unsure that ntile() is really what you want. You describe a situation where you want to limit the number of rows per bucket to one million. row_number() would make more sense, as this would avoid hardcoding the number of buckets as ntile does:

select 1 + floor(rn / 1000000) num,  min(profile_id) min_id, max(profile_id) max_id
from (
    select profile_id, row_number() over(order by profile_id) rn
    from dba_cdd_review_ds.review_data_store 
) t
group by 1 + floor(rn / 1000000)

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top