-
Can any one help me optimize this query:
UPDATE TABLE_1 A
SET A.COL_1 =
(SELECT COUNT (DISTINCT B.USER_ID)
FROM TABLE_2 B
WHERE B.DATE_COL >= '01-APR-01'
AND B.DATE_COL < '01-MAY-01'
AND A.COL_2 = B.COL_2
AND A.COL_3 = 'TOTAL'
GROUP BY B.COL_2)
WHERE A.COL_3 = 'TOTAL';
there is an index on a.col_2 and a seperate index on a.col_3, table_2 is partition by day and I can not create any indexes on it, I also tried to execute it in parallel.
table_1 have about 450 records and about 40-50 meet the where creteria, table_2 is very big probably about 1 mill per day.
Any ideas???
-
Please post the plan of execution and is writing this in PL/SQL a choice?
uday
-
Yes PLSQL is a chooice, and I'll generate the exec plan
-
Originally posted by Highlander
...and I can not create any indexes on it, ...
Why not?
It would seem to me that :
- This should definitely be done in parallel (~30 threads - 1 per partition)
- There should be a local index on B: DATE_COL, COL_2
There might be some tricks to be done with the query, but I'd have to see the plan.
- Chris
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|