 Performance Problem [message #249014] |
Tue, 03 July 2007 01:36  |
barakula
Messages: 5
Registered: June 2007
|
Junior Member |
|
|
Hello all .
I have a serious performance problem ....
I have a table with 4 million rows which name is fvx478_bp_compare_53 .
a simple query of 5 rows takes over 2 minutes ....
I am trying to do the following on this table :
select distinct account_id,owner_id,item_code,rate_scheme_code,
nvl((select sum(a.kesef) from fvx478_bp_compare_53 a
where a.account_id=b.account_id
and a.item_code=b.item_code
and a.rate_scheme_code=b.rate_scheme_code
and a.bp_number=143
group by a.item_code),'0') kesef_old,
nvl((select sum(c.kesef) from fvx478_bp_compare_53 c
where c.account_id=b.account_id
and c.item_code=b.item_code
and c.rate_scheme_code=b.rate_scheme_code
and c.bp_number=144
group by c.account_id),'0') kesef_new,
null,null,description
from fvx478_bp_compare_53 b;
because i need to see the difference on the field KESEF between the two bp_number fields in one row .
for example :
i want to see this :
account_id owner_id item_code rate_scheme_code kesef_old kesef_new null null description
1234567 6666666 9 TORO 314.3 450.6 null null firifiri
PLEASE HELP ...
Thanks,
Barak .
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Performance Problem [message #249228 is a reply to message #249014] |
Tue, 03 July 2007 12:39  |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
Hello,
your query does not seem simple to me as you join your table with itself twice on (account_id, item_code, rate_scheme_code). Composite index on these columns could help. How many rows are (in average) with the same (account_id, item_code, rate_scheme_code) values? Also it would be good to know how many rows are with bp_number = 143 (144).
Also I would reduce the number of processed rows by putting DISTINCT into inner query, like SELECT account_id,owner_id,item_code,rate_scheme_code,
<other columns>
FROM (SELECT DISTINCT account_id,owner_id,item_code,rate_scheme_code, description
FROM fvx478_bp_compare_53) b;
If the inner query returns 5 rows (cardinality of your resultset), this should help.
If it will not, post explain plan and requested row counts.
[Edit: Slightly rephrased the end]
[Updated on: Tue, 03 July 2007 23:02] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
