Hi, guys.
Thanks for all the help.
I have a partitioned table with roughly B5.5 records.
I want to index one of the columns, which suppose to be a unique identifier.
Let's say it's column X in table t
What is the fastest way to tell if col x has nulls or duplicates?
- I thought about letting a query run over the weekend to catch nulls.
"select t.rowid from t where x is null"
Thanks for the help
-David
If you have a *good* level of confidence that there are very few exceptions, then you can add the constraint and then validate it, eg
SQL> create table T as select * from dba_Objects;
Table created.
SQL>
SQL> alter table T add constraint UQ primary key ( object_id ) enable novalidate;
Table altered.
SQL>
SQL> select count(*) from t where object_id is null;
COUNT(*)
----------
1
1 row selected.
SQL> @?/rdbms/admin/utlexcpt
Table created.
SQL> desc exceptions
Name Null? Type
----------------------------------------------------------------------------------- -------- ----------------------
-----------
ROW_ID ROWID
OWNER VARCHAR2(128)
TABLE_NAME VARCHAR2(128)
CONSTRAINT VARCHAR2(128)
SQL> alter table T modify constraint UQ enable validate exceptions into exceptions;
alter table T modify constraint UQ enable validate exceptions into exceptions
*
ERROR at line 1:
ORA-02437: cannot validate (MCDONAC.UQ) - primary key violated
SQL> select * from exceptions;
ROW_ID OWNER TABLE_NAME
------------------ ------------------------------ ------------------------------
CONSTRAINT
--------------------------------------------------------------------------------------------------------------------
AAAcFWAAGAAJwn1AAa MCDONAC T
UQ
1 row selected.
Of course, if you've got 1billion nulls...then loading them into an exceptions table perhaps isn't a great idea :-)
In that instance, then a parallel query should do the trick, eg
SQL> create table ERRS parallel as
2 select /*+ parallel */ rowid rid from T
3 where object_id is null
4 and rownum <= 100000;
Table created.
SQL>
SQL> set serverout on
SQL> declare
2 x int;
3 begin
4 select count(*) into x from errs;
5
6 if x = 100000 then
7 dbms_output.put_line('Whoa baby...over 100,000 rows got problems');
8 else
9 dbms_output.put_line('We might be ok, there are only '||x||' problems');
10 end if;
11 end;
12 /
We might be ok, there are only 1 problems
PL/SQL procedure successfully completed.