Re: Function Based Index

From: rplumley <member26369_at_dbforums.com>
Date: Tue, 11 Mar 2003 23:35:48 +0000
Message-ID: <2631800.1047425748@dbforums.com>

I posted this question to a DBA, and found the following solution.

Basically, when a function returns a VARCHAR2, Oracle assumes the maximum value (4000 bytes). This is true even if you return SUBSTR(ret_value,1,3), Oracle comes out with 4000 bytes.

Solution:

When creating the index, utilize the SUBSTR() function, and parse out only the maximum length that the function woud require.

CREATE INDEX dw_pdm.dss_clms_idx20 ON dw_pdm.dss_clms ( SUBSTR( dw_pdm.pdm_pkg.coverage_type (

      rmt_src_cd


     ,li_proc_cd

     ,pvdr_typ_cd

     ,pvdr_sufx_cd

     ,clm_catg_cd

     ,li_typ_svc_cd

     ,cond_caus_cd ),  1, 1 )

)
..

This is not officially a bug, but a feature of Oracle. In once sense, Oracle cannot assume the length being returned, so it goes for the maximum. It would be nice if there were data types of a specific length or maximum length.

--
Posted via http://dbforums.com

Received on Tue Mar 11 2003 - 17:35:48 CST