This discussion is archived
3 Replies Latest reply: Feb 14, 2013 7:24 AM by lxiscas RSS

A questions regarding query performance

lxiscas Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    >
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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

Legend

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