Home » RDBMS Server » Performance Tuning » Optimize this query.. (10g)
Optimize this query.. [message #325608] |
Fri, 06 June 2008 12:58 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
beetel
Messages: 96 Registered: April 2007
|
Member |
|
|
Hi,
I know this query can be rewritten to improve performace. I just do not know how
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
|
|
|
|
Re: Optimize this query.. [message #325904 is a reply to message #325608] |
Mon, 09 June 2008 08:04 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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.
|
|
|
Re: Optimize this query.. [message #325963 is a reply to message #325904] |
Mon, 09 June 2008 14:10 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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.
|
|
|
|
Re: Optimize this query.. [message #326184 is a reply to message #325963] |
Tue, 10 June 2008 09:08 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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.
|
|
|
Re: Optimize this query.. [message #326193 is a reply to message #326184] |
Tue, 10 June 2008 09:57 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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.
|
|
|
Re: Optimize this query.. [message #326228 is a reply to message #325608] |
Tue, 10 June 2008 13:54 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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
|
|
|
Re: Optimize this query.. [message #326230 is a reply to message #325608] |
Tue, 10 June 2008 13:57 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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
|
|
|
|
Goto Forum:
Current Time: Thu Aug 29 22:04:58 CDT 2024
|