PPI issue - all partitions scanned instead of single one on V2R6.2

Database

PPI issue - all partitions scanned instead of single one on V2R6.2

Hello All,

We have recently upgraded one of our test machines to V2R6.2 from V2R5.1 and we have a performance issue afterwards. We are using huge transaction tables with PPI and simple view without from clause, that stands for a constant for effective date as follows:

CREATE SET TABLE dw.Transaction ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
Account_Nbr CHAR(16) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
Effective_Date DATE FORMAT 'YYYY-MM-DD' NOT NULL,
Post_Date DATE FORMAT 'YYYY-MM-DD' NOT NULL,
Tran_Seq_Id DECIMAL(7,0) NOT NULL,
….
)
UNIQUE PRIMARY INDEX Transaction_UPPI ( Account_Nbr ,Effective_Date ,
Post_Date ,Tran_Seq_Id )
PARTITION BY RANGE_N(Post_Date BETWEEN DATE '1999-01-01' AND DATE '2030-12-31' EACH INTERVAL '1' MONTH )
INDEX Transaction_NUSI ( Account_Nbr );

REPLACE VIEW dw_meta.V_DW_Date AS
SELECT cast(1080311 as date) DW_Date, 1 Date_Weight;

The problem is that new V2R6 is not using this view as effectively as V2R5 in conditional clauses. For example for this select:

select * from dw.Transaction where Post_Date = dw_meta.V_DW_Date.DW_Date

explains shows on V2R5 only single one partition is searched on all amps,

3) We do an all-AMPs RETRIEVE step from a single partition of
dw.Transaction with a condition of (
"dw.Transaction.Post_Date = DATE '2008-03-11'") with a
residual condition of ("dw.Transaction.Post_Date = DATE
'2008-03-11'") into Spool 1 (group_amps), which is built locally
on the AMPs. The input table will not be cached in memory, but it
is eligible for synchronized scanning. The size of Spool 1 is
estimated with high confidence to be XYZ rows. The
estimated time for this step is 5.29 seconds.

but on V2R6 it is an all rows scan across all partitions. Furthermore, data from views with constants no longer seem to be interpreted as such, but are regularly joined as tables in V2R6.
6) We do an all-AMPs JOIN step from Spool 5 (Last Use) by way of an
all-rows scan, which is joined to dw.transaction by way
of an all-rows scan with no residual conditions. Spool 5 and
dwkb.posted_transaction are joined using a product join, with a
join condition of ("dw.transaction.Post_Date = DWE_DATE").
The input table dwkb.posted_transaction will not be cached in
memory, but it is eligible for synchronized scanning. The result
goes into Spool 4 (group_amps), which is built locally on the AMPs.
The size of Spool 4 is estimated with no confidence to be XYZ
rows. The estimated time for this step is 1 minute and 5 seconds.

Does anybody know why? Is there any way to prevent this?

Best Regards

Lubomir
3 REPLIES

Re: PPI issue - all partitions scanned instead of single one on V2R6.2

Sorry I can't help here - I just wanted to say I have exactly the same situation. We migrated to 2.6 from 2.5 and a 2 billion row table is now being accessed by an all-rows scan - painful.

The table was implemented with a timestamp as the partitioning range key wrapped inside a date function. Predicates look like this....WHERE CAST(ppi_key AS DATE FORMAT 'YYYY-MM-DD) BETWEEN '2008-01-01' AND '2008-01-03'

I've tried rewriting the casts and played with timestamps and dates as well as sys_calendar - no joy. As I said this worked fine under 2.5 but it's too late to go back now. Stats are up to date.

We previously had a 10 minutes.

If anyone has any ideas I would be hugely grateful.

Re: PPI issue - all partitions scanned instead of single one on V2R6.2

Sorry I can't help here - I just wanted to say I have exactly the same situation. We migrated to 2.6 from 2.5 and a 2 billion row table is now being accessed by an all-rows scan - painful.

The table was implemented with a timestamp as the partitioning range key wrapped inside a date function. Predicates look like this....WHERE CAST(ppi_key AS DATE FORMAT 'YYYY-MM-DD) BETWEEN '2008-01-01' AND '2008-01-03'

I've tried rewriting the casts and played with timestamps and dates as well as sys_calendar - no joy. As I said this worked fine under 2.5 but it's too late to go back now. Stats are up to date.

We previously had a 10 minutes.

If anyone has any ideas I would be hugely grateful.

Re: PPI issue - all partitions scanned instead of single one on V2R6.2

Sorry for the duplicates - just started on this forum - and discovered angle brackets are a bad idea.

When I said "we had a 10 mins" What I typed was... "we previously had a less-than 10 second response time and now greater-than 10 mins"