We help IT Professionals succeed at work.

Simple Query

ajexpert
ajexpert asked
on
Medium Priority
958 Views
Last Modified: 2013-12-19
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

Comment
Watch Question

Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
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
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
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

Database Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012
Commented:
you can also use xmlaggregation



SELECT count(*),index_name,rtrim
           (replace
                (replace
                     (xmlagg(xmlelement("x", column_name)).getclobval(),
                      '<x>',
                      NULL),
                 '</x>',
                 ','),
            ',')
FROM all_ind_columns
group by index_name

Author

Commented:
Sean,
I just want to query ALL_IND_COLUMNS and acheive the desired result.
Can't it be done without stragg  ?
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
yes, I posted an alternate, but stragg is so much simpler and is reusable for any kind of string aggregration problems like this.

Author

Commented:
Thanks Sean, Sorry missed ur alternate query.
But, the result column is of clob type any ideas to get VARCHAR column type?
 
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012
Commented:
change getclobval()  to getstringval()

and it will be returned as a varchar2

Author

Commented:
Thanks Sean!!
Sean StuberDatabase Developer & Administrator
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2012

Commented:
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;

Author

Commented:
I missed one point, i need to order by the column_position ascending.  Can you please give me the query

Author

Commented:
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 :)