1. I have created following view as follows:
REPLACE VIEW UDW2BASESECUREVIEW1.ADJD_MCE_RPT_poc
AS LOCKING ROW FOR ACCESS
,(erly_srvc_dt - EXTRACT(DAY FROM erly_srvc_dt) + 1) as INCUR_SRVC_DT
2. I want to run the following query against this view
select INCUR_SRVC_DT, sum( CHK_AMT) from udw2basesecureview1.adjd_mce_rpt_poc where INCUR_SRVC_DT = DATE '2012-09-01' group by 1;
2) We do an all-AMPs RETRIEVE step from UDW2BASE.ADJD_MCE in
view udw2basesecureview1.adjd_mce_rpt_poc by way of an
all-rows scan with a condition of ("(((UDW2BASE.ADJD_MCE in
view udw2basesecureview1.adjd_mce_rpt_poc.ERLY_SRVC_DT )-
(EXTRACT(DAY FROM (UDW2BASE.ADJD_MCE in view
udw2basesecureview1.adjd_mce_rpt_poc.ERLY_SRVC_DT ))))+ 1 )=
DATE '2012-09-01'") into Spool 11 (all_amps) (compressed
columns allowed), 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 11 is estimated
with no confidence to be 24,065,033 rows (649,755,891 bytes).
The estimated time for this step is 4 hours and 31 minutes.
3. Since INCUR_SRVC_DT is a derived column, it is doing table scan. To avoid table scan I want to do some kind of indexing on the derived field itself. Is there a strategy like indexing or something similar which will help and avoid table scan.
Any input is greatly appreciated! Thanks, DA
You can't do indexes on calculated columns, but you might create a Join Index with that calculation.
But unless you have lots of years of data in your table an index will be not selective enough and the optimizer will not use it.