Current Position:Home > Code Search HELP

Code Search HELP

Update:10-11Source: network consolidation
Advertisement
Here is my situation. I have a table that has a list of codes almost 100K. I have another table that has code listed in term of an in house regular expression.
Example:
40a.[1,2,3]
12b
Where
a is 0 - 9
b is 1 - 9
I have tried using REGEXP_LIKE but I get ORA-04030: out of process memory error.
My question is there a way to do this search such that it will be fast and efficient.
I have through about expanding the Expressed code, but that could lead to a lot of codes, and it specially gets tricky if the code is in the format of "1a2b". In this case I would need to somehow create 90 (10*9) rows. I am not entriely sure how to do this dynamically, because there could be a lot of variations.
The current approcach I am thinking of is to search based on character placement. Such that each character becomes a column and I do the comparisson as
Base_Char_1 = Code_Exp_1
Base_Char_2 = Code_Exp_2
I have not figured out how this would work. Shown below is the REGEXP_LIKE approach. Any help or advice would be great.
WITH t1 AS
     (SELECT '40a.[0,1,9]1' AS code_exp
        FROM DUAL
     ), t2 AS
       (SELECT code_exp
             , '^' || REPLACE (REPLACE (REPLACE (REPLACE (code_exp, 'a', '[0-9]'), '.', '\.'), 'b', '[1-9]'), ',') || '$' AS code_regexp
          FROM t1)
SELECT *
  FROM t2
WHERE REGEXP_LIKE ('402.11', code_regexp)Thanks.

The Best Answer

Advertisement
Well, it is probably a design issue - joining 100 000 codes with 10 000 regular expressions based on regexp_like() will effectively mean 1e9 calculated regular expressions, which means a lot of work i suppose.
Besides that, there is indeed something strange on pga/uga memory consumption when regular expressions are stored in the table. I've set up small test case based on your data
SQL> CREATE TABLE codes AS
  2  SELECT dbms_random.string('p',10) code
  3  FROM dual
  4  CONNECT BY LEVEL <=100000
  5  UNION ALL
  6  SELECT '402.11' FROM dual
  7  /
Table created.
SQL> CREATE TABLE code_expressions AS
  2  WITH t1 AS
  3  (SELECT '40a.[0,1,9]1' AS code_exp
  4          FROM DUAL
  5  ), t2 AS
  6  (SELECT code_exp, '^' ||
  7          REPLACE (REPLACE (REPLACE (REPLACE (code_exp, 'a', '[0-9]'), '.', '\.'), 'b', '[1-9]'), ',')
  8          || '$' AS code_regexp
  9          FROM t1)
10  SELECT * FROM t2
11  /
Table created.
SQL> exec dbms_stats.gather_table_stats(user,'codes')
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats(user,'code_expressions')
PL/SQL procedure successfully completed.
SQL> set lines 100
SQL> col code for a15
SQL> col code_exp for a15
SQL> col code_regexp for a25
SQL> exec runStats_pkg.rs_start;
PL/SQL procedure successfully completed.
SQL> SELECT * FROM codes,code_expressions WHERE regexp_like(code,'^40[0-9]\.[019]1$');
CODE            CODE_EXP        CODE_REGEXP
402.11          40a.[0,1,9]1    ^40[0-9]\.[019]1$
SQL> exec runStats_pkg.rs_middle;
PL/SQL procedure successfully completed.
SQL> SELECT * FROM codes,code_expressions WHERE regexp_like(code,code_regexp);
CODE            CODE_EXP        CODE_REGEXP
402.11          40a.[0,1,9]1    ^40[0-9]\.[019]1$
SQL> exec runStats_pkg.rs_stop;
Run1 ran in 34 hsecs
Run2 ran in 276 hsecs
run 1 ran in 12.32% of the time
Name                                  Run1        Run2        Diff
STAT...session cursor cache co           1           0          -1
.... sniped
STAT...CPU used when call star          37         276         239
STAT...CPU used by this sessio          37         276         239
STAT...DB time                          37         277         240
STAT...Elapsed Time                     36         283         247
STAT...session pga memory           65,536           0     -65,536
STAT...session uga memory           65,560           0     -65,560
STAT...session pga memory max      262,144  10,551,296  10,289,152
STAT...session uga memory max      261,964  10,592,024  10,330,060
Run1 latches total versus runs -- difference and pct
        Run1        Run2        Diff       Pct
       1,009       1,348         339     74.85%
