Home » SQL & PL/SQL » SQL & PL/SQL » Oracle - generating combination of numbers
| Oracle - generating combination of numbers [message #480586] |
Mon, 25 October 2010 19:22  |
electriceel
Messages: 30
Registered: August 2010
Location: USA
|
Member |
|
|
I have to write a stored procedure/function which has to generate the combination of numbers
For eg: IF I/p is an array of numbers a(i) = [1,2,3]
I want to get various combinations of numbers with these three digits.
Can someone help me in writing the stored procedure generating the output for this
|
|
|
|
|
|
| Re: Oracle - generating combination of numbers [message #480591 is a reply to message #480586] |
Mon, 25 October 2010 21:42  |
 |
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
You need to provide more specifics. There are always various ways to do things, but you will need to create a cartesian product somehow. The following examples assume that the input row and output rows are always three digits and that none of the digits may be repeated in the same row.
-- procedure:
SCOTT@orcl_11gR2> CREATE OR REPLACE TYPE three_number_array AS ARRAY (3) OF NUMBER;
2 /
Type created.
SCOTT@orcl_11gR2> SHOW ERRORS
No errors.
SCOTT@orcl_11gR2> CREATE OR REPLACE PROCEDURE your_proc
2 (p_three_number_array IN three_number_array,
3 p_results OUT SYS_REFCURSOR)
4 AS
5 BEGIN
6 OPEN p_results FOR
7 SELECT t1.digit1, t2.digit2, t3.digit3
8 FROM (SELECT t.column_value digit1, ROWNUM rn1
9 FROM TABLE (p_three_number_array) t) t1,
10 (SELECT t.column_value digit2, ROWNUM rn2
11 FROM TABLE (p_three_number_array) t) t2,
12 (SELECT t.column_value digit3, ROWNUM rn3
13 FROM TABLE (p_three_number_array) t) t3
14 WHERE t1.rn1 != t2.rn2
15 AND t1.rn1 != t3.rn3
16 AND t2.rn2 != t3.rn3
17 ORDER BY digit1, digit2, digit3;
18 END your_proc;
19 /
Procedure created.
SCOTT@orcl_11gR2> SHOW ERRORS
No errors.
SCOTT@orcl_11gR2> VARIABLE g_results REFCURSOR
SCOTT@orcl_11gR2> EXEC your_proc (three_number_array (1, 2, 3), :g_results)
PL/SQL procedure successfully completed.
SCOTT@orcl_11gR2> PRINT g_results
DIGIT1 DIGIT2 DIGIT3
---------- ---------- ----------
1 2 3
1 3 2
2 1 3
2 3 1
3 1 2
3 2 1
6 rows selected.
SCOTT@orcl_11gR2> DROP PROCEDURE your_proc
2 /
Procedure dropped.
SCOTT@orcl_11gR2> DROP TYPE three_number_array FORCE
2 /
Type dropped.
-- function:
SCOTT@orcl_11gR2> CREATE OR REPLACE TYPE three_number_array AS ARRAY (3) OF NUMBER;
2 /
Type created.
SCOTT@orcl_11gR2> CREATE OR REPLACE TYPE three_number_typ AS OBJECT
2 (digit1 NUMBER,
3 digit2 NUMBER,
4 digit3 NUMBER);
5 /
Type created.
SCOTT@orcl_11gR2> CREATE OR REPLACE TYPE three_number_tab AS TABLE OF three_number_typ;
2 /
Type created.
SCOTT@orcl_11gR2> SHOW ERRORS
No errors.
SCOTT@orcl_11gR2> CREATE OR REPLACE FUNCTION your_func
2 (p_three_number_array IN three_number_array)
3 RETURN three_number_tab PIPELINED
4 AS
5 BEGIN
6 FOR r IN
7 (SELECT t1.digit1, t2.digit2, t3.digit3
8 FROM (SELECT t.column_value digit1, ROWNUM rn1
9 FROM TABLE (p_three_number_array) t) t1,
10 (SELECT t.column_value digit2, ROWNUM rn2
11 FROM TABLE (p_three_number_array) t) t2,
12 (SELECT t.column_value digit3, ROWNUM rn3
13 FROM TABLE (p_three_number_array) t) t3
14 WHERE t1.rn1 != t2.rn2
15 AND t1.rn1 != t3.rn3
16 AND t2.rn2 != t3.rn3
17 ORDER BY digit1, digit2, digit3)
18 LOOP
19 PIPE ROW (three_number_typ (r.digit1, r.digit2, r.digit3));
20 END LOOP;
21 END your_func;
22 /
Function created.
SCOTT@orcl_11gR2> SHOW ERRORS
No errors.
SCOTT@orcl_11gR2> SELECT * FROM TABLE (your_func (three_number_array (1, 2, 3)))
2 /
DIGIT1 DIGIT2 DIGIT3
---------- ---------- ----------
1 2 3
1 3 2
2 1 3
2 3 1
3 1 2
3 2 1
6 rows selected.
SCOTT@orcl_11gR2> DROP FUNCTION your_func
2 /
Function dropped.
SCOTT@orcl_11gR2> DROP TYPE three_number_tab
2 /
Type dropped.
SCOTT@orcl_11gR2> DROP TYPE three_number_typ
2 /
Type dropped.
SCOTT@orcl_11gR2> DROP TYPE three_number_array FORCE
2 /
Type dropped.
SCOTT@orcl_11gR2>
|
|
|
|
| Re: Oracle - generating combination of numbers [message #480592 is a reply to message #480591] |
Mon, 25 October 2010 22:17 |
|
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
This could also be done with just SQL, without a procedure or function or types, as shown below.
SCOTT@orcl_11gR2> with test_tab as
2 (select 1 digit, 1 rn from dual union all
3 select 2 digit, 2 rn from dual union all
4 select 3 digit, 3 rn from dual)
5 select t1.digit digit1, t2.digit digit2, t3.digit digit3
6 from test_tab t1, test_tab t2, test_tab t3
7 where t1.rn != t2.rn
8 and t1.rn != t3.rn
9 and t2.rn != t3.rn
10 /
DIGIT1 DIGIT2 DIGIT3
---------- ---------- ----------
1 2 3
1 3 2
2 1 3
2 3 1
3 1 2
3 2 1
6 rows selected.
SCOTT@orcl_11gR2>
|
|
|
|
|
|
|
|
| Re: Oracle - generating combination of numbers [message #480865 is a reply to message #480860] |
Wed, 27 October 2010 22:03 |
|
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Your requirements are unclear and your examples inconsistent. You have switched from a comma-separated list of digits to a number consisting of concatenated digits. If the length of input and output is variable, then you can use dynamic sql to generate the results, as demonstrated below.
SCOTT@orcl_11gR2> CREATE OR REPLACE FUNCTION your_func
2 (p_digits IN VARCHAR2)
3 RETURN SYS_REFCURSOR
4 AS
5 v_digits SYS_REFCURSOR;
6 v_sql VARCHAR2 (32767);
7 BEGIN
8 v_sql :=
9 'WITH digits AS ' || CHR (10) ||
10 '(SELECT SUBSTR (:b1_digits, ROWNUM, 1) digit, ROWNUM rn' || CHR (10) ||
11 'FROM DUAL ' || CHR (10) ||
12 'CONNECT BY LEVEL <= LENGTH (:b2_digits))' || CHR (10) ||
13 'SELECT DISTINCT ';
14 FOR i IN 1 .. LENGTH (p_digits) LOOP
15 v_sql := v_sql || 't' || i || '.digit || ';
16 END LOOP;
17 v_sql := RTRIM (v_sql, '|| ') || ' digits' || CHR (10) || 'FROM ';
18 FOR i IN 1 .. LENGTH (p_digits) LOOP
19 v_sql := v_sql || 'digits t' || i || ',';
20 END LOOP;
21 v_sql := RTRIM (v_sql, ',') || CHR (10) || 'WHERE 1 = 1';
22 FOR i IN 1 .. (LENGTH (p_digits) - 1) LOOP
23 FOR j IN (i + 1) ..LENGTH (p_digits) LOOP
24 v_sql := v_sql || ' AND t' || i || '.rn != t' || j || '.rn';
25 END LOOP;
26 END LOOP;
27 v_sql := v_sql || CHR (10) || 'ORDER BY digits';
28 dbms_output.put_line (v_sql);
29 OPEN v_digits FOR v_sql USING p_digits, p_digits;
30 RETURN v_digits;
31 END your_func;
32 /
Function created.
SCOTT@orcl_11gR2> SHOW ERRORS
No errors.
SCOTT@orcl_11gR2> VARIABLE g_digits REFCURSOR
SCOTT@orcl_11gR2> EXEC :g_digits := your_func ('12342387')
WITH digits AS
(SELECT SUBSTR (:b1_digits, ROWNUM, 1) digit, ROWNUM rn
FROM
DUAL
CONNECT BY LEVEL <= LENGTH (:b2_digits))
SELECT DISTINCT t1.digit ||
t2.digit || t3.digit || t4.digit || t5.digit || t6.digit || t7.digit || t8.digit
digits
FROM digits t1,digits t2,digits t3,digits t4,digits t5,digits t6,digits
t7,digits t8
WHERE 1 = 1 AND t1.rn != t2.rn AND t1.rn != t3.rn AND t1.rn !=
t4.rn AND t1.rn != t5.rn AND t1.rn != t6.rn AND t1.rn != t7.rn AND t1.rn !=
t8.rn AND t2.rn != t3.rn AND t2.rn != t4.rn AND t2.rn != t5.rn AND t2.rn !=
t6.rn AND t2.rn != t7.rn AND t2.rn != t8.rn AND t3.rn != t4.rn AND t3.rn !=
t5.rn AND t3.rn != t6.rn AND t3.rn != t7.rn AND t3.rn != t8.rn AND t4.rn !=
t5.rn AND t4.rn != t6.rn AND t4.rn != t7.rn AND t4.rn != t8.rn AND t5.rn !=
t6.rn AND t5.rn != t7.rn AND t5.rn != t8.rn AND t6.rn != t7.rn AND t6.rn !=
t8.rn AND t7.rn != t8.rn
ORDER BY digits
PL/SQL procedure successfully completed.
SCOTT@orcl_11gR2> PRINT g_digits
DIGITS
--------------------------------
12233478
12233487
12233748
12233784
12233847
... results truncated to save space
87432312
87432321
87433122
87433212
87433221
10080 rows selected.
SCOTT@orcl_11gR2>
|
|
|
|
|
|
| Re: Oracle - generating combination of numbers [message #480960 is a reply to message #480959] |
Thu, 28 October 2010 11:17 |
|
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
electriceel wrote on Thu, 28 October 2010 09:05
Thanks for your reply. This is a wonderful answer.
I am trying to understand the logic here
At the end, when you said
OPEN v_digits FOR v_sql USING p_digits, p_digits;
why do you need to declare p_digits twice. Can you please explain ?
You have to have "p_digits" once for each bind variable in the generated select statement, which contains bind variables ":b1_digits" and ":b2_digits". Even if I had named them the same, you would still need "p_digits" twice. Otherwise, you get an error that not all variables are bound.
WITH digits AS
(SELECT SUBSTR (:b1_digits, ROWNUM, 1) digit, ROWNUM rn
FROM
DUAL
CONNECT BY LEVEL <= LENGTH (:b2_digits))
SELECT DISTINCT t1.digit ||
t2.digit || t3.digit || t4.digit || t5.digit || t6.digit || t7.digit || t8.digit
digits
FROM digits t1,digits t2,digits t3,digits t4,digits t5,digits t6,digits
t7,digits t8
WHERE 1 = 1 AND t1.rn != t2.rn AND t1.rn != t3.rn AND t1.rn !=
t4.rn AND t1.rn != t5.rn AND t1.rn != t6.rn AND t1.rn != t7.rn AND t1.rn !=
t8.rn AND t2.rn != t3.rn AND t2.rn != t4.rn AND t2.rn != t5.rn AND t2.rn !=
t6.rn AND t2.rn != t7.rn AND t2.rn != t8.rn AND t3.rn != t4.rn AND t3.rn !=
t5.rn AND t3.rn != t6.rn AND t3.rn != t7.rn AND t3.rn != t8.rn AND t4.rn !=
t5.rn AND t4.rn != t6.rn AND t4.rn != t7.rn AND t4.rn != t8.rn AND t5.rn !=
t6.rn AND t5.rn != t7.rn AND t5.rn != t8.rn AND t6.rn != t7.rn AND t6.rn !=
t8.rn AND t7.rn != t8.rn
ORDER BY digits
|
|
|
|
| Re: Oracle - generating combination of numbers [message #480963 is a reply to message #480586] |
Thu, 28 October 2010 12:47 |
electriceel
Messages: 30
Registered: August 2010
Location: USA
|
Member |
|
|
Great gottit, wonderful!
Also, I have another question.
In the beginning of the function,
v_sql :=
'WITH digits AS '
|| CHR (10)
|| '(SELECT SUBSTR (:b1_digits, ROWNUM, 1) digit, ROWNUM rn'
|| CHR (10)
|| 'FROM DUAL '
|| CHR (10)
|| 'CONNECT BY LEVEL <= LENGTH (:b2_digits))'
|| CHR (10)
|| 'SELECT DISTINCT ';
Why is this included 'WITH digits AS '. I mean what does this do. Can you please explain.
|
|
|
|
|
|
| Re: Oracle - generating combination of numbers [message #480967 is a reply to message #480964] |
Thu, 28 October 2010 13:14 |
 |
Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
Huh, he'll drawn in the ocean! (i.e. the whole SQL Reference book).
"WITH" is used to emulate a table that, actually, doesn't exist. For example, I could do a simple example as follows: first create a table, insert some records into it, and - finally - select from the table:SQL> create table test (id number, ename varchar2(20));
Table created.
SQL> insert all
2 into test (id, ename) values (1, 'Little')
3 into test (id, ename) values (2, 'Foot')
4 select * from dual;
2 rows created.
SQL> select sum(id) from test;
SUM(ID)
----------
3
Or, using the WITH:SQL> with test as
2 (select 1 id, 'Little' ename from dual
3 union
4 select 2 id, 'Foot' ename from dual
5 )
6 select sum(id) from test;
SUM(ID)
----------
3
SQL>
See?
[Updated on: Thu, 28 October 2010 13:14] Report message to a moderator |
|
|
|
| Re: Oracle - generating combination of numbers [message #480968 is a reply to message #480586] |
Thu, 28 October 2010 13:27 |
 |
Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
SQL> def x=MICHEL
SQL> with
2 numbers as (select level i from dual connect by level <= length('&x')),
3 permutations as (
4 select replace(sys_connect_by_path(i,'*'),'*','') p, rownum n
5 from numbers
6 where level = length('&x')
7 connect by nocycle prior i != i
8 ),
9 elems as (
10 select n, i, substr('&x', substr(p, i, 1), 1) elem
11 from permutations, numbers
12 )
13 select replace(sys_connect_by_path(elem,'*'),'*','') res
14 from elems
15 where i = length('&x')
16 connect by prior n = n and prior i = i-1
17 start with i = 1
18 /
RES
----------
MICHEL
MICHLE
MICEHL
MICELH
MICLHE
MICLEH
MIHCEL
<stripped to save space>
LEIHCM
LECMIH
LECMHI
LECIMH
LECIHM
LECHMI
LECHIM
LEHMIC
LEHMCI
LEHIMC
LEHICM
LEHCMI
LEHCIM
720 rows selected. |
|
|
|
| Re: Oracle - generating combination of numbers [message #480970 is a reply to message #480968] |
Thu, 28 October 2010 14:02 |
|
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
I had a feeling there was a way to do this without dynamical SQL and Michel figured it out. However, assuming as before that you want only distinct values, that they are not re-used, and that you want it in a function, I believe it can be simplified as demonstrated below.
SCOTT@orcl_11gR2> CREATE OR REPLACE FUNCTION your_func
2 (p_digits IN VARCHAR2)
3 RETURN SYS_REFCURSOR
4 AS
5 v_digits SYS_REFCURSOR;
6 BEGIN
7 OPEN v_digits FOR
8 select distinct replace (sys_connect_by_path (digit, '*'), '*', '') digits
9 from (select level i,
10 substr (p_digits, level, 1) digit
11 from dual
12 connect by level <= length (p_digits))
13 where level = length (p_digits)
14 connect by nocycle prior i != i
15 order by digits;
16 RETURN v_digits;
17 END your_func;
18 /
Function created.
SCOTT@orcl_11gR2> SHOW ERRORS
No errors.
SCOTT@orcl_11gR2> VARIABLE g_digits REFCURSOR
SCOTT@orcl_11gR2> EXEC :g_digits := your_func ('12342387')
PL/SQL procedure successfully completed.
SCOTT@orcl_11gR2> PRINT g_digits
DIGITS
--------------------------------------------------------------------------------
12233478
12233487
12233748
12233784
12233847
... truncated to save space
87432312
87432321
87433122
87433212
87433221
10080 rows selected.
SCOTT@orcl_11gR2>
|
|
|
|
|
|
| Re: Oracle - generating combination of numbers [message #480976 is a reply to message #480968] |
Thu, 28 October 2010 16:15 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Michel Cadot wrote on Thu, 28 October 2010 14:27
720 rows selected.
Hierarchical query is good for small number of permutations. Recursive pipelined function is faster:
CREATE OR REPLACE
FUNCTION permutations(
p_str VARCHAR2,
p_tmp VARCHAR2
)
RETURN sys.OdciVarchar2List
PIPELINED
IS
v_perm VARCHAR2(4000);
BEGIN
IF LENGTH(p_str) = 1
THEN
PIPE ROW(p_tmp || p_str);
ELSE
FOR v_i IN 1..LENGTH(p_str) LOOP
FOR v_rec IN (
SELECT *
FROM TABLE(
permutations(
SUBSTR(p_str,1,v_i - 1) || SUBSTR(p_str,v_i + 1),
p_tmp || SUBSTR(p_str,v_i,1)
)
)
) LOOP
PIPE ROW(v_rec.column_value);
END LOOP;
END LOOP;
END IF;
END;
/
SQL> select count(*)
2 from (
3 select *
4 from table(permutations('MICHEL',NULL))
5 )
6 /
COUNT(*)
----------
720
Elapsed: 00:00:00.15
SQL> select count(*)
2 from (
3 select *
4 from table(permutations('MICHEL ',NULL))
5 )
6 /
COUNT(*)
----------
5040
Elapsed: 00:00:01.07
SQL> select count(*)
2 from (
3 select *
4 from table(permutations('MICHEL C',NULL))
5 )
6 /
COUNT(*)
----------
40320
Elapsed: 00:00:07.35
SQL> select count(*)
2 from (
3 select *
4 from table(permutations('MICHEL CA',NULL))
5 )
6 /
COUNT(*)
----------
362880
Elapsed: 00:01:06.59
SQL> set verify off
SQL> def x="MICHEL"
SQL> with
2 numbers as (select level i from dual connect by level <= length('&x')),
3 permutations as (
4 select replace(sys_connect_by_path(i,'*'),'*','') p, rownum n
5 from numbers
6 where level = length('&x')
7 connect by nocycle prior i != i
8 ),
9 elems as (
10 select n, i, substr('&x', substr(p, i, 1), 1) elem
11 from permutations, numbers
12 )
13 select count(*) from (
14 select replace(sys_connect_by_path(elem,'*'),'*','') res
15 from elems
16 where i = length('&x')
17 connect by prior n = n and prior i = i-1
18 start with i = 1
19 )
20 /
COUNT(*)
----------
720
Elapsed: 00:00:00.59
SQL> def x="MICHEL "
SQL> /
COUNT(*)
----------
5040
Elapsed: 00:00:00.71
SQL> def x="MICHEL C"
SQL> /
COUNT(*)
----------
40320
Elapsed: 00:00:05.01
SQL> def x="MICHEL CA"
SQL> /
COUNT(*)
----------
362880
Elapsed: 00:06:48.35
SQL>
As you can see, hierarchical "MICHEL CA" took 06:48.35 while it took recursive pipelined function only 01:06.59.
SY.
|
|
|
|
| Re: Oracle - generating combination of numbers [message #480979 is a reply to message #480976] |
Thu, 28 October 2010 17:16 |
|
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Solomon,
Part of my assumption was that the values be distinct and that has not been disputed, although it has not been confirmed either. Assuming that is the case, applying distinct outside your function, I am getting faster results with a pipelined function that uses recursive sql than with your recursive function. Am I missing something or is there a better way?
Barbara
SCOTT@orcl_11gR2> ALTER SYSTEM FLUSH SHARED_POOL
2 /
System altered.
SCOTT@orcl_11gR2> CREATE OR REPLACE FUNCTION permutations
2 (p_str VARCHAR2,
3 p_tmp VARCHAR2)
4 RETURN sys.OdciVarchar2List PIPELINED
5 IS
6 v_perm VARCHAR2(4000);
7 BEGIN
8 IF LENGTH(p_str) = 1 THEN
9 PIPE ROW(p_tmp || p_str);
10 ELSE
11 FOR v_i IN 1..LENGTH(p_str) LOOP
12 FOR v_rec IN
13 (SELECT *
14 FROM TABLE
15 (permutations
16 (SUBSTR(p_str,1,v_i - 1) || SUBSTR(p_str,v_i + 1),
17 p_tmp || SUBSTR(p_str,v_i,1))))
18 LOOP
19 PIPE ROW(v_rec.column_value);
20 END LOOP;
21 END LOOP;
22 END IF;
23 END permutations;
24 /
Function created.
SCOTT@orcl_11gR2> SHOW ERRORS
No errors.
SCOTT@orcl_11gR2> CREATE OR REPLACE FUNCTION my_func
2 (p_digits IN VARCHAR2)
3 RETURN sys.OdciVarchar2List PIPELINED
4 AS
5 BEGIN
6 FOR v_rec IN
7 (select distinct replace (sys_connect_by_path (digit, '*'), '*', '') digits
8 from (select level i,
9 substr (p_digits, level, 1) digit
10 from dual
11 connect by level <= length (p_digits))
12 where level = length (p_digits)
13 connect by nocycle prior i != i
14 order by digits)
15 LOOP
16 PIPE ROW (v_rec.digits);
17 END LOOP;
18 END my_func;
19 /
Function created.
SCOTT@orcl_11gR2> SHOW ERRORS
No errors.
SCOTT@orcl_11gR2> set timing on
SCOTT@orcl_11gR2> select count(*)
2 from (select distinct column_value
3 from table (permutations ('12342387', null)))
4 /
COUNT(*)
----------
10080
1 row selected.
Elapsed: 00:00:04.82
SCOTT@orcl_11gR2> select count(*)
2 from (select *
3 from table (my_func ('12342387')))
4 /
COUNT(*)
----------
10080
1 row selected.
Elapsed: 00:00:00.72
SCOTT@orcl_11gR2>
|
|
|
|
|
|
| Re: Oracle - generating combination of numbers [message #481056 is a reply to message #481051] |
Fri, 29 October 2010 14:42 |
|
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
syakobson wrote on Fri, 29 October 2010 10:51
... I wonder what performance level recursive subquery factoring would have. ...
It didn't do well, but I don't know if I wrote it in the most efficient way.
SCOTT@orcl_11gR2> ALTER SYSTEM FLUSH SHARED_POOL
2 /
System altered.
SCOTT@orcl_11gR2> CREATE OR REPLACE FUNCTION permutations
2 (p_str VARCHAR2,
3 p_tmp VARCHAR2)
4 RETURN sys.OdciVarchar2List PIPELINED
5 IS
6 v_perm VARCHAR2(4000);
7 BEGIN
8 IF LENGTH(p_str) = 1 THEN
9 PIPE ROW(p_tmp || p_str);
10 ELSE
11 FOR v_i IN 1..LENGTH(p_str) LOOP
12 FOR v_rec IN
13 (SELECT *
14 FROM TABLE
15 (permutations
16 (SUBSTR(p_str,1,v_i - 1) || SUBSTR(p_str,v_i + 1),
17 p_tmp || SUBSTR(p_str,v_i,1))))
18 LOOP
19 PIPE ROW(v_rec.column_value);
20 END LOOP;
21 END LOOP;
22 END IF;
23 END permutations;
24 /
Function created.
SCOTT@orcl_11gR2> SHOW ERRORS
No errors.
SCOTT@orcl_11gR2> CREATE OR REPLACE FUNCTION my_func
2 (p_digits IN VARCHAR2)
3 RETURN sys.OdciVarchar2List PIPELINED
4 AS
5 BEGIN
6 FOR v_rec IN
7 (select distinct replace (sys_connect_by_path (digit, '*'), '*', '') digits
8 from (select level i,
9 substr (p_digits, level, 1) digit
10 from dual
11 connect by level <= length (p_digits))
12 where level = length (p_digits)
13 connect by nocycle prior i != i
14 order by digits)
15 LOOP
16 PIPE ROW (v_rec.digits);
17 END LOOP;
18 END my_func;
19 /
Function created.
SCOTT@orcl_11gR2> SHOW ERRORS
No errors.
SCOTT@orcl_11gR2> CREATE OR REPLACE FUNCTION new_func
2 (p_digits IN VARCHAR2)
3 RETURN sys.OdciVarchar2List PIPELINED
4 AS
5 BEGIN
6 FOR v_rec IN
7 (with
8 numbers (i, digit) as
9 (select rownum,
10 substr ('12342387', rownum, 1)
11 from dual
12 union all
13 select i + 1,
14 substr ('12342387', i + 1, 1)
15 from numbers
16 where i < length ('12342387')),
17 scbp (lvl, digits, i) as
18 (select 1 lvl, digit digits, numbers.i
19 from numbers
20 union all
21 select lvl + 1 lvl, digits || digit digits, numbers.i
22 from scbp, numbers
23 where scbp.i != numbers.i
24 and lvl < length ('12342387'))
25 cycle i set cycle to 1 default 0
26 select distinct digits
27 from scbp
28 where lvl = length ('12342387')
29 and cycle = 0
30 order by digits)
31 LOOP
32 PIPE ROW (v_rec.digits);
33 END LOOP;
34 END new_func;
35 /
Function created.
SCOTT@orcl_11gR2> SHOW ERRORS
No errors.
SCOTT@orcl_11gR2> set timing on
SCOTT@orcl_11gR2> select count(*)
2 from (select distinct column_value
3 from table (permutations ('12342387', null)))
4 /
COUNT(*)
----------
10080
1 row selected.
Elapsed: 00:00:04.69
SCOTT@orcl_11gR2> select count(*)
2 from (select *
3 from table (my_func ('12342387')))
4 /
COUNT(*)
----------
10080
1 row selected.
Elapsed: 00:00:00.64
SCOTT@orcl_11gR2> select count(*)
2 from (select *
3 from table (new_func ('12342387')))
4 /
COUNT(*)
----------
10080
1 row selected.
Elapsed: 00:00:14.10
SCOTT@orcl_11gR2>
|
|
|
|
|
|
| Re: Oracle - generating combination of numbers [message #611626 is a reply to message #611610] |
Fri, 04 April 2014 12:54 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Just for fun, using powermultiset:
with pallets as (
select 100.2 weight from dual union all
select 120.34 from dual union all
select 123.54 from dual union all
select 82.23 from dual union all
select 98.45 from dual
),
permutations as (
select t.column_value pallet_set,
(
select sum(tt.column_value)
from table(t.column_value) tt
) total_weight
from table(
powermultiset(
(
select cast(collect(weight) as KU$_OBJNUMSET)
from pallets
)
)
) t
),
t as (
select pallet_set,
total_weight,
dense_rank() over(order by abs(total_weight - 202)) rnk
from permutations
)
select pallet_set,
total_weight
from t
where rnk = 1
/
PALLET_SET TOTAL_WEIGHT
------------------------------------------------------- ------------
KU$_OBJNUMSET(120.34, 82.23) 202.57
SCOTT@pdb1orcl12 >
SY.
|
|
|
|
|
|
| Re: Oracle - generating combination of numbers [message #611628 is a reply to message #611626] |
Fri, 04 April 2014 13:34 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
And we can do some optimization. We only need to look at all pallets of smaller than order weight and one smallest pallet of same or larger than order weight. No need to look at the rest of pallets of same or larger than order weight. This will reduce number of permutations to calculate. However, number of skipped permutaions should be large enough so cost of calcuting skipped permutations is more than cost of determining what pallets to take. Anyway:
variable order_weight number
exec :order_weight := 202;
with pallets as (
select 100.2 weight from dual union all
select 120.34 from dual union all
select 123.54 from dual union all
select 82.23 from dual union all
select 98.45 from dual
),
-- flag pallets for the order: we will only need to look at all pallets smaller than order weight and
-- one smallest pallet of same or larger than order weight
pallet_flag as (
select weight,
case
when weight < :order_weight then 1
when lag(weight,1,:order_weight - 1) over(order by weight) < :order_weight then 1
end take_this_pallet
from pallets
),
permutations as (
select t.column_value pallet_set,
(
select sum(tt.column_value)
from table(t.column_value) tt
) total_weight
from table(
powermultiset(
(
select cast(collect(weight) as KU$_OBJNUMSET)
from pallet_flag
where take_this_pallet = 1
)
)
) t
),
t as (
select pallet_set,
total_weight,
dense_rank() over(order by abs(total_weight - 202)) rnk
from permutations
)
select pallet_set,
total_weight
from t
where rnk = 1
/
PALLET_SET TOTAL_WEIGHT
------------------------------------------------------- ------------
KU$_OBJNUMSET(82.23, 120.34) 202.57
SCOTT@pdb1orcl12 >
SY.
[Updated on: Fri, 04 April 2014 13:36] Report message to a moderator |
|
|
|
| Re: Oracle - generating combination of numbers [message #611629 is a reply to message #611627] |
Fri, 04 April 2014 13:44 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Michel Cadot wrote on Fri, 04 April 2014 14:32
Nice.
Still, model or recursive subquery factoring would be much more optimal solutions since they allow to stop adding pallets to current set as soon as we hit set of pallets covering the order and start building next set instead of adding more pallets to such set like powermultiset does.
SY.
|
|
|
|
|
|
| Re: Oracle - generating combination of numbers [message #611881 is a reply to message #611863] |
Wed, 09 April 2014 10:06 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
create or replace
type pallet_obj_type
as object(
weight number,
code varchar2(10)
)
/
create or replace
type pallet_tbl_type
as table of pallet_obj_type
/
variable order_weight number
exec :order_weight := 202;
with pallets as (
select pallet_obj_type(100.2,'SSCC') pallet from dual union all
select pallet_obj_type(120.34,'SSCC') from dual union all
select pallet_obj_type(123.54,'SSCC') from dual union all
select pallet_obj_type(82.23,'SSCC') from dual union all
select pallet_obj_type(98.45,'SSCC') from dual
),
-- flag pallets for the order: we will only need to look at all pallets smaller than order weight and
-- one smallest pallet of same or larger than order weight
pallet_flag as (
select pallet,
case
when p.pallet.weight < :order_weight then 1
when lag(p.pallet.weight,1,:order_weight - 1) over(order by p.pallet.weight) < :order_weight then 1
end take_this_pallet
from pallets p
),
permutations as (
select t.column_value pallet_set,
(
select sum(weight)
from table(t.column_value) tt
) total_weight
from table(
powermultiset(
(
select cast(collect(pallet) as pallet_tbl_type)
from pallet_flag
where take_this_pallet = 1
)
)
) t
),
t as (
select pallet_set,
total_weight,
dense_rank() over(order by abs(total_weight - 202)) rnk
from permutations
)
select pallet_set,
total_weight
from t
where rnk = 1
/
PALLET_SET(WEIGHT, CODE) TOTAL_WEIGHT
-------------------------------------------------------------------------------- ------------
PALLET_TBL_TYPE(PALLET_OBJ_TYPE(82.23, 'SSCC'), PALLET_OBJ_TYPE(120.34, 'SSCC')) 202.57
SQL>
SY.
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Apr 26 17:15:54 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
