Home » SQL & PL/SQL » SQL & PL/SQL » Tuning SQL query (merged 2 cross-posts)
Tuning SQL query (merged 2 cross-posts) [message #197281] Tue, 10 October 2006 20:49 Go to next message
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,
Tuning SQL query [message #197282 is a reply to message #197281] Tue, 10 October 2006 20:50 Go to previous messageGo to next message
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: Tuning SQL query [message #197287 is a reply to message #197282] Tue, 10 October 2006 21:20 Go to previous messageGo to next message
BlackSwan
Messages: 25032
Registered: January 2009
Location: SoCal
Senior Member
Do NOT cross post.
Do NOT off topic (SQL question in PL/SQL forum).
Re: Tuning SQL query [message #197288 is a reply to message #197287] Tue, 10 October 2006 21:22 Go to previous messageGo to next message
enkhbold
Messages: 36
Registered: July 2005
Location: Mongolia
Member

Sorry
Re: How tuning query [message #197290 is a reply to message #197281] Tue, 10 October 2006 21:33 Go to previous messageGo to next message
rleishman
Messages: 3724
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
Re: How tuning query [message #197339 is a reply to message #197290] Wed, 11 October 2006 00:27 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
http://www.orafaq.com/forum/fa/1578/72104/

[Updated on: Wed, 11 October 2006 01:09]

Report message to a moderator

Re: How tuning query [message #197563 is a reply to message #197339] Wed, 11 October 2006 17:27 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
High praise indeed coming from you. The OP is welcome too.
Previous Topic: Column Description in Sybase (merged cross-posts)
Next Topic: PLEASE HELP ME! ERROR STRIKES ME!!
Goto Forum:
  


Current Time: Fri Dec 02 12:07:44 CST 2016

Total time taken to generate the page: 0.07449 seconds