oracle大表关联怎样优化

[xixi_168]

oracle两张大表table1 和table2数据量都在5000万左右。
两张表均有a,b,c三个字段，并通过这三个字段关联
查询的sql语句如下

SELECT A.XX FROM TABLE1 A, TABLE2 B
WHERE A.A=B.A
AND A.B=B.B
AND A.C=B.C
AND B.TYPE IN (1,2,3)

B.TYPE 有4个类型为1,2,3,4平均都是1000多万。
请问这个sql怎样优化，做索引感觉没啥效果，因为基本上是全表关联，那做分区表如何，比如两张表按照（a,b,c）做hash分区
。请问这种语句应该怎样优化，大家帮忙给个建议，谢谢！

[xixi_168]

引用 7 楼 wildwave 的回复:

分区在这里没用 只是两张大表简单的连接，返回千万级的结果 全表扫描+哈希连接是免不了的，没什么可优化的地方 这个查询有什么用处呢？还是只是为了建一个视图

谢谢斑竹，这个语句是为了建一个中间表？作为中间处理过程表。

[tomshenhao]

个人建议LZ可以考虑在FROM子句做调整，尽量缩小数据集大小试试，另外要优化，最后给出执行计划。 例如: SELECT A.XX FROM (select a,b,c from TABLE1) A, (select a,b,c from TABLE2 where type in (1,2,3)) B WHERE A.A=B.A AND A.B=B.B AND A.C=B.C

[xixi_168]

引用 9 楼 wildwave 的回复:

这个需要看整个语句。一般情况下，一条查询语句总有一个关键的过滤条件，优化要从这里入手

整个语句就是这个

SELECT A.XX FROM TABLE1 A, TABLE2 B
WHERE A.A=B.A
AND A.B=B.B
AND A.C=B.C
AND B.TYPE IN (1,2,3)

B.TYPE 一共1,2,3,4 这个条件只排除掉1/4也就是还是有3/4的数据。 另外设置数据库参数其他方面有没有可优化的地方，只要速度上去就可以了

[fwx515354]

在两表的abc三个列上都建联合索引，不知效果如何。

[xixi_168]

引用 13 楼 fwx515354 的回复:

在两表的abc三个列上都建联合索引，不知效果如何。

这个基本上是全表，用不到吧

[小灰狼W]

引用 12 楼 xixi_168 的回复:

[quote=引用 9 楼 wildwave 的回复:] 这个需要看整个语句。一般情况下，一条查询语句总有一个关键的过滤条件，优化要从这里入手

整个语句就是这个

SELECT A.XX FROM TABLE1 A, TABLE2 B
WHERE A.A=B.A
AND A.B=B.B
AND A.C=B.C
AND B.TYPE IN (1,2,3)

B.TYPE 一共1,2,3,4 这个条件只排除掉1/4也就是还是有3/4的数据。 另外设置数据库参数其他方面有没有可优化的地方，只要速度上去就可以了[/quote] 这就是整个语句的话，就没什么好优化的了。我是不太清楚，一个查询结果有几千万条记录，那么这个查询的意义何在

[小灰狼W]

因为几乎全表都要输出，索引什么的，就压根没有必要了。准备好足够大的内存和temp空间，进行hash join，单单是查询结果传到应用端就要耗茫茫多的网络流量，快不了的

[Fatalists]

SELECT A.XX FROM TABLE1 A, TABLE2 B WHERE A.A=B.A AND A.B=B.B AND A.C=B.C AND B.TYPE IN (1,2,3) a,b,c建立索引，b表的type建立列表分区试试

[biandongfeng]

这个查询很简单 基本没有优化的可能了，楼主如果用来建中间表，效率应该可以了接受吧 又用的不多 还有就是in这个东西是用不到索引的

[gothic_zxc]

7楼对 这个背景下 这两个表的join 如果可以 就hash 其次 sort merge 即便连接的列有索引 也没有效果 全表扫描已经无法避免 只能 where 后的非join条件上 优化 尽量减少结果集 对了 大表关注下水位线问题

