Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, mo.

Asked: May 26, 2016 - 3:40 pm UTC

Last updated: July 05, 2017 - 12:36 am UTC

Version: 11G

Viewed 1000+ times

You Asked

I have a table with 1 column. I need max permutation for each row values.

COLUMN1
--------
a
b
c
d

For above table output should be:
a_b
a_c
a_d
b_c
b_d
c_d

Your response in this regard is deeply appreciated.

and Connor said...

SQL> create table t as select chr(ascii('a')-1+rownum) x from dual connect by level <= 4;

Table created.

SQL>
SQL> select *
  2  from t t1, t t2
  3  where t1.x < t2.x;

X X
- -
a b
a c
a d
b c
b d
c d

6 rows selected.


Rating

  (6 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

powermultiset_by_cardinality ?!?!?!

Rajeshwaran, Jeyabal, May 27, 2016 - 1:41 pm UTC 

demo@ORA12C> create or replace type sample_ntt is table of varchar2(10);
  2  /

Type created.

demo@ORA12C> select * from t;

X
---
a
b
c
d

4 rows selected.

demo@ORA12C> select cast( collect(x) as sample_ntt) from t;

CAST(COLLECT(X)ASSAMPLE_NTT)
------------------------------------------------------------------------------------------------------
SAMPLE_NTT('a', 'b', 'c', 'd')

1 row selected.

demo@ORA12C> select * from table( powermultiset( sample_ntt('a','b','c','d')) ) ;

COLUMN_VALUE
------------------------------------------------------------------------------------------------------
SAMPLE_NTT('a')
SAMPLE_NTT('b')
SAMPLE_NTT('a', 'b')
SAMPLE_NTT('c')
SAMPLE_NTT('a', 'c')
SAMPLE_NTT('b', 'c')
SAMPLE_NTT('a', 'b', 'c')
SAMPLE_NTT('d')
SAMPLE_NTT('a', 'd')
SAMPLE_NTT('b', 'd')
SAMPLE_NTT('a', 'b', 'd')
SAMPLE_NTT('c', 'd')
SAMPLE_NTT('a', 'c', 'd')
SAMPLE_NTT('b', 'c', 'd')
SAMPLE_NTT('a', 'b', 'c', 'd')

15 rows selected.

demo@ORA12C> select * from table( powermultiset_by_cardinality( sample_ntt('a','b','c','d') ,2) ) ;

COLUMN_VALUE
------------------------------------------------------------------------------------------------------
SAMPLE_NTT('a', 'b')
SAMPLE_NTT('a', 'c')
SAMPLE_NTT('a', 'd')
SAMPLE_NTT('b', 'c')
SAMPLE_NTT('b', 'd')
SAMPLE_NTT('c', 'd')

6 rows selected.

demo@ORA12C>

Dependency on Schema level Nested Tables

Rajeshwaran, Jeyabal, May 27, 2016 - 1:54 pm UTC

Team,

After running those above queries, tried to drop "SAMPLE_NTT" but ended up with this error.

demo@ORA12C> drop type sample_ntt ;
drop type sample_ntt
*
ERROR at line 1:
ORA-02303: cannot drop or replace a type with type or table dependents


looking for dependency found this.

demo@ORA12C> @printtbl 'select * from user_dependencies where referenced_name ="SAMPLE_NTT"'
NAME                          : "SYSTPjxJkZiidRCub/S33b4MlYg=="
TYPE                          : "TYPE"
REFERENCED_OWNER              : "DEMO"
REFERENCED_NAME               : "SAMPLE_NTT"
REFERENCED_TYPE               : "TYPE"
REFERENCED_LINK_NAME          : ""
SCHEMAID                      : "135"
DEPENDENCY_TYPE               : "HARD"
-----------------

PL/SQL procedure successfully completed.

demo@ORA12C> drop type "SYSTPjxJkZiidRCub/S33b4MlYg==";

Type dropped.

demo@ORA12C> drop type sample_ntt ;

Type dropped.

demo@ORA12C>


Could you help me to understand Why this dependency type "SYSTPjxJkZiidRCub/S33b4MlYg==" got created ?

Connor McDonald
May 28, 2016 - 1:22 am UTC

From MOS: Transient Objects by COLLECT Function (Doc ID 1603706.1)

SYMPTOMS

Transient objects for created Type left over when using CAST(COLLECT()) even when the session closed

SQL> select * from user_objects where object_name like 'SYSTP%';

no rows selected

SQL> create type varchar2_tt as table of varchar2(4000);

Type created.

SQL> create or replace view vw as
select object_type,
cast(collect(object_name) as varchar2_tt) as object_names
from user_objects
group by object_type ;

View created.

SQL> select * from user_objects where object_name like 'SYSTP%';

OBJECT_NAME
------------------------------------------------------------------------------
--
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
------------------------------ ---------- -------------- -------------------
CREATED LAST_DDL_ TIMESTAMP STATUS T G S NAMESPACE
--------- --------- ------------------- ------- - - - ----------
EDITION_NAME
------------------------------
SYSTP4A8EEzKtQPLgQwEAAH9H2Q==
77213 TYPE
26-JUN-13 26-JUN-13 2013-06-26:14:45:32 VALID N N N 1



SQL> purge recyclebin ;

Recyclebin purged.

SQL> select * from user_objects where object_name like 'SYSTP%';

OBJECT_NAME
------------------------------------------------------------------------------
--
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
------------------------------ ---------- -------------- -------------------
CREATED LAST_DDL_ TIMESTAMP STATUS T G S NAMESPACE
--------- --------- ------------------- ------- - - - ----------
EDITION_NAME
------------------------------
SYSTP4A8EEzKtQPLgQwEAAH9H2Q==
77213 TYPE
26-JUN-13 26-JUN-13 2013-06-26:14:45:32 VALID N N N 1

SQL>exit
SQL>conn SH/SH

SQL> create or replace view vw as
select object_type,
cast(collect(object_name) as varchar2_tt) as object_names
from user_objects
group by object_type ;

View created.


SQL> select * from user_objects where object_name like 'SYSTP%';

OBJECT_NAME
------------------------------------------------------------------------------
--
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
------------------------------ ---------- -------------- -------------------
CREATED LAST_DDL_ TIMESTAMP STATUS T G S NAMESPACE
--------- --------- ------------------- ------- - - - ----------
EDITION_NAME
------------------------------
SYSTP4A8EEzKtQPLgQwEAAH9H2Q==
77213 TYPE
26-JUN-13 26-JUN-13 2013-06-26:14:45:32 VALID N N N 1



CHANGES

Even when the session exist for every creation of the view that will use CAST(COLLECT()) a new transient object will be created

CAUSE

The functionality of the cleanup of the Transient objects by SMON is designed to be called every 12 hours

This is expected as per design

Following Query Could be used to check for objects that is not cleaned by SMON after 12 hours :

alter session set nls_date_format = 'dd/mm/yyyy hh24:mi:ss';
col owner for a30
col object_name for a30
col created for a30

SQL>select owner,object_name,created,TRUNC((sysdate - created) * 24)
from dba_objects
where object_name like 'SYSTP%'
and TRUNC((sysdate - created) * 24) > 12
order by 3,4,2 ;


SOLUTION

1.start the instance with pfile where this event is set :
*.event="22834 trace name context forever, level 5"

This will enforce SMON to clean-up those transient objects every 5 minutes . The default interval between cleanups is 12 hrs. When the clean up function
is called first, if no interval was specified at the time, it will wait for 12 hrs before getting executed again. If you have set 22834 level 5, it will wait 12 hrs and then get called every 5 minutes. But for it to be active from the beginning the event + level has to be set up at DB start time.

=====================================

You can see this in your demo

SQL> create table t as select chr(ascii('a')-1+rownum) x from dual connect by level <= 4;

Table created.

SQL> create or replace type sample_ntt is table of varchar2(10);
  2  /

Type created.

SQL> select object_name, last_ddl_time from user_objects where object_type = 'TYPE' order by 2;

OBJECT_NAME                              LAST_DDL_TIME
---------------------------------------- --------------------
SAMPLE_NTT                               28-may-2016 09:11:26

SQL> select cast( collect(x) as sample_ntt) from t;

CAST(COLLECT(X)ASSAMPLE_NTT)
---------------------------------------------------------------------------------------------------
SAMPLE_NTT('a', 'b', 'c', 'd')

SQL> select object_name, last_ddl_time from user_objects where object_type = 'TYPE' order by 2;

OBJECT_NAME                              LAST_DDL_TIME
---------------------------------------- --------------------
SAMPLE_NTT                               28-may-2016 09:11:26
SYSTP4Pnv/j/vSwOdfASNad25UQ==            28-may-2016 09:12:11

SQL> select * from table( powermultiset( sample_ntt('a','b','c','d')) ) ;

COLUMN_VALUE
---------------------------------------------------------------------------------------------------
SAMPLE_NTT('a')
SAMPLE_NTT('b')
SAMPLE_NTT('a', 'b')
SAMPLE_NTT('c')
SAMPLE_NTT('a', 'c')
SAMPLE_NTT('b', 'c')
SAMPLE_NTT('a', 'b', 'c')
SAMPLE_NTT('d')
SAMPLE_NTT('a', 'd')
SAMPLE_NTT('b', 'd')
SAMPLE_NTT('a', 'b', 'd')
SAMPLE_NTT('c', 'd')
SAMPLE_NTT('a', 'c', 'd')
SAMPLE_NTT('b', 'c', 'd')
SAMPLE_NTT('a', 'b', 'c', 'd')

15 rows selected.

SQL> select object_name, last_ddl_time from user_objects where object_type = 'TYPE' order by 2;

OBJECT_NAME                              LAST_DDL_TIME
---------------------------------------- --------------------
SAMPLE_NTT                               28-may-2016 09:11:26
SYSTP4Pnv/j/vSwOdfASNad25UQ==            28-may-2016 09:12:11
SYSTPnNZ6+836Tp+yWuozQnktmA==            28-may-2016 09:12:11

SQL> select * from table( powermultiset_by_cardinality( sample_ntt('a','b','c','d') ,2) ) ;

COLUMN_VALUE
---------------------------------------------------------------------------------------------------
SAMPLE_NTT('a', 'b')
SAMPLE_NTT('a', 'c')
SAMPLE_NTT('a', 'd')
SAMPLE_NTT('b', 'c')
SAMPLE_NTT('b', 'd')
SAMPLE_NTT('c', 'd')

6 rows selected.

SQL> select object_name, last_ddl_time from user_objects where object_type = 'TYPE' order by 2;

OBJECT_NAME                              LAST_DDL_TIME
---------------------------------------- --------------------
SAMPLE_NTT                               28-may-2016 09:11:26
SYSTP4Pnv/j/vSwOdfASNad25UQ==            28-may-2016 09:12:11
SYSTPnNZ6+836Tp+yWuozQnktmA==            28-may-2016 09:12:11


My inference is that collect must return "something", so the type is created on the fly, and *then* that type is cast into the one you specified.

Dependency on Schema level Nested Tables

Rajeshwaran, Jeyabal, May 28, 2016 - 5:17 am UTC

Thanks for the Explanation.

Jess, June 26, 2017 - 12:51 am UTC

What would be the most efficient way of getting all the permutations if your data was in multiple tables instead of one? For example say you have 3 tables, A, B, C, each containing a different number of rows
A
==
1
2
3
4
5

B
==
11
12
13

C
==
101
102

a). What is the most efficient query to produce output containing all permutations? E.g.,
1-11-101
1-12-101
1-13-101
1-11-102
1-12-102
1-13-102
...
2..
3..
4..
...
5-11-101
5-12-101
5-13-101
5-11-102
5-12-102
5-13-102

