OraQA

How to determine the table creation order in SQL

Frank Zhou — Wed, 29 Jul 2009 05:55:52 +0000

The following SQL pattern can be used to determine the table creation order for a set of tables with parent-child relationship.
This SQL solution is built on top of a sql from the AskTom website, it assumes no circular dependency between the tables

create table p    ( x int primary key );
create table c1   ( x primary key references p );
create table c2   ( x primary key references c1 );
create table c3   ( x primary key references c2 );
create table c4   ( x primary key references c2 );
create table p_t ( x int primary key );
create table c_t ( x primary key references p_t );
create table c_t2 ( x primary key references c_t );
create table c_t3 ( x primary key references c_t2 );
create table c_t4 ( x primary key references c_t3 );
create table c_t5 ( x primary key references c_t4 );
create table m3   (x int, y int, z int );
ALTER TABLE m3 ADD (CONSTRAINT x unique (x) );
ALTER TABLE m3 ADD (CONSTRAINT y unique (y) );
ALTER TABLE m3 ADD (CONSTRAINT z unique (z) );
ALTER TABLE m3 ADD (CONSTRAINT cx FOREIGN KEY (x) REFERENCES c_t5 (x));
ALTER TABLE m3 ADD (CONSTRAINT cy FOREIGN KEY (y) REFERENCES c3 (x));
ALTER TABLE m3 ADD (CONSTRAINT cz FOREIGN KEY (z) REFERENCES c4(x));

variable input varchar2(1028) exec :input := 'P,C1,C2,C3,C4,M3,P_T,C_T,C_T2,C_T3,C_T4,C_T5'

————————————–SQL Solution—————————————–

With table_list AS
(
select doc.extract('/X/text( )').getStringVal() as t_name
from
TABLE(xmlSequence(extract(XMLType(''||
      REGEXP_REPLACE(:input, ',', '')||''),'/DOC/X'))) doc
)
select table_name, row_number( ) over (order by le) table_creation_order
from
(select distinct table_name, le
 from
 (select le, max(le) over (partition by table_name) max_level, table_name
  from
  (select table_name, level le
   from
   (select table_name, constraint_name pkey,
           to_char(null) fkey, to_char(null) r_con
    from   user_constraints
    where  constraint_type in ('U', 'P')
    and    owner = USER
    and table_name in (select  t_name from table_list)
    union all
    select a.table_name,
           a.constraint_name pkey,
           b.constraint_name fkey,
           b.r_constraint_name r_con
    from user_constraints a, user_constraints b
    where a.table_name = b.table_name
      and a.constraint_type in ('U', 'P')
      and b.constraint_type = 'R'
      and a.owner = USER
      and b.owner = a.owner
      and a.table_name in (select  t_name from table_list)
  )
  start with fkey is null
  connect by nocycle prior pkey = r_con
  order SIBLINGS by table_name DESC
  )
  )
  where le = max_level
);

TABLE_NAME TABLE_CREATION_ORDER

—————————— ——————–

P 1

P_T 2

C1 3

C_T 4

C2 5

C_T2 6

C_T3 7

C3 8

C4 9

C_T4 10

C_T5 11

M3 12

12 rows selected.

How to solve the Largest 7-Digit Number Puzzle in SQL

Frank Zhou — Mon, 19 Jan 2009 00:07:31 +0000

The following is an interesting problem posted by mathforum.org:

Work out the largest 7-digit number you can applying 2 rules only:

1) every digit in the number must be able to be divided into the number;
2) no digit can be repeated.

——————————-SQL Solution ——————–

