Bhushan Bhushan - 29 days ago 9
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

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>)
Comments