Oracle - 最佳句子 : IN(), like、REGEXP_LIKE、otherone

Question

我回来了，因为我再次需要你的帮助!
以下哪一个是更好的选择？

问题是:
我有一个带有 ['DateYYYYMMDD','field1', 'field2', 'field3', 'MyField'] 的表 myTable ，每天都会有人插入许多记录。< br/>

我必须创建 2 个(快速) View myView1 和 myView2 来选择记录(来自 myTable)在过去 30 天内创建，并且具有不同的 MYFIELD 值。

我找到了一些独特的简单解决方案，我想知道哪个是最快的:

解决方案1

--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')

--解决方案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%')

--解决方案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'))

希望能解释我的需要，如果有更好的解决方案，请提出!非常非常感谢!

Answer 1

为什么不直接使用 LIKE？

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

等等

REGEXP 函数功能强大，但速度不快。