I have always been surprised why Oracle does a implicit commit when DDL statements (e.g create table …) are executed. DDL statements simply write information (insert, delete and update) to data dictionary, so why DDL statement cannot be rolled back. Am I missing some thing?
I was learning SQL Server few months ago and I noticed that SQL Server does allow rollback of DDL statements if I am not mistaken. It lloks like there is something in Oracle architecture which prevents rollback of DDL satements. Rollback of all staements should be allowed not just of DML statements.
> I have always been surprised why Oracle does a implicit commit when > DDL statements (e.g create table …) are executed. DDL statements > simply write information (insert, delete and update) to data > dictionary, so why DDL statement cannot be rolled back. Am I missing > some thing?
> I was learning SQL Server few months ago and I noticed that SQL Server > does allow rollback of DDL statements if I am not mistaken. It lloks > like there is something in Oracle architecture which prevents rollback > of DDL satements. Rollback of all staements should be allowed not just > of DML statements.
> Appercaite your insight.
Why don't you get Tom Kyte's 10g book the first several chapters of architecture related material could give you the insight you are looking for probably.
news:57ce7648-b6db-4e34-ad52-4dc171ceb0a9@v16g2000prc.googlegroups.com... I have always been surprised why Oracle does a implicit commit when DDL statements (e.g create table …) are executed. DDL statements simply write information (insert, delete and update) to data dictionary, so why DDL statement cannot be rolled back. Am I missing some thing?
I was learning SQL Server few months ago and I noticed that SQL Server does allow rollback of DDL statements if I am not mistaken. It lloks like there is something in Oracle architecture which prevents rollback of DDL satements. Rollback of all staements should be allowed not just of DML statements.
Appercaite your insight.
Wanting it different than it is isn't going to change the facts. In general in Oracle you don't execute DDL statements on the fly. (eg create and drop tables on the fly. There are global temporary tables for that and they are created one time) In SQL Server there is often more of a need to create a temporary table on the fly due to locking issues (that Oracle does not have). So SQL Server HAS to work that way or their are up the proverbial creek without a paddle. In Oracle you shouldn't be doing DML and the DDL that you want ot roll back. Could Oracle change the way it does that? Sure it is technically possible. Could Oracle allow dirty reads? Sure it is technically possible, but it goes against their philosophy. Jim
>> I have always been surprised why Oracle does a implicit commit when >> DDL statements (e.g create table …) are executed.
> It's called database consistency. > Only real databases have it.
But creating objects in a database needs to be consistent as well. I don't see why having transactional DDL contradicts database consistency.
Actually I think it adds to consistency.
>> DDL statements >> simply write information (insert, delete and update) to data >> dictionary, so why DDL statement cannot be rolled back.
> Oh no they most certainly don't just do that!
So what else are they doing?
>> I was learning SQL Server few months ago and I noticed that SQL Server
>> does allow rollback of DDL statements if I am not mistaken.
> It does not.
Yes it does (Note that I do not like SQL Server at all, but that is definitely a plus - or it would be if SQL Server wasn't that awful when it comes to locking...)
>> Rollback of all staements should be allowed not just >> of DML statements.
> Actually, it should not. There is no requirement > anywhere for such in any of the available standards > for relational database architecture or design.
There might not be a requirement, but it *is* very handy.
Just think of an database upgrade script that fails in the middle. With transactional DDL I simply do a rollback fix the cause of the error and start the script again. With non-transactional DDL I need to remove all objects that were created until then to put the DB into the state before I ran the script.
On Mon, 10 Nov 2008 10:02:08 +0100, Thomas Kellerer wrote: > But creating objects in a database needs to be consistent as well. I > don't see why having transactional DDL contradicts database consistency.
> Actually I think it adds to consistency.
So, I create table, Noons creates an index to that table, in his own schema. If I was able to roll back the table creation, what happens to Nuno's index? Flashback table to before drop is possible with all releases that support flashback area, since the version 7.3.6.
> On Mon, 10 Nov 2008 10:02:08 +0100, Thomas Kellerer wrote:
>> But creating objects in a database needs to be consistent as well. I >> don't see why having transactional DDL contradicts database consistency.
>> Actually I think it adds to consistency.
> So, I create table, Noons creates an index to that table, in his own > schema. If I was able to roll back the table creation, what happens to > Nuno's index? Flashback table to before drop is possible with all releases > that support flashback area, since the version 7.3.6.
Of course, Noons could only create the index AFTER you commit your DDL! So no roll back would be possible anymore.
> On Mon, 10 Nov 2008 10:02:08 +0100, Thomas Kellerer wrote:
>> But creating objects in a database needs to be consistent as well. I >> don't see why having transactional DDL contradicts database consistency.
>> Actually I think it adds to consistency.
> So, I create table, Noons creates an index to that table, in his own > schema. If I was able to roll back the table creation, what happens to > Nuno's index? Flashback table to before drop is possible with all releases > that support flashback area, since the version 7.3.6.
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.
On Mon, 10 Nov 2008 10:34:53 +0100, Shakespeare wrote: > Mladen Gogala schreef: >> On Mon, 10 Nov 2008 10:02:08 +0100, Thomas Kellerer wrote:
>>> But creating objects in a database needs to be consistent as well. I >>> don't see why having transactional DDL contradicts database >>> consistency.
>>> Actually I think it adds to consistency.
>> So, I create table, Noons creates an index to that table, in his own >> schema. If I was able to roll back the table creation, what happens to >> Nuno's index? Flashback table to before drop is possible with all >> releases that support flashback area, since the version 7.3.6.
> Of course, Noons could only create the index AFTER you commit your DDL! > So no roll back would be possible anymore.
> Shakespeare
OK, that would make things even worse, because you would have "local" objects, visible only from the creating session. There would be no way to meaningfully monitor the system and explain SQL as the "noncommitted" table would not exist from the perspective of any other user.
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.
> On Mon, 10 Nov 2008 10:34:53 +0100, Shakespeare wrote:
>> Mladen Gogala schreef: >>> On Mon, 10 Nov 2008 10:02:08 +0100, Thomas Kellerer wrote:
>>>> But creating objects in a database needs to be consistent as well. I >>>> don't see why having transactional DDL contradicts database >>>> consistency.
>>>> Actually I think it adds to consistency. >>> So, I create table, Noons creates an index to that table, in his own >>> schema. If I was able to roll back the table creation, what happens to >>> Nuno's index? Flashback table to before drop is possible with all >>> releases that support flashback area, since the version 7.3.6.
>> Of course, Noons could only create the index AFTER you commit your DDL! >> So no roll back would be possible anymore.
>> Shakespeare
> OK, that would make things even worse, because you would have "local" > objects, visible only from the creating session. There would be no way > to meaningfully monitor the system and explain SQL as the "noncommitted" > table would not exist from the perspective of any other user.
Indeed. I wasn't saying it would be any better.... or any good at all. But isn't this the case with all uncomitted transactions? What about two users creating the same table(s) with a delayed commit? And what about DROP TABLE statements? Should they be roll backed as well?????
> 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.
Other RDBMS can handle that. rdb/VMS was able to handle things like that 15 years ago. Oracle should have a look at the source code as they now own that product :)
Thomas Kellerer wrote,on my timestamp of 10/11/2008 8:02 PM:
> >> I was learning SQL Server few months ago and I noticed that SQL Server >>> does allow rollback of DDL statements if I am not mistaken.
>> It does not. > Yes it does (Note that I do not like SQL Server at all, but that is
No, it does not. Don't try that rubbish with me, I've been breathing SS2K5 for the last 14 months. It does not. The front end you most likely use to access it does allow that. But that is not SQL Server.
> There might not be a requirement, but it *is* very handy.
No it is not. Try having a process that relies on dropping a table to succeed and another that relies on a create index on that table. Which one is supposed to end first, when?
> Just think of an database upgrade script that fails in the middle. With > transactional DDL I simply do a rollback fix the cause of the error and > start the script again. With non-transactional DDL I need to remove all > objects that were created until then to put the DB into the state before > I ran the script.
So what? Test the script first. Databases are not to be managed like digital photography where if you don't like something you erase it. Databases are to be handled by supposedly responsible folks who presumably now what they are doing. The last thing a db needs is the "bang-head-on-wall-until-something-gives" approach.
Thomas Kellerer wrote,on my timestamp of 10/11/2008 10:08 PM:
>> 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.
Or even manage space in that system. Yeah, we know: "disk is cheap". Try that with SAN platinum disks and see how high it flies with the CFO...
> Other RDBMS can handle that. rdb/VMS was able to handle things like that > 15 years ago. Oracle should have a look at the source code as they now > own that product :)
Given they have owned the product for that long and haven't even bothered duplicating that one should tell us something?
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 Lab
> > 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.
> Other RDBMS can handle that. rdb/VMS was able to handle things like that 15 years ago. > Oracle should have a look at the source code as they now own that product :)
> Thomas
I think that on Oracle Database, now, it would be technically ease to make some DDL transactional, as create and drop table, but it seems to me not so useful. What tecnically seems to me a problem seems to be something such as adding and removing columns. Especially when removing columns Oracle has to go into each block, it seems to me complicated and dangerous make it transactional.
> I have always been surprised why Oracle does a implicit commit when > DDL statements (e.g create table …) are executed. DDL statements > simply write information (insert, delete and update) to data > dictionary, so why DDL statement cannot be rolled back. Am I missing > some thing?
> I was learning SQL Server few months ago and I noticed that SQL Server > does allow rollback of DDL statements if I am not mistaken. It lloks > like there is something in Oracle architecture which prevents rollback > of DDL satements. Rollback of all staements should be allowed not just > of DML statements.
> Appercaite your insight.
"DDL statements simply write information (insert, delete and update) to data dictionary, so why DDL statement cannot be rolled back. Am I missing some thing?"
Yes you are! Suppose you drop a table (or delete a column). All data, indices or whatever will be lost and not regained by merely rolling back the dictionary tables.... So there must be a little bit more to it than just those tables.
I understand your need for rolling back DDL though, regarding the number of typing errors in 11 lines of text..... <g>
On Mon, 10 Nov 2008 12:08:37 +0100, Thomas Kellerer wrote:
> Other RDBMS can handle that. rdb/VMS was able to handle things like that > 15 years ago. Oracle should have a look at the source code as they now > own that product :)
> Thomas
So, you are suggesting that Oracle RDBMS should learn from the RDBMS that has failed and vanished from the market? Why would they do that? To look more like SQL Server, a DB that Oracle has wiped the floor with? Hardly seems like a goal worth pursuing.
Noons wrote: > Thomas Kellerer wrote,on my timestamp of 10/11/2008 8:02 PM:
>> >> I was learning SQL Server few months ago and I noticed that SQL >> Server >>>> does allow rollback of DDL statements if I am not mistaken.
>>> It does not. >> Yes it does (Note that I do not like SQL Server at all, but that is
> No, it does not. Don't try that rubbish with me, I've > been breathing SS2K5 for the last 14 months. It does not. > The front end you most likely use to access it does allow that. > But that is not SQL Server.
That's the first I hear about that. Can you point to any docs explaining the behavior you imply? I'm also told that SQL Server (like DB2) has transactional DDL. Even TRUNCATE table is transactional.
>> There might not be a requirement, but it *is* very handy. > No it is not. Try having a process that relies on dropping a > table to succeed and another that relies on a create index on > that table. Which one is supposed to end first, when?
Oracle has to deal with that today! No matter how you call it the CREATE statement still needs to be ATMIC. And atomic implies that there is a statement level savepoint. If any of the work against the db schema fails for any reason teh DDL statement needs to be rolled back. And that CREATE INDEX statement must not be permitted until the CREATE TABLE statement successfully COMPLETEs. What if someone else tries to create a table with the same name? If someone else drops the original table conflicting with the table I'm trying to create. Obviously the table also cannot be used by anyone until that commit happens. Call it a lock or call it a latch, doesn't matter. Oracle has to sort out timing today. The only difference is that it has a statement level view on things rather than a transaction level view.
Cheers Serge
-- Serge Rielau DB2 Solutions Development IBM Toronto Lab
On Mon, 10 Nov 2008 06:52:42 -0500, Serge Rielau wrote: > 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.
Hmmm, I am not sure that any Oracle DBA worth his salt would like to see that. As a DBA, I am frequently running explain plan on other sessions. What would be gained by transactional DDL? Why is it so significant?
Mladen Gogala wrote: > On Mon, 10 Nov 2008 06:52:42 -0500, Serge Rielau wrote:
>> 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.
> Hmmm, I am not sure that any Oracle DBA worth his salt would like to see > that. As a DBA, I am frequently running explain plan on other sessions. > What would be gained by transactional DDL? Why is it so significant?
OK, let's walk through a simple example. Assume a table with a column c1 and a view on that table. CREATE TABLE T(c1 NUMBER); CREATE OR REPLACE VIEW V AS SELECT c1 AS X FROM T;
You application is up and running with various users referring to V. Now want to rename c1 to c2 (or change the type or whatever):
ALTER TABLE T RENAME COLUMN c1 TO C2; The view is now invalidated (it depends on T.c1). Immediately your other users will start receiving errors from the view. It can't recompile. It's broken.
Of course you as the DBA knew that (and you did test your DDL change). So: CREATE OR REPLACE VIEW V AS SELECT C2 AS X FROM T;
All is fine now, your users will be happy.
With transactional DDL your users would never have seen the invalid view. The ALTER TABLE and the CRATE OR REPLACE VIEW are a semantic unit. They should be executed together. Note that there very well may have been an impact to the users already depending on the actions taken on the table. All that happens here is that a bit more is packed onto the same atomic unit to preserve consistency of the schema.
If I understand recent posts and comments Mark made to me at IOD correctly "editions" in 11gR2 are meant to solve this problem (and presumably others).
Hope that brings the discussion down to a more technical level again.
Cheers Serge -- Serge Rielau DB2 Solutions Development IBM Toronto Lab
>> Other RDBMS can handle that. rdb/VMS was able to handle things like that >> 15 years ago. Oracle should have a look at the source code as they now >> own that product :)
>> Thomas
> So, you are suggesting that Oracle RDBMS should learn from the RDBMS that > has failed and vanished from the market?
Well Oracle _made_ it vanish from the market to begin with.
Only because something is old, doesn't mean that it didn't have good features. I think rdb had data partitioning built in (e.g by index) long before Oracle was even thinking about it.
> Why would they do that? To look more like SQL Server?
That has nothing to do with making it look like SQL Server. I'm definitely not a fan of SQL Server (in fact I hate it) but I like transactional DDL and SQL Server isn't the only one to have this. Postgres and DB2 have that as well.
|