PL/SQL procedure successfully completed.
SQL>
SQL> conn scott/tiger
Connected.
SQL> set lines 100
SQL> col code for a15
SQL> col code_exp for a15
SQL> col code_regexp for a25
SQL>
SQL> exec runStats_pkg.rs_start;
PL/SQL procedure successfully completed.
SQL> SELECT * FROM codes,code_expressions WHERE regexp_like(code,'^40[0-9]\.[019]1$');
CODE            CODE_EXP        CODE_REGEXP
402.11          40a.[0,1,9]1    ^40[0-9]\.[019]1$
SQL> exec runStats_pkg.rs_middle;
PL/SQL procedure successfully completed.
SQL> SELECT * FROM codes,code_expressions
  2  WHERE regexp_like(code,'^' ||
  3  REPLACE (REPLACE (REPLACE (REPLACE (code_exp, 'a', '[0-9]'), '.', '\.'), 'b', '[1-9]'), ',') ||
  4           '$');
CODE            CODE_EXP        CODE_REGEXP
402.11          40a.[0,1,9]1    ^40[0-9]\.[019]1$
SQL> exec runStats_pkg.rs_stop;
Run1 ran in 37 hsecs
Run2 ran in 335 hsecs
run 1 ran in 11.04% of the time
Name                                  Run1        Run2        Diff
STAT...recursive cpu usage               3           2          -1
.... sniped
STAT...CPU used when call star          40         336         296
STAT...CPU used by this sessio          40         336         296
STAT...DB time                          40         336         296
STAT...Elapsed Time                     39         338         299
STAT...session pga memory           65,536           0     -65,536
STAT...session uga memory           65,560           0     -65,560
STAT...session pga memory max      262,144  10,551,296  10,289,152
STAT...session uga memory max      261,964  10,592,024  10,330,060
Run1 latches total versus runs -- difference and pct
        Run1        Run2        Diff       Pct
       1,022       1,348         326     75.82%
PL/SQL procedure successfully completed.
SQL> explain plan for
  2  SELECT * FROM codes,code_expressions WHERE regexp_like(code,'^40[0-9]\.[019]1$');
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
Plan hash value: 1698472622
| Id  | Operation            | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
|   0 | SELECT STATEMENT     |                  |  5000 |   205K|    59   (6)| 00:00:01 |
|   1 |  MERGE JOIN CARTESIAN|                  |  5000 |   205K|    59   (6)| 00:00:01 |
|   2 |   TABLE ACCESS FULL  | CODE_EXPRESSIONS |     1 |    31 |     3   (0)| 00:00:01 |
|   3 |   BUFFER SORT        |                  |  5000 | 55000 |    56   (6)| 00:00:01 |
|*  4 |    TABLE ACCESS FULL | CODES            |  5000 | 55000 |    56   (6)| 00:00:01 |
Predicate Information (identified by operation id):
   4 - filter( REGEXP_LIKE ("CODE",'^40[0-9]\.[019]1$'))
16 rows selected.
SQL> explain plan for
  2  SELECT * FROM codes,code_expressions WHERE regexp_like(code,code_regexp);
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
Plan hash value: 3098457583
| Id  | Operation          | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
|   0 | SELECT STATEMENT   |                  |  5000 |   205K|    57   (2)| 00:00:01 |
|   1 |  NESTED LOOPS      |                  |  5000 |   205K|    57   (2)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| CODE_EXPRESSIONS |     1 |    31 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| CODES            |  5000 | 55000 |    54   (2)| 00:00:01 |
Predicate Information (identified by operation id):
   3 - filter( REGEXP_LIKE ("CODE","CODE_REGEXP"))
