RE: disable validate on a partitioned table?

[Khedr, Waleed]

Title: disable validate on a partitioned table?

As you know for a partitioned table: unique constraints could be enforced by a local index or global index.

 

For local index: the unique key will be part of the partitioning key.

--------------------

--------------------

So for a partitioned table with a unique key that is a part of the partitioning key, Loading a partition or exchanging it does not require a full table scan or reading all partitions.

 

Uniqueness will be checked in memory during the load/exchange process for only one partition.

 

But if the unique key is not part of the partitioning key, reading the key data from all the partitions and checking it against the new loaded data in memory for any duplicates will be required.

 

 

Waleed

 

-----Original Message-----
From: Jacques Kilchoer [mailto:[EMAIL PROTECTED]]
Sent: Monday, September 30, 2002 4:48 PM
To: Multiple recipients of list ORACLE-L
Subject: disable validate on a partitioned table?

I read the following in the Oracle 8.1 manual:

Oracle8i SQL Reference, Release 3 (8.1.7), Part Number A85397-01
SQL Statements:
CREATE SYNONYM to DROP ROLLBACK SEGMENT, 3 of 31

<<DISABLE VALIDATE disables the constraint and drops the index on the constraint, but keeps the constraint valid. This feature is most useful in data warehousing situations, where the need arises to load into a range-partitioned table a quantity of data with a distinct range of values in the unique key. In such situations, the disable validate state enables you to save space by not having an index. You can then load data from a nonpartitioned table into a partitioned table using the exchange_partition_clause of the ALTER TABLE statement or using SQL*Loader. All other modifications to the table (inserts, updates, and deletes) by other SQL statements are disallowed.

If the unique key coincides with the partitioning key of the partitioned table, disabling the constraint saves overhead and has no detrimental effects. If the unique key does not coincide with the partitioning key, Oracle performs automatic table scans during the exchange to validate the constraint, which might offset the benefit of loading without an index.>>

This seems to say that with the disable validate constraint, Oracle will not need to do a full table scan during a load to find out if the value is unique, even though there is no index. How is that possible?

Assuming that it's true, wouldn't it be beneficial to have the constraint disabled only during the exchange partition or sql*load time? When the load is done, the constraint should be re-enabled?

Would this be a real-life example of how the disable validate constraint would be created?
create table bank_account
  (account# number (6) not null,
   name varchar2 (30)
  )
  partition by range (account#)
  (partition bank_account_part_0 values less than (100000),
   partition bank_account_part_1 values less than (200000),
   partition bank_account_part_max values less than (maxvalue)
  ) ;
alter table bank_account
 add (constraint bank_account_uq1
      unique (account#) disable validate
     ) ;
N.B. The unique constraint is on the partition column.