non-prefixed indexes on composite partitioned tables

Amusing background: My current shop has 90 instances. The previous data architect read about partitioning and thought it sounded good. A couple tests showed better response, so he said to himself, "If partitioning is good, subpartitioning must be even better!" So every table over 50K rows here is composite-hash partitioned, pre-created back in 2003 out to 2006. Some of them have 394 subpartitions. No archival strategy. Every row is still here. One of the largest, the one in my example below has 55 columns, and 13 indexes so far, some of them heinous composites. My favorite: on tables where there wasn't an obvious, selective candidate for the hash, he picked any ol' column, often favoring one that has a couple of distinct values.

</END VENT>

Anyway, so I'm developing a quick report to give me a bird's eye of the mess, er I mean the tables, indexes, partitioning on each, etc.

user_part_indexes seems to be reporting that certain indexes are non-prefixed, even when they use the table's range key as their first column.

For example, table BOGUS (table and column names changed) is range partitioned on mydate and hash partitioned on mycode. Here is a sampling of a few indexes on BOGUS, gleaned from user_part_tables and user_part_indexes

BOGUS                       RANGE(mydate) + HASH(mycode)
    BOGUS_PK                NORMAL GLOBAL NON-PARTITIONED
      columns:  c1
    BOGUS_UK                RANGE(mydate) + HASH(mycode)LOCAL PREFIXED
      columns:  mydate c2 c3 c4 mycode c5 c6 c7 c8 c9 c10 c11 c12 c13 c14
c15 c16
    BOGUS_COMP2_LIDX        RANGE(mydate) + HASH(mycode)LOCAL NON_PREFIXED
      columns:  c6 mkt_cd mydate mycode c4 c2
    BOGUS_COMP3_LIDX        RANGE(mydate) + HASH(mycode)LOCAL PREFIXED
      columns:  mydate end_dt_gmt c14 c2 c3 c4 mycode c10 c11 c6 mkt_cd c1
    BOGUS_COMP4_LIDX        RANGE(mydate) + HASH(mycode)LOCAL NON_PREFIXED
      columns:  mydate c16
    BOGUS_COMP6_LIDX        RANGE(mydate) + HASH(mycode)LOCAL NON_PREFIXED
      columns:  mycode c3
    BOGUS_COMP7_LIDX        RANGE(mydate) + HASH(mycode)LOCAL NON_PREFIXED
      columns:  mydate c2 end_dt_gmt c6 mkt_cd

I expected that indexes leading with the range-keyed date would be considered PREFIXED, like the UK, comp3, comp4 and comp7. Instead comp4 and comp7 are non-prefixed. Why? I can see the relationship that you seem to need both mydate and mycode to be prefixed, but if so, why wouldn't mycode be required as the second column, as mydate is required as the first in order to be prefixed?

--
http://www.freelists.org/webpage/oracle-l