Here is the query:

DELETE
FROM A
WHERE SEGMENT_ID IN
(SELECT DISTINCT PARTY_ID
FROM B)

Any inputs would be deeply appreciated. Thanks in advance.

Not sure what you want to do, but try this instead:

DELETE FROM TABLE_1 A
WHERE EXISTS
(SELECT 1 FROM TABLE_2 B where B.SEGMENT_ID = A.SEGMENT_ID)

Obviously, you will want an index on SEGMENT_ID for TABLE_2

Thanks for the reply. Actually SEGMENT_ID is joined with PARTY_ID and not with SEGMENT_ID of the second table. I modified the query according to your suggestion and did not find any improvement in performance.

I ran db2expln utility on the query I pasted for optimization and your version of the query but the Estimated cost (=1171144.250000) remained the same.

Is there any other way apart from what you suggested earlier. Thanks once again

Thanks for the reply. Actually SEGMENT_ID is joined with PARTY_ID and not with SEGMENT_ID of the second table. I modified the query according to your suggestion and did not find any improvement in performance.

I ran db2expln utility on the query I pasted for optimization and your version of the query but the Estimated cost (=1171144.250000) remained the same.

Is there any other way apart from what you suggested earlier. Thanks once again

How about posting the access plan. The number does nothing for us. How many rows are in table_1 and table_2? How many rows do you expect to be deleted? Do you have an index on party_id in table_2? Do you have an index on segment_id in table_1?

Andy

DELETE
FROM ADDRESS
WHERE SEGMENT_ID IN
(SELECT DISTINCT PARTY_ID
FROM SUBSCRIBER_DATA)

Section Code Page = 1208

Estimated Cost = 1171144.250000
Estimated Cardinality = 153268.000000

Access Table Name = EXTRACT.SUBSCRIBER_DATA ID = 4,57
| #Columns = 1
| Relation Scan
| | Prefetch: Eligible
| Lock Intents
| | Table: Intent Share
| | Row : Next Key Share
| Sargable Predicate(s)
| | Process Build Table for Hash Join
Hash Join
| Early Out: Single Match Per Outer Row
| Estimated Build Size: 10320000
| Estimated Probe Size: 4928000
| Access Table Name = EXTRACT.ADDRESS ID = 4,13
| | #Columns = 1
| | Relation Scan
| | | Prefetch: Eligible
Isolation Level: Read Stability
| | Lock Intents
| | | Table: Intent Exclusive
| | | Row : Exclusive
| | Sargable Predicate(s)
| | | Process Probe Table for Hash Join
Establish Row Position
| Access Table Name = EXTRACT.ADDRESS ID = 4,13
Delete: Table Name = EXTRACT.ADDRESS ID = 4,13

End of section


Optimizer Plan:

DELETE
( 2)
/----/ \
FETCH Table:
( 3) EXTRACT
/---/ \ ADDRESS
HSJOIN Table:
( 4) EXTRACT
/-/ \ ADDRESS
TBSCAN TBSCAN
( 5) ( 6)
| |
Table: Table:
EXTRACT EXTRACT
ADDRESS SUBSCRIBER_DATA

FYI, there is no index on either of the tables.

Without indexes, the best you can get are tablescans, which is what you are getting. If you want to speed it up, you need indexes.

Andy

Thanks Andy. I understand that indexes are needed but I feel it might hamper the performance of other queries. Anyway I will see what I can do. Thanks very much.

Your tables have no indexes. All current queries will be doing tablescans--the worst possible access plan. How can adding index hamper the performance of other queries?

Andy

I am sorry. You're right. I got mixed up with another table of the same sort.

I specfically told you that my example needed an index to work efficiently.

Adding an index will not adversely affect other selects, but will add slight overhead to inserts and deletes.

I suggest that you add the index and test my query (with the correct column names), and also test inserts and deletes with and without the index.