SELECT  str_num
FROM
(SELECT str_num
  FROM
  (SELECT str_num, to_number(substr(str_num,LEVEL,1)) n1
   FROM
   (SELECT to_number(utl_raw.cast_to_varchar2(utl_raw.reverse(utl_raw.cast_to_raw(
           replace(sys_connect_by_path(n,','),',')))))  str_num
    FROM (SELECT LEVEL n FROM dual CONNECT BY LEVEL <10)
    WHERE LEVEL = 7
    CONNECT BY NOCYCLE PRIOR n != n
    AND LEVEL < 8
    AND CASE LEVEL
        WHEN 2
	THEN CASE WHEN n in (2, 6, 8)
	          THEN CASE WHEN MOD(CONNECT_BY_ROOT(n), 2) = 0 THEN 1 END
		  WHEN n in (4)
		  THEN CASE WHEN MOD(to_number(PRIOR N||N), n) = 0 THEN 1 END
		  WHEN n = 5
		  THEN CASE WHEN PRIOR N in (5) THEN 1 END
		  ELSE 1 END
	WHEN 3
	THEN CASE WHEN n in (2, 6)
		  THEN CASE WHEN MOD(CONNECT_BY_ROOT(n), 2) = 0 THEN 1 END
		  WHEN n in (4)
		  THEN CASE WHEN MOD(to_number(PRIOR N||N), n) = 0 THEN 1 END
		  WHEN n in (8)
		  THEN CASE WHEN MOD(to_number(CONNECT_BY_ROOT(n)||PRIOR N||N),n)= 0
			    THEN 1 END
	          WHEN n = 5
		  THEN CASE WHEN CONNECT_BY_ROOT(n) in (5) THEN 1 END
		  ELSE 1 END
        WHEN 4
        THEN CASE WHEN n in (2, 4, 6 ,8)
		  THEN CASE WHEN MOD(CONNECT_BY_ROOT(n), 2) = 0 THEN 1 END
		  WHEN n = 5
                  THEN CASE WHEN CONNECT_BY_ROOT(n) in (5) THEN 1 END
		  ELSE 1 END
	WHEN 5
        THEN CASE WHEN n in (2, 4, 6 ,8)
		  THEN CASE WHEN MOD(CONNECT_BY_ROOT(n), 2) = 0 THEN 1 END
		  WHEN n = 5
	          THEN CASE WHEN CONNECT_BY_ROOT(n) in (5 ) THEN 1 END
		  ELSE 1 END
	WHEN 6
        THEN CASE WHEN n in (2, 4, 6 ,8)
		  THEN CASE WHEN MOD(CONNECT_BY_ROOT(n), 2) = 0 THEN 1 END
		  WHEN n = 5
                  THEN CASE WHEN CONNECT_BY_ROOT(n) in (5) THEN 1 END
		  ELSE 1 END
        WHEN 7
        THEN CASE WHEN n in (2, 4, 6 ,8)
		  THEN CASE WHEN MOD(CONNECT_BY_ROOT(n), 2) = 0 THEN 1 END
		  WHEN n = 5
		  THEN CASE WHEN CONNECT_BY_ROOT(n) in (5) THEN 1 END
		  ELSE 1 END
        ELSE 1 END = 1
      )
      CONNECT BY PRIOR str_num = str_num
      AND LEVEL <= length(str_num)
      AND PRIOR DBMS_RANDOM.STRING ('P',20) IS NOT NULL
   )
   GROUP BY str_num
   HAVING count(CASE WHEN mod(str_num, n1) = 0
                     THEN 1 END ) = length(str_num)
   ORDER BY str_num DESC
)
WHERE ROWNUM  = 1;

STR_NUM
———-
9867312

How to solve the Find Maximum Possible Product Puzzle in SQL

Frank Zhou — Fri, 19 Dec 2008 05:40:16 +0000

The following is an interesting problem posted by mathforum.org:

Use all the digits from 1 to 9 without repeating, to form two numbers such that their product is maximum. A digit used should be unique across both the numbers. For example, the numbers formed could be 1234 and 56789.

—————————–SQL Solution—————————

SELECT n1, n2, product as MAX_PRODUCT
FROM
(SELECT max(n1 * n2 ) over ( ) max_product, n1, n2, n1 * n2 product
 FROM
 (
  WITH DATA AS
  (SELECT replace(sys_connect_by_path(num, ','), ',') str_num
   FROM (SELECT LEVEL num FROM dual CONNECT BY LEVEL <10)
   WHERE LEVEL = 9
   CONNECT BY NOCYCLE PRIOR num != num
   AND LEVEL <= 9
   AND CONNECT_BY_ROOT(num) = 9
  )
  SELECT to_number(substr(str_num,0,LEVEL)) n1, to_number(substr(str_num,LEVEL+1)) n2
  FROM DATA
  CONNECT BY PRIOR str_num = str_num
  AND LEVEL <= length(str_num)-1
  AND PRIOR DBMS_RANDOM.STRING ('P',20) IS NOT NULL
 )
)
WHERE max_product = product;

        N1                  N2                   MAX_PRODUCT
----------               ----------                -----------
      9642                87531                      843973902

How to solve the Find all 3 digit numbers have the digital sum of nine Puzzle in SQL

Frank Zhou — Fri, 19 Dec 2008 05:37:54 +0000

The following is an interesting problem posted by mathforum.org:

Find out how many 3 digit numbers have the digital sum of nine?

