Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, parag.

Asked: September 09, 2021 - 3:58 am UTC

Last updated: September 20, 2021 - 4:02 am UTC

Version: 11.2.4

Viewed 1000+ times

You Asked

Hi Tom
Want to understand how and where memory is allocated in an instance to evaluate decode, regexp functions in oracle? If we are reading data from a table and writing it any other location(not oracle), then what would be better-->while firing the query with regexp function in where clause OR taking the data out of oracle without regexp filter and applying regexp in some other server.

E. G.
Select col1, col2 from table1 where regexp_like(col1, '[^a-zA-Z0-9]') and date_col between to_date('08/02/2021 00.00.00') and to_date('08/02/2021 06.00.00')

OR

Select col1, col2 from table1 where date_col between to_date('08/02/2021 00.00.00') and to_date('08/02/2021 06.00.00')



and Connor said...

Ultimately Oracle is just a big C/C++ program like anything else. Thus the cost of running a regex on the database server is pretty much the same of running a regex on any other server.

Probably the most notable difference is the cost of *moving* data. Let explore a couple of examples with your query

Example 1
=======
a) Select + regex + between => returns 1000 rows of data sent to destination
b) Select + between => returns 1100 rows of data to destination where an app server uses regex to filter out the 100 rows you shouldn't have.

The cost differential between (a) and (b) is near zero.

Example 2
=======
a) Select + regex + between => returns 1000 rows of data
b) Select + between => returns 100,000 rows of data

The cost differential between (a) and (b) is now ENORMOUS. The cpu/network cost to ship 99,00 rows is probably more than the cost of running the entire query. People often forget that it takes CPU *just* to send stuff across the network.

In general - you always want to process data as close as possible to where that data is stored.




Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, September 09, 2021 - 7:57 am UTC

Thanks!!! I also thought the same. Better to confirm with an expert.

Chuck Jolley, September 09, 2021 - 4:08 pm UTC

It might also be worth mentioning that PhD level mathematicians have been working on optimising the Oracle DB engine for over 4 decades. It is very unlikely that the outside server is going to be more efficient than the DB engine at much of anything.
I used to follow the site of a former Oracle internal programmer. It was seriously hard core geeksville.
Chris Saxon
September 10, 2021 - 12:39 pm UTC

Great point

The cpu/network cost to ship 99,00 rows

Rajeshwaran Jeyabal, September 13, 2021 - 4:34 am UTC

given this

a) Select + regex + between => returns 1000 rows of data
b) Select + between => returns 100,000 rows of data

The cost differential between (a) and (b) is now ENORMOUS. The cpu/network cost to ship 99,00 rows

dont it mean "The cpu/network cost to ship 99,000 rows" instead of
"99,00 rows" ?
Connor McDonald
September 20, 2021 - 4:02 am UTC

Yes :-)
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.