DB - DDL - how much time each operation takes - a doc needed

Is there a doc describing how much time each DDL can take,
e.g. (n is the number of records in the table):
add nullable column = 0(1)
add column with default falue = o(n)
etc'...


Tal Olier
otal@.mercury.co.ilI haven't seen any documentation. Unfortunitly this isn't a simple answer. If you are adding an attribute to the end of a table or deleteing an attribute from the end, then it should go quick.
If you use EM to insert or delete an attribute in the middle then EM creates the new table with the name Tmp_<table name>, inserts the data from your table into the Tmp_ table, Drops the current table and renames the Tmp_ table to the correct name, adds any constraints, and finally adds and indexes. The time it takes EM to do all of this will depend on the number of rows in the original table.

Did this answer your question?|||Originally posted by Paul Young
I haven't seen any documentation. Unfortunitly this isn't a simple answer. If you are adding an attribute to the end of a table or deleteing an attribute from the end, then it should go quick.
If you use EM to insert or delete an attribute in the middle then EM creates the new table with the name Tmp_<table name>, inserts the data from your table into the Tmp_ table, Drops the current table and renames the Tmp_ table to the correct name, adds any constraints, and finally adds and indexes. The time it takes EM to do all of this will depend on the number of rows in the original table.

Did this answer your question?

No it hasn't, I am looking for something like:

# Operation DB Type Example Time
1 Rename a table Oracle Alter table x rename .. o(1)
MS-SQL Sp_rename o(1)
2 Rename a index Oracle Alter index x rename.. o(1)
MS-SQL Sp_rename t.x.. o(1)
3 Add column Oracle Alter table x add y NULL o(1)
Oracle Alter table x add y default (10)/default(null) o(n)
MS-SQL Alter table x add o(1)

DateDiff

I'm using a datediff(mi, start, stop) to get the duration of an operation. i want it displayed in HH:MM format. can anyone help me w/ a way to do that??
tia,
e3wittI'd use:SELECT Convert(CHAR(5), DateAdd(min
, DateDiff(min, start, stop), '00:00:00')
, 108)-PatP|||thanks! that works like a charm....|||I'd use:SELECT Convert(CHAR(5), DateAdd(min
, DateDiff(min, start, stop), '00:00:00')
, 108)-PatP

[shameless post]

Thank GOD!

Someone is taking all the difficult questions...

Where would we be otherwise?

OH that's right x002548 is awol

Now will somone PLEASE fix me a 'rita

[/shameless post]|||This is the code PaulYoung posted some time ago, as as such became public domain.
if object_id('dbo.fn_ElapsedTime') is not null
drop function dbo.fn_ElapsedTime
go
/*
************************************************** **************
Created : 06/01/2004
By : Paul Young (dbForums participant)
Purpose : Return elapsed time in 99:99:99.999 format.
Execute : select dbo.fn_ElapsedTime(@.date_start, @.date_end)
************************************************** **************
*/
create function dbo.fn_ElapsedTime (
@.starttime datetime,
@.endtime datetime = Null) returns varchar(40)
as begin
declare @.d int,
@.h int,
@.m int,
@.s int,
@.ms int,
@.dif1 int,
@.ret varchar(40)
select @.d = 0,
@.h = 0,
@.m = 0,
@.s = 0,
@.ms = 0
set @.d = datediff(dd,@.starttime,@.endtime)
set @.dif1 = datediff(ms,dateadd(dd,@.d,@.starttime),@.endtime)

if (@.dif1 > 0) begin
set @.ms = @.dif1 % 1000
set @.dif1 = @.dif1 - @.ms
set @.s = ((@.dif1 / 1000) % 60)
set @.dif1 = @.dif1 - (@.s * 1000)
set @.m = ((@.dif1 / 60000) % 60)
set @.dif1 = @.dif1 - (@.m * 60000)
set @.h = ((@.dif1 / 3600000) % 60)
end

set @.ret = cast(@.d as varchar(25)) + ':' +
right('00' + cast(@.h as varchar(2)),2) + ':' +
right('00' + cast(@.m as varchar(2)),2) + ':' +
right('00' + cast(@.s as varchar(2)),2) + '.' +
right('000' + cast(@.ms as varchar(3)),3)

return @.ret
end