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

Editor to share with you the Oracle function to invalidate the index column how to do, I hope you have something to gain after reading this article, let's discuss it together!

I. data version and original statement and related information

1. Version information

SQL > select * from v$version BANNER-- -- Oracle Database 10g Release 10.2.0.3.0-64bit Production PL/SQL Release 10.2.0.3.0-Production CORE 10.2.0.3.0 Production TNS for Linux: Version 10.2. 0.3.0-Production NLSRTL Version 10.2.0.3.0-Production

two。 The original statement and its execution plan

SQL > set autotrace traceonly exp SELECT acc_num Curr_cd, DECODE ('20110728' (SELECT TO_CHAR (LAST_DAY (TO_DATE ('20110728,' YYYYMMDD') 'YYYYMMDD') FROM DUAL) 0 Adj_credit_int_lv1_amt + adj_credit_int_lv2_amt-adj_debit_int_lv1_amt- Adj_debit_int_lv2_amt) AS interest FROM acc_pos_int_tbl ACC_POS_INT_TBL1 WHERE SUBSTR (business_date 1, 6) = SUBSTR ('20110728, 1 6) AND business_date set linesize 190SQL > @ Idx_Info Enter value for owner: goex_admin old 10: AND owner = upper ('& owner') new 10: AND owner = upper ('goex_admin') Enter value for table_name: ACC_POS_INT_TBL old 11: AND a.table_name = upper ('& table_name') new 11: AND A.table_name = upper ('ACC_POS_INT_TBL') TABLE_NAME INDEX_NAME COL_NAM CL_ POS STATUS IDX_TYP DSCD-ACC_POS_ INT_TBL ACC_POS_INT_10DIG_IDX SYS_NC00032 $1 VALID FUNCTION-BASED ASC NORMAL ACC_POS_INT_TBL ACC_POS_INT_10DIG_IDX BUSINESS_DATE 2 VALID FUNCTION-BASED ASC NORMAL ACC_POS_INT_TBL ACC_POS_INT_10DIG_IDX CURR_CD 3 VALID FUNCTION-BASED ASC NORMAL ACC_POS_INT_TBL PK_ACC_POS_INT_TBL ACC_NUM 1 VALID NORMAL ASC ACC_POS_INT_TBL PK_ACC_POS_INT_TBL BUSINESS_DATE 2 VALID NORMAL ASC

From the point of view of the index, there is an index based on the primary key that contains BUSINESS_DATE columns, while the query statement does not walk the index but selects a full table scan, and the rows Rows and bytes returned by the estimate are also surprisingly large, with a cost value of 96399, close to 10W.

Second, analyze and reform the SQL statement

1. Analysis of the original SQL statement

The business_date column of the where clause in the SQL statement filters the records business_date = to_char (add_months (to_date ('20110728) + 1))

3. Modified SQL statement

SELECT acc_num, curr_cd, DECODE ('20110728' (SELECT TO_CHAR (LAST_DAY (TO_DATE ('20110728,' YYYYMMDD') 'YYYYMMDD') FROM DUAL), 0 Adj_credit_int_lv1_amt + adj_credit_int_lv2_amt-adj_debit_int_lv1_amt-adj_debit_int_lv2_amt) AS interest FROM Acc_pos_int_tbl ACC_POS_INT_TBL1 WHERE business_date > = to_char (last_day (add_months (to_date ('20110728') 'yyyymmdd'),-1)) + 1 'yyyymmdd') AND business_date =' 20110701' AND "BUSINESS_DATE" = '20110701' AND "BUSINESS_DATE" @ Tab_Stat Enter value for input_table_name: ACC_POS_INT_TBL old 11: WHERE table_name = upper ('& input_table_name') new 11: WHERE table_name = upper ('ACC_POS_INT_TBL') Enter value for input_owner: goex_admin old 12: AND owner = upper ('& input_owner') new 12: AND owner = upper ('goex_admin') NUM_ROWS BLKS EM_BLKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN AVG_ROWS_PER_BLOCK LST_ANLY STA- -33659947 437206 1322 855 0 99 77 27-SEP-11 NO

two。 Information about the index

SQL > @ Idx_Stat Enter value for input_table_name: ACC_POS_INT_TBL old 11: WHERE table_name = upper ('& input_table _ name') new 11: WHERE table_name = upper ('ACC_POS_INT_TBL') Enter value for input_owner: goex_admin Old 12: AND owner = upper ('& input_owner') new 12: AND owner = upper ('goex_admin') BLEV IDX_NAME LF_BLKS DST_KEYS NUM_ROWS LF_PER_KEY DAT_BLK_PER_KEY CLUS_FCT LST_ANLY -- 3 PK_ACC_POS_INT_TBL 155658 33777720 33777720 11 33777447 27-SEP-11 3 ACC_POS_INT_10DIG_IDX 160247 32850596 32850596 11 32763921 27-SEP-11

3. Try to create an index on the BUSINESS_DATE column

SQL > create index I_ACC_POS_INT_TBL_BS_DT on ACC_POS_INT_TBL (BUSINESS_DATE) tablespace tbs_tmp nologging; Index created. SQL > @ Idx_Stat Enter value for input_table_name: ACC_POS_INT_TBL old 11: WHERE table_name = upper ('& input_ Table_name') new 11: WHERE table_name = upper ('ACC_POS_INT_TBL') Enter value for input_owner: goex_admin Old 12: AND owner = upper ('& input_owner') new 12: AND owner = upper ('goex_admin') BLEV IDX_NAME LF_BLKS DST_KEYS NUM_ROWS LF_PER_KEY DAT_BLK_PER_KEY CLUS_FCT LST_ANLY -2 Illustrated ACCPLOSINTION TBLTHANGBS2 DT 93761 908 33659855 103 506 460007 30-SEP-11 3 PK_ACC_POS_INT_TBL 155658 33777720 33777720 11 33777447 27-SEP-11 3 ACC_POS_INT_10DIG_IDX 160247 32850596 32850596 11 32763921 27-SEP-11

After the establishment of the index, the clustering factor is small, almost close to the number of blocks on the table.

4. Use the execution plan after the newly created index

Execution Plan Plan hash value: 2183566226- -| Id | Operation | Name | | Rows | Bytes | Cost (% CPU) | Time |-| -| 0 | SELECT STATEMENT | | 1065K | 39m | 17586 (1) | 00:03:32 | | 1 | FAST DUAL | | 1 | 2 (0) | 00:00:01 | | 2 | TABLE ACCESS BY INDEX ROWID | ACC_POS_INT_TBL | | 1065K | 39m | 17586 (1) | 00:03:32 | | * 3 | INDEX RANGE SCAN | I_ACC_POS_INT_TBL_BS_DT | 1065K | | 2984 (1) | 00:00:36 |-- | -Predicate Information (identified by operation id): 3-access ("BUSINESS_DATE" > = '20110701' AND "BUSINESS_DATE"

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.