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
