Fastest way to generate permutations of a table with n columns and put in a new table added one more question to same how to remove duplicate rows

oracleoracle-12cplsql

As i am from mathematics background and require to do some huge permutations to start in my work kind help.

If i have a table by name sample1 with n columns what is fastest way to get all the permutations of the rows of sample1 into a new table. n>=12 say.

Please provide kind help with a link where i can get help or any code or query.

An example

say the 6 columns of a row contain

E1       E2      E3        E4        E5        E6

'[0,1]' '[1,2]'  '[2,3]'   '[3.4]'   '[4,5]'   '[9,10]'

I need to include its permutations

like 

E1       E2      E3        E4        E5        E6

'[1,2]' '[0,1]'   '[2,3]'   '[3.4]'   '[4,5]'  '[9,10]'

 '[0,1]'   '[2,3]' '[3.4]' '[4,5]'  '[9,10]' '[1,2]'

and other permutations of row 1 above if there are n columns there will be n! permutations i need them also to be generated quickly and put into a new table.
U can the headers as column name each is a varchar.

Like for the above table if there are two rows with same elements in each of its columns how to remove duplicates kind help with any query

Best Answer

This isn't really an answer, more of a discussion of the problem. If I understand correctly, you would have 1 row with twelve columns turn into 479,001,600 rows. From the initial row you provided there are no duplicate values within the columns of the same row, so it doesn't appear that you are trying to eliminate duplicates within a row.

You didn't say how many rows are in your initial table, but this should mean that 100 rows in the initial table would equate to 47,900,160,000 rows in the resulting table. The only way there could be duplicates there is if two rows in the initial table had a row with the same values as another row.

If those values are in the same order, then a simple select distinct * from t1 would eliminate the duplicates. If instead the values are in a different order, then you actually have to determine the permutations for each row and make sure only one of those is in the initial table.

For smaller values of n, it would probably be easiest just to determine all the permutations of each row and then remove the duplicates. For larger values of n eliminating from the initial set would certainly be advantageous for lower numbers of rows in the initial table. As the initial table size grows the benefit of this early elimination is diminished.

Here is a way to get the permutations of one row. It is likely not the best way.

Here is a way to get rows that have duplicates in a different order in the initial table.

Related Question