Simple Oracle Select the query optimization

I have below simple dynamic select query

Select RELATIONSHIP
  from DIME_MASTER
 WHERE CIN=? AND SSN=? AND ACCOUNT_NUMBER=?

The table has 1,083,701 records. This query takes 11 to 12 secs to execute which is expensive. DIME_MASTER table has ACCOUNT, CARD_NUMBER INDEXES. Please help me to optimize this query so that query execution time is under fraction of second.

---

Look at the predicate information:

--------------------------------------
 1 - filter(TO_NUMBER("DIME_MASTER"."SSN")=226550956
            AND TO_NUMBER("DIME_MASTER"."ACCOUNT_NUMBER")=4425050005218650
            AND TO_NUMBER("DIME_MASTER"."CIN")=00335093464)

The type of your columns is NVARCHAR, but parameters in the query are NUMBERs.
Oracle must cast numbers to strings, but it is sometimes not very smart in casting.
Oracles and fortune-tellers are not always right ;)

These casts prevents the query from using indices.

Rewrite the query using explicit conversion into:

Select RELATIONSHIP
  from DIME_MASTER
 WHERE CIN=to_char(?) AND SSN=to_char(?) AND ACCOUNT_NUMBER=to_char(?)

then run this command:

exec dbms_stats.gather_table_stats( user, 'DIME_MASTER' );

and run the query and show us a new explain plan.

Would you please do not paste explain plans here, they are unreadable,
please use pastebin instead, and paste only links here, thank you.

Look at this simple example, it shows why you need explicit casts:

CREATE TABLE "DIME_MASTER" (
  "ACCOUNT_NUMBER" NVARCHAR2(16)
);
insert into dime_master
select round( dbms_random.value( 1, 100000 )) from dual
connect by level <= 100000;
commit;
create index dime_master_acc_ix on dime_master( account_number );

explain plan for select * from dime_master
where account_number = 123;

select * from table( dbms_xplan.display );

Plan hash value: 1551952897

---------------------------------------------------------------------------------
| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             |     3 |    54 |    70   (3)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| DIME_MASTER |     3 |    54 |    70   (3)| 00:00:01 |
---------------------------------------------------------------------------------

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

   1 - filter(TO_NUMBER("ACCOUNT_NUMBER")=123)

explain plan for select * from dime_master
where account_number = to_char( 123 );

select * from table( dbms_xplan.display );
Plan hash value: 3367829596

---------------------------------------------------------------------------------------
| Id  | Operation        | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                    |     3 |    54 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| DIME_MASTER_ACC_IX |     3 |    54 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

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

   1 - access("ACCOUNT_NUMBER"=U'123')