Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2009
    Location
    Mumbai
    Posts
    29

    Unanswered: Query Optimized with re-ordering of conditions nad tables, want know reason..?

    hi all,

    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.

    example :

    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

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    What is your exact Oracle version and to what is your optimizer goal set? This is a typical behaviour of the RBO (rule based optimizer).
    What are the execution plans for these queries?

    Are you sure the two queries are equivalent. At first glance it seems you are doing a different outer join than before.

    Did you try to rewrite the join syntax using ansi joins?

  3. #3
    Join Date
    Jul 2009
    Location
    Mumbai
    Posts
    29
    My Oracle Version is 9i

    I don't have knowledge of execution plans (i have basic knowldege of writing sql queries)

    The SQL query are generated by java framework Hibernate, i re-ordered the table names only.HIbernate is generating same SQL query with conditions re-ordered w.r.t to tables i keep.

    I am 100% sure the query is same.

    Thanks.

  4. #4
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by scharan07
    My Oracle Version is 9i
    You are aware that this version is no longer supported?

    You'll have to check if you are using the RBO or the CBO.
    http://download.oracle.com/docs/cd/B10501_01/server.920/a96533/optimops.htm#38183

    If you are using the CBO, then you'll have to make sure your statistics are up-to-date:
    http://download.oracle.com/docs/cd/B10501_01/server.920/a96533/stats.htm#13547

    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.
    http://download.oracle.com/docs/cd/B10501_01/server.920/a96533/ex_plan.htm#19259

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •