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@xxxxxxxxx> 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
--
//www.freelists.org/webpage/oracle-l
