Re: Suboptimal query plan with connect by

Hi Dimitre,

Since your predicates in where clause are not join predicates, they are executed AFTER "connect by" and they work just filters for the tree, so your first and second queries are not equal to each other.

On Mon, Oct 29, 2018, 15:51 Radoulov, Dimitre <cichomitiko_at_gmail.com> wrote:

> Hello all,
>
> env: single instance EE 12.2.0.1 PSU 201807 on RHEL 7.5
>
> we have the a query similar to this one:
>
> SELECT A.col1,
> A.col2,
> ... other columns ...
> LEVEL
> FROM t A
> WHERE A.date_col < ADD_MONTHS(TO_DATE('20180901', 'YYYYMMDD'), 1)
> OR A.other_date_col IS NULL
> OR A.other_date_col >= TO_DATE('20180901', 'YYYYMMDD')
> CONNECT BY (
> (A.new_id = PRIOR A.id AND A.other_id = PRIOR A.other_id)
> OR (A.id = PRIOR A.id AND A.new_other_id = PRIOR A.other_id))
> START WITH (
> A.other_date_col IS NULL
> OR A.other_date_col >= ADD_MONTHS(TO_DATE('20180901', 'YYYYMMDD'), 1)
> )
>
> 1. Parallel execution can only be achieved if we remove the "OR"
> operator in the "CONNECT BY" clause (with parallel hint or object level
> degree > 1).
> 2. The query performs a full table scan of A even when we add a
> selective where clause on indexed column:
>
> ...
> WHERE A.date_col < ADD_MONTHS(TO_DATE('20180901', 'YYYYMMDD'), 1)
> AND id in (val1, val2) -- there is an existing index with id as a first
> column
>
> It uses the correct index if we transform the query like this:
>
> SELECT B.col1,
> B.col2,
> ... other columns ...
> LEVEL
> FROM (
> SELECT A.col1,
> A.col2,
> ... other columns ...
> FROM t A
> WHERE A.date_col < ADD_MONTHS(TO_DATE('20180901', 'YYYYMMDD'), 1)
> AND id in (val1, val2)
> OR A.other_date_col IS NULL
> OR A.other_date_col >= TO_DATE('20180901', 'YYYYMMDD')
> ) B
> CONNECT BY (
> (B.new_id = PRIOR B.id AND B.other_id = PRIOR B.other_id)
> OR (B.id = PRIOR B.id AND B.new_other_id = PRIOR B.other_id))
> START WITH (
> B.other_date_col IS NULL
> OR B.other_date_col >= ADD_MONTHS(TO_DATE('20180901', 'YYYYMMDD'), 1)
> )
>
> Oracle support engineer suggested to test with the following parameters:
>
> ALTER SESSION SET "_unnest_subquery" = FALSE;
> ALTER SESSION SET "_connect_by_use_union_all" = 'OLD_PLAN_MODE';
>
> Nothing changed.
> Oracle support says also that this is not a bug and that we need to
> rewrite the query as in the second example.
>
> Any insight would be appreciated!
>
>
> Regards
> Dimitre
>
>
>
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l