Oracle模拟MySQL的showindexfromtable命令_MySQL

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了Oracle模拟MySQL的showindexfromtable命令_MySQL，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含3682字，纯文字阅读大概需要6分钟。

内容图文

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 ;

内容总结

以上是互联网集市为您收集整理的Oracle模拟MySQL的showindexfromtable命令_MySQL全部内容，希望文章能够帮你解决Oracle模拟MySQL的showindexfromtable命令_MySQL所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端

---