Home » RDBMS Server » Performance Tuning » Optimize this query.. (10g)
Optimize this query.. [message #325608] Fri, 06 June 2008 12:58 Go to next message
beetel
Messages: 96
Registered: April 2007
Member
Hi,

I know this query can be rewritten to improve performace. I just do not know how Sad
Anyone wants to try??

SELECT a.ADJUSTMENT_ID,
       a.LAST_UPDATE_DATE,
       a.CREATION_DATE,
       a.AMOUNT,
       a.APPLY_DATE,
       a.PAYMENT_SCHEDULE_ID,
       a.RECEIVABLES_TRX_ID,
       a.DISTRIBUTION_SET_ID,
       MIN(i.trx_line_id)
  FROM ADJUSTMENTS a,
       INV_FACT   i
 WHERE a.customer_Trx_id = i.customer_trx_id(+)
 group by a.ADJUSTMENT_ID,
          a.LAST_UPDATE_DATE,
          a.CREATION_DATE,
          a.AMOUNT,
          a.APPLY_DATE,
          a.PAYMENT_SCHEDULE_ID,
          a.RECEIVABLES_TRX_ID,
          a.DISTRIBUTION_SET_ID

Report message to a moderator

Re: Optimize this query.. [message #325609 is a reply to message #325608] Fri, 06 June 2008 13:02 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>I know this query can be rewritten to improve performace
How/why do you believe this?

It appears to be optimally tuned to me.

[Updated on: Fri, 06 June 2008 13:12] by Moderator

Report message to a moderator

Re: Optimize this query.. [message #325904 is a reply to message #325608] Mon, 09 June 2008 08:04 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If you don't have duplicate data in the sets of columns selected from ADJUSTMENTS, it's possible (although not certain) that this might run faster:
SELECT a.ADJUSTMENT_ID,
       a.LAST_UPDATE_DATE,
       a.CREATION_DATE,
       a.AMOUNT,
       a.APPLY_DATE,
       a.PAYMENT_SCHEDULE_ID,
       a.RECEIVABLES_TRX_ID,
       a.DISTRIBUTION_SET_ID,
       (SELECT MIN(i.trx_line_id) 
        FROM   INV_FACT i 
        WHERE  a.customer_Trx_id = i.customer_trx_id)
  FROM ADJUSTMENTS a


Is there an invoice on INV_FACT(customer_trx_id,trx_line_id) - that should help.

Regardless of what you do, you're going to be reading every record deom ADJUSTMENTS, which will slow things down if it's a big table.

Report message to a moderator

Re: Optimize this query.. [message #325963 is a reply to message #325904] Mon, 09 June 2008 14:10 Go to previous messageGo to next message
JackyShu
Messages: 25
Registered: May 2008
Junior Member
use correlated scalar subquery (nested loop) is okay when resultset is small, but it doesn't scale well in case of high volume resultset expected.
the first query is actually better for high volume, oracle will choose hash join instead of nested loop.

JRowbottom wrote on Mon, 09 June 2008 09:04
If you don't have duplicate data in the sets of columns selected from ADJUSTMENTS, it's possible (although not certain) that this might run faster:
SELECT a.ADJUSTMENT_ID,
       a.LAST_UPDATE_DATE,
       a.CREATION_DATE,
       a.AMOUNT,
       a.APPLY_DATE,
       a.PAYMENT_SCHEDULE_ID,
       a.RECEIVABLES_TRX_ID,
       a.DISTRIBUTION_SET_ID,
       (SELECT MIN(i.trx_line_id) 
        FROM   INV_FACT i 
        WHERE  a.customer_Trx_id = i.customer_trx_id)
  FROM ADJUSTMENTS a


Is there an invoice on INV_FACT(customer_trx_id,trx_line_id) - that should help.

Regardless of what you do, you're going to be reading every record deom ADJUSTMENTS, which will slow things down if it's a big table.

Report message to a moderator

Re: Optimize this query.. [message #326184 is a reply to message #325963] Tue, 10 June 2008 09:08 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The inline subquery doesn't tend to use a nested loops - it produces a Sort Aggregate in the tests I ran:
explain plan for
select vb_col_1
       ,vb_col_2
       ,(select min(vbl_col_2)
         from   vb_lookup
         where  vbl_col_1 = vb_col_2) vbl_col_2
from   very_big;

select * from table(dbms_xplan.display('plan_table'));

 
--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |  1000K|  7812K|  2461 |
|   1 |  SORT AGGREGATE      |             |     1 |    10 |       |
|   2 |   TABLE ACCESS FULL  | VB_LOOKUP   |    10 |   100 |     2 |
|   3 |  TABLE ACCESS FULL   | VERY_BIG    |  1000K|  7812K|  2461 |
--------------------------------------------------------------------
 
Note: cpu costing is off, PLAN_TABLE' is old version


That being said, the original query is substantially faster. I'd assumed that the GROUP BY would impose more of an overhead than that.

Report message to a moderator

Re: Optimize this query.. [message #326193 is a reply to message #326184] Tue, 10 June 2008 09:57 Go to previous messageGo to next message
JackyShu
Messages: 25
Registered: May 2008
Junior Member
yeah, that sort aggregate is for small table.
i was wondering what execution path it really is from step 1, 3 to step 0. think it might be hash join or sort merge.

Report message to a moderator

Re: Optimize this query.. [message #326228 is a reply to message #325608] Tue, 10 June 2008 13:54 Go to previous messageGo to next message
coleing
Messages: 213
Registered: February 2008
Senior Member
One way to speed up now is to parallelise the query if your server setup supports it (multi disk IO subsystem, multiple CPU's, PARALLEL_MAX_SERVERS set up to a reasonable level).

I would try something like this on a decent sized server:-

Note, you will be consuming remarkably more CPU and temp resources doing it in paralle, but if your server supports it, it will run lots quicker.

SELECT /*+ PARALLEL(a,4) PARALLEL(i,4) USE_HASH(a,i) */ 
       a.ADJUSTMENT_ID,
       a.LAST_UPDATE_DATE,
       a.CREATION_DATE,
       a.AMOUNT,
       a.APPLY_DATE,
       a.PAYMENT_SCHEDULE_ID,
       a.RECEIVABLES_TRX_ID,
       a.DISTRIBUTION_SET_ID,
       MIN(i.trx_line_id)
  FROM ADJUSTMENTS a,
       INV_FACT   i
 WHERE a.customer_Trx_id = i.customer_trx_id(+)
 group by a.ADJUSTMENT_ID,
          a.LAST_UPDATE_DATE,
          a.CREATION_DATE,
          a.AMOUNT,
          a.APPLY_DATE,
          a.PAYMENT_SCHEDULE_ID,
          a.RECEIVABLES_TRX_ID,
          a.DISTRIBUTION_SET_ID

Report message to a moderator

Re: Optimize this query.. [message #326230 is a reply to message #325608] Tue, 10 June 2008 13:57 Go to previous messageGo to next message
coleing
Messages: 213
Registered: February 2008
Senior Member
also inline view INV_FACT might help to restrict rows before join (if there are lots per trx_id):-

SELECT a.ADJUSTMENT_ID,
       a.LAST_UPDATE_DATE,
       a.CREATION_DATE,
       a.AMOUNT,
       a.APPLY_DATE,
       a.PAYMENT_SCHEDULE_ID,
       a.RECEIVABLES_TRX_ID,
       a.DISTRIBUTION_SET_ID,
       i.min_id
  FROM ADJUSTMENTS a,
       (select customer_trx_id, min(trx_line_id) as min_id from INV_FACT group by customer_trx_id)   i
 WHERE a.customer_Trx_id = i.customer_trx_id(+)
 group by a.ADJUSTMENT_ID,
          a.LAST_UPDATE_DATE,
          a.CREATION_DATE,
          a.AMOUNT,
          a.APPLY_DATE,
          a.PAYMENT_SCHEDULE_ID,
          a.RECEIVABLES_TRX_ID,
          a.DISTRIBUTION_SET_ID


Actually, depending on your data, you might then get away without the group by on the main read:-
SELECT a.ADJUSTMENT_ID,
       a.LAST_UPDATE_DATE,
       a.CREATION_DATE,
       a.AMOUNT,
       a.APPLY_DATE,
       a.PAYMENT_SCHEDULE_ID,
       a.RECEIVABLES_TRX_ID,
       a.DISTRIBUTION_SET_ID,
       i.min_id
  FROM ADJUSTMENTS a,
       (select customer_trx_id, min(trx_line_id) as min_id from INV_FACT group by customer_trx_id)   i
 WHERE a.customer_Trx_id = i.customer_trx_id(+)

[Updated on: Tue, 10 June 2008 13:59]

Report message to a moderator

Re: Optimize this query.. [message #330281 is a reply to message #325608] Sat, 28 June 2008 15:12 Go to previous message
Kriptas
Messages: 118
Registered: June 2008
Senior Member
las one query is the best, i think.

Report message to a moderator

Previous Topic: Long running SQL needs tuning
Next Topic: Segment Tuning --- TablePartition
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Jun 27 20:55:54 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.