如何使用SQL OUTER JOIN有效地连接大型表

Question

Oracle 10g 64位Red Hat Enterprise Linux 5 64bit

我目前可以访问一个规范化的第三方数据库。它们有大量的数据，我的要求是通过连接许多表来公开Materialized VIEW。

表1:Example_Master列:MasterID (VARCHAR2(250)) MasterName (VARCHAR2(250))行数:9000万行主键:MasterID

表2:example_childda1列:childid (VARCHAR2(250)) MasterID(VARCHAR2(250))行数:2500万

表3:example_childdaid列:ChildA1ID (VARCHAR2(250)) ChildA1Name(VARCHAR2(250))主键:childdaid

表4:example_childda2列:ChildA2ID (VARCHAR2(250)) MasterID(VARCHAR2(250))行数:3500万

表5:example_childda2id列:ChildA2ID (VARCHAR2(250)) ChildA2Name(VARCHAR2(250))主键:childda2id

等。

每个子表可以有一个与MASTERID相等的表项，也可以没有。所以我必须得到所有MASTERID和它相关的子名称。如果任何子元素都没有相等的值，那么它必须返回'NULL'。所以我现在在下面物化视图语法

 SELECT a.MasterName, c.ChildA1Name, e.ChildA2Name, g.ChildA3Name
 FROM 
 Example_Master a,
 Example_ChildA1 b,
 Example_ChildA1ID c,
 Example_ChildA2 d,
 Example_ChildA2ID e,
 Example_ChildA3 f,
 Example_ChildA3ID g
 WHERE 
 c.ChildA1ID(+) = b.ChildA1ID
 AND e.ChildA2ID(+) = d.ChildA2ID
 AND g.ChildA3ID(+) = f.ChildA3ID
 AND a.MasterID=b.MasterID (+)
 AND a.MasterID=d.MasterID (+)
 AND a.MasterID=f.MasterID (+)

我必须像上面那样加入5个以上的子表，并且这个查询的成本变得如此巨大，以至于需要接近16分钟才能获得结果。是否有更好的方法来使用OUTER join ?如果你需要更多关于我的问题的细节，请告诉我。

谢谢!

Answer 1

当您对像这样的大型表进行等距连接时，您可以期望的最佳计划是一组散列外连接，其成本通常等于全表扫描的成本加上大型散列表溢出到磁盘的开销。

全表扫描的成本只有在有覆盖索引的情况下才能真正降低。

哈希表溢出到磁盘的可能性当然可以通过拥有大量可用内存来降低，但也可以通过在连接键上对表进行等分区来帮助。通常，这是通过散列分区完成的，使用尽可能多的散列分区来防止将散列表保留在内存中。更多文档:https://docs.oracle.com/cd/B28359_01/server.111/b32024/part_avail.htm#CIHCDBIF(或搜索"分区明智连接")

请注意，您不需要使用并行查询来受益于分区连接——串行查询也受益于分区连接。

顺便说一下，感谢您不是在sub-10g版本上—您不能有效地与LARGE_TABLE LEFT OUTER JOIN SMALL TABLE等价连接，因为优化器不能首先访问小表来创建散列表—这将始终是一个嵌套循环:(