Re: Index and Indexed columns

From: Barbara Boehmer <baboehme_at_hotmail.com>
Date: 10 Jun 2005 15:25:41 -0700
Message-ID: <1118442341.832772.265110@g47g2000cwa.googlegroups.com>

There are various ways to do this. I have demonstrated a few ways below.

scott_at_ORA92> -- sql only:
scott_at_ORA92> COLUMN columns_indexed FORMAT A15 WORD_WRAPPED
scott_at_ORA92> SELECT index_name,
  2  	    MAX (SUBSTR (SYS_CONNECT_BY_PATH (column_name, ', '), 3))
  3  	      AS columns_indexed
  4  FROM   (SELECT index_name, column_name,
  5  		    ROW_NUMBER () OVER
  6  		      (PARTITION BY index_name
  7  		       ORDER BY column_position) AS curr,
  8  		    ROW_NUMBER () OVER
  9  		      (PARTITION BY index_name
 10  		       ORDER BY column_position) - 1 AS prev
 11  	     FROM   user_ind_columns
 12  	     WHERE  table_name IN ('TEST_TAB', 'COMPANY'))

 13 START WITH curr = 1
 14 CONNECT BY PRIOR curr = prev AND PRIOR index_name = index_name  15 GROUP BY index_name
 16 ORDER BY index_name
 17 /

INDEX_NAME                     COLUMNS_INDEXED


------------------------------ ---------------

COMPANY_KEYWORD_INDEX          DUMMY
INDX1                          A, B
INDX2                          C, D, E
INDX3                          A

scott_at_ORA92> -- anonymous pl/sql block
scott_at_ORA92> -- (or you could put the same code in a procedure):

scott_at_ORA92> SET SERVEROUTPUT ON
scott_at_ORA92> DECLARE
  2 v_columns VARCHAR2(32767);
  3 BEGIN
  4 DBMS_OUTPUT.PUT_LINE
  5 ('INDEX_NAME COLUMNS_INDEXED');   6 DBMS_OUTPUT.PUT_LINE

  7  	 ('------------------------------  ---------------');
  8    FOR r_ind IN
  9  	 (SELECT index_name
 10  	  FROM	 user_indexes
 11  	  WHERE  table_name IN ('TEST_TAB', 'COMPANY')
 12  	  ORDER  BY index_name)
 13    LOOP
 14  	 v_columns := NULL;
 15  	 FOR r_col IN
 16  	   (SELECT column_name
 17  	    FROM   user_ind_columns
 18  	    WHERE  index_name = r_ind.index_name
 19  	    ORDER  BY column_position)
 20  	 LOOP
 21  	   v_columns := v_columns || ', ' || r_col.column_name;
 22  	 END LOOP;
 23  	 v_columns := LTRIM (v_columns, ', ');
 24  	 DBMS_OUTPUT.PUT_LINE
 25  	   (RPAD (r_ind.index_name, 32) || v_columns);

 26 END LOOP;
 27 END;
 28 /

INDEX_NAME                      COLUMNS_INDEXED
------------------------------  ---------------
COMPANY_KEYWORD_INDEX           DUMMY
INDX1                           A, B
INDX2                           C, D, E
INDX3                           A

PL/SQL procedure successfully completed.

scott_at_ORA92> -- your own user-defined function specific to the situation,
scott_at_ORA92> -- that "returns the columns as a varchar2 for a given index"
scott_at_ORA92> -- as suggested by Gerald H. Pille: scott_at_ORA92> CREATE OR REPLACE FUNCTION your_func   2 (p_index_name IN VARCHAR2,

  3  	p_delimiter  IN VARCHAR2 DEFAULT ', ')
  4    RETURN		VARCHAR2
  5  AS
  6   v_columns 	VARCHAR2(32767);

  7 BEGIN
  8 FOR rec IN

  9  	 (SELECT column_name
 10  	  FROM	 user_ind_columns
 11  	  WHERE  index_name = p_index_name
 12  	  ORDER  BY column_position)
 13    LOOP
 14  	 v_columns := v_columns || p_delimiter || rec.column_name;

 15 END LOOP;
 16 RETURN LTRIM (v_columns, p_delimiter);  17 END your_func;
 18 /

Function created.

scott_at_ORA92> SHOW ERRORS
No errors.
scott_at_ORA92> SELECT index_name,
  2 your_func (index_name) AS columns_indexed   3 FROM user_indexes
  4 WHERE table_name IN ('TEST_TAB', 'COMPANY')   5 ORDER BY index_name
  6 /

INDEX_NAME                     COLUMNS_INDEXED


------------------------------ ---------------

COMPANY_KEYWORD_INDEX          DUMMY
INDX1                          A, B
INDX2                          C, D, E
INDX3                          A

scott_at_ORA92> -- Tom Kyte's stragg user-defined aggregate function

scott@ORA92> -- (search for stragg on http://asktom.oracle.com): scott_at_ORA92> SELECT index_name,
  2 stragg (column_name) AS columns_indexed   3 FROM user_ind_columns
  4 WHERE table_name IN ('TEST_TAB', 'COMPANY')   5 GROUP BY index_name
  6 ORDER BY index_name
  7 /

INDEX_NAME                     COLUMNS_INDEXED


------------------------------ ---------------

COMPANY_KEYWORD_INDEX          DUMMY
INDX1                          A,B
INDX2                          C,D,E
INDX3                          A

scott_at_ORA92> Received on Fri Jun 10 2005 - 17:25:41 CDT