0

I am trying to find out whether a set of columns uniquely identifies observations in a HUGE table using Oracle SQL. I am accessing an Orcacle Exadata platform through Python using cx_Oracle.

Background: I am new to SQL (Oracle SQL) and my assignment requires me to work with HUGE tables that are unfortunately not documented very well (I do not even know the Primary keys). By looking at the data I am trying to make sense of it and I am trying to find out whether a certain set of columns identify rows in the table uniquely (and could as such act as PK).

safex
  • 2,398
  • 17
  • 40

2 Answers2

1

You can check if they are unique using aggregation:

select col1, col2, col3
from t
group by col1, col2, col3
having count(*) > 1;

You also need to check that none of the values are NULL.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
1

If the table is huge using group by query could be very slow. Try to see if there are column statistics already. (WARNING I write the query below from my memory, the actual column names could be different check your oracle version):

select column_name, num_distinct

from user_tab_col_statistics where table_name = YOUR_TABLE_NAME

the column "last_analysed" tells you when statistics where gathered If they are very old you could regather them with this command (but you need the appropriate permission):

begin
   dbms_stats.gather_table_stats('YOUR_TABLE_NAME', method-opt -> 'for all columns size 1');

end; /

hth!

Nik
  • 247
  • 1
  • 8