Shulou(Shulou.com)06/01 Report--

The following is excerpted from Chapter 12.4.2 of Oracle SQL Advanced programming-function-based indexing [in which part of the code has been modified, please refer to the book]

If a predicate applies a function to an index column, the optimizer does not select the index on that column. For example, for the predicate to_char (CYRQ, 'YYYY-MM-DD') =' 2014-01-21 indexes, the index on the CYRQ column is not selected because the to_char function is applied on the index column. This limitation can be overcome by the expression to_char (CYRQ) when creating a function-based index. The result of the function-based index stored in the function. The expression declared in the predicate must match based on the expression declared by the index of the function.

A function-based index can also be built on a user-defined function, but the function must be defined as a deterministic function, that is, a consistent value must be returned for each execution of the function. User-defined functions that do not follow this rule cannot be used to create function-based indexes.

In listing 12-14, the SELECT statement accesses the CK10_ GHDJ table using the to_char (CYRQ, 'YYYY-MM-DD') =' 2014-01-21 'clause. If there is no function-based index, the optimizer chooses a full table scan access plan. After the function-based index INDEX_CK10_GHDJ_CYRQ2 is added through the expression to_char (CYRQ, 'YYYY-MM-DD'), the optimizer chooses the index-based access path for the SELECT statement.

CREATE INDEX INDEX_CK10_GHDJ_CYRQ2 ON CK10_GHDJ (TO_CHAR (CYRQ,'YYYY-MM-DD')); SELECT COUNT (0) FROM CK10_GHDJ G WHERE TO_CHAR (G.CYRQ, 'YYYY-MM-DD') =' 2014-01-21'

Notice the last printed access predicate "SYS_NC00009 $" = '1000' in listing 12-14. Some of the details on the implementation based on functional indexes are listed in listing 12-15. The function-based index adds a virtual column, the declared expression value as the default value, and then indexes on the virtual column. This virtual column is visible from the dba_tab_cols view, and the dba_tab_cols.data_default column shows the expression used to populate the virtual column. A further dba_ind_columns view shows that the virtual column is indexed.

SELECT DATA_DEFAULT, HIDDEN_COLUMN, VIRTUAL_COLUMN FROM DBA_TAB_COLS WHERE TABLE_NAME = 'CK10_GHDJ' AND VIRTUAL_COLUMN =' YES'

It is important to collect the statistics of the table after the function-based index is added. If not collected, the new virtual column has no statistics, which can lead to performance anomalies. The script analyze_table_sfp.sql is used to collect table statistics and set cascade= > true. Code 12-16 shows the contents of the analyze_talbe_sfp.sql script.

Listing 12-16 Analyze_table_sfp.sql script

Begin dbms_stats.gather_table_stats (ownname = > user, tabname = > 'CK10_GHDJ', estimate_percent = > 30, cascade = > true); end;/

Function-based indexes can also be displayed using virtual columns. Indexes can also be added on this virtual column. The added benefit of this approach is that you can also use virtual columns as partitioning keys to apply the partitioning scheme. In listing 12-17, a new virtual column cyrq_char is added to the table using the virtual keyword. Then a global partitioned index is established on the cyrq_char column. The execution plan of the SELECT statement shows that the table is accessed using the newly created index, and the predicate to _ char (CYRQ, 'YYYY-MM-DD') =' 2014-01-21'is rewritten as the predicate cyrq_char='2014-01-21'to use the virtual column.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.