This discussion is archived
10 Replies Latest reply: Mar 20, 2013 2:15 PM by sufiyan RSS

Few questions

sufiyan Newbie
Currently Being Moderated
Hi all,

I am writing a query which involves about 12 or 13 tables. Most of my columns are coming from 2 or 3 tables but i have another database from which 10 or more tables, i need.

I just wanted to know what should be my best practice for this query?

Should i just write the query in the simplest form or i can follow some other practice?
  • 1. Re: Few questions
    sb92075 Guru
    Currently Being Moderated
    Z KHAN wrote:
    Hi all,

    I am writing a query which involves about 12 or 13 tables. Most of my columns are coming from 2 or 3 tables but i have another database from which 10 or more tables, i need.

    I just wanted to know what should be my best practice for this query?

    Should i just write the query in the simplest form or i can follow some other practice?
    write the SQL such that you get the desired results.

    First make it work, then make it faster (if possible & necessary)
  • 2. Re: Few questions
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    Z KHAN wrote:
    Hi all,

    I am writing a query which involves about 12 or 13 tables. Most of my columns are coming from 2 or 3 tables but i have another database from which 10 or more tables, i need.

    I just wanted to know what should be my best practice for this query?

    Should i just write the query in the simplest form or i can follow some other practice?
    I'm not real sure what you're asking. If you need all the data, then you need to join all the tables.

    How is this information being used? Is this on some kind of interactive form? If so, you might use some kind of Drill Down report, where you display data from. say, 3 tables, along with a button that users can press to see the details from the other 10 tables that are related to the selected row in the first display.
  • 3. Re: Few questions
    sufiyan Newbie
    Currently Being Moderated
    Hi guys, thanks for a quick response.

    I just need to be able to display all those fields data on the output. What i am asking is that, i have some folks write a query and then write another query and join that query to the first query. I can write the query and join the tables too, but i just want to know the best practice to approach this kind of task. Like will a high skilled PL/SQL dev look at my query and laugh? And say this is not the way to write it. or they can look at it and say "Not bad, could have made it better".
  • 4. Re: Few questions
    Frank Kulash Guru
    Currently Being Moderated
    Hi,

    Sorry, I'm still not sure what you're asking.
    Z KHAN wrote:
    Hi guys, thanks for a quick response.

    I just need to be able to display all those fields data on the output. What i am asking is that, i have some folks write a query and then write another query and join that query to the first query.
    Do you mean having 2 separate people write 2 separate queries, each unaware of what the other is doing, and then try to combine them?
    What are the advantages of doing it that way?
    I can write the query and join the tables too, but i just want to know the best practice to approach this kind of task.
    If you know how to write the whole query, why not do that?
  • 5. Re: Few questions
    sufiyan Newbie
    Currently Being Moderated
    Sorry i know my question is confusing. I will try my best.

    What i am asking is that, two databases here. database1 and database2. I need to bring data from three tables from database1 and 10 tables from database2. What i want to know is what would be the best and efficient approach for this? Ofcourse, everyone wnats their queries to run as fast as possible.

    Should i just write the query like this:
    Select
    db1.table1.x,
    db1.table2.y,
    db1.table3.z,
    db2.table1.a,
    db2.table2.b,
    db2.table3.c,
    and so on...

    from
    db1.table1,
    db1.table2,
    db1.table3,
    db2.table1,
    db2.table2,
    db2.table3
    where
    join all tables

    OR should i write a separate query first to bring data from database1 and separate query to bring data from database2 and then join both queries? Can i do that? What would be the best?

    I hope i was clear i am sorry for confusion.
  • 6. Re: Few questions
    Frank Kulash Guru
    Currently Being Moderated
    Hi,

    The optimizer does a better job than most of us can do. Don't worry about which tables are in which database; just write the query in a logical way. If, after you've done your best, performance is unacceptable, then see the forum FAQ {message:id=9360003}

    It would certainly be faster if all the tables were in one database, but I assume there's no chance of that.
    Can you replicate the relevant parts from one database to the other? That is, could you create a materialized view that selects and joins all the relevant data from one database, and then copies it to the other one, where the query will actually run? This could be much faster at run time, because a lot of the work would be done at replicate time. That usually means the replicated data can be somewhat out of date, however.
  • 7. Re: Few questions
    sufiyan Newbie
    Currently Being Moderated
    Thank you Frank,

    MV is not an option for me. I will try to write the query as best as possible. If anything i will ask surely ask again, really appreciate your help on this. Thanks again.
  • 8. Re: Few questions
    sufiyan Newbie
    Currently Being Moderated
    Hi Frank,

    So i got a modified requirement and this is better. I now have a different but similar question. So i have to bring in a field from database 2 table. And i was going to insert a subquery for that (isn't that the best approach?).

    However, when i insert a sub query, it says "subquery returns more than 1 row" now i understand, i dont have to have the sub query but if i am only bringing one data element form database 2 table, shouldnt i just use the sub query? Sorry if i was confusing and thanks in advance.
  • 9. Re: Few questions
    Iordan Iotzov Expert
    Currently Being Moderated
    As Frank Kulash previously noted, for most business rules writing a single SQL statement is the best way to go.

    There are a variety of reasons why the Oracle CBO gets “confused” and picks a bad execution plan. Working with large SQLs, i.e. SQLs that involve many tables, increases the chance of getting a sub-optimal execution plan.

    I had a presentation at Hotsos 2013 about this topic. Here are the ppt and the white paper http://iiotzov.wordpress.com/?attachment_id=396 and http://iiotzov.wordpress.com/?attachment_id=395 .

    Developers/designers should avoid constructs and features that cause the Oracle to make unnecessary guesses. When preventing “confusing” statements is not possible, you can try to mitigate the issue.

    Physically splitting a query into two or more SQLs is last resort effort and should be user sparingly.


    Iordan Iotzov
    http://iiotzov.wordpress.com/
  • 10. Re: Few questions
    sufiyan Newbie
    Currently Being Moderated
    Thank you, Iordan. Appreciate this useful information.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points