Oracle - Best Sentence: IN () like REGEXP_LIKE, otherone

I am returning here as I need your help again!
Which of the following is the best choice?

Question:
I have a table myTable

with ['DateYYYYMMDD','field1', 'field2', 'field3', 'MyField']

and every day someone is inserting a lot of records.

I need to create 2 (quick) views myView1

and myView2

that select records (from myTable

) created in the last 30 days and with different values MYFIELD

.

I found some simple simple solutions and I would like to know which one is the fastest:

Solution1

--myView1:
select field1, field2, ...., fieldn, MYFIELD
  from myTable
 where DateYYYYMMDD > sysdate -30
   and MYFIELD in ('65643L', '65643L174', '65643L8N',
                   ...
                   '6564L7174', '6564L78N','6564L78N_2O15',
                   ...
                   '6564L78N3226T2_2O15', '6564L78N8N322',
                   '6564L78N6T2', '6564L78N6T2_2O15', 
                   '6564L7-NOTT1-6T2', '6564L76T2',
                   ...
                   '6563XP8N322', '6563XP8N322_2O15',
                   '6563XP8N3226T2', '6563XP8N3226T2_2O15',
                   '6563XP8N6T2', '6563XP-NOTT1-6T2',
                   '6563XP6T2', '9563XPT1',
                   '9563XPT1_2O15',
                   ...
                   '9566UB', '9566UB_2O15',
                   '9566UB174', '9566UB8N',
                   '6566UB8N_2O15', '6566UB8N174',
                   '6566UB8N322',
                   ...)


myView2:
select field1, field2, ...., fieldn, MYFIELD
  from myTable
 where DateYYYYMMDD > sysdate -30
   and MYFIELD in ('9P26_B', '9P26_BN',
                   '9P26_8N',
                   ...
                   '9P26_8NN', '9P26_2O158N9',
                   '556_B', '556_8N',
                   ...
                   '5566NP4P', '696N65T',
                   '696N65T6T2',
                   ...
                   '696W1P_B', '696W1P_8N')

      

- solution 2

--myView1:
select field1, field2, ...., fieldn, MYFIELD
  from myTable
 where DateYYYYMMDD > sysdate -30
   and (MYFIELD like '656%' or MYFIELD like '956%')

--myView2:
select field1, field2, ...., fieldn, MYFIELD
  from myTable
  where DateYYYYMMDD > sysdate -30
    and (MYFIELD like '9P26%' 
         or MYFIELD like '556_%' 
         or MYFIELD like '5566%' 
         or MYFIELD like '696%')

      

- solution 3

--myView1:
select field1, field2, ...., fieldn, MYFIELD
  from myTable
 where DateYYYYMMDD > sysdate -30
   and (REGEXP_LIKE(MYFIELD, '^656') or REGEXP_LIKE(MYFIELD, '^956'))

--myView2:
select field1, field2, ...., fieldn, MYFIELD
  from myTable
 where DateYYYYMMDD > sysdate -30
   and (REGEXP_LIKE(MYFIELD, '^9P26') 
        or REGEXP_LIKE(MYFIELD, '^556_') 
        or REGEXP_LIKE(MYFIELD, '^5566') 
        or REGEXP_LIKE(MYFIELD, '^696'))

      

I hope this explains what I need, if there is a better solution please suggest this! Many thanks!

+3


source to share


2 answers


Why not just use LIKE?

--myView1:
select field1, field2, ...., fieldn, MYFIELD
from myTable
where DateYYYYMMDD > sysdate -30
and
MYFIELD like '656%' or MYFIELD like '956%'

      



and etc.

REGEXP functions are powerful, but not fast.

+4


source


Like @Tony Andrews, I would avoid the REGEXP_LIKE option because you don't need any of the functionality it provides, which LIKE does not.



Having an appropriate index will help you a lot more than switching between IN and LIKE. Ideally, you would have a pointer to DateYYYYMMDD, MYFIELD. If you do, I would be surprised if the difference between IN / LIKE makes any noticeable difference in the way you use them.

+1


source







All Articles