Re: Searching Approach

On Sep 4, 3:08 am, spacedog <s1m0nc..._at_hotmail.com> wrote:
> Hi,
>
> I am looking for the most efficient way to perform a daily search of
> 15,000 customers against 60M transactions in the system, using a
> complicated close matching set of rules. Any matches will then be
> recorded for future use.
>
> The transactions have a number of indexes which have been put on the
> table to help optimize the search.
>
> The current approach suggested is to take batches of the customers and
> fire off multiple searches. This doesn't seem to me like a very
> efficient way of doing things as it looks like we'll be doing lots of
> repeat gets from disk. The indexes are about 15 to 20 GB in size so
> its doubtful that we'll be able to store them in memory.
>
> Any ideas would be much appreciated.
>
> Thanks
> Simon

It depends.

Please give exact versions of software and hardware. Please give examples of the matching rules.

Are these matches going to be done while the transactions are updating? Can the indexes satisfy the search? Do you have partitioning? Do you have a performance level to keep, both for transactions and this analysis? How do you present this information?

Things could go downhill fast using common advice here. If you have many batches and they start conflicting with each other and transactions, or if you have a big batch that doesn't complete before the next big batch starts... You may want to run this on another machine, or even do it outside of Oracle. It depends on your usage of the machine.

To start, you should do the big join like Frank suggests, so you can start chewing on real measurements. You may have to start adjusting things like undo size and retention. Sometimes you don't want to do the "most efficient" thing for practical reasons.

jg

--
@home.com is bogus.
http://www.computerworlduk.com/technology/mobile-wireless/mobile-wireless/news-analysis/index.cfm?articleid=743