ops$tkyte%ORA10GR2> CREATE TABLE t
ops$tkyte%ORA10GR2> (
ops$tkyte%ORA10GR2>   dt  date,
ops$tkyte%ORA10GR2>   x   int,
ops$tkyte%ORA10GR2>   y   varchar2(30)
ops$tkyte%ORA10GR2> )
ops$tkyte%ORA10GR2> PARTITION BY RANGE (dt)
ops$tkyte%ORA10GR2> (
ops$tkyte%ORA10GR2>   PARTITION part1 VALUES LESS THAN (to_date('13-mar-2003','dd-mon-yyyy')) ,
ops$tkyte%ORA10GR2>   PARTITION part2 VALUES LESS THAN (to_date('14-mar-2003','dd-mon-yyyy')) ,
ops$tkyte%ORA10GR2>   PARTITION junk VALUES LESS THAN (MAXVALUE)
ops$tkyte%ORA10GR2> )
ops$tkyte%ORA10GR2> /
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> insert into t select to_date( '12-mar-2003', 'dd-mon-yyyy')+mod(rownum,3), mod(rownum,5), rpad('*',30,'*') from all_objects;
ops$tkyte%ORA10GR2> */
ops$tkyte%ORA10GR2> drop index t_idx;

Index dropped.

ops$tkyte%ORA10GR2> create index t_idx on t(dt,x,y) local;

Index created.

ops$tkyte%ORA10GR2> select partition_name, bytes from user_segments where segment_name = 'T_IDX';

PARTITION_NAME                      BYTES
------------------------------ ----------
PART1                             2097152
PART2                             2097152
JUNK                              2097152

ops$tkyte%ORA10GR2> drop index t_idx;

Index dropped.

ops$tkyte%ORA10GR2> create index t_idx on t(dt,x,y) compress 3 local;

Index created.

ops$tkyte%ORA10GR2> select partition_name, bytes from user_segments where segment_name = 'T_IDX';

PARTITION_NAME                      BYTES
------------------------------ ----------
PART1                              262144
PART2                              262144
JUNK                               262144

ops$tkyte%ORA11GR2> CREATE TABLE t
  2  (
  3    dt  date,
  4    x   int,
  5    y   varchar2(30)
  6  )
  7  PARTITION BY RANGE (x)
  8  (
  9    PARTITION part1 VALUES LESS THAN (1),
 10    PARTITION part2 VALUES LESS THAN (2)
 11  )
 12  /

Table created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> create index t_idx on t(y) local tablespace users;

Index created.

ops$tkyte%ORA11GR2> select partition_name, tablespace_name from user_ind_partitions where index_name = 'T_IDX';

PARTITION_NAME                 TABLESPACE_NAME
------------------------------ ------------------------------
PART1                          USERS
PART2                          USERS

ops$tkyte%ORA11GR2> alter index t_idx modify default attributes tablespace p1;

Index altered.

ops$tkyte%ORA11GR2> alter table t add partition part3 values less than (3);

Table altered.

ops$tkyte%ORA11GR2> select partition_name, tablespace_name from user_ind_partitions where index_name = 'T_IDX';

PARTITION_NAME                 TABLESPACE_NAME
------------------------------ ------------------------------
PART1                          USERS
PART2                          USERS
PART3                          P1

ops$tkyte%ORA10GR2> CREATE TABLE t
  2  (
  3    dt  date,
  4    x   int,
  5    y   varchar2(30)
  6  )
  7  PARTITION BY RANGE (dt)
  8  (
  9    PARTITION part1 VALUES LESS THAN (to_date('13-mar-2003','dd-mon-yyyy')) ,
 10    PARTITION part2 VALUES LESS THAN (to_date('14-mar-2003','dd-mon-yyyy')) ,
 11    PARTITION junk VALUES LESS THAN (MAXVALUE)
 12  )
 13  /

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create index t_idx1 on t(x) local;

Index created.

ops$tkyte%ORA10GR2> create index t_idx2 on t(y) global partition by range(y)
  2  ( partition x values less than ('x'),
  3    partition y values less than ( maxvalue )
  4  );

Index created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select index_name, locality from user_part_indexes where index_name like 'T_IDX_';

