The query performs a Cartesian join if

Question

I have a query that should return 2 rows. However, it returns 48 rows. It acts as one of the tables that join, does not exist. But if I add a column from this table to the select clause, without any changes to from or from parts of the query, it will return 2 rows.

Here is what “Explain plan” says without “m. *” In the selection:

Here again after adding m. * in select:

Can anyone explain why he should behave this way?

Update . We had this problem only on one system, and not on another. The DBA confirmed that the one who has the problem running optimizer_features_enable is set to 10.2.0.5, and the one where it does not exist runs optimizer_features_enable set to 10.2.0.4. Unfortunately, the client’s site is running 10.2.0.5.

Answer 1

About the union exception that was introduced in 10gR2:

Deleting a table (alternately called "join elimination") removes redundant tables from the query. A table is redundant if only its columns are referenced by join predicates, and it is guaranteed that these joins are neither a filter nor an extension of the resulting rows. There are several cases where Oracle will eliminate the redundant table.

Maybe such a related mistake or so. Check out this article.

Answer 2

Looks like a mistake. What are the limitations?

Logically, if all lines in MASTERSOURCE_FUNCTION had a NON-OSDA function, then this would not exclude any lines (or if none of them had this value, then all lines would be excluded).

Going further, if each line in MASTERSOURCE has one or zero NON-OSDA line in MASTERSOURCE_FUNCTION, then it should be an exception candidate. But between identifiers MASTERSOURCE and NAME should also be one-to-one.

I would pull the ROWID from ACCOUNTSOURCE for 48 rows, then track the MASTERSOURCE and NAME identifier and see on what basis these rows are duplicated or not excluded. That is, there are 12 duplicate names in MASTERSOURCE, where it is expected to be unique using the NOVALIDATE constraint.