CODEWITHSUNDEEP
sqloracle-databaseperformance
saikumarm
saikumarm

Reputation: 1575

Generate numbers between min_value and max_value oracle

I have two methods for solution, but both are high inefficient to work with value of the order 108 and greater.

Method 1

 select 100 + rownum - 1 from dual connect by level <= (200 - 100 + 1)

Method 2

select rownum + 100 - 1
from (select 1 from dual group by cube(1, 2, 3, 4, 5, 6, 7, 8, 9))
where rownum < (200 - 100 + 1)

but first method fails when the max value is 100,000,000 and second method takes lot of time to process.

please suggest an efficient method, I could think of sequences but I suppose the time cost will be higher.

update

Error in First method

ORA-30009: Not enough memory for CONNECT BY operation

Upvotes: 3

Views: 558

Answers (3)

Arkadiusz Łukasiewicz
Arkadiusz Łukasiewicz

Reputation: 6356

Variation of your first method.

select  rownum + min_val
    from (
               select rownum rn
               from
                       (select rownum rn from dual connect by level <= 1000) t1,
                       (select rownum rn from dual connect by level <= 1000) t2,
                       (select rownum rn from dual connect by level <= 1000) t3
               where rownum <= max_val- min_val +1
       )

Upvotes: 0

MT0
MT0

Reputation: 168361

Some other options:

Option 1 - Generate a collection:

CREATE TYPE intlist IS TABLE OF NUMBER(10,0);
/

CREATE FUNCTION list_between(
  min_value NUMBER,
  max_value NUMBER
) RETURN intlist DETERMINISTIC
AS
  o_lst intlist := intlist();
BEGIN
  IF ( min_value <= max_value ) THEN
    o_lst.EXTEND( max_value - min_value + 1 );
    FOR i IN 0 .. max_value - min_value LOOP
      o_lst( i ) := i + min_value;
    END LOOP;
  END IF;
  RETURN o_lst;
END;
/

SELECT COLUMN_VALUE
FROM   TABLE( list_between( 123456789, 987654321 ) );

Option 2 - Use a recursive sub-query factoring clause:

WITH sqfc ( min_value, max_value ) AS (
  SELECT 123456789, 987654321 FROM DUAL
  UNION ALL
  SELECT min_value + 1, max_value
  FROM   sqfc
  WHERE  min_value < max_value
)
SELECT min_value AS value
FROM   sqfc;

Option 3 - Use a pipelined function:

CREATE FUNCTION pipelined_list_between (
  min_value NUMBER,
  max_value NUMBER
) RETURN intlist DETERMINISTIC PIPELINED
AS
BEGIN
  FOR i IN min_value .. max_value LOOP
    PIPE ROW ( i );
  END LOOP;
END;
/

SELECT COLUMN_VALUE
FROM   TABLE( pipelined_list_between( 123456789, 987654321 ) );

Upvotes: 2

user330315
user330315

Reputation:

For that many rows a pipelined function would probably the best solution:

create or replace TYPE t_numbers IS TABLE OF NUMBER;
/

create or replace function generate_series(p_min integer, p_max integer)
  return t_numbers pipelined
as  
begin
  for i in p_min..p_max loop
    pipe row (i);
  end loop;
end;
/

A simple:

select count(*)
from table(generate_series(1,100000000));

This takes about 30 seconds on my laptop

Upvotes: 7

Related Questions