Tuning SQL query (merged 2 cross-posts) [message #197281] |
Tue, 10 October 2006 20:49  |
enkhbold
Messages: 36 Registered: July 2005 Location: Mongolia
|
Member |

|
|
Hi all
I have a large amount of records in the tables.
And, i wanted to get last 10 transaction for each account from then table.
my query is based on rowcount.
Can you provide me best solution on that?
Thanks,
|
|
|
|
|
|
Re: How tuning query [message #197290 is a reply to message #197281] |
Tue, 10 October 2006 21:33   |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
I get it, this is like one of those team-building workshops where they sit everybody down without telling them anything to see if anyone takes the initiative to ask what the heck they're supposed to be doing. Right? I got it, didn't I? My Mum always said I'd go far....
To compliment my razor-sharp perceptiveness, I also list chiefly amongst my weaponry (that was for your benefit JR and Jim), a psychic-like intuition.
Quote: | I have a large amount of records in the tables
| Which tables? How many? I'll guess: one table, say... MY_TRANSACTIONS. How'd I go?
Quote: | And, i wanted to get last 10 transaction for each account from then table
| OK, so MY_TRANSACTIONS has an ACCOUNT_NO. But what could "last 10" be? Last 10 ordered by physical storage location in the table (rowid)? Perhaps last 10 sorted by date... but that means another column - lets call it TRANSACTION_DATE.
Quote: | my query is based on rowcount
| You could try that, but ROW_NUMBER might work better.
OK, so now we have a requirement. We want to select the latest 10 transactions (by TRANSACTION_DATE) for each account (ACCOUNT_NO) from the MY_TRANSACTIONS table.
SELECT *
FROM (
SELECT account_no,
row_number() OVER (PARTITION BY account_no
ORDER BY transaction_date DESC) AS rn
FROM my_transactions
WHERE rn <= 10
This would probably be pretty inefficient. Maybe it could be made a bit better by placing an index on ACCOUNT_NO, TRANSACTION_DATE, but the analytic function will still have to perform a SORT, and it will still FULL SCAN the index- so don't expect orders of magnitude improvement.
Is there another way? Yep, but it's not easy. Create an index on ACCOUNT_NO, TRANSACTION_DATE, and then retrieve the data using PL/SQL.
FOR acct IN (SELECT account_no FROM my_accounts) LOOP
rn = 0;
FOR txn IN (SELECT *
FROM my_transactions
WHERE account_no = acct.account_no
ORDER BY transaction_date DESC
) LOOP
rn := rn + 1;
-- output the row ....
EXIT WHEN rn = 10;
END LOOP;
END LOOP;
You could embed this snippet in a PIPELINED FUNCTION, and then access it via a SQL statement (search for Pilelined Functions in the doco of this site).
So why is this so much faster? Normally cursor-within-a-cursor sucks for performance. Not this time. We exploit a little-known feature of the CBO, it can use an index to resolve an ORDER BY if the WHERE clause uses a range-scan on the same index.
This means that for each account, the inner-cursor will access only 10 rows - all of the rest do not need to be processed. I know of know way to achieve the same effect with conventional SQL.
Ross Leishman
|
|
|
|
|