some observations -
1. "spa_usage1 contains app 54 million rows" vs. "The size of Spool 2 is estimated with low confidence to be 567,176,439 rows"
which is a 10x difference. Stats not up to date? or wrong expectations?
2. PPI "PARTITION BY RANGE_N(CAST((USG_DT_SK ) AS INTEGER) BETWEEN 17898 AND 18170 EACH 1, NO RANGE)" does not support at least this query well...
3. PI (S_SK,C_SK) indicate multiple rows for S_SK. Do you have highly / extreme skewed values?
select count(*) from misc.SPA_USAGE1 shows rows as 575,641,759
The skew value of the table is irrelevant as it is reflecting the S_SK,C_SK combination.
select top 100 hashrow(S_SK), count(*)
group by 1
order by 2 desc
PPI - for this specific table you might be better of without PPI...
All rows have same count value.
But in this case it mean that for each matching S_SK your answer set will generate up to 14144 result rows.
Is that what you expect?
sel day_of_year from sys_calendar.calendar where calendar_date=date
it displays no of days previous date means (2011/05/03)
how to write query