Oracle模拟MySQL的show index from table命令


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;
  16. RETURN SUBSTR (l_COLUMN_EXPRESSION, 1, 4000);
  17. END;
  18. /

 

  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;

相关内容