This discussion is archived
1 2 Previous Next 16 Replies Latest reply: Dec 1, 2012 2:50 AM by chris227 RSS

tuning regexp_like

949210 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    the order by sign is required
    it displays
    S-423
    K-567
    C-43
  • 5. Re: tuning regexp_like
    ascheffer Expert
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    Would we not expect something like ORA-20000: Oracle Text error: DRG-10599: column is not indexed otherwise?
  • 13. Re: tuning regexp_like
    ascheffer Expert
    Currently Being Moderated
    Create a function based index on lower( prod_details )
  • 14. Re: tuning regexp_like
    jihuyao Journeyer
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points