Use Wm_concat to generate index reference fields

When it came time to clean up and organize the database, it felt like a piece of hemp to see the pair of tables and indexes. There's not a lot of time to clean up.

1. How many more tables have been added during this period.

2. How many tables have been modified during this time.

3. There are also indexes and how many indexes have been built. Whether there are rules for naming.

Since the current database is a data warehouse, it is desirable to standardize names in terms of indexes and to check and add them in batches. Know Oracle has wm_concat function, so Google, with the following scheme.

With Sql_tmp as
(Select Index_name,
Wm_concat (column_position) over (partition by index_name ORDER by Column_position) Column_position,
Wm_concat (column_name) over (partition by index_name ORDER by column_name) column_name
From User_ind_columns)
Select Index_name, Max (column_position), Max (column_name)
From Sql_tmp
GROUP BY Index_name
;

Refer to the following:
Wm_concat is useful when you are in rows, but scheduling problems are often difficult to control during row-and-column transfers.

The following examples are visible:
Prepare test table:
drop table t;
CREATE TABLE t (n number,m number);
INSERT into T values (1,1);
INSERT into T values (5,3);
INSERT into T values (3,3);
INSERT into T values (6,5);
INSERT into T values (7,2);
INSERT into T values (2,2);
INSERT into T values (0,1);
INSERT into T values (11,1);
INSERT into T values (15,3);
INSERT into T values (13,3);
INSERT into T values (16,5);
INSERT into T values (17,2);
INSERT into T values (12,2);
INSERT into T values (10,1);
Commit

Sql> SELECT * FROM T-order by 2, 1;

Nm
———- ———-
1 P
1 1
10 1
11 1
2 2
7 2
12 2
17 2
7 ·
5 3
17 ·
15 3
6 5
16 5

Test the order after Wm_concat:
Test 1:
Sql> Select M,wm_concat (n) from T Group by M;

M Wm_concat (N)
———- ——————————————————————————–
1 11,0,1,10
2 17,2,7,12
3 15,3,5,13
5 16,6

The order of the visible wm_concat is not in the order of large-> small, or small-> large.

Test 2:
– Refer to some online solutions
Sql> Select M,wm_concat (n)
2 from (select N,m from T-m,n)
3 GROUP by M;

M Wm_concat (N)
———- ——————————————————————————–
1 0,11,10,1
2 2,17,12,7
3 3,15,13,5
5 6,16

The visual order problem is still unresolved.

The Final Solution idea:
Sql> Select M, max (R)
2 from (select M, Wm_concat (n) over (partition by M order by N) r from T)
3 GROUP by M;

M MAX (R)
———- ——————————————————————————–
1 0,1,10,11
2 2,7,12,17
3 3,5,13,15
5 6,16