Is the order of conditions in a where clause relevant if both sides are left joined entities?

Question

We have two queries on our database, where the only difference is the order of the conditions in an OR clause.

Queries of the same structure work fine on a similar data set on another database. We are working with Oracle 12.2.

The Bestand table has two connected entities where either one of them is always filled. In our example the Article is connected the KbArticle is always null. In one of our queries, we get all results for which the article id works in the other case we do not. All of the Bestand entities fulfill the two last conditions (cod_lbr = 12, flg_sperre = 0)

SELECT bestand0_.*
FROM tbl_bestand bestand0_
LEFT OUTER JOIN tbl_kb_artikel customeror1_
ON bestand0_.id_kb_artikel=customeror1_.id_kb_artikel
LEFT OUTER JOIN tbl_artikel article2_
ON bestand0_.id_artikel          =article2_.id_artikel
WHERE (customeror1_.id_kb_artikel=3017874 OR article2_.id_artikel          =3017874)
AND bestand0_.cod_lbr            =12
AND NVL(bestand0_.flg_sperre,0)  = 0;

yields results Link to Explain Plan

SELECT bestand0_.*
FROM tbl_bestand bestand0_
LEFT OUTER JOIN tbl_kb_artikel customeror1_
ON bestand0_.id_kb_artikel=customeror1_.id_kb_artikel
LEFT OUTER JOIN tbl_artikel article2_
ON bestand0_.id_artikel          =article2_.id_artikel
WHERE (article2_.id_artikel          =3017874 OR customeror1_.id_kb_artikel=3017874)
AND bestand0_.cod_lbr            =12
AND NVL(bestand0_.flg_sperre,0)  = 0;

yields no results Link to Explain Plan

What makes absolutely no sense to me, as all of the results that we should get fulfill all of the statements in the where clause is that if you leave the last two conditions, the query returns the expected results, so if we modify our query to:

SELECT bestand0_.*
FROM tbl_bestand bestand0_
LEFT OUTER JOIN tbl_kb_artikel customeror1_
ON bestand0_.id_kb_artikel=customeror1_.id_kb_artikel
LEFT OUTER JOIN tbl_artikel article2_
ON bestand0_.id_artikel          =article2_.id_artikel
WHERE (article2_.id_artikel          =3017874 OR customeror1_.id_kb_artikel=3017874);

we get results again Link to Explain Plan

I would expect both queries to yield the same results, I can not understand why the order of the conditions should affect the outcome of the query in any way. Do we have some indices messed up here? It must be database specific as our queries work fine on another database, same version, with a similar set of data.

** UPDATE **

Unfortunately the query with the AND OTHER_ID IS NULL as @kfinity had the same problem as the queries above which makes absolutely no sense to me. After we disabled the Adaptive Statistics Optimizer that @Kuvick mentioned our queries both returned results again with the following explain plans:

Explain plan Query 1 Explain plan Query 2

So turning that optimizer off solved our problems.

Answer 1

We identified the problem to be the Oracle setting OPTIMIZER_ADAPTIVE_PLANS and OPTIMIZER_ADAPTIVE_STATISTICS. By default after setting up the DB the first one is active and the second one is deactivated.

In our case both were activated. Right after deactivating OPTIMIZER_ADAPTIVE_STATISTICS (i.e. going back to the default settings) both queries returned the same results (as one would expect). These settings can be checked with the follwing query:

SELECT * FROM v$parameter WHERE 1 = 1 AND LOWER(name) LIKE LOWER('optimizer_ad%') ORDER BY 1 DESC;