Bhushan Bhushan - 11 months ago 70
SQL Question

Dynamic parameter for NTILE

I have table which will have row count between 25k to 250k. Now I need to divide this whole table in chunks for processing using

NTILE
. If I will give fixed count as
NTILE(4)
, It works fine for me. Do we have any way to pass parameter dynamically?

I want to maintain individual batch size to 25k. So if table has 40k rows parameter value should be 2, If its 150k, it should be 6 and so on...

Kindly suggest how to achieve this.

Answer Source

From your description it is not clear why you want to pass the parameter dynamically, and not have it as a static expression, perhaps something like

EDITED: initially I had the count in a subquery directly in the argument to NTILE(), but that doesn't work - not supported by the Oracle implementation.

This should work - tested on the EMP table in the SCOTT schema:

select empno, ename,
       ntile ( ceil(ct/4) ) over (partition by ct order by empno) as n_tile
from emp cross join ( select count(*) as ct from emp );

     EMPNO ENAME          N_TILE
---------- ---------- ----------
      7369 SMITH               1
      7499 ALLEN               1
      7521 WARD                1
      7566 JONES               1
      7654 MARTIN              2
      7698 BLAKE               2
      7782 CLARK               2
      7788 SCOTT               2
      7839 KING                3
      7844 TURNER              3
      7876 ADAMS               3
      7900 JAMES               4
      7902 FORD                4
      7934 MILLER              4

 14 rows selected 

So, the count of rows in the base table needs to be done separately, and then use a cross join (which is fine, because one of the tables has just one row).

So:

  select .....  ,  ntile ( ceil(ct/25000) ) over (partition by ct order by ..... ) 
  ....
  from <base_table> CROSS JOIN (select count(*) as ct from <base_table>)