INDEX_NAME                     LOCALI
------------------------------ ------
T_IDX1                         LOCAL
T_IDX2                         GLOBAL

Local Indexes
Oracle makes a distinction between the following two types of local indexes:
 * Local prefixed indexes: These are indexes whereby the partition keys are on the leading edge of the index definition. For example, if a table is range partitioned on a column named LOAD_DATE, a local prefixed index on that table would have LOAD_DATE as the first column in its column list.
 * Local nonprefixed indexes: These indexes do not have the partition key on the leading edge of their column list. The index may or may not contain the partition key columns.
Both types of indexes are able to take advantage of partition elimination, both can support uniqueness (as long as the non-prefixed index includes the partition key), and so on. The fact is that a query that uses a local prefixed index will always allow for index partition elimination, whereas a query that uses a local non-prefixed index might not. This is why local non-prefixed indexes are said to be “slower” by some people—they do not enforce partition elimination (but they do support it). 
There is nothing inherently better about a local prefixed index as opposed to a local nonprefixed index when that index is used as the initial path to the table in a query. What I mean by that is that if the query can start with “scan an index” as the first step, there isn’t much difference between a prefixed and a nonprefixed index. 
Partition Elimination Behavior
For the query that starts with an index access, whether or not it can eliminate partitions from consideration all really depends on the predicate in your query. A small example will help demonstrate this. The following code creates a table, PARTITIONED_TABLE, that is range partitioned on a numeric column A such that values less than two will be in partition PART_1 and values less than three will be in partition PART_2:
ops$tkyte@ORA11GR2> CREATE TABLE partitioned_table
  2  ( a int,
  3    b int,
  4    data char(20)
  5  )
  6  PARTITION BY RANGE (a)
  7  (
  8  PARTITION part_1 VALUES LESS THAN(2) tablespace p1,
  9  PARTITION part_2 VALUES LESS THAN(3) tablespace p2
 10  )
 11  /
Table created.
We then create both a local prefixed index, LOCAL_PREFIXED, and a local nonprefixed index, LOCAL_NONPREFIXED. Note that the nonprefixed index does not have A on the leading edge of its definition, which is what makes it a nonprefixed index:
ops$tkyte@ORA11GR2> create index local_prefixed on partitioned_table (a,b) local;
Index created.

ops$tkyte@ORA11GR2> create index local_nonprefixed on partitioned_table (b) local;
Index created.
Next, we’ll insert some data into one partition and gather statistics:
ops$tkyte@ORA11GR2> insert into partitioned_table
  2  select mod(rownum-1,2)+1, rownum, 'x'
  3    from all_objects;
72771 rows created.

ops$tkyte@ORA11GR2> begin
  2     dbms_stats.gather_table_stats
  3     ( user,
  4      'PARTITIONED_TABLE',
  5       cascade=>TRUE );
  6  end;
  7  /
PL/SQL procedure successfully completed.
We take offline tablespace P2, which contains the PART_2 partition for both the tables and indexes:
ops$tkyte@ORA11GR2> alter tablespace p2 offline;
Tablespace altered.
Taking tablespace P2 offline will prevent Oracle from accessing those specific index partitions. It will be as if we had suffered “media failure,” causing them to become unavailable. Now we’ll query the table to see what index partitions are needed by different queries. This first query is written to permit the use of the local prefixed index:
ops$tkyte@ORA11GR2> select * from partitioned_table where a = 1 and b = 1;
         A          B DATA
---------- ---------- --------------------
         1          1 x
That query succeeded, and we can see why by reviewing the explain plan. We’ll use the built-in package DBMS_XPLAN to see what partitions this query accesses. The PSTART (partition start) and PSTOP (partition stop) columns in the output show us exactly what partitions this query needs to have online and available in order to succeed:
ops$tkyte@ORA11GR2> delete from plan_table;
4 rows deleted.

ops$tkyte@ORA11GR2> explain plan for
  2  select * from partitioned_table where a = 1 and b = 1;
Explained.

