Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2008
    Posts
    26

    Unanswered: Best way to analyze a sql query

    Hi Guys,

    I just wanted to learn what is the best way to analyze a complex query.

    Actually i understand the types of joins in oracle sql, but when i have a complex query im not sure where to start from when im analyzing it.

    For instance we have such a query.

    select ...
    from tab1,
    tab2,
    tab3
    tab4,
    tab5
    where
    tab1.col1 = ''
    tab2.col3 = ''
    tab2.col1(+) = tab3.col2
    tab4.col2 = tab5.col3(+)

    I this case, im not sure whether i shall look at the equi join first, or left/right outer join.

    How do we know which is the base table to be used

    If i remove tab4.col2 = tab5.col3(+), then i know all rows in tab2 will be retained. But if i add tab4.col2 = tab5.col3(+), im not sure exactly whats happening when theres too many outer/inner/self joins. Can be really confusing at times, i just need some guidelines to get started analzying queries with multiple joins. thanks alot for any response

  2. #2
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by ajitpal.s
    If i remove tab4.col2 = tab5.col3(+), then i know all rows in tab2 will be retained. But if i add tab4.col2 = tab5.col3(+), im not sure exactly whats happening when theres too many outer/inner/self joins. Can be really confusing at times, i just need some guidelines to get started analzying queries with multiple joins. thanks alot for any response
    Regardless on the condition you mentioned the relationship between TAB2 and TAB3 (tab2.col1(+) = tab3.col2) will stay the same. By the way, all rows from TAB3 will be taken but only when CROSS JOINED tables contain any row (see below).

    In the join type list, you forgot CROSS JOIN (cartesian product) - it will be used when no joining condition is present among tables. In the original example, TAB1 will be cross joined with (TAB2,TAB3) and (TAB4,TAB5). The order may be any - it is Oracle job to choose the (sub)optimal one. You may read the currently used join order from the explain plan belonging to that query.

    Maybe you would benefit from using ANSI JOIN syntax, as it directly specifies join types among tables.

  3. #3
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Code:
    tab1.col1 = ''
    tab2.col3 = ''
    will not work.
    There is no such thing as an empty string in Oracle. An empty string is null in Oracle.
    You will need to write
    Code:
    WHERE tab1.col1 IS NULL 
      AND tab2.col2 IS NULL
    (I just assumed that it is an AND between the conditions as you did not provide a syntactically correct statement)

  4. #4
    Join Date
    Nov 2008
    Posts
    26
    thanks for the replies guys, appreciate it..

Posting Permissions

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