Thanks Sanjay.

I have below query, taking 1 min over to fetch a record from over 10Million rows table. Which is not considerable.

SELECT USER_NO,VALUE_LEFT,EXPIRE_DATE
FROM MST_USER
WHERE ACCOUNT_NO != '7594299380771992' /*'3839059713682615'*/
AND SALE_STATUS='V'
AND (MST_USER.STATUS_ID='A' OR MST_USER.STATUS_ID='C')
AND VALUE_LEFT = 0
AND ROWNUM <= 1

I have an composite Index on this table based on ACCOUNT_NO,SALE_STATUS, STATUS_ID, VALUE_LEFT. If I see explain plan for this statement it is still FTS. Help me to tune this statement.

Explain Plan:
LEVEL ID OPERATION OPTIONS OBJECT POSITION
---------- --- ------------------------------ ------------------ -------------------- ----------
1 0 SELECT STATEMENT cost=9232
2 1 COUNT STOPKEY
3 2 TABLE ACCESS FULL MST_USER


If I use INDEX Hint :

LEVEL ID OPERATION OPTIONS OBJECT POSITION
---------- --- ------------------------------ ------------------ -------------------- ----------
1 0 SELECT STATEMENT cost=56057
2

2 1 COUNT STOPKEY
3 2 TABLE ACCESS BY INDEX ROWID MST_USER
4 3 INDEX FULL SCAN MSTUSERIDX


Can you please help me to tune this statement.

Regards