No Spool space issue

Database

No Spool space issue

Hi,

We need to remove duplicate records from the table with more than 50M records in same table. We are using simple query for this functionallty.That is in below,

DELETE FROM '|| getRawTableName ||' mastertbl WHERE mastertbl.InvoiceId IN
(SELECT tbl4.InvoiceId FROM(
SELECT tbl3.*,ROW_NUMBER() OVER (
PARTITION BY BillToAccountNumber,InvoiceDate,InvoiceNumber
,OriginalAmountDue,ShipmentDate,BundleNumber
,NumberOfPieces,ExpressOrGroundTrackingID
,OriginalCustomerReference,OriginalRef#2
,OriginalRef#3_PONumber,OriginalDepartmentReferenceDescription
,ActualWeightAmount,ActualWeightUnits,RatedWeightAmount
,RatedWeightUnits,ZoneCode,ServiceType,StoreId
,RecipientName,RecipientCompany,RecipientAddressLine1
,RecipientAddressLine2,RecipientCity,RecipientState
,RecipientZipCode,RecipientCountry,ShipperCompany
,ShipperName,ShipperAddressLine1,ShipperAddressLine2
,ShipperCity,ShipperState,ShipperZipCode,ShipperCountry
,DimLength,DimWidth,DimHeight,DimDivisor
,TransportationChargeAmount,TrackingIDChargeDescription1
,TrackingIDChargeAmount1,TrackingIDChargeDescription2
,TrackingIDChargeAmount2,TrackingIDChargeDescription3
,TrackingIDChargeAmount3,TrackingIDChargeDescription4
,TrackingIDChargeAmount4,TrackingIDChargeDescription5
,TrackingIDChargeAmount5,TrackingIDChargeDescription6
,TrackingIDChargeAmount6,TrackingIDChargeDescription7
,TrackingIDChargeAmount7,TrackingIDChargeDescription8
,TrackingIDChargeAmount8,TrackingIDChargeDescription9
,TrackingIDChargeAmount9,TrackingIDChargeDescription10
,TrackingIDChargeAmount10,TrackingIDChargeDescription11
,TrackingIDChargeAmount11,TrackingIDChargeDescription12
,TrackingIDChargeAmount12,TrackingIDChargeDescription13
,TrackingIDChargeAmount13,TrackingIDChargeDescription14
,TrackingIDChargeAmount14,TrackingIDChargeDescription15
,TrackingIDChargeAmount15,TrackingIDChargeDescription16
,TrackingIDChargeAmount16,TrackingIDChargeDescription17
,TrackingIDChargeAmount17,TrackingIDChargeDescription18
,TrackingIDChargeAmount18,TrackingIDChargeDescription19
,TrackingIDChargeAmount19,TrackingIDChargeDescription20
,TrackingIDChargeAmount20,TrackingIDChargeDescription21
,TrackingIDChargeAmount21,TrackingIDChargeDescription22
,TrackingIDChargeAmount22,TrackingIDChargeDescription23
,TrackingIDChargeAmount23,TrackingIDChargeDescription24
,TrackingIDChargeAmount24,TrackingIDChargeDescription25
,TrackingIDChargeAmount25
,TotalOfPositive,TotalOfNegative,Flag1,Flag2
ORDER BY ExpressOrGroundTrackingID) AS GetRank2
FROM(
SELECT tbl.*, COUNT(*) OVER (
PARTITION BY BillToAccountNumber,InvoiceDate,InvoiceNumber
,OriginalAmountDue,ShipmentDate,BundleNumber
,NumberOfPieces,ExpressOrGroundTrackingID
,OriginalCustomerReference,OriginalRef#2
,OriginalRef#3_PONumber,OriginalDepartmentReferenceDescription
,ActualWeightAmount,ActualWeightUnits,RatedWeightAmount
,RatedWeightUnits,ZoneCode,ServiceType,StoreId
,RecipientName,RecipientCompany,RecipientAddressLine1
,RecipientAddressLine2,RecipientCity,RecipientState
,RecipientZipCode,RecipientCountry,ShipperCompany
,ShipperName,ShipperAddressLine1,ShipperAddressLine2
,ShipperCity,ShipperState,ShipperZipCode,ShipperCountry
,DimLength,DimWidth,DimHeight,DimDivisor
,TransportationChargeAmount,TrackingIDChargeDescription1
,TrackingIDChargeAmount1,TrackingIDChargeDescription2
,TrackingIDChargeAmount2,TrackingIDChargeDescription3
,TrackingIDChargeAmount3,TrackingIDChargeDescription4
,TrackingIDChargeAmount4,TrackingIDChargeDescription5
,TrackingIDChargeAmount5,TrackingIDChargeDescription6
,TrackingIDChargeAmount6,TrackingIDChargeDescription7
,TrackingIDChargeAmount7,TrackingIDChargeDescription8
,TrackingIDChargeAmount8,TrackingIDChargeDescription9
,TrackingIDChargeAmount9,TrackingIDChargeDescription10
,TrackingIDChargeAmount10,TrackingIDChargeDescription11
,TrackingIDChargeAmount11,TrackingIDChargeDescription12
,TrackingIDChargeAmount12,TrackingIDChargeDescription13
,TrackingIDChargeAmount13,TrackingIDChargeDescription14
,TrackingIDChargeAmount14,TrackingIDChargeDescription15
,TrackingIDChargeAmount15,TrackingIDChargeDescription16
,TrackingIDChargeAmount16,TrackingIDChargeDescription17
,TrackingIDChargeAmount17,TrackingIDChargeDescription18
,TrackingIDChargeAmount18,TrackingIDChargeDescription19
,TrackingIDChargeAmount19,TrackingIDChargeDescription20
,TrackingIDChargeAmount20,TrackingIDChargeDescription21
,TrackingIDChargeAmount21,TrackingIDChargeDescription22
,TrackingIDChargeAmount22,TrackingIDChargeDescription23
,TrackingIDChargeAmount23,TrackingIDChargeDescription24
,TrackingIDChargeAmount24,TrackingIDChargeDescription25
,TrackingIDChargeAmount25
,TotalOfPositive,TotalOfNegative,Flag1,Flag2
ORDER BY ExpressOrGroundTrackingID) AS GetRank
FROM '|| getRawTableName ||' tbl
)tbl3
WHERE tbl3.GetRank>1)tbl4 WHERE tbl4.GetRank2>=2)

