| Partitioned table, Merge Query [message #64757] |
Thu, 22 January 2004 01:56 |
panna
Messages: 6
Registered: January 2004
|
Junior Member |
|
|
Hi,
I am facing the problem while executing the following merge query on partitioned table WEEKUSRID. Execution time is too large @ 2:20 Hrs.
MERGE INTO WEEKUSERIDS w1
USING (SELECT DISTINCT u.user_ident user_id, t.week_ending_day date_id,
'XYZ' AS prod_id, STREAMIDS.channel_id chan_id
FROM Test u, TIMES t, STREAMIDS
WHERE u.event_type = 15000
AND u.date_id = TO_DATE('10-nov-03')
AND u.date_id = t.date_id
AND u.stream_id = STREAMIDS.stream_id
AND STREAMIDS.stream_uid = 'XYZ') w2
ON (w1.user_id = w2.user_id
AND w1.date_id = w2.date_id
AND w1.product_id = w2.prod_id
AND w1.channel_id = w2.chan_id)
WHEN MATCHED THEN
UPDATE SET w1.last_visit_date = TO_DATE('10-nov-03')
WHEN NOT MATCHED THEN
INSERT (user_id, date_id, product_id, channel_id, last_visit_date)
VALUES (w2.user_id, w2.date_id, w2.prod_id, w2.chan_id, TO_DATE(
'10-nov-03'))
I observed that w1.date_id = w2.date_id join is causing the problem, coz when I had commented the other joins in ON cluase the query gets executed in 2 min.
I then created an nonunique index on WEEKUSERIDS table for date_id column. Note: Date_Id is not a PK.
Also when I had removed the partition from WEEKUSERIDS table and executed the query with all the joins mentioned in ON Clause, the query worked fine.
Is there any problem when we try to join a partitoned table along with the derived table. Kindly Advice, its URGENT !!!!
Thanks in advance.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
