5 Replies Latest reply: Feb 15, 2013 8:44 AM by APC RSS

    What data dictionary view can tell me which index columns are compressed?

      So I can create a compressed index on a table like this:

      create index t23_idx on t23(col3, col5, col1) compress 2;

      This creates an index with a compression degree of 2. So far so good.

      But how can I find out what columns are actually compressed, at some later date? The USER_INDEX view tells me whether COMPRESSION is enabled but there is nothing on the USER_IND_COLUMNS view to tell which columns are compressed.

      Obviously I can recover the information through DBMS_METADATA.GET_DDL but I'm really after a query, as that's more convenient.

      I had hopes for SYS.ICOL$.SPARE1 but that doesn't appear to be consistent with what I'm expecting. However, if anybody can confirm that this column is indeed the badger then I will gladly adjust my expectations.

      Cheers, APC