RE: disable validate on a partitioned table?

From: Khedr, Waleed <Waleed.Khedr_at_FMR.COM>
Date: Mon, 30 Sep 2002 15:03:22 -0800
Message-ID: <F001.004DCF27.20020930150322@fatcity.com>

Content-Type: multipart/alternative;
boundary="----_=_NextPart_001_01C268CC.97833250" ------_=_NextPart_001_01C268CC.97833250
Content-Type: text/plain;
charset="iso-8859-1"

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-----
Sent: Monday, September 30, 2002 4:48 PM To: Multiple recipients of list ORACLE-L

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.

------_=_NextPart_001_01C268CC.97833250
Content-Type: text/html;
charset="iso-8859-1"

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<TITLE>disable validate on a partitioned table?</TITLE>

<META content="MSHTML 5.50.4915.500" name=GENERATOR></HEAD>

<BODY>
<DIV>As you know for a partitioned table: unique constraints could be enforced by a local index or global index.</DIV> <DIV> </DIV>
<DIV>For local index: the unique key will be part of the partitioning key.</DIV>

<DIV><SPAN class=618454921-30092002><FONT face=Arial color=#0000ff 
size=2>--------------------</FONT></SPAN></DIV>
<DIV><SPAN class=618454921-30092002><FONT face=Arial color=#0000ff 
size=2>--------------------</FONT></SPAN></DIV>
<DIV><SPAN class=618454921-30092002><FONT face=Arial color=#0000ff size=2>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.</DIV> <DIV> </DIV>
<DIV>Uniqueness will be checked in memory during the load/exchange process for only one partition.</DIV>
<DIV> </DIV>
<DIV>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.</DIV> <DIV> </DIV>
<DIV> </DIV>
<DIV>Waleed</DIV>
<DIV> </DIV>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px"> <DIV class=OutlookMessageHeader dir=ltr align=left>-----Original Message----- From: Jacques Kilchoer [mailto:Jacques.Kilchoer_at_quest.com] Sent: Monday, September 30, 2002 4:48 PM To: Multiple recipients of list ORACLE-L Subject: disable validate on a partitioned table? </DIV> 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. </BLOCKQUOTE></BODY></HTML>

------_=_NextPart_001_01C268CC.97833250--

------=_NextPartTM-000-0c11d8c8-ac0f-47dc-ae9a-d59d82d725b6--

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Khedr, Waleed
  INET: Waleed.Khedr_at_FMR.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 - 18:03:22 CDT