Oracle模拟MySQL的show index from table命令_MySQL

Oracle模拟MySQL的show index from table命令

实验数据初始化:

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

显示该表所有索引的信息。

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

以普通用户登录

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. l_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 ( l_COLUMN_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 a
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 ;