CODEWITHSUNDEEP
sqloracle-databaseperformanceselectoracle11g
JavaSheriff
JavaSheriff

Reputation: 7677

How to restrict oracle LIKE clause results

I have an Oracle table with 20 million users,
I would like to query the table for users with first name Like "Patel" or "Pat" performance when querying using "like clause" is very bad. 

select * from users  where first name like '%Patel%'  
Or
select * from users  where first name like '%Pat%'

And as far as I know if I will restrict the results by rownum - it will happen only after the LIKE - so I have a full table scan... I don't want to scan the entire 20 Million records

select * from users  where first name like '%Pat%' where rownum<100

Is it possible to tell oracle to stop after finding 100 rows?

Upvotes: 0

Views: 135

Answers (3)

Mindaugas Tamosevicius
Mindaugas Tamosevicius

Reputation: 172

select * from users where first name like '%Pat%' where rownum<100

Oracle is smart enough to do everything for You. Execution plan for this query is:

SELECT STATEMENT, GOAL = ALL_ROWS      
     COUNT STOPKEY          
          TABLE ACCESS FULL

COUNT STOPKEY means that full scan will be stopped when Oracle will find enough records to satisfy the condition.

Upvotes: 2

Olafur Tryggvason
Olafur Tryggvason

Reputation: 4874

Since the question is tagged Oracle 11g, I'll give an answer that works in 11g.

Use the optimizer hint for first_rows_100 and wrap it into an inline view.

Example:

select *
  from (select /*+ opt_param('optimizer_mode','first_rows_100') */
              u.*, rownum as rn
          from users u
         where instr (name, 'Pat') > 0 or instr (name, 'Patel') > 0) inlineview
 where rn <= 100

Regards

Olafur

Upvotes: 1

Mureinik
Mureinik

Reputation: 311808

Oracle 12c (finally) introduced the fetch first syntax, which should perform a bit better:

SELECT *
FROM   users
WHERE  first_name LIKE '%Pat%' 
FETCH  FIRST 100 ROWS ONLY

Upvotes: 2

Related Questions