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!
source to share
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.
source to share