————————SQL Solution————————-

SELECT to_number(str_num) as str_num,
       CASE WHEN ROWNUM = 1
	    THEN count(*) over ( )
       END AS CNT
FROM
(SELECT replace(sys_connect_by_path(num, ','), ',') str_num
 FROM (SELECT LEVEL - 1 num FROM dual CONNECT BY LEVEL <=10)
 WHERE LEVEL = 3
 CONNECT BY LEVEL < 4
 AND CASE LEVEL
          WHEN 2
          THEN CASE WHEN (PRIOR num != 0) AND (PRIOR num + num <= 9)
		    THEN 1 END
	  WHEN 3
	  THEN CASE WHEN CONNECT_BY_ROOT(num) + PRIOR num + num = 9
		    THEN 1 END
	  ELSE 1  END = 1
 AND PRIOR dbms_random.string('p', 20) IS NOT NULL
);

STR_NUM CNT
———- ———-
108 45
117
126
135
144
153
162
171
180
207
216
225
234
243
252
261
270
306
315
324
333
342
351
360
405
414
423
432
441
450
504
513
522
531
540
603
612
621
630
702
711
720
801
810
900

45 rows selected.

SQL>

Is it best to use SQL or PL/SQL?

Laurent Schneider — Mon, 10 Nov 2008 06:11:40 +0000

The issue is very often related to context switching. A context switch is when you write plsql code that contains sql statement, or sql that contains plsql functions.

select emp,dbms_random.value from emp;

implies a context switch

begin update emp set sal=0; end;

implies a context switch too.

However, it is sometimes better practice to have a few context switches rather than a way too much complex SQL query, that nobody will ever be able to maintain but you.

Ok, let’s try to print the equation abc+def=ghi where abcdefghi are digits from 1 to 9 with no duplicate.

SQL> create or replace function f return sys.odcivarchar2list pipelined deterministic is
  2    a7 number;
  3    a8 number;
  4    a9 number;
  5  begin
  6    -- How to solve the 3 Digits Plus 3 Digits Puzzle
  7    -- where a1a2a3 + a4a5a6 = a7a8a9 and each a is an unique digit from 1 to 9
  8    for a1 in 1..9 loop
  9      for a2 in 1..9 loop
 10        if a2 not in (a1) then
 11          for a3 in 1..9 loop
 12            if a3 not in (a1,a2)
 13            then
 14              for a4 in 1..9 loop
 15                if a4 not in (a1,a2,a3) and a1+a4<10
 16                then
 17                  for a5 in 1..9 loop
 18                    if a5 not in (a1,a2,a3,a4) and a1*10+a2+a4*10+a5<100
 19                    then
 20                      for a6 in 1..9 loop
 21                        if a6 not in (a1,a2,a3,a4,a5) and a1*100+a2*10+a3+a4*100+a5*10+a6<1000
 22                        then
 23                          a9:=a3+a6;
 24                          a8:=trunc(a9/10);
 25                          a9:=a9-a8*10;
 26                          if a9 not in (a1,a2,a3,a4,a5,a6,0)
 27                          then
 28                            a8:=a8+a2+a5;
 29                            a7:=trunc(a8/10);
 30                            a8:=a8-a7*10;
 31                            if a8 not in (a1,a2,a3,a4,a5,a6,a9,0)
 32                            then
 33                              a7:=a7+a1+a4;
 34                              if a7 not in (a1,a2,a3,a4,a5,a6,a8,a9,0)
 35                              then
 36                                pipe row(a1||a2||a3||'+'||a4||a5||a6||'='||a7||a8||a9);
 37                              end if;
 38                            end if;
 39                          end if;
 40                        end if;
 41                      end loop;
 42                    end if;
 43                  end loop;
 44                end if;
 45              end loop;
 46            end if;
 47          end loop;
 48        end if;
 49      end loop;
 50    end loop;
 51  end;
 52  /

Function created.

SQL>
SQL> set timi on
SQL> select column_value as Equation_str, decode(rownum,1,count(*) over()) cnt from table(f);
124+659=783                 336
125+739=864
...
784+152=936

336 rows selected.

