Home » RDBMS Server » Performance Tuning » Joining tables - Compare multiple columns of a table with a Single column of oth
| Joining tables - Compare multiple columns of a table with a Single column of oth [message #65935] |
Tue, 01 February 2005 22:39  |
priya
Messages: 108
Registered: February 2000
|
Senior Member |
|
|
Due to some reason, RULE based optimization is used at our place. The table referred is also a denormalized table with columns like Date0, Date1...Date9. The respective share values are Share0, Share1 ...Share9. There is a separate Price table. Now, the Value0, Value1....Value9 should be calculated by multiplying Share0 with Price based on the Date0 column. Roughly the query looks as given below:
INSERT INTO.....
SELECT h.owner_id, h.issue_id,
decode(n0.price_value, null, h.value0, h.shares0 * n0.price_value),
h.shares1 * n1.price_value,
h.shares2 * n2.price_value,
h.shares3 * n3.price_value,
h.shares4 * n4.price_value,
h.shares5 * n5.price_value,
h.shares6 * n6.price_value,
h.shares7 * n7.price_value,
h.shares8 * n8.price_value,
h.shares9 * n9.price_value,
n0.price_value,
n1.price_value,
n2.price_value,
n3.price_value,
n4.price_value,
n5.price_value,
n6.price_value,
n7.price_value,
n8.price_value,
n9.price_value
FROM issue_price n0,
issue_price n1,
issue_price n2,
issue_price n3,
issue_price n4,
issue_price n5,
issue_price n6,
issue_price n7,
issue_price n8,
issue_price n9,
temp_holding_mf1 h,
WHERE n0.price_date (+) = h.date0
AND n1.price_date (+) = h.date1
AND n2.price_date (+) = h.date2
AND n3.price_date (+) = h.date3
AND n4.price_date (+) = h.date4
AND n5.price_date (+) = h.date5
AND n6.price_date (+) = h.date6
AND n7.price_date (+) = h.date7
AND n8.price_date (+) = h.date8
AND n9.price_date (+) = h.date9
AND n0.issue_id (+) = h.issue_id
AND n1.issue_id (+) = h.issue_id
AND n2.issue_id (+) = h.issue_id
AND n3.issue_id (+) = h.issue_id
AND n4.issue_id (+) = h.issue_id
AND n5.issue_id (+) = h.issue_id
AND n6.issue_id (+) = h.issue_id
AND n7.issue_id (+) = h.issue_id
AND n8.issue_id (+) = h.issue_id
AND n9.issue_id (+) = h.issue_id ;
There are about 34 lakhs record in the issue_price table and about 41 lakhs record in the other temporary table. Is there any better way of doing this for improved performance on Oracle 9i ?
|
|
|
|
|
|
| Re: Joining tables - Compare multiple columns of a table with a Single column of [message #65941 is a reply to message #65935] |
Thu, 03 February 2005 19:55  |
priya
Messages: 108
Registered: February 2000
|
Senior Member |
|
|
Temp_Holding_MF1
-----------------
Owner_ID Issue_id Shares0 Date0 Value0
OWN1 FUNDA 1,000 31-Dec-2004 1,00,000
Shares1 Date1
100 30-Sep-2004
Shares2 Date2
200 30-Jun-2004
Shares3 Date3
300 31-Mar-2004
Shares4 Date4
400 31-Dec-2003
Shares5 Date5
500 30-Sep-2003
Shares6 Date6
600 30-Jun-2003
Shares7 Date7
700 31-Mar-2003
Shares8 Date8
800 31-Dec-2002
Shares9 Date9
900 30-Sep-2002
issue_price
-------------
Issue_id Price_date Price
FUNDA 31-Dec-2004 100
FUNDA 30-Sep-2004 100
FUNDA 30-Jun-2004 100
FUNDA 31-Mar-2004 100
FUNDA 31-Dec-2003 100
FUNDA 30-Sep-2003 100
FUNDA 30-Jun-2003 100
FUNDA 31-Mar-2003 100
FUNDA 31-Dec-2002 100
FUNDA 30-Sep-2002 100
Output Table
--------------
Owner_id Issue_Id Value0 Price0
OWN1 FUNDA 1,00,000 100
Value1 Price1
10,000 100
Value2 Price2
20,000 100
Value3 Price3
30,000 100
Value4 Price4
40,000 100
Value5 Price5
50,000 100
Value6 Price6
60,000 100
Value7 Price7
70,000 100
Value8 Price8
80,000 100
Value9 Price9
90,000 100
The values in the output table depends on Shares * Price. For example,
Value1 = Shares1 * Price
Value2 = Shares2 * Price
.........
|
|
|
|
Goto Forum:
Current Time: Wed Apr 24 12:41:09 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
