I have a complex query with 14 tables in the from clause. In where clause i have 14 AND conditions among them 4 are right outer joins.
It's called from PL/SQL using the java code.
So when the PL/SQL query is called using java code it's running very slow approximately 90 seconds.
If i execute the same query in Oracle developer query is running fast less than 10 seconds.
I have verified all the columns in the where clause are indexed and it won't suppress index ( not used NULL, NOT IN , SUBSTR, athematic operator ).
Why running the same query from the PL/SQL is slow.
I have plan to create a temporary table first insert records with important columns from important tables.
Then update the other columns by fetch the data now it will be simple update query,
Once I captured the data in java side then i will delete the data in temporary table.
Is there any better idea than this.