Elapsed: 00:00:00.96
SQL>
SQL> SELECT num1||' + '||num2||' = '|| num3 as Equation_str,
  2         CASE WHEN lag(cnt) over (order by num1)  cnt
  3                OR lag(cnt) over (order by num1) IS NULL
  4       THEN  cnt END AS Counter
  5  FROM
  6  (SELECT num1, num2, num3, count(*) over ( ) as cnt
  7   FROM
  8   (SELECT substr(num,1,3) num1, substr(num,4,3) num2, substr(num,7,3) num3
  9    FROM
 10    (SELECT replace(sys_connect_by_path(n,','), ',') num
 11     FROM (SELECT LEVEL n FROM dual CONNECT BY LEVEL <=9)
 12     WHERE LEVEL = 9
 13     CONNECT BY NOCYCLE PRIOR n != n
 14     AND LEVEL <=9
 15     AND CASE LEVEL
 16              WHEN 1
 17              THEN CASE WHEN n < 9 THEN 1 END
 18              WHEN 4
 19              THEN CASE WHEN n  + CONNECT_BY_ROOT(n) = 3 THEN 1 END
 23                        ELSE CASE WHEN n >= CONNECT_BY_ROOT(n) + 1 THEN 1 END
 24                   END
 25             ELSE 1 END = 1
 26     )
 27    )
 28   WHERE to_number(num1)     + to_number(num2)       =  to_number(num3)
 29   );
124 + 659 = 783             336
125 + 739 = 864
...
784 + 152 = 936

336 rows selected.

Elapsed: 00:00:22.91

hmm, do not you just love select * from table(f); ? Just a sinister scheme to seduce you.

How to solve the 3 Digits Plus 3 Digits Puzzle in SQL

Frank Zhou — Mon, 03 Nov 2008 04:06:32 +0000

The following is an interesting problem posted by mathforum.org:

Use the digits 1 to 9 only once in a sum that must be a three-digit number plus another three-digit number to equal another three-digit number. Each digit can only be used once, but all must be used.

———————————-SQL Solution——————————-

SELECT num1||' + '||num2||' = '|| num3 as Equation_str,
       CASE WHEN lag(cnt) over (order by num1) <> cnt
              OR lag(cnt) over (order by num1) IS NULL
     THEN  cnt END AS Counter
FROM
(SELECT num1, num2, num3, count(*) over ( ) as cnt
 FROM
 (SELECT substr(num,1,3) num1, substr(num,4,3) num2, substr(num,7,3) num3
  FROM
  (SELECT replace(sys_connect_by_path(n,','), ',') num
   FROM (SELECT LEVEL n FROM dual CONNECT BY LEVEL <=9)
   WHERE LEVEL = 9
   CONNECT BY NOCYCLE PRIOR n != n
   AND LEVEL <=9
   AND CASE LEVEL
            WHEN 1
            THEN CASE WHEN n < 9 THEN 1 END
            WHEN 4
            THEN CASE WHEN n  + CONNECT_BY_ROOT(n) < 10 THEN 1 END
            WHEN 7
            THEN CASE WHEN CONNECT_BY_ROOT(n) =1
                      THEN CASE WHEN n >= 3 THEN 1 END
                      ELSE CASE WHEN n >= CONNECT_BY_ROOT(n) + 1 THEN 1 END
                 END
           ELSE 1 END = 1
   )
  )
 WHERE to_number(num1)  + to_number(num2)  =  to_number(num3)
 );

EQUATION_STR                                               COUNTER
------------------------------------------                  ----------
124 + 659 = 783                                              336
125 + 739 = 864
127 + 359 = 486
127 + 368 = 495
128 + 367 = 495
128 + 439 = 567
129 + 357 = 486
129 + 735 = 864
129 + 654 = 783
129 + 438 = 567
134 + 658 = 792
.....................................................
.....................................................
.....................................................
.....................................................
.....................................................

762 + 183 = 945
763 + 182 = 945
782 + 154 = 936
782 + 163 = 945
783 + 162 = 945
784 + 152 = 936

336 rows selected.

How to solve the Closest Palindromic Dates Puzzle in SQL

Frank Zhou — Mon, 03 Nov 2008 04:01:48 +0000

The following is an interesting problem posted by mathforum.org:

Using the abbreviation date.month.year (the last two digits of the year), what are the 2 palindromic dates (of any number of digits) closest together in the 1900s?

———————————-SQL Solution——————————-

