Fast Path Delete on PPI tables

Database
Highlighted
Enthusiast

Fast Path Delete on PPI tables

I've been working on getting fast path delete to work when deleting all the data in a single partition and am getting some strange results.

I'm hoping someone can explain the behavior I am seeing.

The table in quesition has no secondary indexes, join indexes, constraints or RI defined on it.

It has single level partitioning defined by multiple ranges (BusMoYr is an integer).

The detetes are done through a view, but the view is just a select * from the table.

PPI Definition for the target table:

PRIMARY INDEX DIFOT_SchedLn_CCD_NUPI ( SO_HdrNbr )

PARTITION BY RANGE_N(BusMoYr  BETWEEN 200701  AND 200712  EACH 1 ,

200801  AND 200812  EACH 1 ,

200901  AND 200912  EACH 1 ,

201001  AND 201012  EACH 1 ,

201101  AND 201112  EACH 1 ,

201201  AND 201212  EACH 1 ,

 NO RANGE, UNKNOWN)

;

Example 1:

Delete data from 1 partition in a single range. 

Fast Path delete looks like it is used (“delete OF partition”), but estimate is very high.

Explain

delete from DIFOT_SchedLn_CCD

where BusMoYr =201002;

1) First, we lock a distinct DEV_EIS_T."pseudo table" for write on a RowHash to prevent global deadlock for DEV_EIS_T.DIFOT_SchedLn_CCD.

2) Next, we lock DEV_EIS_T.DIFOT_SchedLn_CCD in view DEV_EIS_ETL.DIFOT_SchedLn_CCD for write.

3) We do an all-AMPs DELETE of a single partition from DEV_EIS_T.DIFOT_SchedLn_CCD in view DEV_EIS_ETL.DIFOT_SchedLn_CCD with a condition of ("DEV_EIS_T.DIFOT_SchedLn_CCD in view DEV_EIS_ETL.DIFOT_SchedLn_CCD.BusMoYr = 201002"). The size is estimated with high confidence to be 15,037,202 rows. The estimated time for this step is 20 minutes and 47 seconds.

4) 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. The total estimated time is 20 minutes and 47 seconds.

Example 2:

Delete data from 2 partitions in a single range. 

Fast Path delete used.  Estimate is 0 seconds.

explain

delete from DIFOT_SchedLn_CCD

where BusMoYr in ('201002','201003');

1) First, we lock a distinct DEV_EIS_T."pseudo table" for write on a RowHash to prevent global deadlock for DEV_EIS_T.DIFOT_SchedLn_CCD.

2) Next, we lock DEV_EIS_T.DIFOT_SchedLn_CCD in view DEV_EIS_ETL.DIFOT_SchedLn_CCD for write.

3) We do an all-AMPs DELETE of 2 partitions from DEV_EIS_T.DIFOT_SchedLn_CCD in view DEV_EIS_ETL.DIFOT_SchedLn_CCD with a condition of ("(DEV_EIS_T.DIFOT_SchedLn_CCD in view DEV_EIS_ETL.DIFOT_SchedLn_CCD.BusMoYr = 201002) OR (DEV_EIS_T.DIFOT_SchedLn_CCD.BusMoYr = 201003)"). The size is estimated with high confidence to be 1 row. The estimated time for this step is 0.00 seconds.

4) 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. The total estimated time is 0.00 seconds.

Example 3:

Delete data from 2 partitions in different ranges. 

This is the strangest one as there are two delete steps in the plan. 

One looks (step 3) looks like a row by row delete.

The other (step 4) appears to use Fast Path delete, but the estimate is high.

Explain

delete from DIFOT_SchedLn_CCD

where BusMoYr in (201012,201101);

  1) First, we lock a distinct DEV_EIS_T."pseudo table" for write on a

     RowHash to prevent global deadlock for DEV_EIS_T.DIFOT_SchedLn_CCD.

  2) Next, we lock DEV_EIS_T.DIFOT_SchedLn_CCD in view

     dev_eis_etl.DIFOT_SchedLn_CCD for write.

  3) We do an all-AMPs DELETE from a single partition of

     DEV_EIS_T.DIFOT_SchedLn_CCD in view dev_eis_etl.DIFOT_SchedLn_CCD

     with a condition of ("(DEV_EIS_T.DIFOT_SchedLn_CCD in view

     dev_eis_etl.DIFOT_SchedLn_CCD.BusMoYr = 201012) OR

     (DEV_EIS_T.DIFOT_SchedLn_CCD.BusMoYr = 201101)").

  4) We do an all-AMPs DELETE of 2 partitions from

     DEV_EIS_T.DIFOT_SchedLn_CCD in view dev_eis_etl.DIFOT_SchedLn_CCD

     with a condition of ("(DEV_EIS_T.DIFOT_SchedLn_CCD in view

     dev_eis_etl.DIFOT_SchedLn_CCD.BusMoYr = 201012) OR

     (DEV_EIS_T.DIFOT_SchedLn_CCD in view

     dev_eis_etl.DIFOT_SchedLn_CCD.BusMoYr = 201101)").  The size is

     estimated with high confidence to be 23,534,349 rows.  The

     estimated time for this step is 32 minutes and 31 seconds.

  5) We spoil the parser's dictionary cache for the table.

  6) 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.

Tags (1)
4 REPLIES
Enthusiast

Re: Fast Path Delete on PPI tables

No help???

Re: Fast Path Delete on PPI tables

Did you figure out the reason of the behavior? I am basically in the similar situation.  Also what should be the speed of the fast delete - sub second?

Thanks

Junior Contributor

Re: Fast Path Delete on PPI tables

Depending on the number of rows the speed might not be subsecond, but way less than the expected minutes.

When there are two DELETE steps this might be due to the NO_RANGE.

The best way to find if it's actually using a FastPath delete would be checking the data from DBQL, CPU and IO should be quite low.

Dieter

Re: Fast Path Delete on PPI tables

We found the issue with deletes, if your procedure (that contains deletes) is compiled / called via ANSI mode it is extremly slow, TERA mode works just fine - the fast delete We were expecting.