We are increase the size spool and memory, but we are still getting same issue.Anyone can help us  to solve issue or give suggestion to optimize the query.

1 REPLY

Re: No Spool space issue

Please try the below steps to remove the duplicate records.

INSERT INTO TABLE_DEDUP -- HOLDS ONLY DUPLICATE RECORDS
SELECT tbl.* FROM '|| getRawTableName ||' tbl
GROUP BY
BillToAccountNumber,InvoiceDate,InvoiceNumber
,OriginalAmountDue,ShipmentDate,BundleNumber
,NumberOfPieces,ExpressOrGroundTrackingID
,OriginalCustomerReference,OriginalRef#2
,OriginalRef#3_PONumber,OriginalDepartmentReferenceDes cription
,ActualWeightAmount,ActualWeightUnits,RatedWeightAmoun t
,RatedWeightUnits,ZoneCode,ServiceType,StoreId
,RecipientName,RecipientCompany,RecipientAddressLine1
,RecipientAddressLine2,RecipientCity,RecipientState
,RecipientZipCode,RecipientCountry,ShipperCompany
,ShipperName,ShipperAddressLine1,ShipperAddressLine2
,ShipperCity,ShipperState,ShipperZipCode,ShipperCountr y
,DimLength,DimWidth,DimHeight,DimDivisor
,TransportationChargeAmount,TrackingIDChargeDescriptio n1
,TrackingIDChargeAmount1,TrackingIDChargeDescription2
,TrackingIDChargeAmount2,TrackingIDChargeDescription3
,TrackingIDChargeAmount3,TrackingIDChargeDescription4
,TrackingIDChargeAmount4,TrackingIDChargeDescription5
,TrackingIDChargeAmount5,TrackingIDChargeDescription6
,TrackingIDChargeAmount6,TrackingIDChargeDescription7
,TrackingIDChargeAmount7,TrackingIDChargeDescription8
,TrackingIDChargeAmount8,TrackingIDChargeDescription9
,TrackingIDChargeAmount9,TrackingIDChargeDescription10
,TrackingIDChargeAmount10,TrackingIDChargeDescription11
,TrackingIDChargeAmount11,TrackingIDChargeDescription12
,TrackingIDChargeAmount12,TrackingIDChargeDescription13
,TrackingIDChargeAmount13,TrackingIDChargeDescription14
,TrackingIDChargeAmount14,TrackingIDChargeDescription15
,TrackingIDChargeAmount15,TrackingIDChargeDescription16
,TrackingIDChargeAmount16,TrackingIDChargeDescription17
,TrackingIDChargeAmount17,TrackingIDChargeDescription18
,TrackingIDChargeAmount18,TrackingIDChargeDescription19
,TrackingIDChargeAmount19,TrackingIDChargeDescription20
,TrackingIDChargeAmount20,TrackingIDChargeDescription21
,TrackingIDChargeAmount21,TrackingIDChargeDescription22
,TrackingIDChargeAmount22,TrackingIDChargeDescription23
,TrackingIDChargeAmount23,TrackingIDChargeDescription24
,TrackingIDChargeAmount24,TrackingIDChargeDescription25
,TrackingIDChargeAmount25
,TotalOfPositive,TotalOfNegative,Flag1,Flag2
ORDER BY ExpressOrGroundTrackingID
HAVING COUNT(*) > 1;

DELETE FROM '|| getRawTableName ||' mastertbl WHERE mastertbl.InvoiceId IN
(SELECT InvoiceId FROM TABLE_DEDUP)

INSERT INTO '|| getRawTableName ||' mastertbl SELECT * FROM TABLE_DEDUP;