Hey Geluvu watch this:

SQL> create index my_fn_IDX on emp (upper(ename), upper(enamep));
Index created.

SQL> analyze table emp compute statistics for all indexed columns for all
indexes;
Table analyzed.

SQL> alter session set query_rewrite_enabled=true;
Session altered.

SQL> alter session set optimizer_mode=first_rows;
Session altered.

SQL> select * from emp where upper(ename) like 'MARTIN';

EMPNO ENAME JOB MGR HIREDATE SAL COMM
DEPTNO ENAMEP
---------- ---------- --------- ---------- --------- ---------- ----------
---------- --------------------
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400
30 regis


Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=2 Card=6 Bytes=5
22)

1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=2 Card=6 Byte
s=522)

2 1 INDEX (RANGE SCAN) OF 'MY_FN_IDX' (NON-UNIQUE) (Cost=1 C
ard=6)

IMPORATANT:
I've done many tests that I can't unfortunately post... and one thing I
recommend you doing is to use CBO (FIRST_ROWS), You can add it as a hint
though...
A second thing is for You to created *individual indexes* instead of the
*composite FBI index*:
This is because the index will be used *if only if* the driving column(first
column) in the index is used on the where clause.

Try it and let us know please...

Regis
*********************************************************************
This electronic transmission is strictly confidential and intended solely
for the addressee. It may contain information which is covered by legal,
professional or other privilege. If you are not the intended addressee,
you must not disclose, copy or take any action in reliance of this
transmission. If you have received this transmission in error,
please notify the sender as soon as possible.

This footnote also confirms that this message has been swept
for computer viruses.
**********************************************************************

Oracle LazyDBA home page