Query Transformation-集合篇

对于集合操作,CBO的处理之前两篇文章也介绍过,比如子查询为集合操作的子查询展开、内嵌视图为集合操作的连接谓词推入等等,这篇文章主要介绍集合操作相关的另外一种查询转换SJC – set join conversion

The purpose of set to join conversion is to avoid sort operations in compound queries involving INTERSECT and MINUS. This query transformation also postpones the elimination of duplicates to the end of processing for such queries.

A compound query based on the INTERSECT and MINUS set operators is basically carried out in the following way:

  1. Every component query is independently executed, the result set is sorted, and duplicates are eliminated.
  2. Then the set operations are executed, and the final result set is determined.

This way of executing a query involving an INTERSECT or MINUS operation isn’t always efficient. For example, when the component queries return a lot of data, but the majority of data is eliminated by the set operator, most of the data that’s later eliminated ends up being unnecessarly sorted. The set to join conversion avoids that inefficiency by transforming the query in a way that allows rows to be thrown out prior to the sort rather than after it. In addition, since the set operator is replaced by a join, additional access paths are enabled. This is a heuristic-based query transformation that, by default, isn’t enabled.The set_to_join hint has to be specified to take advantage of it.




此条目发表在Oracle, Oralce performance分类目录,贴了, 标签。将固定链接加入收藏夹。


您的电子邮箱地址不会被公开。 必填项已用*标注