Re: Selecting in sorted order and joining

Brian -

I'll make a couple of presumptions, then dive in: 1 - you need to handle (at some point or other) ALL of the 'unhandled transactions'
in table A.
2 - You've got an index (maybe even a primary key!) on CustomerID or Customer_NUM --whatever - on your customer table B. 3 - You don't want to walk the customer table B, because there probably isn't a transaction for every customer......

You don't say what version of Oracle, or on what platform, and in what OPTIMIZER_MODE you're running (from the INIT<SID>.ORA file).

So -
I'll assume a recent version of Oracle - cause you're using hints - which point to a cost based optimizer. (Certainly version 7.x.......7.3.3 maybe???).

First - don't assume that using an Index is necessarily the bestest, fastest way to get at your data....the Optimizer has gotten very smart.!! From what I understand, it looks at how many database pages it needs to touch in order to satisfy your query request - in this case - how many pages from the transaction table. When it reaches some threshold, it basically says something like this:
"I've got to touch every/nearly every page in the table to fulfill this request. Rather than doing AT LEAST two i/o by using the index (one to filter in the desired rows, another to get the data) I'll just do a serial scan."

Unless there is some compelling business reason to process the 'oldest' rows first, stop trying to defeat the optimizer! It's like trying to teach a pig to sing:

                            1 - It won't work
                            2 - it really ticks off the pig!!

Now that that's said, there's still a way to do this....server side. Write a stored procedure with an outer loop on a cursor that's set up by date. Write an inner loop that does the required work on only the results from that cursor. You'll get one date at a time from the transaction table, and since there's an index on the date column, it should (key word!!!!) use the index automatically and it should be fast.

Good Luck and best regards..
Craig Brady
Talisman Technologies Inc
Specializing in Oracle Datawarehousing/VLDB's

<<<<<<<<<<<<<<<<<<<<<>>>>>>>>>>>>>>>>>>>>>

Brian Rasmusson wrote in message <3562C6EC.E766A0E1_at_belle.dk>...
>Hi,
>
>I have a question to the performance people..
>
>I have two tables that i join. Table A contains unhandled transactions,
>theoretically millions, and table B contains customer records, hundred
>of thousands.
>
>I want to read table A in ascending order, records with oldest timestamp
>first.
>
>A fast implementation would be select a.date, a.somedata, b.customerid
>from a, b where a.date < sysdate and b.primarykey = a.somefield order by
>date.

<<SNIP>> Received on Wed May 20 1998 - 19:19:16 CDT