连接性能较差的SQL查询(SQL query with poor join performance)

我在为Oracle 12c编写的查询中遇到了非常糟糕的性能。这可能与我对连接的低效使用有关，并且希望有人可以帮我解决我出错的地方。我的查询目前需要一分钟才能运行 。

我正在尝试返回表和列名称：

该列属于主键列类型为Number 表的所有者是MY_OWNER 主键是单列约束

目前我的查询如下所示

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 );

感谢您给我的任何帮助。

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.

最满意答案

使用分析函数会加快查询速度吗？

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;

甚至聚合？

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';

这是第三种选择：

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 );

这消除了聚合，查找应该相对较快。

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.

更多推荐