Tag Archives: join elimination

CONNECT BY oddity

April 7, 2010 – 23:09

This week I’ve seen an issue with a CONNECT BY query: for some reason Oracle 10.2.0.4 decided to build a weird plan (the query is weird too, but that’s not my point here :)). An explanation of why that happened looks interesting, so here it is.

Continue reading →

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

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)