PPI delete shows high estimates

Database

PPI delete shows high estimates

DELETE FROM db.tbl_HIST
WHERE trans_date = (SELECT trans_date  FROM db.tbl2 )
AND trans_date = (SELECT trans_date  FROM db.tbl GROUP BY 1)
;

DELETE FROM  db.tbl_HIST
WHERE trans_date between date '2015-05-01' and date '2015-06-01'

I have PPI table and I am trying to insert data into the history table. Now the estimated time is very high which ends up results in query gettign throttled. I have tried doing a full partition delete, but even then the estimated time is high. No secondary indexes on the tables used.Both the hist table and the source table have the same UPI but the History table is partitioned while the source table is not.

UPI(Trans_date, col1, col2) partition by (trans_Date between '2010-01-01' and date '2015-12-31' each interval '1' month)

Any suggestions will be highly appreciated!

4 REPLIES

Re: PPI delete shows high estimates

can you share some demographics of both tables and explain plan ?

Re: PPI delete shows high estimates

EXPLAIN DELETE FROM DB.TBL_HIST
WHERE TRANS_DATE = (SELECT TRANS_DATE  FROM DB. TBL2)
AND TRANS_DATE = (SELECT TRANS_DATE  FROM DB.TBL GROUP BY 1 )

  1) First, we lock a distinct DB."pseudo table"
     for write on a RowHash to prevent global deadlock for
     DB.TBL_HIST.
  2) Next, we lock a distinct DB."pseudo table"
     for read on a RowHash to prevent global deadlock for
     DB.TBL.
  3) We lock a distinct DB."pseudo table" for
     read on a RowHash to prevent global deadlock for
     DB.TBL2.
  4) We lock DB.TBL_HIST for
     write, we lock DB.TBL for
     read, and we lock DB.TBL2 for read.
  5) We do an all-AMPs RETRIEVE step from
     DB.TBL2 by way of an all-rows scan
     with no residual conditions into Spool 1 (all_amps), which is
     built locally on the AMPs.  The size of Spool 1 is estimated with
     high confidence to be 1 row (25 bytes).  The estimated time for
     this step is 0.03 seconds.
  6) We do an all-AMPs DISPATCHER RETRIEVE step from Spool 1 (Last Use)
     by way of an all-rows scan and send the rows back to the
     Dispatcher.
  7) We do an all-AMPs SUM step to aggregate from
     DB.TBL by way of an
     all-rows scan with no residual conditions , grouping by field1 (
     DB.TBL.TRANS_DATE).
     Aggregate Intermediate Results are computed globally, then placed
     in Spool 4.  The size of Spool 4 is estimated with high confidence
     to be 1 row (21 bytes).  The estimated time for this step is 0.38
     seconds.
  8) We do an all-AMPs RETRIEVE step from Spool 4 (Last Use) by way of
     an all-rows scan into Spool 2 (all_amps), which is built locally
     on the AMPs.  The size of Spool 2 is estimated with high
     confidence to be 1 row (25 bytes).  The estimated time for this
     step is 0.04 seconds.
  9) We do an all-AMPs DISPATCHER RETRIEVE step from Spool 2 (Last Use)
     by way of an all-rows scan and send the rows back to the
     Dispatcher.
 10) We do an all-AMPs DELETE from a single partition of
     DB.TBL_HIST with a
     condition of (
     "DB.TBL_HIST.TRANS_DATE =
     :%SSQ21") with a residual condition of (
     "(DB.TBL_HIST.TRANS_DATE =
     :%SSQ20) AND
     (DB.TBL_HIST.TRANS_DATE =
     :%SSQ21)").  The size is estimated with low confidence to be
     1,038,027 rows.  The estimated time for this step is 14 minutes
     and 41 seconds.
 11) Finally, we send out an END TRANSACTION step to all AMPs involved
     in processing the request.
  -> No rows are returned to the user as the result of statement 1.

Thsi is the explain plan. Stats info below:

Hist table

51.8 milliion rows   -- UPI

58 rows - trans_date

Source table or tbl

620 rows - UPI

1 row - trans_date

tbl2 - one row  lookup table

1 row - Trans_Date - UPI

Re: PPI delete shows high estimates

Hi,

I created similar scneario and loaded a table with 12 millions rows .

Explain before collecting stats on Partition and UPI

  3) We do an all-AMPs DELETE from a single partition of
tbl_HIST with a condition of (
"tbl_HIST.Week_End_Dt = DATE '2015-06-15'") with
a residual condition of ("tbl_HIST.Week_End_Dt =
DATE '2015-06-15'"). The size is estimated with no confidence to
be 1,250,386 rows. The estimated time for this step is 37.00
seconds.

After Collecting Stats on UPI and Partition
4) We do an all-AMPs DELETE of 60 partitions from
tbl_HIST with a condition of (
"(tbl_HIST.Week_End_Dt >= DATE '2014-04-04') AND
(tbl_HIST.Week_End_Dt <= DATE '2015-06-04')").
The size is estimated with high confidence to be 12,443,006 rows.
The estimated time for this step is 6 minutes and 39 seconds.

Steps to do :
COLLECT STATISTICS ON tbl_HIST COLUMN PARTITION;
COLLECT STATISTICS ON tbl_HIST COLUMN (trans_date, PARTITION );
COLLECT STATISTICS ON tbl_HIST COLUMN (upi );
/* Might be helpful if lot of dates are there or interval is 1 day */
COLLECT STATISTICS ON tbl_HIST COLUMN (trans_date );

Re: PPI delete shows high estimates

Hi ,

I forgot to ask you ,partition is on 1 month interval or 1 week inteval .

if you have less partiton to delete then we can use partition no  for deleting all rows from that partition

DELETE FROM tbl_history      WHERE  PARTITION = (
SELECT th.PARTITION FROM tbl_history th WHERE Week_End_Dt BETWEEN (SELECT MAX(col1) FROM dates ) AND (SELECT MAX(col3) FROM dates ) GROUP BY 1) ;