Unanswered: Query Optimized with re-ordering of conditions nad tables, want know reason..?
i have query which is complex with joins on 3 tables, i just re arranged the order of tables and corresponding table conditions in where clause it increased the performance can i know what is the reason behind this.
select a,b,c from I, S, C, A
where I.c1 = s.c1(+) and I.c2 = C.c2(+) and I.c3 = A.c3 e.t.c
order by c4 DESC
is been re arranged to
select a,b,c from I, A, C, S
where I.c1 = A.c1(+) and I.c2 = C.c2(+) and I.c3 = S.c3
order by c4 DESC
the second query is improved perfomace by 6 times faster than first query, can i know the reason by reordering of the tables and its conditions in where caluse has imporved the perfomance
You are aware that this version is no longer supported?
You'll have to check if you are using the RBO or the CBO.
If you are using the CBO, then you'll have to make sure your statistics are up-to-date:
The CBO in 9i is not as sophisticated as newer ones. It could very well be that this is a problem of your outdated Oracle version.
I have never seen a query change that dramatically only because of re-ordering of the tables.
So I would be tempted to guess that your installation is using the RBO (which is a really bad idea).
If you are indeed using the CBO, then my first guess is, that your statistics are outdated.
I don't have knowledge of execution plans (i have basic knowldege of writing sql queries)
Then you should start learning that. It is the most basic that you will need when working with any DBMS.
The SQL query are generated by java framework Hibernate, i re-ordered the table names only.
Sigh... Hibernate... No further comment.
Can you tell Hibernate to use ANSI syntax instead? Maybe that changes something.