ops$tkyte@ORA11GR2> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------
| Operation                          | Name              | Rows  | Pstart| Pstop |
----------------------------------------------------------------------------------
| SELECT STATEMENT                   |                   |     1 |       |       |
|  PARTITION RANGE SINGLE            |                   |     1 |     1 |     1 |
|   TABLE ACCESS BY LOCAL INDEX ROWID| PARTITIONED_TABLE |     1 |     1 |     1 |
|    INDEX RANGE SCAN                | LOCAL_PREFIXED    |     1 |     1 |     1 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("A"=1 AND "B"=1)
Note The DBMS_XPLAN output has been edited to remove information that was not relevant, in order to permit the examples to fit on the printed page.
So, the query that uses LOCAL_PREFIXED succeeds. The optimizer was able to exclude PART_2 of LOCAL_PREFIXED from consideration because we specified A=1 in the query, and we can see that clearly in the plan PSTART and PSTOP are both equal to 1. Partition elimination kicked in for us. The second query fails, however:
ops$tkyte@ORA11GR2> select * from partitioned_table where b = 1;
ERROR:
ORA-00376: file 13 cannot be read at this time
ORA-01110: data file 13: '/home/ORA11GR2/.../o1_mf_p2_1dzn8jwp_.dbf'
no rows selected
And using the same technique, we can see why:
ops$tkyte@ORA11GR2> delete from plan_table;
4 rows deleted.

ops$tkyte@ORA11GR2> explain plan for
  2  select * from partitioned_table where b = 1;
Explained.

ops$tkyte@ORA11GR2> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------
| Operation                          | Name              | Rows  | Pstart| Pstop |
----------------------------------------------------------------------------------
| SELECT STATEMENT                   |                   |     1 |       |       |
|  PARTITION RANGE ALL               |                   |     1 |     1 |     2 |
|   TABLE ACCESS BY LOCAL INDEX ROWID| PARTITIONED_TABLE |     1 |     1 |     2 |
|    INDEX RANGE SCAN                | LOCAL_NONPREFIXED |     1 |     1 |     2 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("B"=1)
Here the optimizer was not able to remove PART_2 of LOCAL_NONPREFIXED from consideration—it needed to look in both the PART_1 and PART_2 partitions of the index to see if B=1 was in there. Herein lies a performance issue with local nonprefixed indexes: they do not make you use the partition key in the predicate as a prefixed index does. It is not that prefixed indexes are better; it’s just that in order to use them, you must use a query that allows for partition elimination.
If we drop the LOCAL_PREFIXED index and rerun the original successful query as follows:
ops$tkyte@ORA11GR2> drop index local_prefixed;
Index dropped.

ops$tkyte@ORA11GR2> select * from partitioned_table where a = 1 and b = 1;
         A          B DATA
---------- ---------- --------------------
         1          1 x
it succeeds, but as we’ll see, it used the same index that just a moment ago failed us. The plan shows that Oracle was able to employ partition elimination here—the predicate A=1 was enough information for the database to eliminate index partition PART_2 from consideration:
ops$tkyte@ORA11GR2> delete from plan_table;
4 rows deleted.

ops$tkyte@ORA11GR2> explain plan for
  2  select * from partitioned_table where a = 1 and b = 1;
Explained.

ops$tkyte@ORA11GR2> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------
| Operation                          | Name              | Rows  | Pstart| Pstop |
----------------------------------------------------------------------------------
| SELECT STATEMENT                   |                   |     1 |       |       |
|  PARTITION RANGE SINGLE            |                   |     1 |     1 |     1 |
|   TABLE ACCESS BY LOCAL INDEX ROWID| PARTITIONED_TABLE |     1 |     1 |     1 |
|    INDEX RANGE SCAN                | LOCAL_NONPREFIXED |     1 |     1 |     1 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("A"=1)
   3 - access("B"=1)
Note the PSTART and PSTOP column values of 1 and 1.This proves that the optimizer is able to perform partition elimination even for nonprefixed local indexes.
If you frequently query the preceding table with the following queries:
select ... from partitioned_table where a = :a and b = :b;
select ... from partitioned_table where b = :b;
then you might consider using a local nonprefixed index on (b,a). That index would be useful for both of the preceding queries. The local prefixed index on (a,b) would be useful only for the first query.
The bottom line here is that you should not be afraid of nonprefixed indexes or consider them as major performance inhibitors. If you have many queries that could benefit from a nonprefixed index as outlined previously, then you should consider using one. The main concern is to ensure that your queries contain predicates that allow for index partition elimination whenever possible. The use of prefixed local indexes enforces that consideration. The use of nonprefixed indexes does not. Consider also how the index will be used. If it will be used as the first step in a query plan, there are not many differences between the two types of indexes. 

