I can see that the two tables have UPIs with only 1 column matching. Have you thought of creating AJI and see the preformance?
All those unique indexes seem suspicious, are they actually based on access/join paths or do they just implement the logical keys? A NUPI (FIRST_DT ,VISITOR_ID) on T_CUST would allow a direct join without preparartion.
Joining up to 1.300.000.000 rows and aggregating them is a lot of work, can you show the explain?
The explain looks like expected, redistributing CUST_T and then a local join to KEY_T.
There's "no confidence" for join and aggregate (which stats are collected?), if the actual number is much larger than expected it will be much slower. But you should never use wall clock time as it's dependant on the load on your system.
Do you have access to the Querylog? The QryLogStepsV will have all details about estimated vs. actual.
How often is this query supposed to run?
I was just analysing your scenaio, and got this link, just read the disadvantages of PPI and I hope you will be able to optimize your query.
There's no PARTITION stats on KEY_T and no re-collection for three weeks.
The UPI on T_CUST does not provide a better distribution than a NUPI (FIRST_DT ,VISITOR_ID), but prevents a fast AMP-local join.
And the number of rows is probbaly underestimated.
Btw, you'll probably not run a select like that returning millions of rows, is this part of an Insert/Select or Create Table As?
I think you need to revisit on PI (NUPI) selection , suiting the business requirement.
Do you really need partition day wise?
If you have option to revise PI, you can improve performance of this query. You might need to implement following in CUST_T table. This will help to maintain uniquness, support to other queries using USI and improved performance of mentioned or similar to mentioned queries.
PRIMARY INDEX ( FIRST_DT ,VISITOR_ID )
PARTITION BY RANGE_N(FIRST_DT BETWEEN DATE '2010-08-01'
AND '2099-12-31' EACH INTERVAL '1' DAY )
UNIQUE INDEX ( FIRST_DT ,VISITOR_ID,CUST_ID );