From: Tim X on
Ibo <ibodogan(a)> writes:

> i got object or schema name in following format:
> owner[.object_name[.partition_name]]
> This naming allows to define schemans, objects and partitions.
> I have a sp_crackname procedure to extract fields form this name but i
> think it is running slow when processign thousands of names.
> I need to use REGEXP_SUBSTR to extract indivvidual fields.
> Ex -1 :
> objname = 'busdep'
> REGEXP_SUBSTR (... , 1) should return 1. field which is 'busdep'
> REGEXP_SUBSTR (... , 2) and REGEXP_SUBSTR (... , 3) should return
> Ex -1 :
> objname = 'busdep.orders.part_1'
> REGEXP_SUBSTR (... , 1) should return 'busdep'
> REGEXP_SUBSTR (... , 2) should return 'orders' and REGEXP_SUBSTR
> (... , 3) should return 'part_1'
> Please let me know whats the fastest way to do this.

Well, I'll preface this by saying I've not yet found a need to use RE in
Oracle. In some languages, like perl, I use them quite a lot, but have
not yet found a need in Oracle. I'm not saying they are not needed, only
that I've personally not come across a situation where I couldn't get
the outcome with much simpler string manipulation functions such as
substr et al.

In general, if you can do what you want with other techniques that don't
use backtracking etc, they will be faster than REs. Therefore, I would
be tempted to try just using basic string search and substring commands
rather than a RE in this case. You have a well defined token separator
i.e. '.', so you should be able to extract the components very easily
using instr and substr functions.

If you really want to use REs, one of the most important thing to do is
make sure that the RE is well anchored as this will reduce the amount of
backtracking that will occur. to anchor the RE, you need to tie it to
some non-variaant part. this could be the start or end of the string or
some sequence that is always there and always in the same position in
the strings of interest.

The only other comment I have is to ask why have all three components in
one string in the first place. If you need to do large amount of
operations where you need individual components, maybe you should use
something like an Oracle object type. You could store the individual
components in their own separate variables within the object and could
even define methods to extract each component and the full specification
etc. Even simpler, just a table with the components as distinct fields.

While not e\meaining to criticise you specifically, in general, I find
people often have a tendency to define fields that are really composites
of other fields and then find they have processing issues because they
need to manipulate the individual components separately more often than
they need to manipulate the composite object. The way you select your
data structures and design your data model should reflect how you need
to manipulate the data objects rather than what looks neat or
comfortable for humans to read.

Of course, sometimes, you don't have the luxury of doing things they way
you want due to other restrictions outside your control. In such
situations, you an only do wht you can


tcross (at) rapttech dot com dot au