3 Replies Latest reply: Feb 14, 2013 9:24 AM by lxiscas RSS

    A questions regarding query performance

    lxiscas
      Hi, gurus:

      I have a questions regarding query performance: We are developing a data warehouse application. Our customer asked to track history of processing every unprocessed invoice every day, which makes rows in our history table increases quickly. For example, I estimated 20000 rows stored every day. And we are required to provide function to pull out history data back to any given date in past 1 year,or even several years. So I am worried for potential query performance problem if I have to query this history table. Could anyone give me a suggestion about solution? If I can avoid join, would it be quicker?

      Thanks.

      Sam
        • 1. Re: A questions regarding query performance
          rp0428
          >
          I have a questions regarding query performance: We are developing a data warehouse application. Our customer asked to track history of processing every unprocessed invoice every day, which makes rows in our history table increases quickly. For example, I estimated 20000 rows stored every day. And we are required to provide function to pull out history data back to any given date in past 1 year,or even several years. So I am worried for potential query performance problem if I have to query this history table. Could anyone give me a suggestion about solution? If I can avoid join, would it be quicker?
          >
          You need to provide more information and some example data.

          What does 'history of processing every unprocessed invoice every day' mean? Provide an example original invoice and the 'history' that you need to keep.

          Explain how, if an invoice is 'unprocessed', it has a history?
          • 2. Re: A questions regarding query performance
            asahide
            Hi,

            Could you use Partition option ?
            If you can You are able to reduce the access to data.

            <<http://www.oracle.com/technetwork/database/options/partitioning/index.html>>

            Regards,
            • 3. Re: A questions regarding query performance
              lxiscas
              hi, guys:

              Thank you for your help! Let me explain the detail:

              This is an application for factoring business. The company buys invoices from many small transportation company with discount, and collect money from debtors.

              Every day there are some invoices that are not paid off yet (invoice balance >0 etc.), whose purchase date (invdate) falls into 6 date periods, like 37- 39, 40-42, 43-44, 45-46, 47-59, 60+ days before sysdate. Employees are supposed to call debtors according to these 6 time frames if the invoices are not paid off. If an employee called a debtor in any of these periods, he will leave a note in database. Our application needs to record all of detail information everyday (Here type means whether an employee calls a debtor regarding invoice on time with these values (valid call, invalid call, no collection call), category means time period, employee name means employee who leaves latest note in the period):

              date, category, type, inv#, invdate, amt, balance, employee_name

              Every day some new unpaid invoices falls into these 6 periods (first 37-39 days, until 60+ days, it will always stays in a period unless it is paid of or written off by company), and some are paid off and leaves these periods. So I cannot generate these report on fly.

              I need to backup all of reports generated everyday. and users may pull of history data according to date and category. I estimated there are 2500 invoices in a category by average so far, so 2500* 6=15000. How can I ensure performance to query a history table that increases 15000-20000 rows everyday?

              select type, inv#, invdate, amt, balance, employee_name
              from history_table
              where date='input_date'
              and category='input_category'

              Edited by: lxiscas on Feb 14, 2013 7:14 AM

              Edited by: lxiscas on Feb 14, 2013 7:23 AM

              Edited by: lxiscas on Feb 14, 2013 7:23 AM

              Edited by: lxiscas on Feb 14, 2013 7:24 AM