This discussion is archived
7 Replies Latest reply: Jan 25, 2013 2:54 AM by BillyVerreynne RSS

How to get fast output from large table

pradip010189 Newbie
Currently Being Moderated
Hello Friends,

i have three tables and all of these tables have around 30L records.
Using join i am retrieving records from these tables but it is taking much more time to get output.
Partition can improve performance or any other suggestion to improve query performance.??
Please help me.

--
Pradip Patel
  • 1. Re: How to get fast output from large table
    asahide Expert
    Currently Being Moderated
    Hi,

    What about using "Materialized View"?

    Regards,
  • 2. Re: How to get fast output from large table
    pradip010189 Newbie
    Currently Being Moderated
    Hi,

    can u say me in brief how can i use materialized view ??
    thanks.
  • 3. Re: How to get fast output from large table
    asahide Expert
    Currently Being Moderated
    Hello,

    Basic MV manual here!
    <<http://docs.oracle.com/cd/E11882_01/server.112/e25554/basicmv.htm#i1007299>>



    Rgd,
  • 4. Re: How to get fast output from large table
    karan Pro
    Currently Being Moderated
    The purpose of a materialized view is to increase query execution performance but a materialized view consumes storage space, so see if u can take that or not. And also the contents of the materialized view must be updated when the underlying detail tables are modified. So refreshing them efficiently is another challenge.
  • 5. Re: How to get fast output from large table
    karan Pro
    Currently Being Moderated
    Most of your questions are unresolved make sure you mark the answers correct and resolved once ur questions are answered. This saves time.
  • 6. Re: How to get fast output from large table
    Purvesh K Guru
    Currently Being Moderated
    pradip010189 wrote:
    Hello Friends,

    i have three tables and all of these tables have around 30L records.
    Using join i am retrieving records from these tables but it is taking much more time to get output.
    Partition can improve performance or any other suggestion to improve query performance.??
    Please help me.
    If you have problem with performance of Query, read and post details as mentioned at {message:id=3292438}

    At many occasions, problem lies in the way SQL is written and the Normalization of tables. So, unless you post
    1. Your Table, Index Structures in details
    2. Data present in Tables
    3. Stats (select table_name, num_rows, last_analyzed from user_tables where table_name in (your_table_names)
    4. Explain Plan (follow advice mentioned to fetch the Explain Plan linked in the Post)
    5. Use {noformat}
    {noformat} tags before and after the details, to preserve the formatting and improve readability. Under no circumstances, post an Explain Plan without these tags.
    6. select * from v$version;
    
    
    If you can help us with these details, it might be possible to help you.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
  • 7. Re: How to get fast output from large table
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    pradip010189 wrote:

    i have three tables and all of these tables have around 30L records.
    What is 30L? 30 litres? Please use International English terminology in an international English forum .
    Using join i am retrieving records from these tables but it is taking much more time to get output.
    Performance depends on how much works needs to be done. I/O is usually the main contributor to the workload - as I/O is a very expensive operation.

    You can join 3 billion row tables in less than a second via unique index scans for 10 rows. You may need several hours to cartesian join 3 tables with a mere million rows each, for all rows.

    So it is not about the total number of rows. It is about how much I/O is needed via index and table reads.

    A materialised view is NOT the answer.

    Why?

    Because the problem is unknown. Your join could be very expensive due missing indexes and poor statistics. The SQL may be not optimally designed and coded. Etc.

    The 1st Rule Of Performance Tuning is to IDENTIFY the problem. If you do not know what the problem is, how would you know how to solve it? Or whatever a materialised view is the most appropriate solution for the problem?

    Forget about materialised views for now. Focus on the issue at hand - WHAT is the problem? (the poor performance is a symptom - not the problem).

Legend

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