Oracle – SQL Query to Find Columns Creating Unique Key for Table

oracleunique-constraint

I have a table with a dozen or so columns and no primary key defined for it. There are several million rows stored in it. Is there a SQL query I could use that would return the combination of columns that will give a unique key for the table? It is specifically an Oracle database.

Best Answer

First, it seems like the wrong way of approaching the problem. I would start by investigation what is supposed to be unique according to the business rules.

Nevertheless, you will have to investigate the power set of column combinations. The cardinality of the power set on n is 2^n, but the empty set can be ignored. So if you have 10 columns this means 2^10-1=1023 combinations to investigate. It's probably easiest to use a host language to generate the sql, but you can use group by cube to get the combinations. Assuming columns c1, c2, c3:

  with t(a,b,c) as ( values ('c1','c2','c3') ) 
  select a,b,c 
  from t group by cube (a,b,c)

A  B  C 
-- -- --
-  c2 c3
-  -  c3
-  c2 - 
-  -  - 
c1 c2 - 
c1 -  - 
c1 -  c3
c1 c2 c3

Now you can generate the sql by looping over this result set:

select c2,c3 from t group by c2,c3 having count(1) > 1

if no rows exists the column combination is a candidate for a key. Just verify that no sub set of this combination is also a candidate.

Related Solutions

Mysql – How to use Unique key via combinations of table fields

Did you try

CREATE TABLE contacts (
 id int auto_increment primary key, 
 name varchar(20), 
 network_id int,
 network_contact_id int, 
 UNIQUE KEY (`network_id`, `network_contact_id`)
);

Simple Oracle query hangs depending on WHERE clause

following the advice of a_horse_with_no_name,

Please show us the execution plan for the second query – a_horse_with_no_name 4 hours ago

I have posted the relevenat parts of the execution plan... however I can't make heads or tails of what is wrong with it from that....

I followed Mat's advice and guess that lack of indexing is the issue. Because there is no indexing, it must be as Matt says, that this simple query is doing a full table scan, which could take longer than the few minutes I have been waiting for the query to complete...

Useful link about the concept of indexing: http://www.orafaq.com/node/1403

Is that column indexed? Otherwise you'll get a full table scan, which can take a while if the table is big. – Mat 7 hours ago

select index_name, column_name from dba_ind_columns where index_owner = 'THE_SCHEMA' and table_name = 'YOUR_TAB' order by index_name will get you all the indexed columns. Look at the explain plan to see what it is doing (look into set autotrace or use a GUI that has that feature, like sqldeveloper) – Mat 7 hours ago

Thanks for your help guys. Let me know if I might be wrong in that assumption, or if you might have additional insight a_horse_with_no_name....

Best Answer

Related Solutions

Mysql – How to use Unique key via combinations of table fields

Simple Oracle query hangs depending on WHERE clause

Related Question