当前位置:Gxlcms > mysql > Oracle模拟MySQL的showindexfromtable命令_MySQL

Oracle模拟MySQL的showindexfromtable命令_MySQL

时间:2021-07-01 10:21:17 帮助过:5人阅读

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 ;

人气教程排行