Simulate the showindexfromtable command of MySQL in Oracle _ MySQL

Simulate the showindexfromtable command of MySQL in Oracle Simulate the show index from table command of MySQL in Oracle

Experiment data initialization:

1. Create table t as select * from hr. employees

   ;

2. Create index inx_t1 on t (employee_id, first_name desc, last_name );
3. Create index inx_t2 on t (job_id, hire_date );

Displays information about all indexes of the table.

Log on as a dba

1. Set linesize 300

   ;

2. Set pagesize 100;
3. Col c1 format a20;
4. Col c2 format a20;
5. Col c3 format a20;
6. Col c4 format a20;
7. Col c5 format a20;
8. Col INDEX_NAME format a20;
9. Select INDEX_NAME,
10. Max (decode (COLUMN_POSITION, 1, COLUMN_NAME | ',' | COLUMN_LENGTH | ',' | DESCEND, null) c1,
11. Max (decode (COLUMN_POSITION, 2, COLUMN_NAME | ',' | COLUMN_LENGTH | ',' | DESCEND, null) c2,
12. Max (decode (COLUMN_POSITION, 3, COLUMN_NAME | ',' | COLUMN_LENGTH | ',' | DESCEND, null) c3,
13. Max (decode (COLUMN_POSITION, 4, COLUMN_NAME | ',' | COLUMN_LENGTH | ',' | DESCEND, null) c4,
14. Max (decode (COLUMN_POSITION, 5, COLUMN_NAME | ',' | COLUMN_LENGTH | ',' | DESCEND, null) c5
15. From (
16. Select INDEX_NAME, COLUMN_NAME, COLUMN_LENGTH, COLUMN_POSITION, DESCEND
17. From dba_ind_columns
18. Where table_owner = 'lihuilin'
19. AND table_name ='t'
20. Order by INDEX_NAME, column_position
21. ) Group by INDEX_NAME;

Log on as a common user

1. Set linesize 300

   ;

2. Set pagesize 100;
3. Col c1 format a20;
4. Col c2 format a20;
5. Col c3 format a20;
6. Col c4 format a20;
7. Col c5 format a20;
8. Col INDEX_NAME format a20;
9. Select INDEX_NAME,
10. Max (decode (COLUMN_POSITION, 1, COLUMN_NAME | ',' | COLUMN_LENGTH | ',' | DESCEND, null) c1,
11. Max (decode (COLUMN_POSITION, 2, COLUMN_NAME | ',' | COLUMN_LENGTH | ',' | DESCEND, null) c2,
12. Max (decode (COLUMN_POSITION, 3, COLUMN_NAME | ',' | COLUMN_LENGTH | ',' | DESCEND, null) c3,
13. Max (decode (COLUMN_POSITION, 4, COLUMN_NAME | ',' | COLUMN_LENGTH | ',' | DESCEND, null) c4,
14. Max (decode (COLUMN_POSITION, 5, COLUMN_NAME | ',' | COLUMN_LENGTH | ',' | DESCEND, null) c5
15. From (
16. Select INDEX_NAME, COLUMN_NAME, COLUMN_LENGTH, COLUMN_POSITION, DESCEND
17. From user_ind_columns
18. Where table_name ='t'
19. Order by INDEX_NAME, column_position
20. ) Group by INDEX_NAME;

1. Create or replace function long_2_varchar

   (

2. P_index_name IN user_ind_expressions. index_name % TYPE,
3. P_table_name IN user_ind_expressions. table_name % TYPE,
4. P_COLUMN_POSITION IN user_ind_expressions. table_name % TYPE)
5. RETURN VARCHAR2
6. AS
7. Rochelle column_expression LONG;
8. BEGIN
9. SELECT COLUMN_EXPRESSION
10. INTO l_COLUMN_EXPRESSION
11. FROM user_ind_expressions
12. WHERE index_name = p_index_name
13. AND table_name = p_table_name
14. AND COLUMN_POSITION = p_COLUMN_POSITION;
15. Return substr (maid expression, 1, 4000 );
16. END;
17. /

1. Set linesize 300

   ;

2. Set pagesize 100;
3. Col c1 format a20;
4. Col c2 format a20;
5. Col c3 format a20;
6. Col c4 format a20;
7. Col c5 format a20;
8. Col INDEX_NAME format a20;
9. SELECT INDEX_NAME,
10. MAX (DECODE (COLUMN_POSITION, 1, COLUMN_NAME | ''| DESCEND, NULL ))
11. C1,
12. MAX (DECODE (COLUMN_POSITION, 2, COLUMN_NAME | ''| DESCEND, NULL ))
13. C2,
14. MAX (DECODE (COLUMN_POSITION, 3, COLUMN_NAME | ''| DESCEND, NULL ))
15. C3,
16. MAX (DECODE (COLUMN_POSITION, 4, COLUMN_NAME | ''| DESCEND, NULL ))
17. C4,
18. MAX (DECODE (COLUMN_POSITION, 5, COLUMN_NAME | ''| DESCEND, NULL ))
19. C5
20. FROM (SELECT a. INDEX_NAME,
21. REPLACE (
22. DECODE (
23. Descend,
24. 'Desc', long_2_varchar (B. index_name,
25. B. table_NAME,
26. B. COLUMN_POSITION ),
27. A. column_name ),
28. '"',
29. '')
30. COLUMN_NAME,
31. A. COLUMN_LENGTH,
32. A. COLUMN_POSITION,
33. DESCEND
34. FROM user_ind_columns
35. LEFT JOIN
36. User_ind_expressions B
37. ON a. index_name = B. index_name
38. AND a. table_name = B. table_name
39. WHERE a. table_name ='t'
40. Order by INDEX_NAME, column_position)
41. Group by INDEX_NAME;