how to optimize this sql searching for metadata in Oracle

Question

this is the sql

SELECT distinct a.owner,
                a.table_name,
                a.column_name,
                a.data_type,
                b.comments,
                a.data_length,
                a.data_precision,
                a.data_scale,
                a.char_length,
                a.column_id,
                d.column_name,
                (
                    CASE
                        WHEN c.constraint_type IS NOT NULL THEN
                            1
                        ELSE
                            0
                        END
                    ),
                ct.r_table,
                ct.r_field
FROM all_tab_columns a
LEFT JOIN all_col_comments b ON a.table_name = b.table_name AND b.column_name = a.column_name AND b.OWNER = a.OWNER
LEFT JOIN (
    SELECT aa.table_name,
           aa.column_name,
           aa.OWNER,
           bb.constraint_type
    FROM all_cons_columns aa
    INNER JOIN all_constraints bb ON aa.constraint_name = bb.constraint_name AND bb.constraint_type = 'P'
) c ON c.table_name = a.table_name AND c.column_name = a.column_name AND c.owner = a.OWNER
LEFT JOIN all_part_key_columns d ON d.column_name = a.column_name AND d.owner = a.OWNER AND d.name = a.table_name
LEFT JOIN (SELECT acs_l.CONSTRAINT_NAME,
                           acs_l.CONSTRAINT_TYPE,
                           acs_l.R_CONSTRAINT_NAME,
                           acc_l.OWNER       l_owner,
                           acc_l.TABLE_NAME  l_table,
                           acc_l.COLUMN_NAME l_field,
                           acc_r.OWNER       r_owner,
                           acc_r.TABLE_NAME  r_table,
                           acc_r.COLUMN_NAME r_field
                    FROM all_constraints acs_l
                    LEFT JOIN all_cons_columns acc_l ON acc_l.CONSTRAINT_NAME = acs_l.CONSTRAINT_NAME
                    LEFT JOIN all_cons_columns acc_r ON acs_l.R_CONSTRAINT_NAME = acc_r.CONSTRAINT_NAME
    ) ct ON ct.l_owner = a.OWNER AND ct.l_table = a.TABLE_NAME AND ct.l_field = a.column_name
WHERE a.table_name in (:tableNames)
  AND a.owner in (:owners)

data count shows below enter image description here

it takes 17s for running out result and A better performance is needed.how I can optimize the SQL query for performance?

1. tried replace 'in' by 'exist'. not works
2. It's not allowed to add any index;

Answer 1

Don't use DISTINCT - there should not be duplicate tables/columns/comments/primary key constraints. If you are getting duplicates then work out which part of the query is getting duplicates and use DISTINCT in only that sub-query and not the entire query because the majority of the query is going to produce unique values.

SELECT ...,
       CASE
       WHEN c.constraint_type IS NOT NULL
       THEN 1
       ELSE 0
       END,
       ...
FROM   ...
       LEFT JOIN (
         SELECT aa.table_name,
                aa.column_name,
                aa.OWNER,
                bb.constraint_type
         FROM   all_cons_columns aa
                INNER JOIN all_constraints bb
                ON aa.constraint_name = bb.constraint_name
                   AND bb.constraint_type = 'P'
       ) c 
       ON c.table_name = a.table_name
          AND c.column_name = a.column_name
          AND c.owner = a.OWNER
       ...

Can be rewritten without the join as:

SELECT ...,
       CASE
       WHEN EXISTS(
         SELECT 1
         FROM   all_cons_columns aa
                INNER JOIN all_constraints bb
                ON aa.constraint_name = bb.constraint_name
         WHERE  bb.constraint_type = 'P'
         AND    aa.table_name  = a.table_name
         AND    aa.column_name = a.column_name
         AND    aa.owner       = a.OWNER
       )
       THEN   1
       ELSE   0
       END,
       ...
FROM   ...

Finally, it is not going to affect performance but

WHERE a.table_name in (:tableNames)
AND   a.owner in (:owners)

can be simply written as:

WHERE a.table_name = :tableNames
AND   a.owner      = :owners

A bind variable is treated as a single scalar value. If you pass a comma-delimited list then it is NOT going to be expanded within the query to fill an IN list, it is just going to be treated as a single value and will match nothing.