Avoid cartesian queries - find a proper "where clause"

Question

The following code generates alter password statements to change all standard passwords in an Oracle Database. In version 12.1.0.2.0 it’s not possible anymore to change it to in invalid Password values. That’s why I had to build this switch case construct. Toad gives a warning (rule 5807) on the join construct at the end. It says “Avoid cartesian queries – use a where clause...”. Any Ideas on a “where clause” which works on all oracle database versions?

SET TERMOUT  OFF
SET ECHO     OFF
SET LINESIZE 140
SET FEEDBACK OFF
SET PAGESIZE 0

SPOOL user.sql

SELECT    'alter user '
       || username
       || ' identified by values '
       || CHR (39)
       || CASE
             WHEN b.version = '12.1.0.2.0' THEN '462368EA9F7AD215'
             ELSE 'Invalid Password'
          END
       || CHR (39)
       || ';'
  FROM DBA_USERS_WITH_DEFPWD a,
       (SELECT DISTINCT version
          FROM PRODUCT_COMPONENT_VERSION) b;

SPOOL OFF
@user.sql

You need a record returned by the query for each user and each product component version? Or are there combinations you don't want to be returned? — Stefan Steinegger, Apr 08 '16 at 12:06
the simplest way, which doesn't add any overhead (aside from the quite correct statement about reorganizing the query to SQL 92 standards) is to add "where 1 = 1" to the query. — T Gray, Apr 08 '16 at 15:16

score 2 · Answer 1 · answered Apr 08 '16 at 12:05

Toad is simply giving you a warning about the fact that you are doing a join with no JOIN conditions. In this case, you are really doing that, but one of the involved entities (the query from PRODUCT_COMPONENT_VERSION) should return only one value, so the problem is a false one. You are doing a cartesian with an entity that only has 1 element, so it IS a cartesian, but it does not multiply your values

score 1 · Answer 2 · answered Apr 08 '16 at 12:24

Rather than using the old join syntax (without a join condition - which is what Toad is warning about) you could use the (ANSI/ISO) SQL-92 Join Syntax and explicitly state that the join is a CROSS JOIN:

SELECT    'alter user '
       || username
       || ' identified by values '
       || CHR (39)
       || CASE
             WHEN b.version = '12.1.0.2.0' THEN '462368EA9F7AD215'
             ELSE 'Invalid Password'
          END
       || CHR (39)
       || ';'
  FROM DBA_USERS_WITH_DEFPWD a
       CROSS JOIN
       (SELECT DISTINCT version
          FROM PRODUCT_COMPONENT_VERSION) b;

Since the second table joined will only return a single row then you will not increase the number of rows returned and CROSS JOIN does not expect a join condition (so hopefully Toad will stop needlessly complaining).

A slightly more efficient version (but it is minimal since there are not many rows involved) would be to use:

SELECT version FROM PRODUCT_COMPONENT_VERSION WHERE ROWNUM = 1

Then Oracle can stop reading from the table after the first row and does not have to perform the DISTINCT operation.

score 1 · Accepted Answer · answered Apr 08 '16 at 13:14

On my machine (a laptop with the free XE version of Oracle - simplest arrangement possible) the table PRODUCT_COMPONENT_VERSION has FOUR rows, not one. There are versions for different products, including the Oracle Database and PL/SQL. On my machine the version is the same in all four rows, but I don't see why that should be expected in general.

If they may be different, FIRST you can ignore all the answers that tell you the cross join is not a problem because you return only one row. You don't; you may return more than one row. SECOND, in your query itself, why are you returning the version from ALL the rows of PRODUCT_COMPONENT_VERSION, and not just the version for the Oracle Database? I imagine something like

WHERE PRODUCT LIKE 'Oracle%'

should work - and you won't need DISTINCT in the SELECT clause. Good luck!

That is a good idea - another example: `WHERE UPPER (product) LIKE '%DATABASE%` — r0tt, Apr 11 '16 at 08:09

score 0 · Answer 4 · answered Apr 08 '16 at 12:08

If you need all the combinations of users and product components, the cartesian product is the right choice. Keep an eye on the number of lines that could potentially be written to the file (users * component versions). You probably need some kind o batching.

Avoid cartesian queries - find a proper "where clause"

4 Answers4