[xixi_168]

引用 16 楼 wildwave 的回复:

因为几乎全表都要输出，索引什么的，就压根没有必要了。准备好足够大的内存和temp空间，进行hash join，单单是查询结果传到应用端就要耗茫茫多的网络流量，快不了的

那hash join是oracle优化器自动选择的吗，增加内存是增加pga部分的内存吗？ 这个查询是为了生成数据插入到一张表中。etl中的其中一步。 有规定是需要在规定的时间内完成的。如果确实需要加大内存或者调增pga中的参数可以实现，那也是可以的。

[xixi_168]

引用 16 楼 wildwave 的回复:

因为几乎全表都要输出，索引什么的，就压根没有必要了。准备好足够大的内存和temp空间，进行hash join，单单是查询结果传到应用端就要耗茫茫多的网络流量，快不了的

另外这两张表用这三个字段做hash分区有效果没？

[小灰狼W]

没有效果。hash连接和hash分区没什么关系 如果结果集就有这么大，该查询没有优化余地。倒是可以试着分几批来插入

[softwakaka]

如果这张表只是用来查询的话，为什么type不建位图索引？

[softwakaka]

个人觉得， 先用type滤出一部分的数据(这里可用位图索引加速)， 即使是1000万 也是少了4000万，然后用滤除来的这部分做驱动表去关联另外一张大表，在连接条件上建索引，用索引去fetch 这张大表。 这样总的数据量会少很多。

[AcHerat]

如果不嫌麻烦，可以试着把表试着分割为多个表，每个表记录给定几百万，话说楼主几千万的数据A B C一起没有重复的么？这种查询在索引或者分区上基本没什么能优化的地方，就像版主说的，在实际的查询中按where条件的筛选去尽量减少发生表关联的数据量，分割表也是这一思想。 其实也不用做这个中间表吧！你可以把一步分为三步去做，取第一个表符合它条件的记录，取第二个表符合它条件的记录，第三步看情况放到一起去关联。

[softwakaka]

引用 20 楼 xixi_168 的回复:

[quote=引用 16 楼 wildwave 的回复:] 因为几乎全表都要输出，索引什么的，就压根没有必要了。准备好足够大的内存和temp空间，进行hash join，单单是查询结果传到应用端就要耗茫茫多的网络流量，快不了的

那hash join是oracle优化器自动选择的吗，增加内存是增加pga部分的内存吗？ 这个查询是为了生成数据插入到一张表中。etl中的其中一步。 有规定是需要在规定的时间内完成的。如果确实需要加大内存或者调增pga中的参数可以实现，那也是可以的。[/quote] 你又不需要排序，加大PGA 做什么。

[softwakaka]

引用 27 楼 softwakaka 的回复:

[quote=引用 20 楼 xixi_168 的回复:] [quote=引用 16 楼 wildwave 的回复:] 因为几乎全表都要输出，索引什么的，就压根没有必要了。准备好足够大的内存和temp空间，进行hash join，单单是查询结果传到应用端就要耗茫茫多的网络流量，快不了的

那hash join是oracle优化器自动选择的吗，增加内存是增加pga部分的内存吗？ 这个查询是为了生成数据插入到一张表中。etl中的其中一步。 有规定是需要在规定的时间内完成的。如果确实需要加大内存或者调增pga中的参数可以实现，那也是可以的。[/quote] 你又不需要排序，加大PGA 做什么。[/quote] 没看上面， hash join 是要耗内存的， 但是如果是对hash join 调， 这点内存加上 也是杯水车薪

[ccjk311]

如果条件允许，就加大内存、开并行，索引分区神马的都没用

[华而不实]

这么简单的sql，貌似真没有什么优化的地方。几乎全表查询，索引什么的应该用不到，直接就是全表扫描。 前两天看书，看到一个"并行执行"，LZ可以试一下，不知道能不能快点。 select /*+ parallel(t,4)*/ t.a from t