disable validate on a partitioned table?

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Mon, 30 Sep 2002 12:48:27 -0800
Message-ID: <F001.004DCD9E.20020930124827@fatcity.com>

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.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jacques Kilchoer
  INET: Jacques.Kilchoer_at_quest.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Mon Sep 30 2002 - 15:48:27 CDT