具有许多查找的大型 Oracle 表的索引和分区的最佳组合

Question

我有一个Oracle 10g基因组数据数据库，其中包含几个>1亿行表，如下所示：

ID    AssayID    Sample      Mutation    Call Frequency
101   12578      Sample01    T367G       P    0.87
102   31384      Sample01    A2345C      A    0.28
103   3453       Sample01    T247C       P    0.67
104   12578      Sample02    G235del     M    0.11
105   7868       Sample02    None        P    0.98

• ID是唯一的PK，AssayID和Sample是外键。
• 假设对于每个Sample值，有 ~50k 行。
• 每个AssayID每Sample恰好发生一次。
• Mutation相对随机，Call可以是三个值之一。
• 对此表的查询可以通过AssayID和Sample使用链接表中的AssayID、Sample、Mutation、Call或值的任意一个或组合。

一个典型的查询：

select t.*
from this_table t
    join assay_table a on t.assayid = a.assayid
    join sample_table s on t.sample = s.sample
where 
    s.name = 'xxx' and a.gene in ('abc', 'xyz') and t.call = 'P'

• 针对这些表的查询始终联接多个较小的表。
• WHERE 语句通常会筛选多个列上的数据，但绝不会仅从基本数据表中筛选数据。

如何设计表以在选择所有列时获得最佳查询性能？
是仅使用索引、仅使用分区还是两者的组合？ 磁盘空间和插入/更新性能不是问题。

Answer 1

作为第一步，您可以运行Oracle的SQL Access Advisor，看看它提供了哪些建议。

Answer 2

在创建了具有不同索引和分区组合的表的大量测试副本，并使用广泛的查询选择运行了一系列性能分析之后，我认为这个问题没有一个单一的简单答案。 每种情况都不同，这个问题的范围对于本论坛来说太宽泛了。 谢谢大家的反馈，这一切都很有帮助。