SELECT date_str, next_date, min_diff_days
FROM
(SELECT min(diff_days) over ( ) min_diff_days, date_str, next_date, diff_days
 FROM
 (SELECT lead(date_str) over (order by pal_date) next_date, date_str,
         lead(pal_date) over (order by pal_date) - pal_date  as diff_days
  FROM
  (SELECT to_char(to_date('01/01/1900','DD/MM/YYYY')-1+ LEVEL,'fmDD.MM.fmYY') date_str,
          to_date('01/01/1900','DD/MM/YYYY')-1  + LEVEL pal_date
   FROM dual
   CONNECT BY to_date('01/01/1900','MM/DD/YYYY')+LEVEL <= to_date('01/01/2000','MM/DD/YYYY')
  )
  WHERE replace(date_str,'.') =
       utl_raw.cast_to_varchar2(utl_raw.reverse(utl_raw.cast_to_raw((replace(date_str,'.')))))
 )
)
WHERE diff_days = min_diff_days;

DATE_STR     NEXT_DAT     MIN_DIFF_DAYS
——–                    ——–        ————-
29.8.92         2.9.92                 4

Elapsed: 00:00:00.41

How to solve the 1000 $1 Bills in 10 Envelopes Puzzle in SQL

Frank Zhou — Mon, 13 Oct 2008 01:44:30 +0000

The following is an interesting problem posted by mathforum.org:

You are given 1000 one dollar bills and 10 envelopes. Put the bills
into the envelopes in such a way that someone can ask you for any
amount of money from $1 to $1000 (examples $532, $619, $88, etc.)
and you can give it to them through a combination of the envelopes.

The following SQL pattern can be used to solve this puzzle, it also proves
that the result is correct.

—————————–SQL Solution———————–

SELECT   ROW_NUMBER () OVER (ORDER BY num) envelope_num,
         num dollars,
         MIN from_min,
         MAX to_max,
         cnt combo_cnt
    FROM (WITH DATA AS
               (SELECT num
                  FROM (SELECT CASE
                                  WHEN SUM (num) OVER (ORDER BY num) > 1000
                                     THEN   num
                                          - (  SUM (num) OVER (ORDER BY num)
                                             - 1000
                                            )
                                  ELSE num
                               END AS num
                          FROM (SELECT     POWER (2, LEVEL - 1) AS num
                                      FROM DUAL
                                CONNECT BY POWER (2, LEVEL - 1) <= 1000))),
               data_check AS
               (SELECT MAX (num) MAX,
                       MIN (num) MIN,
                       COUNT (*) cnt
                  FROM (SELECT DISTINCT SUM
                                           (TO_NUMBER (REGEXP_SUBSTR (str,
                                                                      '[^,]+',
                                                                      1,
                                                                      LEVEL
                                                                     )
                                                      )
                                           ) num
                                   FROM (SELECT     SYS_CONNECT_BY_PATH (num,
                                                                         ','
                                                                        ) str
                                               FROM (SELECT num
                                                       FROM DATA)
                                         CONNECT BY PRIOR num < num)
                             CONNECT BY PRIOR str = str
                                    AND REGEXP_SUBSTR (str, '[^,]+', 1, LEVEL) IS NOT NULL
                                    AND PRIOR DBMS_RANDOM.STRING ('p', 20) IS NOT NULL
                               GROUP BY str))
          SELECT MAX,
                 MIN,
                 cnt,
                 num
            FROM data_check FULL OUTER JOIN DATA ON (data_check.MIN = DATA.num
                                                    )
                 )
ORDER BY num NULLS FIRST;

SQL> /

ENVELOPE_NUM DOLLARS FROM_MIN TO_MAX COMBO_CNT ------------ ---------- ---------- ---------- ---------- 1 1 1 1000 1000 2 2 3 4 4 8 5 16 6 32 7 64 8 128 9 256 10 489

10 rows selected.

Elapsed: 00:00:00.43

How to solve the Find the Seven Numbers Puzzle in SQL

Frank Zhou — Mon, 13 Oct 2008 01:43:08 +0000

The following is an interesting problem posted by mathforum.org:

There are seven numbers, A, B, C, D, E, F, and G.

When all these numbers except A are added together, 116 is obtained.
When all these numbers except B are added together, 122 is obtained
When all these numbers except C are added together, 123 is obtained
When all these numbers except D are added together, 108 is obtained
When all these numbers except E are added together, 110 is obtained
When all these numbers except F are added together, 119 is obtained
When all these numbers except G are added together, 100 is obtained

Find each of the seven numbers.

COLUMN letter FORMAT A6

—————————–SQL Solution———————————-


