Hello,
I have a trouble with executing my queries under my application written in .Net.
The queries work well and fast when I run them from Toad. But running them from the .Net application is very slow. I do not think that problem is in ODP.Net because when I run it using System.Data.OracleClient classes - the problem is the same.
But if I run the queries under .Net application using Odbc - everything is ok.
I built plans for each case (I did not put them here because I cannot make them readable) and found that the difference is because for Toad Oracle uses TEMP TABLE TRANSFORMATION in the begin of the plan. And for ODP.Net and System.Data.OracleClient it does not.
I cannot rewrite the query because it is automatically generated - I will have to rewrite query generator engine to achieve this.
The question is: why might it happen that behavior is different for Toad/Odbc and for ODP.Net/System.Data.OracleClient?
This is the query:
select t.id, t.name, t.is_active
from v_cost_center_master t
where (nvl(id, (select value from const where id_key='cc_for_orphans')) in (
select cco.cost_center
from v_cost_center_owners cco
where cco.actual_owner_badge = :approver_badge
and nvl(cco.doa_case_id_key,'-') <> :self_doa_partial
)
) and (id in (
select org_cost_center
from v_sb_snap_full
where renewal_id=:renewal_id
)
)
order by t.id asc
Thanks in advance!
p.s. how to put some text in monospaced font?
done!
Edited by: Neco on Sep 19, 2011 10:57 PM
put code into 'code' brackets