java - 优化大表的选择查询？

Question

我试图在给定索引列值的情况下获取 Oracle 数据库中的特定行。为了便于处理，我选择了索引列，然后是整行数据。表上大约有 1.5 亿行，我选择了大约 100 - 20 万行。我正在使用 NVL() 替换我的 IN 子句中的空值，因为我在 JAVA 中动态创建这些语句。 IN 子句分为 1000 个索引 block ，由 OR 拼凑在一起，以避免“列表中表达式的最大数量为 1000”错误。

如您所想，此查询需要很长时间才能运行。我可以做些什么来优化此查询但保持相同的功能吗？ (忽略索引值，因为它们只是示例)

select INDEX_COLUMN_A, INDEX_COLUMN_B, INDEX_COLUMN_C, TABLE.* from TABLE     
Where (NVL(INDEX_COLUMN_A,''), NVL(INDEX_COLUMN_B,''), NVL(INDEX_COLUMN_C,'')) 
IN (('1','1','1'), ('2','2','2'), ... ('1000','1000','1000')) 
OR (NVL(INDEX_COLUMN_A,''), NVL(INDEX_COLUMN_B,''), NVL(INDEX_COLUMN_C,'')) 
IN (('1001','1001','1001'), ('1002','1002','1002'), ... ('2000','2000','2000')) 
OR...

Answer 1

如果在 ('1','1', '1') 或 ('2000','2000','2000'):

select 
  INDEX_COLUMN_A, INDEX_COLUMN_B, INDEX_COLUMN_C, 
  TABLE.* 
from 
  TABLE     
where 
  (INDEX_COLUMN_A, INDEX_COLUMN_B, INDEX_COLUMN_C) 
  in (
    ('1','1','1'), 
    ('2','2','2'), 
    ...,
    ('1000','1000','1000')
  ) 
  OR 
  (INDEX_COLUMN_A, INDEX_COLUMN_B, INDEX_COLUMN_C) 
  in (
    ('1001','1001','1001'), 
    ('1002','1002','1002'),
     ...,
    ('2000','2000','2000')
  ) 
  or
  ...

此外，像 a in (x1,x2) or a in (x3,x4) 这样的表达式表示 ((a=x1 or a=x2) or (a=x3 or a =x4))。在这种情况下可以省略括号而不会产生任何后果:(a=x1 or a=x2 or a=x3 or a=x4) 可以用 in 表达式缩短为 a in (x1, x2, x3, x4)。因此初始查询(如果要测试的值中没有空值)与以下相同:

select 
  INDEX_COLUMN_A, INDEX_COLUMN_B, INDEX_COLUMN_C, 
  TABLE.* 
from 
  TABLE     
where 
  (INDEX_COLUMN_A, INDEX_COLUMN_B, INDEX_COLUMN_C) 
  in (
    ('1','1','1'), 
    ('2','2','2'), 
    ...,
    ('1000','1000','1000')
    ...,
    ('1001','1001','1001'), 
    ('1002','1002','1002'),
     ...,
    ('2000','2000','2000')
  ) 

附言

a in (x,y,z) 只是一组由 or 连接的等式关系的快捷方式:((a=x) or ( a=y or (a=z)) 和 null 永远不会等于 null，所以表达式 null in (x,y,z) 永远不会返回 true 而不管 x、y 和 z 值。所以如果你真的需要处理 null 值然后表达式必须更改为类似 nvl(a,'some_never_encountered_value') in (nvl('1', 'some_never_encountered_value'), nvl('2','some_never_encountered_value') ,...)。但在这种情况下，您不能在表上使用简单索引。可以构建函数式索引来处理此类表达式，但这是一个非常不同的故事。

附言如果列包含数字，则必须针对数字对其进行测试:您应该使用 (1,1,1) 而不是 ('1','1','1') .