7 Replies Latest reply: Jan 25, 2013 4:54 AM by Billy~Verreynne RSS

    How to get fast output from large table

    pradip010189
      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
          Hi,

          What about using "Materialized View"?

          Regards,
          • 2. Re: How to get fast output from large table
            pradip010189
            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
              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
                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
                  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
                    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
                      Billy~Verreynne
                      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).