Use Wm_concat to generate index reference fields

Source: Internet
Author: User

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);

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

———- ———-
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;

———- ——————————————————————————–
1 0,1,10,11
2 2,7,12,17
3 3,5,13,15
5 6,16

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.