SQL query with poor join performance

Question

I'm experiencing very poor performance in a query I've written for Oracle 12c. It's probably related to my inefficient use of joins and was hoping someone could help me out with where I'm going wrong. The query I have is currently taking over a minute to run.

I'm am trying to return the table and column names where:

The column belongs to a Primary Key
The column type is Number
The owner of the table is MY_OWNER
The Primary Key is a single column constraint

Currently my query looks as follows

SELECT consCols.table_name, consCols.column_name 
FROM all_cons_columns consCols
INNER JOIN all_constraints cons 
  ON cons.constraint_name = consCols.constraint_name
INNER JOIN all_tab_columns cols 
  ON consCols.table_name = cols.table_name AND consCols.column_name = cols.column_name
WHERE cons.constraint_type = 'P' 
AND cons.owner = 'MY_OWNER'
AND cols.data_type = 'NUMBER'
AND consCols.table_name IN(
  SELECT consCols2.table_name 
  FROM all_cons_columns consCols2
  INNER JOIN all_constraints cons2 
    ON cons2.constraint_name = consCols2.constraint_name
  WHERE cons2.constraint_type = 'P' 
  AND cons2.owner = 'MY_OWNER' 
  GROUP BY consCols2.table_name
  HAVING COUNT(consCols2.table_name) = 1
);

Thank you for any help you can give me.

Please run explain plan for the query and post results.

OldProgrammer
– OldProgrammer

2016-04-26 00:18:00 +00:00
Commented Apr 26, 2016 at 0:18 — OldProgrammer
– OldProgrammer, Commented Apr 26, 2016 at 0:18

Gordon Linoff · Accepted Answer · 2016-04-26 00:52:49Z

3

Would using analytic functions speed the query?

SELECT table_name, column_name
FROM (SELECT consCols.table_name, consCols.column_name, cols.data_type,
             COUNT(*) OVER (PARTITION BY consCols.table_name) as cnt
      FROM all_cons_columns consCols INNER JOIN
           all_constraints cons 
           ON cons.constraint_name = consCols.constraint_name INNER JOIN
           all_tab_columns cols 
           ON consCols.table_name = cols.table_name AND consCols.column_name = cols.column_name
      WHERE cons.constraint_type = 'P' AND
            cons.owner = 'MY_OWNER'
     ) tc               
WHERE data_type = 'NUMBER' AND cnt = 1;

Or even aggregation?

  SELECT consCols.table_name, consCols.column_name
  FROM all_cons_columns consCols INNER JOIN
       all_constraints cons 
       ON cons.constraint_name = consCols.constraint_name INNER JOIN
       all_tab_columns cols 
       ON consCols.table_name = cols.table_name AND consCols.column_name = cols.column_name
  WHERE cons.constraint_type = 'P' AND
        cons.owner = 'MY_OWNER'
  GROUP BY consCols.table_name, consCols.column_name
  HAVING COUNT(*) = 1 AND
         MAX(cols.data_type) = 'NUMBER';

Here is a third option:

  SELECT consCols.table_name, consCols.column_name
  FROM all_cons_columns consCols INNER JOIN
       all_constraints cons 
       ON cons.constraint_name = consCols.constraint_name INNER JOIN
       all_tab_columns cols 
       ON consCols.table_name = cols.table_name AND consCols.column_name = cols.column_name
  WHERE cons.constraint_type = 'P' AND
        cons.owner = 'MY_OWNER' AND
        cols.data_type = 'NUMBER' AND
        NOT EXISTS (SELECT 1
                    FROM all_cons_columns acc
                    WHERE acc.constraint_name = consCols.constraint_name AND
                          acc.table_name = consCols.table_name AND
                          acc.column_name <> consCols.column_name
                   );

This eliminates the aggregation and the lookup should be relatively fast.

edited Apr 26, 2016 at 0:52

answered Apr 26, 2016 at 0:24

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Venick Over a year ago

Thanks @Gordon. Both your answers take roughly 20 seconds so that's a win. Whilst I was hoping for a under 5 seconds this is still a significant improvement and helps me greatly.

shonky linux user Over a year ago

Nice Answer. There is an small error in the aggregation SQL - remove the AND on the third-to-last line. I have found that data dictionary queries do take significantly longer since upgrading from 10g to 12c.

Venick Over a year ago

Correction, answer number one works great in about 20 seconds but answer 2 actually returns too many rows. That's fine though I can just stick with #1. Also answer #3 doesn't work for me because all_constraints doesn't have a column_name column :(

Venick Over a year ago

Thanks for the fix. Number #3 works too now and also takes roughly 20 seconds. Is there any advantage between #1 and #3 that I should be concerned about? Any compatibility issues with older Oracle 11g servers?

Gordon Linoff Over a year ago

@Venick . . . Nothing comes to mind as an issue between the first and third. You must have a lot of constraints, tables, and columns.

Collectives™ on Stack Overflow

SQL query with poor join performance

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related