Skip to Main Content
  • Questions

  • Searching a string in all the columns of all the tables in a schema

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Deepak.

Asked: July 05, 2012 - 10:25 am UTC

Last updated: July 09, 2012 - 6:50 pm UTC

Version: 11.2.0.3

Viewed 10K+ times! This question is

You Asked

Hi Tom,

We have a database schema with more than 200 tables(few of the tables contain millions of rows). The requirement is to search for a character string in all the character columns across all the tables in the schema and return the matched strings..

This is a general purpose database.

What would be the best way to implement the search feature. Would Oracle Text be ideal?

Are there any other suitable options?

As always looking forward to your expert advice and help.


and Tom said...

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:37336026927381#42426083757635

I doubt you want to index every string column in every table so text would be not reasonable.

It is a matter of full scanning every table, unless you really thing you want to index them all - and I don't think you want to.

Is this answer out of date? If it is, please let us know via a Comment
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 channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.