RE: Oracle 7.3.4 problem... SLOW Drop user

From: Paul Drake <discgolfdba_at_yahoo.com>
Date: Wed, 4 Aug 2004 10:39:51 -0700 (PDT)
Message-ID: <20040804173951.30879.qmail@web20425.mail.yahoo.com>

Cary Millsap <cary.millsap_at_hotsos.com> wrote:

> The one thing that is slow when a dictionary-managed
> segment has lots of
> extents is DROP. To enable recoverability, DROP
> works this way:

> while exists(extent) {
> seek to the end of the extent list
> drop the final extent
> }

> The problem is that this algorithm is O(n^2). A
> 100-extent table will take
> 100^2 10,000x longer to drop than a 1-extent
> table. A 1,000-extent table
> will take 100x longer to drop than a 100-extent
> table (or a million times
> longer than a 1-extent table).

> With locally-managed segments, this is no longer a
> problem. So, part 2 of
> your solution is that when you rebuild your schema,
> use locally-managed
> tablespaces if you can.

> Cary Millsap

apologies for trimming to avoid filtration.

back in preliminary 8.1.7 testing years ago, I had to drop a DMT, that took 17 hours. After we had imported the data into an LMT, a subsequent drop (reloading exercise) took minutes on the same hardware.

never looked back at DMT again.

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Wed Aug 04 2004 - 12:35:40 CDT