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.