Oracle Performance and Engineering

WITH temp AS
     (
        SELECT /*+ MATERIALIZE */
               rsc_pool_id
          FROM krsc_rsc_pools p
         WHERE linked_entity_id = 313
           AND linked_entity_primary_key IN (
                  SELECT DISTINCT org_unit_id
                             FROM krsc_org_units o
                       START WITH org_unit_id IN (
                                     SELECT linked_entity_primary_key
                                       FROM krsc_rsc_pools
                                      WHERE linked_entity_id = 313
                                        AND rsc_pool_id IN (30014))
                       CONNECT BY PRIOR org_unit_id = parent_org_unit_id))
SELECT *
  FROM temp;

This query brings totally different results between (a) when it's using MATERIALIZE hint and (b) when it's not (i.e., by default using the INLINE mechanism).

On reading the trace file - I found Oracle is totally re-writing the query and dropped a few filters. This is how the temp table is created (materialized) -

INSERT      /*+ APPEND BYPASS_RECURSIVE_CHECK */INTO sys_temp_0fd9d6607_e098df80
   SELECT rsc_pool_id
     FROM krsc_rsc_pools p
    WHERE linked_entity_id = 313
      AND linked_entity_primary_key =
             ANY (SELECT DISTINCT org_unit_id
                             FROM krsc_org_units o
                       CONNECT BY parent_org_unit_id = PRIOR o.org_unit_id
                       START WITH org_unit_id =
                                           ANY (SELECT linked_entity_primary_key
                                                  FROM krsc_rsc_pools))

Notice the filters “WHERE linked_entity_id=313 AND rsc_pool_id IN (30014)” are totally gone!!

Advised by Oracle support, I tried the following three tweaks, none worked -

alter session set "_pred_move_around"=false ;
alter session set "_complex_view_merging"=false ;
alter session set "_unnest_subquery"=false ;

The issue does not reproduce in 10g databases. Finally, Oracle opened a bug - #5421368 on this.