rebuilding indexes, the tests

From: Rachel Carmichael <wisernet100_at_yahoo.com>
Date: Mon, 30 Dec 2002 10:53:38 -0800
Message-ID: <F001.005244AC.20021230105338@fatcity.com>

Okay, I did 4 different tests, as follows:

in each test I created a partitioned table.

Test 1 -- create a unique partitioned index, then create a primary key constraint with the same columns
Test 2 -- create a non-unique partitioned index, then create a primary key constraint with the same columns
Test 3 -- create a primary key constraint with the "using index" clause, partitioned
Test 4 -- same as Test 3

In each case I then loaded valid data via insert and checked that for Test 1 and Test 2 the indexes were being used to enforce the constraint. They were. All partitions were in a USABLE state.

I then loaded, via sqlloader direct=true, one row that would cause one of the partitions to become UNUSABLE.

I then disabled the primary key constraint. For Test 4 only, I did the disable using the "keep index" clause. Next I checked for the existence of the index. In Test 2 (non-unique index) and Test 4 (keep index clause) the index remained. In Test 1 (unique index) and Test 3
(without the keep clause), the index disappeared.
For both Test 2 and Test 4, I still needed to rebuild the index partition after I removed the duplicates to make the partition USABLE again.

so.... the conclusion is, if I want not to lose the entire partitioned index, I need either a non-unique index to enforce the constraint or, for 9i only, the keep index clause when I disable the constraint. Since, in our case, the constraints have already been built using the "INDEX TABLESPACE" clause, I'll use the "keep index" clause when I have to remove duplicates.

Thanks to all who responded and boy did I have fun proving this out :)

Rachel

---

Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Rachel Carmichael
  INET: wisernet100_at_yahoo.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 Dec 30 2002 - 12:53:38 CST