Regexp_instr

Applications Programming
1

I need to select all those values with....

REGEXP_INSTR(column_name,'^((A){0,1}[[:alpha:]]+[. | ]?)+[[:digit:]]+[-|/]?[[:digit:]]*$') = 0

But with " ^((A){0,1} " at the begining it seems to take a lot time.....it goes on infinitely

i also tried the following but in vain.....

'^([A]?[[:alpha:]]+[. | ]?)+[[:digit:]]+[-|/]?[[:digit:]]*$'

'^([ABC]?[[:alpha:]]+[. | ]?)+[[:digit:]]+[-|/]?[[:digit:]]*$'

'^([ABC][[:alpha:]]+[. | ]?)+[[:digit:]]+[-|/]?[[:digit:]]$'

'^((A){0,}[[:alpha:]]+[. | ]?)+[[:digit:]]+[-|/]?[[:digit:]]*$'

'^((A){0,1}[ABC]?[[:alpha:]]+[. | ]?)+[[:digit:]]+[-|/]?[[:digit:]]*$'

'^([A-Z][[:alpha:]]+[. | ]?)+[[:digit:]]+[-|/]?[[:digit:]]$'

'^((A){0,1}[A-Z]+[. | ]?)+[[:digit:]]+[-|/]?[[:digit:]]*$'

provide some alternatives...

2

I don't see how it's the A{0,1} causing the problem. A? is equivalent to A{0,1}. Can you provide us a sample input?

PS: I tried this with grep -E on Linux and it worked fine on the following strings:

AskjYj|AaaA023923-232
Akjsd402
As9
Ax.44-42
3

REGEXP_INSTR is an Oracle function. It cannot use an index, so it does a full table scan.
I agree with Otheus, what are you trying to match in your data, leading A{0,1} seems pointless?

4

Actually its enough if the value satisfies..REG_EXP of

^([[:alpha:]]+[.]? )+(#|# |NO |NO. )?[[:digit:]]

But since it takes much time ...I thought of adding (A){0,1}...wich obviously does not make any difference...

I just need the equivalent of ^([[:alpha:]]+[.]? )+(#|# |NO |NO. )?[[:digit:]]

which works faster....

5

Functions do not use the index. If you have 1,000,000 rows, then the query reads all million rows - a full table scan.

Instead of playing with your regex, fix your SQL first.
Add this line to the top of your code:

ALTER SESSION SET SQL_TRACE TRUE;

Next, get into SQLPLUS

select value from V$PARAMETER where name='user_dump_dest';VALUE
--------------------------------------------------------------------------------
/psw/app/oracle/admin/DEV1/udump

The value in red is the location of your dump directory. Remember it.

Next run your code.
Now, create a tkprof report:

cd  [your dump directory]
ls -rt | tail -1   # produces a file name that ends in .trc This is the output you want
tkprof  [file.trc] myhomedir/rpt.rpt

Now read the rpt.rpt file - it's output is similar to the output from explain plan, but it has actual time for disk i/o and cpu, etc.