1 2 Previous Next 16 Replies Latest reply: Dec 1, 2012 4:50 AM by chris227 RSS

    tuning regexp_like

    949210
      the below query is performing slow. it takes upto 3.5 seconds
      the background:the output should have records that start with what the end user enters(query text)
      if no such records are found display records that have the query text anywhere in them
       SELECT PROD_DETAILS,SIGN,ROWNUM FROM( SELECT ROWNUM,PROD_DETAILS,SIGN,
       (CASE WHEN REGEXP_LIKE(PROD_DETAILS,'^ups','i') THEN '1' ELSE '2' END) AS 
       SIGN2 FROM (SELECT  ROWNUM,PROD_DETAILS,SIGN FROM  
       MV_PROD_SEARCH_DET2 a WHERE REGEXP_LIKE(PROD_DETAILS,'ups','i')) 
       order by SIGN2,SIGN desc) where rownum<15
      the database version is
      Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
      PL/SQL Release 10.2.0.1.0 - Production
      "CORE     10.2.0.1.0     Production"
      TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
      NLSRTL Version 10.2.0.1.0 - Production
      the parameters relevant to optimizer are as follows
      NAME                                               TYPE        VALUE                                                                                          
      
            
      ---------------------------------------------------------------------------------------------------- 
      optimizer_dynamic_sampling                         integer     2                                                                                              
      
            
      optimizer_features_enable                          string      10.2.0.1                                                                                       
      
            
      optimizer_index_caching                            integer     0                                                                                              
      
            
      optimizer_index_cost_adj                           integer     100                                                                                            
      
            
      optimizer_mode                                     string      ALL_ROWS                                                                                       
      
            
      optimizer_secure_view_merging                      boolean     TRUE                                                                                           
      
            
      SQL>sho parameterdb_file_multi
      NAME                                               TYPE        VALUE                                                                                          
      
            
      ---------------------------------------------------------------------------------------------------- 
      db_file_multiblock_read_count                      integer     16                                                                                             
      
            
      SQL>sho parameter db_block_size
      NAME                                               TYPE        VALUE                                                                                          
      
            
      ---------------------------------------------------------------------------------------------------- 
      db_block_size                                      integer     8192                                                                                           
      SQL>sho parameter cursor_sharing
      NAME                                               TYPE        VALUE                                                                                          
      
            
      ---------------------------------------------------------------------------------------------------- 
      cursor_sharing                                     string      EXACT                                                                                          
      
      
      SQL>column sname format a20
      SQL>column pname format a20
      SQL>column pval2 format a20
      SQL>select
                     sname
                   , pname
                   , pval1
                   , pval2
          FROM
                   sys.aux_stats$;      
      
      SNAME                PNAME                     PVAL1 PVAL2              
      -------------------- -------------------- ---------- --------------------
      SYSSTATS_INFO        STATUS                          COMPLETED            
      SYSSTATS_INFO        DSTART                          11-23-2011 03:53     
      SYSSTATS_INFO        DSTOP                           11-23-2011 03:53     
      SYSSTATS_INFO        FLAGS                         1                      
      SYSSTATS_MAIN        CPUSPEEDNW           1618.384401                      
      SYSSTATS_MAIN        IOSEEKTIM                    10                      
      SYSSTATS_MAIN        IOTFRSPEED                 4096                      
      SYSSTATS_MAIN        SREADTIM                                             
      SYSSTATS_MAIN        MREADTIM                                             
      SYSSTATS_MAIN        CPUSPEED                                             
      SYSSTATS_MAIN        MBRC                                                 
      SYSSTATS_MAIN        MAXTHR                                               
      SYSSTATS_MAIN        SLAVETHR                                             
      
      
      ------------------------------SQL TUNING ADVISOR---------------------------------
      ------------------------------SQL TUNING ADVISOR---------------------------------
      
      
      
      GENERAL INFORMATION SECTION
      -------------------------------------------------------------------------------
      Tuning Task Name                  : staName79970
      Tuning Task Owner                 : LOOKING4
      Tuning Task ID                    : 13518
      Scope                             : COMPREHENSIVE
      Time Limit(seconds)               : 1800
      Completion Status                 : COMPLETED
      Started at                        : 11/29/2012 15:09:57
      Completed at                      : 11/29/2012 15:09:58
      Number of SQL Restructure Findings: 2
      
      -------------------------------------------------------------------------------
      Schema Name: LOOKING4
      SQL ID     : 0b4qbmsn9b61q
      SQL Text   : SELECT PROD_DETAILS,SIGN,ROWNUM FROM( SELECT
                   ROWNUM,PROD_DETAILS,SIGN,
                    (CASE WHEN REGEXP_LIKE(PROD_DETAILS,'^ups','i') THEN '1' ELSE
                   '2' END) AS 
                    SIGN2 FROM (SELECT  ROWNUM,PROD_DETAILS,SIGN FROM  
                    MV_PROD_SEARCH_DET2 WHERE REGEXP_LIKE(PROD_DETAILS,'ups','i')) 
                    order by SIGN2,SIGN desc) where rownum<15
      
      -------------------------------------------------------------------------------
      FINDINGS SECTION (2 findings)
      -------------------------------------------------------------------------------
      
      1- Restructure SQL finding (see plan 1 in explain plans section)
      ----------------------------------------------------------------
        The predicate  REGEXP_LIKE ("MV_PROD_SEARCH_DET2"."PROD_DETAILS",'ups','i')
        used at line ID 7 of the execution plan contains an expression on indexed
        column "PROD_DETAILS". This expression prevents the optimizer from selecting
        indices on table "LOOKING4"."MV_PROD_SEARCH_DET2".
      
        Recommendation
        --------------
        - Rewrite the predicate into an equivalent form to take advantage of
          indices. Alternatively, create a function-based index on the expression.
      
        Rationale
        ---------
          The optimizer is unable to use an index if the predicate is an inequality
          condition or if there is an expression or an implicit data type conversion
          on the indexed column.
      
      2- Restructure SQL finding (see plan 1 in explain plans section)
      ----------------------------------------------------------------
        The predicate  REGEXP_LIKE ("MV_PROD_SEARCH_DET2"."PROD_DETAILS",'ups','i')
        used at line ID 7 of the execution plan contains an expression on indexed
        column "PROD_DETAILS". This expression prevents the optimizer from selecting
        indices on table "LOOKING4"."MV_PROD_SEARCH_DET2".
      
        Recommendation
        --------------
        - Rewrite the predicate into an equivalent form to take advantage of
          indices. Alternatively, create a function-based index on the expression.
      
        Rationale
        ---------
          The optimizer is unable to use an index if the predicate is an inequality
          condition or if there is an expression or an implicit data type conversion
          on the indexed column.
      
      -------------------------------------------------------------------------------
      ADDITIONAL INFORMATION SECTION
      -------------------------------------------------------------------------------
      - The optimizer could not merge the view at line ID 5 of the execution plan.
        The optimizer cannot merge a view that contains a "ROWNUM" pseudo column.
      - The optimizer could not merge the view at line ID 2 of the execution plan.
        The optimizer cannot merge a view that contains a "ROWNUM" pseudo column.
      
      -------------------------------------------------------------------------------
      EXPLAIN PLANS SECTION
      -------------------------------------------------------------------------------
      
      1- Original
      -----------
      Plan hash value: 1069350749
       
      --------------------------------------------------------------------------------------------------
      | Id  | Operation                  | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
      --------------------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT           |                     |    14 |  7350 |     3  (34)| 00:00:01 |
      |*  1 |  COUNT STOPKEY             |                     |       |       |            |          |
      |   2 |   VIEW                     |                     |    15 |  7875 |     3  (34)| 00:00:01 |
      |*  3 |    SORT ORDER BY STOPKEY   |                     |    15 |  7875 |     3  (34)| 00:00:01 |
      |   4 |     COUNT                  |                     |       |       |            |          |
      |   5 |      VIEW                  |                     |    15 |  7875 |     2   (0)| 00:00:01 |
      |   6 |       COUNT                |                     |       |       |            |          |
      |*  7 |        MAT_VIEW ACCESS FULL| MV_PROD_SEARCH_DET2 |    15 |   630 |     2   (0)| 00:00:01 |
      --------------------------------------------------------------------------------------------------
       
      Query Block Name / Object Alias (identified by operation id):
      -------------------------------------------------------------
       
         1 - SEL$1
         2 - SEL$2 / from$_subquery$_001@SEL$1
         3 - SEL$2
         5 - SEL$3 / from$_subquery$_002@SEL$2
         6 - SEL$3
         7 - SEL$3 / MV_PROD_SEARCH_DET2@SEL$3
       
      Predicate Information (identified by operation id):
      ---------------------------------------------------
       
         1 - filter(ROWNUM<15)
         3 - filter(ROWNUM<15)
         7 - filter( REGEXP_LIKE ("PROD_DETAILS",'ups','i'))
       
      Column Projection Information (identified by operation id):
      -----------------------------------------------------------
       
         1 - "PROD_DETAILS"[VARCHAR2,1000], "SIGN"[VARCHAR2,42], ROWNUM[4]
         2 - "PROD_DETAILS"[VARCHAR2,1000], "SIGN"[VARCHAR2,42]
         3 - (#keys=2) CASE  WHEN  REGEXP_LIKE ("PROD_DETAILS",'^ups','i') THEN '1' ELSE '2' 
             END [1], INTERNAL_FUNCTION("SIGN")[42], "PROD_DETAILS"[VARCHAR2,1000]
         4 - "PROD_DETAILS"[VARCHAR2,1000], "SIGN"[VARCHAR2,42]
         5 - "PROD_DETAILS"[VARCHAR2,1000], "SIGN"[VARCHAR2,42]
         6 - "PROD_DETAILS"[VARCHAR2,1000], "SIGN"[VARCHAR2,42]
         7 - "PROD_DETAILS"[VARCHAR2,1000], "SIGN"[VARCHAR2,42]
      
      -------------------------------------------------------------------------------
      
      --------------------------------THE BELOW IS THE AUTOTRACE OUTPUT----------------------
      --------------------------------THE BELOW IS THE AUTOTRACE OUTPUT----------------------
      Autotrace Enabled
      Shows the execution plan as well as statistics of the statement.
      Plan hash value: 1069350749
       
      --------------------------------------------------------------------------------------------------
      | Id  | Operation                  | Name                | Rows  | Bytes | Cost (%CPU)| Time     |
      --------------------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT           |                     |    14 |  7350 |     3  (34)| 00:00:01 |
      |*  1 |  COUNT STOPKEY             |                     |       |       |            |          |
      |   2 |   VIEW                     |                     |    15 |  7875 |     3  (34)| 00:00:01 |
      |*  3 |    SORT ORDER BY STOPKEY   |                     |    15 |  7875 |     3  (34)| 00:00:01 |
      |   4 |     COUNT                  |                     |       |       |            |          |
      |   5 |      VIEW                  |                     |    15 |  7875 |     2   (0)| 00:00:01 |
      |   6 |       COUNT                |                     |       |       |            |          |
      |*  7 |        MAT_VIEW ACCESS FULL| MV_PROD_SEARCH_DET2 |    15 |   630 |     2   (0)| 00:00:01 |
      --------------------------------------------------------------------------------------------------
       
      Predicate Information (identified by operation id):
      ---------------------------------------------------
       
         1 - filter(ROWNUM<15)
         3 - filter(ROWNUM<15)
         7 - filter( REGEXP_LIKE ("PROD_DETAILS",'ups','i'))
      
         Statistics
      -----------------------------------------------------------
                    54  recursive calls
                    20  db block gets
                    29  consistent gets
                     0  physical reads
                  4588  redo size
                   552  bytes sent via SQL*Net to client
                   234  bytes received via SQL*Net from client
                     1  SQL*Net roundtrips to/from client
                     1  sorts (memory)
                     0  sorts (disk)
      
      Query Run In:Query Result 2
      ------------------------------the below is explain plan output--------------------------------------- ------------------------------the below is explain plan output--------------------------------------- Plan hash value: 103984305 --------------------------------------------------------------------------------- | Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     | --------------------------------------------------------------------------------- |   0 | SELECT STATEMENT  |             |     8 | 88648 |     2   (0)| 00:00:01 | |*  1 |  TABLE ACCESS FULL| PLAN_TABLE$ |     8 | 88648 |     2   (0)| 00:00:01 | --------------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    1 - filter("STATEMENT_ID"='MyPlan3') Note -----    - dynamic sampling used for this statement ------------------------------the below is tkprof output--------------------------------------- ------------------------------the below is tkprof output--------------------------------------- TKPROF: Release 10.2.0.1.0 - Production on Thu Nov 29 16:40:00 2012 Copyright (c) 1982, 2005, Oracle.  All rights reserved. Trace file: c:\oracle\product\10.2.0\db_1\RDBMS\trace\orcl_ora_684.trc Sort options: default ******************************************************************************** count    = number of times OCI procedure was executed cpu      = cpu time in seconds executing elapsed  = elapsed time in seconds executing disk     = number of physical reads of buffers from disk query    = number of buffers gotten for consistent read current  = number of buffers gotten in current mode (usually for update) rows     = number of rows processed by the fetch or execute call ******************************************************************************** alter session set sql_trace=true call     count       cpu    elapsed       disk      query    current        rows ------- ------  -------- ---------- ---------- ---------- ----------  ---------- Parse        0      0.00       0.00          0          0          0           0 Execute      1      0.00       0.00          0          0          0           0 Fetch        0      0.00       0.00          0          0          0           0 ------- ------  -------- ---------- ---------- ---------- ----------  ---------- total        1      0.00       0.00          0          0          0           0 Misses in library cache during parse: 0 Optimizer mode: ALL_ROWS Parsing user id: SYS ******************************************************************************** select default$ from col$ where rowid=:1 call     count       cpu    elapsed       disk      query    current        rows ------- ------  -------- ---------- ---------- ---------- ----------  ---------- Parse        1      0.01       0.00          0          0          0           0 Execute      1      0.00       0.00          0          0          0           0 Fetch        1      0.00       0.00          0          2          0           1 ------- ------  -------- ---------- ---------- ---------- ----------  ---------- total        3      0.01       0.00          0          2          0           1 Misses in library cache during parse: 1 Misses in library cache during execute: 1 Optimizer mode: CHOOSE Parsing user id: SYS   (recursive depth: 1) Rows     Row Source Operation -------  ---------------------------------------------------       1  TABLE ACCESS BY USER ROWID COL$ (cr=1 pr=0 pw=0 time=35 us) ******************************************************************************** SELECT PROD_DETAILS,SIGN,ROWNUM FROM( SELECT ROWNUM,PROD_DETAILS,SIGN, (CASE WHEN REGEXP_LIKE(PROD_DETAILS,'^ups','i') THEN '1' ELSE '2' END) AS SIGN2 FROM (SELECT  ROWNUM,PROD_DETAILS,SIGN FROM orcl.MV_PROD_SEARCH_DET2 WHERE REGEXP_LIKE(PROD_DETAILS,'ups','i')) order by SIGN2,SIGN desc) where rownum<15 call     count       cpu    elapsed       disk      query    current        rows ------- ------  -------- ---------- ---------- ---------- ----------  ---------- Parse        1      0.00       0.00          0          0          0           0 Execute      1      0.00       0.00          0          0          0           0 Fetch        2      0.67       0.74          0        532          0          14 ------- ------  -------- ---------- ---------- ---------- ----------  ---------- total        4      0.67       0.75          0        532          0          14 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: SYS Rows     Row Source Operation -------  ---------------------------------------------------      14  COUNT STOPKEY (cr=532 pr=0 pw=0 time=748169 us)      14   VIEW  (cr=532 pr=0 pw=0 time=748152 us)      14    SORT ORDER BY STOPKEY (cr=532 pr=0 pw=0 time=748135 us)      95     COUNT  (cr=532 pr=0 pw=0 time=216517 us)      95      VIEW  (cr=532 pr=0 pw=0 time=216038 us)      95       COUNT  (cr=532 pr=0 pw=0 time=215556 us)      95        MAT_VIEW ACCESS FULL MV_PROD_SEARCH_DET2 (cr=532 pr=0 pw=0 time=214981 us) ******************************************************************************** alter session set sql_trace=FALSE call     count       cpu    elapsed       disk      query    current        rows ------- ------  -------- ---------- ---------- ---------- ----------  ---------- Parse        2      0.00       0.00          0          0          0           0 Execute      2      0.00       0.00          0          0          0           0 Fetch        0      0.00       0.00          0          0          0           0 ------- ------  -------- ---------- ---------- ---------- ----------  ---------- total        4      0.00       0.00          0          0          0           0 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: SYS ******************************************************************************** alter session set sql_trace=TRUE call     count       cpu    elapsed       disk      query    current        rows ------- ------  -------- ---------- ---------- ---------- ----------  ---------- Parse        0      0.00       0.00          0          0          0           0 Execute      1      0.00       0.00          0          0          0           0 Fetch        0      0.00       0.00          0          0          0           0 ------- ------  -------- ---------- ---------- ---------- ----------  ---------- total        1      0.00       0.00          0          0          0           0 Misses in library cache during parse: 0 Misses in library cache during execute: 1 Optimizer mode: ALL_ROWS Parsing user id: SYS ******************************************************************************** SELECT NAME NAME_COL_PLUS_SHOW_PARAM,DECODE(TYPE,1,'boolean',2,'string',3,   'integer',4,'file',5,'number',        6,'big integer', 'unknown') TYPE,   DISPLAY_VALUE VALUE_COL_PLUS_SHOW_PARAM FROM V$PARAMETER WHERE UPPER(NAME) LIKE UPPER('%SQL_TRA%') ORDER BY   NAME_COL_PLUS_SHOW_PARAM,ROWNUM call     count       cpu    elapsed       disk      query    current        rows ------- ------  -------- ---------- ---------- ---------- ----------  ---------- Parse        1      0.01       0.01          0          0          0           0 Execute      1      0.00       0.00          0          0          0           0 Fetch        2      0.00       0.00          0          0          0           1 ------- ------  -------- ---------- ---------- ---------- ----------  ---------- total        4      0.01       0.01          0          0          0           1 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: SYS Rows     Row Source Operation -------  ---------------------------------------------------       1  SORT ORDER BY (cr=0 pr=0 pw=0 time=2594 us)       1   COUNT  (cr=0 pr=0 pw=0 time=2555 us)       1    HASH JOIN  (cr=0 pr=0 pw=0 time=2537 us)       1     FIXED TABLE FULL X$KSPPI (cr=0 pr=0 pw=0 time=1082 us)    1381     FIXED TABLE FULL X$KSPPCV (cr=0 pr=0 pw=0 time=4153 us) ******************************************************************************** OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS call     count       cpu    elapsed       disk      query    current        rows ------- ------  -------- ---------- ---------- ---------- ----------  ---------- Parse        4      0.01       0.02          0          0          0           0 Execute      6      0.00       0.00          0          0          0           0 Fetch        4      0.67       0.75          0        532          0          15 ------- ------  -------- ---------- ---------- ---------- ----------  ---------- total       14      0.68       0.77          0        532          0          15 Misses in library cache during parse: 3 Misses in library cache during execute: 1 OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS call     count       cpu    elapsed       disk      query    current        rows ------- ------  -------- ---------- ---------- ---------- ----------  ---------- Parse        1      0.01       0.00          0          0          0           0 Execute      1      0.00       0.00          0          0          0           0 Fetch        1      0.00       0.00          0          2          0           1 ------- ------  -------- ---------- ---------- ---------- ----------  ---------- total        3      0.01       0.00          0          2          0           1 Misses in library cache during parse: 1 Misses in library cache during execute: 1     6  user  SQL statements in session.     1  internal SQL statements in session.     7  SQL statements in session.     0  statements EXPLAINed in this session. ******************************************************************************** Trace file: c:\oracle\product\10.2.0\db_1\RDBMS\trace\orcl_ora_684.trc Trace file compatibility: 10.01.00 Sort options: default       90  sessions in tracefile.        6  user  SQL statements in trace file.        1  internal SQL statements in trace file.        7  SQL statements in trace file.        6  unique SQL statements in trace file.    47071  lines in trace file.       55  elapsed seconds in trace file. sql>sho parameter statistics_level NAME                                               TYPE        VALUE                                                                                                ---------------------------------------------------------------------------------------------------- statistics_level                                   string      TYPICAL                                                                                        timed_statistics                       string     true --------------------------The DBMS_XPLAN.DISPLAY_CURSOR output:----------------------------- --------------------------The DBMS_XPLAN.DISPLAY_CURSOR output:----------------------------- SELECT /*+ gather_plan_statistics */ PROD_DETAILS,SIGN,ROWNUM FROM( SELECT ROWNUM,PROD_DETAILS,SIGN, (CASE WHEN REGEXP_LIKE(PROD_DETAILS,'^ups','i') THEN '1' ELSE '2' END) AS SIGN2 FROM (SELECT  ROWNUM,PROD_DETAILS,SIGN FROM  MV_PROD_SEARCH_DET2 WHERE REGEXP_LIKE(PROD_DETAILS,'ups','i')) order by SIGN2,SIGN desc) where rownum<15; select * from table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST')); SQL_ID  16r9mtafh4x0h, child number 0 ------------------------------------- SELECT /*+ gather_plan_statistics */ PROD_DETAILS,SIGN,ROWNUM FROM( SELECT ROWNUM,PROD_DETAILS,SIGN,  (CASE WHEN REGEXP_LIKE(PROD_DETAILS,'^ups','i') THEN '1' ELSE '2' END) AS   SIGN2 FROM (SELECT  ROWNUM,PROD_DETAILS,SIGN FROM    MV_PROD_SEARCH_DET2 WHERE REGEXP_LIKE(PROD_DETAILS,'ups','i'))   order by SIGN2,SIGN desc) where rownum<15 Plan hash value: 1069350749 --------------------------------------------------------------------------------------------------------------------------------------- | Id  | Operation                  | Name                | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem | --------------------------------------------------------------------------------------------------------------------------------------- |*  1 |  COUNT STOPKEY             |                     |      1 |        |     14 |00:00:00.73 |     532 |       |       |          | |   2 |   VIEW                     |                     |      1 |     15 |     14 |00:00:00.73 |     532 |       |       |          | |*  3 |    SORT ORDER BY STOPKEY   |                     |      1 |     15 |     14 |00:00:00.73 |     532 |  9216 |  9216 | 8192  (0)| |   4 |     COUNT                  |                     |      1 |        |     95 |00:00:00.22 |     532 |       |       |          | |   5 |      VIEW                  |                     |      1 |     15 |     95 |00:00:00.22 |     532 |       |       |          | |   6 |       COUNT                |                     |      1 |        |     95 |00:00:00.22 |     532 |       |       |          | |*  7 |        MAT_VIEW ACCESS FULL| MV_PROD_SEARCH_DET2 |      1 |     15 |     95 |00:00:00.22 |     532 |       |       |          | --------------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): ---------------------------------------------------    1 - filter(ROWNUM<15)    3 - filter(ROWNUM<15)    7 - filter( REGEXP_LIKE ("PROD_DETAILS",'ups','i',HEXTORAW('8CE32E5554A4C4010000000000000000000000000000000013000000ACE32E55               02000000000000000000000085000000') ))
      the previous query used to be
      SELECT count(*) FROM MV_PROD_SEARCH_DET2 WHERE REGEXP_LIKE(PROD_DETAILS,'^" + prefixText + "','i')
      if the above returns less than 7
      SELECT PROD_DETAILS,SIGN FROM (SELECT PROD_DETAILS,SIGN FROM MV_PROD_SEARCH_DET2 order by sign desc) WHERE CATSEARCH(PROD_DETAILS,'" + prefixText + "*',NULL)> 0 AND ROWNUM <= 15
      else( if it returns more than 7)
      select prod_details,sign from(select prod_details,sign from mv_prod_search_det2 where regexp_like(prod_details,'^u','i') order by length(prod_details)) where rownum<15
      we reduced it to the query on top of the page
      I'm looking forward for suggestions how to improve the performance of this statement.
      thanks in advance
      lastly
      few records in the materialized view
      something     K-14483
      anything     S-99
      everything     C-12065
      desc mv_prod_search_det2
      Name         Null Type           
      ------------ ---- -------------- 
      PROD_DETAILS      VARCHAR2(1000) 
      SIGN              VARCHAR2(42)   
      Edited by: 946207 on Nov 29, 2012 6:59 PM
        • 1. Re: tuning regexp_like
          Solomon Yakobson
          REGEXP_LIKE is by nature slower than LIKE. Now in your case all you want is to check if PROD_DETAILS starts with UPS regardless of case, right? So use:
          SELECT  PROD_DETAILS,
                  SIGN,
                  ROWNUM
            FROM  (
                   SELECT  ROWNUM,
                           PROD_DETAILS,
                           SIGN,
                           CASE
                             WHEN LOWER(PROD_DETAILS) LIKE 'ups%' THEN '1'
                             ELSE '2'
                           END AS SIGN2
                    FROM  (
                           SELECT  ROWNUM,
                                   PROD_DETAILS,
                                   SIGN
                             FROM  MV_PROD_SEARCH_DET2 a
                             WHERE LOWER(PROD_DETAILS) LIKE 'ups%'
                          ) 
                    ORDER BY SIGN2,
                             SIGN desc
                  )
           WHERE ROWNUM < 15
          /
          And, if needed, you can create function based index on LOWER(PROD_DETAILS), then oprimizer would use it (if needed). Also, SIGN is a keyword and it is not a good idea to use it as column alias. And ROWNUM in inner SELECT is not ROWNUM in outer SELECT. If you want to carry it to outer level you need to alias inner ROWNUM and use that alias in outer SELECT.

          SY.
          • 2. Re: tuning regexp_like
            949210
            thanks for your responses
            is there a way avoiding full table scan??
            order by prod_details
            let the optimizer know the column is ordered by alphabetically( may be hint)
            execute the query that searches for query text anywhere in between a little later
            the table is just 67000 rows
            also
            this is an enterprise edition
            will partitioning be of any use?
            what about IOT?

            Edited by: 946207 on Nov 29, 2012 7:12 PM
            • 3. Re: tuning regexp_like
              972756
              Is rownum and order by necessary? Anyway I deleted the rownum<15 condition.

              select PROD_DETAILS,SIGN,ROWNUM
              from
              (SELECT
              PROD_DETAILS
              ,SIGN
              ,case when REGEXP_LIKE(PROD_DETAILS,'^ups','i') then 1 else 0 end as flag
              ,max(case when REGEXP_LIKE(PROD_DETAILS,'^ups','i') then 1 else 0 end) over () as max_flag
              FROM
              MV_PROD_SEARCH_DET2 a
              WHERE REGEXP_LIKE(PROD_DETAILS,'ups','i')
              order by SIGN desc)
              where flag=max_flag;

              Let me know if it gives you better performance.
              • 4. Re: tuning regexp_like
                949210
                the order by sign is required
                it displays
                S-423
                K-567
                C-43
                • 5. Re: tuning regexp_like
                  ascheffer
                  Create a function based index on lower( prod_details )
                  And use
                  select rownum
                       , prod_details
                       , sign
                  from ( select prod_details
                              , sign
                              , 1 sign2
                         from ( select prod_details
                                     , sign
                                from mv_prod_search_det2 a
                                where lower( prod_details ) like 'ups%'
                                and rownum < 15
                              )  
                         union all
                         select prod_details
                              , sign
                              , 2 sign2
                         from ( select prod_details
                                     , sign
                                from mv_prod_search_det2 a
                                where lower( prod_details ) like '%ups%'
                                and lower( prod_details ) not like 'ups%'
                                and rownum < 15
                              )
                         order by 3, 2 desc
                       )
                  where rownum < 15     
                  • 6. Re: tuning regexp_like
                    Solomon Yakobson
                    And if performance is critical and ups is the only string you are searching for you could create FBI on instr(lower(prod_details),'ups') and:
                    select rownum
                         , prod_details
                         , sign
                    from ( select prod_details
                                , sign
                                , 1 sign2
                           from ( select prod_details
                                       , sign
                                  from mv_prod_search_det2 a
                                  where instr(lower(prod_details),'ups') = 1
                                  and rownum < 15
                                )  
                           union all
                           select prod_details
                                , sign
                                , 2 sign2
                           from ( select prod_details
                                       , sign
                                  from mv_prod_search_det2 a
                                  where instr(lower(prod_details),'ups') > 1
                                  and rownum < 15
                                )
                           order by 3, 2 desc
                         )
                    where rownum < 15
                    / 
                    SY.
                    • 7. Re: tuning regexp_like
                      949210
                      performance is critical.what the end user enters is not known,it might keep changing
                      any way to avoid the full table scan?
                      • 8. Re: tuning regexp_like
                        chris227
                        First of all, full table scans must not be bad.
                        Secondly, show us your actual query improved by the advises given so far.
                        Did you get rid of all those rownums, for the optimizer to enable to do his work?
                        DO you use like instead of regexp if possible?
                        You only need one rownum which must be aliased:
                        select * from (
                         select rownum r, ... fom (
                           select a,b,c ...
                         )
                        )
                        where r < 15
                        • 9. Re: tuning regexp_like
                          Nikolay Savvinov
                          Hi,

                          I think Oracle Text might be the answer to your problem:

                          http://docs.oracle.com/cd/B19306_01/server.102/b14220/content.htm#sthref2643

                          there's also an Oracle Text forum on this site where you can get more specific help getting started.

                          Best regards,
                          Nikolay

                          Edited by: Nikolay Savvinov on Nov 30, 2012 10:48 AM
                          • 10. Re: tuning regexp_like
                            949210
                            we had implemented catsearch in the inner query
                            if we use catsearch instead of regexp it is slower and the cost is more

                            Edited by: 946207 on Dec 1, 2012 10:46 AM
                            • 11. Re: tuning regexp_like
                              Nikolay Savvinov
                              hi
                              946207 wrote:
                              we had implemented catsearch
                              in the inner query if we use catsearch instead of regexp it is slower and the cost also is more
                              is there a CTXCAT index that could be used by catsearch?

                              Best regards,
                              Nikolay
                              • 12. Re: tuning regexp_like
                                chris227
                                Would we not expect something like ORA-20000: Oracle Text error: DRG-10599: column is not indexed otherwise?
                                • 13. Re: tuning regexp_like
                                  ascheffer
                                  Create a function based index on lower( prod_details )
                                  • 14. Re: tuning regexp_like
                                    jihuyao
                                    Function index on single column is not enough when have to order by two columns

                                    order by SIGN2,SIGN desc

                                    Assuming there are 15 rows having sign2=1, you can not just return 14 rows (rownum<15) without sorting on column sign.
                                    1 2 Previous Next