b). If some of the rows contain the same value (e.g., 101 and 102 are really the same string), what is the most efficient way of getting a distinct set of the permutations?

Many thanks!
Connor McDonald
June 28, 2017 - 12:46 am UTC

Depending on your requirement, you can change your "!=" to just less then, or add a distinct etc.

SQL> create table t as select chr(ascii('a')-1+rownum) x from dual connect by level <=

Table created.

SQL> create table t1 as select chr(ascii('c')-1+rownum) x from dual connect by level <=

Table created.

SQL> create table t2 as select chr(ascii('a')-1+rownum) x from dual connect by level <=

Table created.

SQL>
SQL> select *
  2  from t, t1, t2
  3  where t1.x != t2.x
  4  and   t2.x != t.x
  5  /

X    X    X
---- ---- ----
a    c    b
a    d    b
a    e    b
a    f    b
a    g    b
a    d    c
a    e    c
a    f    c
a    g    c
a    c    d
a    e    d
a    f    d
a    g    d
b    c    a
b    d    a
b    e    a
b    f    a
b    g    a
b    d    c
b    e    c
b    f    c
b    g    c
b    c    d
b    e    d
b    f    d
b    g    d
c    c    a
c    d    a
c    e    a
c    f    a
c    g    a
c    c    b
c    d    b
c    e    b
c    f    b
c    g    b
c    c    d
c    e    d
c    f    d
c    g    d
d    c    a
d    d    a
d    e    a
d    f    a
d    g    a
d    c    b
d    d    b
d    e    b
d    f    b
d    g    b
d    d    c
d    e    c
d    f    c
d    g    c


Jess, July 02, 2017 - 10:51 pm UTC

Hi Connor,
That's clever! But it doesn't produce the full universe of results...

The != (or <) artificially eliminates permutations where 2 tables have the same value (though each in its own context). This won't often be the case, but is entirely possible. So in your example, "a - c - c" is missing (because it's not the same thing as "c - c - a" seen at the bottom).

Is there a comparable way of getting all the permutations efficiently?

Jess, July 04, 2017 - 7:51 pm UTC

Nevermind, that just happens of its own accord if I leave the where clause off... Not sure what went wrong when I tried last, but all's well now :)
Connor McDonald
July 05, 2017 - 12:36 am UTC

thanks for getting back to us
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.