Tag Archives: coalesce subquery

Enhanced Subquery Optimizations in Oracle

March 25, 2010 – 23:28

While googling I found a fresh article from Oracle for the VLDB journal: Enhanced Subquery Optimization in Oracle. It primarily discusses subqueries – how Oracle deals with them on optimization and at run-time.

Continue reading →

By Timur Akhmadeev | Posted in CBO, Oracle | Also tagged , , , | Comments (0)

COALESCE_SQ

January 18, 2010 – 00:16

There are 19 new hints in Oracle 11.2.0.1:

SQL> select name from v$sql_hint where version='11.2.0.1';

NAME
------------------------------
COALESCE_SQ
NO_COALESCE_SQ
TRANSFORM_DISTINCT_AGG
NO_TRANSFORM_DISTINCT_AGG
CONNECT_BY_ELIM_DUPS
NO_CONNECT_BY_ELIM_DUPS
DST_UPGRADE_INSERT_CONV
NO_DST_UPGRADE_INSERT_CONV
STATEMENT_QUEUING
NO_STATEMENT_QUEUING
EXPAND_TABLE
NO_EXPAND_TABLE
XMLINDEX_SEL_IDX_TBL
FACTORIZE_JOIN
NO_FACTORIZE_JOIN
APPEND_VALUES
PLACE_DISTINCT
NO_PLACE_DISTINCT
NO_SUBSTRB_PAD

19 rows selected.

Some of them represents new CBO features (like FACTORIZE_JOIN/NO_FACTORIZE_JOIN). Here I’ll show purpose of COALESCE_SQ hint (NO_COALESCE is it’s inverse).

Continue reading →

By Timur Akhmadeev | Posted in CBO, Hints, Oracle | Also tagged , , , | Comments (3)