Re: Why Oracle does not allow rollback of DDL statements?
From: Serge Rielau <srielau_at_ca.ibm.com>
Date: Mon, 10 Nov 2008 06:52:42 -0500
Message-ID: <6nqlkbF9scbU1@mid.individual.net>
>> On Mon, 10 Nov 2008 10:38:03 +0100, Thomas Kellerer wrote:
>>
>>> Noons can't create an index on a table that I have not committed
>>> (because he will not see it). It's just like he can't delete a row in a
>>> table that I have inserted but not committed.
>>
>> That would imply allowing "local" schema objects, like "local tables"
>> or "local temporary tables". That would make it practically impossible to
>> meaningfully monitor the system and run explain plan on SQL statements.
I think now it's getting to teh meat. Supporting transactional DDL does not come for free.
It's obvious that the community is perfectly happy to wrap each EXECUTE IMMEDIATE 'CREATE OR REPLACE ....' into an autonomous transaction and handle logical undo sripts. So.. no incentive.
Date: Mon, 10 Nov 2008 06:52:42 -0500
Message-ID: <6nqlkbF9scbU1@mid.individual.net>
Thomas Kellerer wrote:
> Mladen Gogala, 10.11.2008 11:56:
>> On Mon, 10 Nov 2008 10:38:03 +0100, Thomas Kellerer wrote:
>>
>>> Noons can't create an index on a table that I have not committed
>>> (because he will not see it). It's just like he can't delete a row in a
>>> table that I have inserted but not committed.
>>
>> That would imply allowing "local" schema objects, like "local tables"
>> or "local temporary tables". That would make it practically impossible to
>> meaningfully monitor the system and run explain plan on SQL statements.
I think now it's getting to teh meat. Supporting transactional DDL does not come for free.
It's obvious that the community is perfectly happy to wrap each EXECUTE IMMEDIATE 'CREATE OR REPLACE ....' into an autonomous transaction and handle logical undo sripts. So.. no incentive.
Other RDBMS which do have transactional DDL can very well monitor their system, even explain a query on DDL that isn't committed so long as the one explaining is the session that did the DDL, just like their own modifications to regular data. The schema catalog is just another set of tables after all.
The locking between the index and teh table is no different than any other update lock.
Cheers
Serge
-- Serge Rielau DB2 Solutions Development IBM Toronto LabReceived on Mon Nov 10 2008 - 05:52:42 CST