15 rows selected.
SQL> explain plan for
  2  SELECT * FROM codes,code_expressions WHERE regexp_like(code,'^' ||
  3  REPLACE (REPLACE (REPLACE (REPLACE (code_exp, 'a', '[0-9]'), '.', '\.'), 'b', '[1-9]'), ',') ||'$');
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
Plan hash value: 3098457583
| Id  | Operation          | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
|   0 | SELECT STATEMENT   |                  |  5000 |   205K|    57   (2)| 00:00:01 |
|   1 |  NESTED LOOPS      |                  |  5000 |   205K|    57   (2)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| CODE_EXPRESSIONS |     1 |    31 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| CODES            |  5000 | 55000 |    54   (2)| 00:00:01 |
Predicate Information (identified by operation id):
   3 - filter( REGEXP_LIKE ("CODE",'^'||REPLACE(REPLACE(REPLACE(REPLACE("CODE_E
              XP",'a','[0-9]'),'.','\.'),'b','[1-9]'),',')||'$'))
16 rows selected.Best regards
Maxim
  • Post code search for N82 maps Update:11-30

    Does any one know how i can do a post code search in the uk for my nokia maps, it only seems to accept american zip codes. Every post code i tryed didnt work. CheersIf you allow nokia maps to connect to the internet it will search online for detailed

  • Window freezes when Product category selected via match-code (search help) Update:11-30

    Hello Experts, I am using SRM 7.0 Extended Classic Scenario. I maintained a product category hierarchy in the SRM environment. I maintained the GL Accounts related, the tax determination, the source system etc. I did all this customizing in a Develop

  • BADI FOR WITHHOLDING TAX CODES SEARCH HELP Update:11-30

    HI, Is there any BADI for witholding tax code search help. i want to make some changes in it. field: WITHT Pls help.Please find the following BADIs which are called during the transaction FB60. BADI_FDCB_SUBBAS01 BADI_FDCB_SUBBAS02 PPA_CUST_BADI BADI

  • Intrastat2007 Missing Functionality:commodity code search by description Update:11-30

    Hello, it would be very helpful that the customer can the commodity code search by description. (Item Master data -> Intrastat Settings) Kind Regards Doreen CieslewskiHi, I don't seem a search by description would be possible in a database to be hand

  • Code Search HELP Update:10-11

    Here is my situation. I have a table that has a list of codes almost 100K. I have another table that has code listed in term of an in house regular expression. Example: 40a.[1,2,3] 12b Where a is 0 - 9 b is 1 - 9 I have tried using REGEXP_LIKE but I

  • Code Search for Keywords Update:10-11

    Hi All, I need some help from advance sql users. I have for ex. some 100 table names in a custom table. and i want to search oracle's dba_source table for packages/functions/procedures. The output i want is e.g. <Package Name> < Table Name> He

  • Code searching in sql developer Update:11-30

    Hi, I was wondering how I could search through all my packages/procedures for a particular piece of text?I don't think there is anything built in.. You can run a query against the source text (although it may take a while) SELECT * FROM sys.all_sourc

  • Transaction Code Search by Keyword Update:11-30

    Is there any way to search for appropriate transaction codes by keyword search without knowing any transaction codes? For e.g. type: Display Report or Display Table and not know the transaction code for that? I already know about Tables: TSTC and TST

  • Project Code search Update:11-30

    I have a client using Project codes with Journal Vouchers / JE. Is there a easier way to search for Project codes and not just scrolling through it. they have lots of them and it is very time consuming. ThanksHi, I am not sure about the business scen

  • T.CODE SEARCH FROM TABLE Update:11-30

    Hii, In our company, we are going to do the acrhiving projject,so in second phase we have to find out t.code which using th4e database,ex.EA60 used table ERDK, We have alomst 110 tables, we have idetified the below process, 1.take program of each t.c