Join query fast in oracle sql developer, super slow in odp.net

I have a sql query (a join) that is super-fast when run on Oracle SQL Developer (milliseconds) but super-slow (minutes) when same query is called from a c# app using ODP.net. The call to OracleDataAdapter.Fill(data table) just waits for oracle.开发者_C百科 What is strange is that previously the join was super-fast in c# as well.

The query:

select t1.col2, t1.col3 from table_1 t1 where exists (select t2.col2, t2.col3 from table_2 t2 where t1.col2 = t2.col2 and t1.col3 = t2.col3)

After some investigation I realized the only change made was the addition of an existing timestamp column as part of the primary key for both table_1 and table_2.

Basically table_1's PK looks like this (Col_TS was added to PK):

Col1 (varchar2), Col2 (varchar2), Col3 (number), Col4 (number), Col_TS (timestamp)

And table_2's PK (Col_TS was added to PK):

Col1 (varchar2), Col2 (varchar2), Col3 (number), Col_TS (timestamp)

But the thing is, I am not using the timestamp columns in my join at all: I am just joining table_1 Col2 to table_2 Col2 and table_1 Col3 to table_3 Col3. Why does the addition of timestamp as part of PK impact the query call from C#?

Additional notes: the only indexes for both tables are the PKs. Should I add an index for the timestamp columns? Running Oracle 10g, .Net 4.

Any insight into the issue is much appreciated.

SQL Developer displays first 50 records only(default option) and the dataset you are interested is large enough which might be the cause for longer time for .Fill(dataset). Can you see how big is the dataset by running a

select count(1) from table_2 t2 where t1.col2 = t2.col2 and t1.col3 = t2.col3)

I had problems with slow OracleDataAdapter.Fill() running and setting larger value to OracleCommand.FetchSize helped to solve them (at least decreased the time of execution).

See details here.