Find all one-to-many tables in the database

Recently, in a project, we encountered a SQL optimization to remove distinct keywords.

The distinct keyword is used as a de-duplication, which will find out all the result sets, sort them, and finally remove duplicate data. Generally, tables that appear after left join are one-to-many for index condition (condition after on).

Now to remove distinct keywords, the first thing we need to do is find out which tables are one-to-many for index columns. This article is about how to find one-to-many tables for index columns in the database, and then do the subsequent deletion of keywords.

1. First, we need to find all the tables that contain index columns.

--Query all containing JOB_ID Table of fields
--JOB_ID For indexed columns
select t.table_name
  from DBA_TAB_COLUMNS t
 where COLUMN_NAME = 'JOB_ID'

2. After finding it, we should classify and judge it. Firstly, if job_id is the primary key, it is impossible to duplicate, so we should eliminate the situation of the primary key. The system table of ORACLE database and the field information are mostly capitalized, so we should pay attention to it. Here we find the table name and the primary key name.

--Query all containing JOB_ID Field and JOB_ID Table names and primary keys of tables whose fields are not primary keys 
select a.TABLE_NAME,
       c.column_name
-- DBA_TAB_COLUMNS Record tables for all fields in the database 
       from DBA_TAB_COLUMNS a 
--user_constraints Primary key information table in database 
       left join user_constraints b 
       on a.TABLE_NAME = b.table_name
 --user_cons_columns Field Constraint Information Table
       left join user_cons_columns c 
       on c.constraint_name = b.constraint_name
       where a.COLUMN_NAME = 'JOB_ID'
--constraint_type = 'P'Primary key constraint
       and b.constraint_type = 'P' 
--The primary key cannot be JOB_ID
       and c.column_name <> 'JOB_ID'

3. The query result set should be judged circularly. It uses the method of database loop, execute mediate.

--Loop the result set above
--Find all pairs of fields JOB_ID Contains multiple cases, i.e. one primary key corresponds to multiple JOB_ID               
declare
     tab_name varchar2(100);
     col_name varchar2(100);
     v_sql varchar2(500);
     conNum number;
begin
     for item in (select a.TABLE_NAME,
               c.column_name 
               from DBA_TAB_COLUMNS a  -- DBA_TAB_COLUMNS Record tables for all fields in the database
               left join user_constraints b --user_constraints Primary key information table in database
               on a.TABLE_NAME = b.table_name
               left join user_cons_columns c  --Field Constraint Table
               on c.constraint_name = b.constraint_name
               where a.COLUMN_NAME = 'JOB_ID'
               and b.constraint_type = 'P' --constraint_type = 'P'Primary key constraint
               and c.column_name <> 'JOB_ID') loop
                   tab_name := item.table_name; --Table name of result set
                   col_name := item.column_name; --Primary key name of result set
                   --Table names cannot be variables, but they can be SQL Statements are defined as strings by execute immediate Execution
                   --Two count Count the number of primary key fields separately and the number of foreign keys after de-duplication. If the difference is not 0, it means that for foreign keys, the table is not one-to-many.
                   execute immediate 'select count('||col_name||') - count(distinct job_id) from ' ||tab_name into conNum;
                   if conNum > 0 then
                      dbms_output.put_line(tab_name);
                   end if;
     end loop;
end;

4. The final result of implementation is:

This method only checks one-to-many tables, and can not directly solve the problem of removing distinct (unless these tables are not included, but distinct is used). After finding the table, we should continue to analyze SQL and find the most suitable method.

Tags: Database SQL Oracle

Posted on Mon, 09 Sep 2019 04:16:32 -0700 by BooRadLey