Oracle模擬MySQL的show index from table命令

壹頁書發表於2014-06-25
OracleMySqlIndex
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;



但是可以看到,以倒序建立的索引欄位，都是以SYS等命名。
Oracle把這種倒序建立的索引欄位看成函式索引。
它的資訊儲存在user_ind_expressions檢視

user_ind_expressions檢視的COLUMN_EXPRESSION欄位型別是long型。

王工的版本可以解決這個問題
  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;

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29254281/viewspace-1193391/，如需轉載，請註明出處，否則將追究法律責任。

相關文章