Link to home
Start Free TrialLog in
Avatar of ajexpert
ajexpertFlag for United States of America

asked on

Simple Query

Hi Experts,
I am trying to get all the indexed column names from ALL_IND_COLUMNS table for a particular table.
What I need is if the column has composite index, (i.e. COUNT(IDX) > 1)  the new column populated (INDEX_COLUMNS) should contain the column names separated by ",".  
Here is the example of what I am expecting





COUNT(IDX)	       INDEX_NAME	INDEX_COLUMNS
2	       IDX_A_B	            A,B
3	       IDX_A_B_C	            A,B,C
1	       IDX_A	                                 A

Open in new window

Avatar of Sean Stuber
Sean Stuber

if you have stragg  (I highly recommend it)

  SELECT COUNT( * ), index_name, stragg(column_name)
    FROM all_ind_columns
GROUP BY index_name
  HAVING COUNT( * ) > 1
here's the code to create stragg
CREATE OR REPLACE TYPE string_agg_type
AS
    OBJECT(total varchar2(4000),
           STATIC FUNCTION odciaggregateinitialize(sctx IN OUT string_agg_type)
               RETURN number,
           MEMBER FUNCTION odciaggregateiterate(self IN OUT string_agg_type, VALUE IN varchar2)
               RETURN number,
           MEMBER FUNCTION odciaggregateterminate(self IN string_agg_type, returnvalue OUT varchar2,
           flags IN number)
               RETURN number,
           MEMBER FUNCTION odciaggregatemerge(self IN OUT string_agg_type, ctx2 IN string_agg_type)
               RETURN number);
 
CREATE OR REPLACE TYPE BODY string_agg_type
IS
    STATIC FUNCTION odciaggregateinitialize(sctx IN OUT string_agg_type)
        RETURN number
    IS
    BEGIN
        sctx   := string_agg_type(NULL);
        RETURN odciconst.success;
    END;
 
    MEMBER FUNCTION odciaggregateiterate(self IN OUT string_agg_type, VALUE IN varchar2)
        RETURN number
    IS
    BEGIN
        self.total   := self.total || ',' || VALUE;
        RETURN odciconst.success;
    END;
 
    MEMBER FUNCTION odciaggregateterminate(self IN string_agg_type, returnvalue OUT varchar2,
    flags IN number)
        RETURN number
    IS
    BEGIN
        returnvalue   := LTRIM(self.total, ',');
        RETURN odciconst.success;
    END;
 
    MEMBER FUNCTION odciaggregatemerge(self IN OUT string_agg_type, ctx2 IN string_agg_type)
        RETURN number
    IS
    BEGIN
        self.total   := self.total || ctx2.total;
        RETURN odciconst.success;
    END;
END;
 
CREATE OR REPLACE FUNCTION stragg(input varchar2)
    RETURN varchar2
    PARALLEL_ENABLE
    AGGREGATE USING string_agg_type;

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Sean Stuber
Sean Stuber

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of ajexpert

ASKER

Sean,
I just want to query ALL_IND_COLUMNS and acheive the desired result.
Can't it be done without stragg  ?
yes, I posted an alternate, but stragg is so much simpler and is reusable for any kind of string aggregration problems like this.
Thanks Sean, Sorry missed ur alternate query.
But, the result column is of clob type any ideas to get VARCHAR column type?
 
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks Sean!!
here's another type of xml aggregation


SELECT   count(*),index_name,
         LTRIM(EXTRACT(XMLAGG(XMLELEMENT("V", ',' || column_name)),
                       '/V/text()'
                      ),
               ','
              ) columns
    FROM all_ind_columns
GROUP BY index_name;
I missed one point, i need to order by the column_position ascending.  Can you please give me the query
continuation...
in other words, the comma separted columns should appear by column_position 1,2,3,...n
will grant extra points to you in my next query :)