SQL> create table t (c1 int, c5 int, c6 int)
  2  partition by list (c1 )
  3  (
  4   partition p1 values(1),
  5   partition p2 values(2),
  6   partition p3 values(3)
  7  );

Table created.

SQL>
SQL> insert /*+ APPENND */ into t
  2  select c1,x,x
  3  from
  4  ( select rownum c1 from dual connect by level <= 3 ),
  5  ( select  rownum x from dual connect by level <= 1000000 );

3000000 rows created.

SQL>
SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats('','T')

PL/SQL procedure successfully completed.

SQL>
SQL> create index ix1 on t ( c5,c6);

Index created.

SQL>
SQL> set autotrace traceonly explain
SQL> select * from t
  2  where c1 = 2
  3  and   c5 = 12345
  4  and   c6 = 12345;

Execution Plan
----------------------------------------------------------
Plan hash value: 1145707456

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |      |     1 |    13 |     6   (0)| 00:00:01 |       |       |
|*  1 |  TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| T    |     1 |    13 |     6   (0)| 00:00:01 |     2 |     2 |
|*  2 |   INDEX RANGE SCAN                         | IX1  |     3 |       |     3   (0)| 00:00:01 |       |       |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("C1"=2)
   2 - access("C5"=12345 AND "C6"=12345)

SQL>
SQL> drop index ix1;

Index dropped.

SQL> create index ix1 on t ( c5,c6) local;

Index created.

SQL>
SQL> set autotrace traceonly explain
SQL> select * from t
  2  where c1 = 2
  3  and   c5 = 12345
  4  and   c6 = 12345;

Execution Plan
----------------------------------------------------------
Plan hash value: 3177884563

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |      |     1 |    13 |     4   (0)| 00:00:01 |       |       |
|   1 |  PARTITION LIST SINGLE                     |      |     1 |    13 |     4   (0)| 00:00:01 |     2 |     2 |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| T    |     1 |    13 |     4   (0)| 00:00:01 |     2 |     2 |
|*  3 |    INDEX RANGE SCAN                        | IX1  |     1 |       |     3   (0)| 00:00:01 |     2 |     2 |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("C5"=12345 AND "C6"=12345)

SQL>
SQL>
SQL>

SQL> create table t (c1 int, c5 int, c6 int, z int)
  2  partition by range (c1 )
  3  (
  4   partition p1 values less than (1000000),
  5   partition p2 values less than (2000000),
  6   partition p3 values less than (3000000)
  7  );

Table created.

SQL>
SQL> insert /*+ APPENND */ into t
  2  select c1*800000+mod(rownum,50),mod(x,5000),mod(x,5000),0
  3  from
  4  ( select rownum c1 from dual connect by level <= 3 ),
  5  ( select  rownum x from dual connect by level <= 1000000 );

3000000 rows created.

SQL>
SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats('','T')

PL/SQL procedure successfully completed.

SQL>
SQL> create index ix1 on t ( c5,c6) local;

Index created.

SQL>
SQL> set autotrace traceonly stat
SQL> select count(z) from t
  2  where c1 = 800012
  3  and   c5 = 2345
  4  and   c6 = 2345;

1 row selected.


Statistics
----------------------------------------------------------
          5  recursive calls
         10  db block gets
        208  consistent gets
         10  physical reads
       2076  redo size
        549  bytes sent via SQL*Net to client
        624  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>
SQL> drop index ix1;

Index dropped.

SQL> create index ix1 on t ( c1,c5,c6) local;

Index created.

SQL>
SQL> set autotrace traceonly stat
SQL> select count(z) from t
  2  where c1 = 800012
  3  and   c5 = 2345
  4  and   c6 = 2345;

1 row selected.


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          3  consistent gets
          2  physical reads
          0  redo size
        549  bytes sent via SQL*Net to client
        624  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>
SQL>