Is index creation allowed for a derived column to improve performance?

Database
da
N/A

Is index creation allowed for a derived column to improve performance?

1. I have created following view as follows:

REPLACE VIEW UDW2BASESECUREVIEW1.ADJD_MCE_RPT_poc

AS LOCKING ROW FOR ACCESS

SELECT

UDW_MED_CLM_ID

,UDW_ADJD_MCE_ID

,....

,CHK_AMT

,ERLY_SRVC_DT

,(erly_srvc_dt  - EXTRACT(DAY FROM erly_srvc_dt) + 1) as INCUR_SRVC_DT

,...

,...

FROM  UDW2BASEVIEW1.ADJD_MCE;

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;

Explain output

       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

1 REPLY
N/A

Re: Is index creation allowed for a derived column to improve performance?

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.