Archives of the TeradataForum
Message Posted: Thu, 17 Jun 2004 @ 17:04:06 GMT
Subj: | | Performance of Multistatement Query with DELETE/INSERT-SELECT |
|
From: | | Santra, Partho |
Hi
We have a table with more than 350 millioin rows. And we are trying to execute this query
DELETE
FROM EDW_DATA.JOURNAL_DETAILS_1
WHERE pp_line_id IN
(SELECT pp_line_id
FROM EDW_DATA.JOURNAL_DETAILS_2
WHERE WAREHOUSE_UPDATE_TIMESTAMP > timestamp '2004-06-15
18:30:13')
;INSERT into EDW_DATA.JOURNAL_DETAILS_1
SELECT *
FROM EDW_DATA.JOURNAL_DETAILS_2
WHERE WAREHOUSE_UPDATE_TIMESTAMP > timestamp '2004-06-15 18:30:13';
The inner subquery takes about 55 mins to return the PP_LINE_ID's. We have Primary Index on pp_line_id. If we create a NUSI on
WAREHOUSE_UPDATE_TIMESTAMP and SELECT WAREHOUSE_UPDATE_TIMESTAMP then the query returns with few secs, but I need PP_LINE_ID's instead.
Can you guyz help me.
TIA
|