OR Expansion

1.定义

OR expansion is a transformation that can be used to optimize disjunctive queries (queries that contain OR clauses). The basic idea in OR expansion is to transform a query containing disjunctions into the form of a UNION ALL query of two or more branches. This is done by splitting the disjunction into its components and associating each component with a branch of a UNION ALL query.

OR-expansion transformation is applied in a cost-based manner in Oracle database.

2.参数

KSPPINM                        KSPPSTVL   KSPPDESC
------------------------------ ---------- ------------------------------------------------------------
_no_or_expansion               FALSE      OR expansion during optimization disabled

hint：or_expand/no_expand 或者 use_concat

use_concat有时会被忽略，详细参考mos    USE_CONCAT hint on different versions(Doc ID 259741.1)
如：
Different columns in the OR ie (col1 =1 or col2 =1)
10.2.0.4 Will use USE_CONCAT hint as long as there is an index driver
11.1.0.7 Will use USE_CONCAT hint as long as there is an index driver
11.2.0.1 Will use USE_CONCAT hint as long as there is an index driver
11.2.0.2 Will use USE_CONCAT hint as long as there is an index driver
Same Columns in the OR ie (col1 =1 or col1 =2)
10.2.0.4 Will ignore USE_CONCAT hint choosing Inlist Iterator as long as there is an index driver
11.1.0.7 Will ignore USE_CONCAT hint choosing Inlist Iterator as long as there is an index driver
11.2.0.1 Will ignore USE_CONCAT hint choosing Inlist Iterator as long as there is an index driver
11.2.0.2 Will ignore USE_CONCAT hint choosing Inlist Iterator as long as there is an index driver

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31347199/viewspace-2121893/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31347199/viewspace-2121893/