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

The SQL text is as follows, the table itself is very small, and the full table scan is very fast, but due to business importance, the query time is required to be as short as possible (comments and text have been removed to ensure customer privacy):

SELECT MERCHCODE AS R_MERCHCODE, TRANDATE, TRANTIME, TRANTYPE AS TRANSTYPE, TRACENO, POSID AS R_POSID, ACCOUNT AS R_CARDNO, AMT, FEE, NVL (RESERVED1,'N') BORDERCARDBUSIFLAG, CASE WHEN I.BANCSRETFLAGIP 0000' THEN' 1' WHEN I.BANCSRETFLAGER 9999' THEN'0' ELSE'2' END AS RETURNCODEFROM IC_MERCHTRANSDETAIL_428 IWHERE GETACCTNO (ACTSTLACCTNO) = GETACCTNO ('14250000000454865') AND ROWNUM

You can see that the predicate information is the customer number, and you can determine that this column is very selective and is very suitable for indexing.

CREATE INDEX IDX_GETACCTNO ON IC_MERCHTRANSDETAIL_428 (GETACCTNO (ACTSTLACCTNO)) PARALLEL 10 TABLESPACE REPT * ERROR at line 1:ORA-30553: The function is not deterministic

Make sure that the function itself is not affected by uncertain values, and create a function index.

Add deterministic and take an alias to view the function creation statement:

CREATE OR REPLACE FUNCTION GETACCTNOCY (acct varchar2) return varchar2 DETERMINISTICistmpacct varchar2 (40); st_res varchar2 (40);-- st_res:=tmpacctbegintmpacct:='';st_res: =''; IF (length (trim (acct)) = 16) THENBEGIN SELECT ACCOUNT INTO tmpacct FROM LINK_L WHERE LINK_L.CARD=LPAD (trim (acct), 20) AND ISO_TYPE='1' AND CATEGORY='0';EXCEPTION WHEN NO_DATA_FOUND THEN tmpacct:=TRIM (ACCT); END;END IF IF (length (trim (acct)) > 17) THEN BEGIN SELECT zh INTO tmpacct FROM load_zhmap WHERE jzh=trim (acct); EXCEPTIONWHEN NO_DATA_FOUND THEN tmpacct:=''; END;END IF; IF (length (trim (acct)) = 17) THEN tmpacct:=substr (acct,1,16); END IF;st_res:=tmpacct;return st_res;EXCEPTIONWHEN OTHERS THENreturn''; END

Create an index:

CREATE INDEX IDX_GETACCTNO ON IC_MERCHTRANSDETAIL_428 (GETACCTNOCY (ACTSTLACCTNO)) TABLESPACE TBSIDX

The execution plan after creating the index is as follows:

The case is relatively simple. I hope it can help you.

| | author profile |

Yao Chong Walk Technology senior database technology expert is familiar with the internal mechanism of Oracle database, rich experience in database and RAC cluster layer fault diagnosis, performance tuning, OWI, database backup recovery and migration.

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.