Kazakh Oracle database nested view tuning record 20151226

1. Query Statement Analysis

/* Formatted on 2015/12/26 15:09:55 (QP5 v5.163.1008.3004) */
  SELECT sheetID,
         b*****,
         b*****,
         b*****,< c5/>b*****,
         b*****,
         b*****,
         b***** from
    view_b2bsaleckex
   WHERE sheetid = ' 151*************** ' ORDER BY 
b*****

(1) The reason for the slow query: Using a nested view, where the function Fgetb2blsckdh (sheetID) in the view View_b2bsaleck, resulting in the sheetID index invalidation;
(2) Modify the proposal: rewrite the SQL statement, no longer use nested view and the function, or add a function index to the function, because the addition of indexes may cause the system to run slowly, so it is recommended to add at night.
(3) The amendments are as follows:
1 no longer use nested view statements to modify the following:

  SELECT sheetID,
         b*****,
         b*****,
         b*****,
         b*****,
         b*****, b*****
    , b***** From Boutdetail 
   WHERE fgetb2blsckdh (sheetid)  = ' 151*************** ' ORDER by 
b*****

2) Create a function index

/* Formatted on 2015/12/26 16:01:55 (QP5 v5.163.1008.3004)/
CREATE INDEX IDX_BOUTDETAIL_FGETB2BLSCKDH on
   Boutdetail (FGETB2BLSCKDH (sheetID));

2, modify the process record

(1) Update FGETB2BLSCKDH function, add deterministic parameter

The CREATE OR REPLACE function d******. FGETB2BLSCKDH (IN_LSDH in varchar2) return
  varchar2 deterministic is
  L_LSCKDH varchar2 (a);
Begin
  begin to

    * * *

  ;
  return L_LSCKDH;
End FGETB2BLSCKDH;
/

(2) Create a function index

/* Formatted on 2015/12/26 16:01:55 (QP5 v5.163.1008.3004)/
CREATE INDEX IDX_BOUTDETAIL_FGETB2BLSCKDH on
   Boutdetail (FGETB2BLSCKDH (sheetID));