SELECT   TRANSLATE (new_str, CHR (0) || str, CHR (0)) letter,
         AVG - num AS num
    FROM (WITH DATA AS
               (SELECT TRANSLATE (old_str, CHR (0) || mis_char, CHR (0))
                                                                       AS str,
                       num
                  FROM (SELECT 'A' AS mis_char,
                               116 AS num
                          FROM DUAL
                        UNION ALL
                        SELECT 'B' AS mis_char,
                               122 AS num
                          FROM DUAL
                        UNION ALL
                        SELECT 'C' AS mis_char,
                               123 AS num
                          FROM DUAL
                        UNION ALL
                        SELECT 'D' AS mis_char,
                               108 AS num
                          FROM DUAL
                        UNION ALL
                        SELECT 'E' AS mis_char,
                               110 AS num
                          FROM DUAL
                        UNION ALL
                        SELECT 'F' AS mis_char,
                               119 AS num
                          FROM DUAL
                        UNION ALL
                        SELECT 'G' AS mis_char,
                               100 AS num
                          FROM DUAL),
                       (SELECT 'ABCDEFG' old_str
                          FROM DUAL))
          SELECT str,
                 num,
                 new_str,
                 AVG
            FROM (SELECT str,
                         num
                    FROM DATA),
                 (SELECT XMLAGG (XMLELEMENT (x, str) ORDER BY NULL).EXTRACT
                                                                  ('/X/text()').getstringval
                                                                           ()
                                                                   AS new_str,
                         MAX (AVG) AVG
                    FROM (SELECT   str,
                                   MAX (num) / COUNT (*) AS AVG
                              FROM (SELECT     SUBSTR (str, LEVEL, 1) str,
                                               num
                                          FROM (SELECT XMLAGG
                                                          (XMLELEMENT (x, str) ORDER BY NULL
                                                          ).EXTRACT
                                                                  ('/X/text()').getstringval
                                                                           ()
                                                                          str,
                                                       SUM (num) AS num
                                                  FROM (SELECT str,
                                                               num
                                                          FROM DATA))
                                    CONNECT BY PRIOR str = str
                                           AND LEVEL <= LENGTH (str)
                                           AND PRIOR DBMS_RANDOM.STRING ('P',
                                                                         20
                                                                        ) IS NOT NULL)
                          GROUP BY str)))
ORDER BY letter;

LETTER        NUM
------ ----------
A              17
B              11
C              10
D              25
E              23
F              14
G              33

7 rows selected.

Elapsed: 00:00:00.31

How to solve the Multiply Two Numbers (No Zeros) to make 5 Billion Puzzle in SQL

Frank Zhou — Sat, 27 Sep 2008 05:42:37 +0000

The following is an interesting problem posted by mathforum.org:

What two numbers – neither of them containing zeros – can be multiplied together to make 5,000,000,000?

The following SQL puzzle solution is built on top of a neat sql trick that I have learned from the SQL_RU Forum.

variable input number
exec :input := 5000000000 ;
———————————SQL Solution —————————–

SELECT str_eq || ' = ' || XMLQuery( str_eq RETURNING CONTENT).getnumberval()  as output
  FROM (SELECT RTRIM
                  (LTRIM
                      (REGEXP_REPLACE
                            (XMLAGG (XMLELEMENT (x, CAST (num AS INTEGER)) ORDER BY num
                                    ),
                             '||',
                             ' * '
                            ),
                       ' * '
                      ),
                   ' * '
                  ) AS str_eq
          FROM (SELECT DISTINCT EXP (SUM (LN (num)) OVER (PARTITION BY num))
                                                                          num
                           FROM (WITH data1 AS
                                      (SELECT     LEVEL l
                                             FROM DUAL
                                       CONNECT BY LEVEL <= SQRT (:input)),
                                      data2 AS
                                      (SELECT l num1,
                                              :input / l num2
                                         FROM data1
                                        WHERE MOD (:input, l) = 0),
                                      data3 AS
                                      (SELECT num1
                                         FROM data2
                                       UNION
                                       SELECT num2 AS num1
                                         FROM data2),
                                      data4 AS
                                      (SELECT num1,
                                              (SELECT MIN (num1)
                                                 FROM data3 data3_1
                                                WHERE data3_1.num1
                                                      / data3.num1 =
                                                         TRUNC (  data3_1.num1
                                                                / data3.num1
                                                               )
                                                  AND data3_1.num1 >
                                                                    data3.num1)
                                                                     data_num
                                         FROM data3)
                                 SELECT     data_num / num1 AS num
                                       FROM data4
                                      WHERE data_num IS NOT NULL
                                 START WITH num1 = 1
                                 CONNECT BY num1 = PRIOR data_num)));

 
OUTPUT
--------------------------------------------------------------------------------
512 * 9765625 = 5000000000

Elapsed: 00:00:01.32