| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Alter name of a table
A copy of this was sent to Brian Peasland <peasland_at_edcmail.cr.usgs.gov>
(if that email address didn't require changing)
On Fri, 24 Sep 1999 12:49:50 GMT, you wrote:
>According to the Oracle Certified Professional DBA Certification Exam
>Guide, Oracle Press, Pg
>162:
>
>"To change object names in Oracle is accomplished using the rename
>command.
>This command allows the DBA to change the name of one table to another
>by
>taking data from one table and automatically moves it to another that is
>called something else. Use of rename can take a long time, depending on
>the
>size of the table involved in the operation. The effect here can be
>duplicated through the use of synonyms. A synonym gives users an
>alternate
>name with which they can use to refer to the existing table. No actual
>data
>movement takes place, as in the rename command, which physically moves
>data
>in the Oracle database to an object with the new name. Renaming tables
>can
>be detrimental to performance, especially if the object being renamed is
>large."
>
>Is this book in error? Or is this just for version 7?
>
the book is wrong. I'd send an email to the author if they have their email someone in the book.
Here in 7.3.4:
tkyte_at_ORA734.WORLD> set timing on;
tkyte_at_ORA734.WORLD> create table X as select * from all_objects;
Table created.
Elapsed: 00:00:05.11
tkyte_at_ORA734.WORLD> rename x to y;
Table renamed.
Elapsed: 00:00:00.25
so, 5 seconds to create table as select (move the data) and 1/4 of a second to rename.
tkyte_at_ORA734.WORLD> drop table y;
Table dropped.
Elapsed: 00:00:00.79
tkyte_at_ORA734.WORLD> create table y as select * from all_objects where 1=0;
Table created.
Elapsed: 00:00:00.81
tkyte_at_ORA734.WORLD> insert into y select * from all_objects;
1072 rows created.
Elapsed: 00:00:02.80
~3 seconds to insert into the table...
tkyte_at_ORA734.WORLD> rename y to x;
Table renamed.
Elapsed: 00:00:00.15
less then a 1/4 of a second to rename....
rename has never moved the data (if it did -- we would use it to reorg and such). if it did, we would have to rebuild every index after a rename (and we don't).
Here is a little more evidence. I turned in sql_trace whilst doing the above and this is what I see:
create table X as select * from all_objects
call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.15 0.34 0 0 0 0 Execute 1 2.54 3.21 13 6668 77 1072 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 2.69 3.55 13 6668 77 1072 ********************************************************************************
rename x to y
call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0 Execute 1 0.07 0.09 0 0 1 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.07 0.09 0 0 1 0 ********************************************************************************
the create table as select did lots and lots of blocks. the rename virtually none (it did a simple update to the data dictionary).
Likewise, the insert:
insert into y select * from all_objects
call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.07 0.07 0 0 0 0 Execute 1 2.31 2.40 20 6689 133 1072 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 2.38 2.47 20 6689 133 1072 ********************************************************************************
rename y to x
call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 0 1 0 Execute 1 0.02 0.02 0 0 1 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 0.03 0.03 0 0 2 0
shows the same performance characteristics.
Rename has never moved the data in 7.x or 8.x.
>Thanks,
>Brian
>
>Thomas Kyte wrote:
>>
>> A copy of this was sent to Brian Peasland <peasland_at_edcmail.cr.usgs.gov>
>> (if that email address didn't require changing)
>> On Thu, 23 Sep 1999 12:49:17 GMT, you wrote:
>>
>> >I haven't heard of this one. It might be worth investigating.
>> >
>> >If you use the "RENAME table1 TO table2;" command, then Oracle actually
>> >moves the data. It doesn't just change the name. This can cause
>>
>> rename table1 to table2 JUST UPDATES THE DICTIONARY!! it does not move the
>> data. The ability to actually move a table was introduced in Oracle8i, release
>> 8.1 -- prior to that -- nothing moves a table short of copying it and dropping
>> it.
>>
>> try this on a big table in 8i:
>>
>> tkyte_at_8i> rename t to t_new;
>> Table renamed.
>> Elapsed: 00:00:00.05
>>
>> tkyte_at_8i> alter table t_new move tablespace users;
>> Table altered.
>> Elapsed: 00:00:02.16
>>
>> rename is fast and in place.
>>
>> >performance problems, especially if the table is very big. You will also
>> >have to verify that your constraints and indexes aren't dropped. I'm not
>> >sure if they get "renamed" too.
>> >
>>
>> they don't get renamed themselves but the base table they point to is 'renamed'
>> (and since that name is stored once in the DD and all triggers/constraints
>> really point to object numbers -- this works fine) consider:
>>
>> 1 create trigger t_trigg
>> 2 before insert on t_new
>> 3 for each row
>> 4 begin
>> 5 null;
>> 6* end;
>> tkyte_at_8i> /
>>
>> Trigger created.
>>
>> Elapsed: 00:00:00.15
>>
>> tkyte_at_8i> select trigger_name, table_name from user_triggers;
>>
>> TRIGGER_NAME TABLE_NAME
>> ------------------------------ ------------------------------
>> T_TRIGG T_NEW
>>
>> tkyte_at_8i> rename t_new to t;
>>
>> Table renamed.
>>
>> Elapsed: 00:00:00.05
>> tkyte_at_8i> select trigger_name, table_name from user_triggers;
>>
>> TRIGGER_NAME TABLE_NAME
>> ------------------------------ ------------------------------
>> T_TRIGG T
>>
>> Elapsed: 00:00:00.00
>> tkyte_at_8i>
>>
>> so they get carried along as well.
>>
>> >But everyone missed the easiest solution: SYNONYMS! Why not just create
>> >a synonym for the table? Quick and easy.
>> >
>>
>> thats true. that'll do it as well.
>>
>> but don't be afraid of rename -- it works as you would expect, it updates the DD
>> and thats about it.
>>
>> (and it is fully documented)
>>
>> >HTH,
>> >Brian
>> >
>> >
>> >Chetan Wagle wrote:
>> >>
>> >> Hi niall,
>> >>
>> >> I have heard of an undocumented command called 'alter table <name> rename
>> >> <newname>'
>> >>
>> >> Check it out. The other solutions won't give you your triggers and
>> >> referential constraints.
>> >>
>> >> HTH,
>> >>
>> >> Chetan
>> >> Bangalore, India
>> >>
>> >> Niall Litchfield wrote in message <7saos6$b9l$1_at_soap.pipex.net>...
>> >> >or if you want to create a backup of the existing one as well, for when
>> >> that
>> >> >app breaks..
>> >> >
>> >> >create table table2 as select * from table1
>> >> >
>> >> >Niall
>> >> >Oracle DBA Audit Commission UK
>> >> ><owais_anjum_at_my-deja.com> wrote in message
>> >> >news:7rq6uv$sb2$1_at_nnrp1.deja.com...
>> >> >> In article <7rpcf7$hfg$1_at_perki.connect.com.au>,
>> >> >> "Tom Zamani" <tom_at_taten.com.au> wrote:
>> >> >> > Is there any way to change the name of a table?
>> >> >> > Like alter table .....
>> >> >> > Please help me out.
>> >> >> > Tom
>> >> >> >
>> >> >> >
>> >> >>
>> >> >> rename table1 to table2;
>> >> >>
>> >> >> pretty straight forward hmmm :)
>> >> >>
>> >> >>
>> >> >> Sent via Deja.com http://www.deja.com/
>> >> >> Share what you know. Learn what you don't.
>> >> >
>> >> >
>>
>> --
>> See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
>> Current article is "Part I of V, Autonomous Transactions" updated June 21'st
>>
>> Thomas Kyte tkyte_at_us.oracle.com
>> Oracle Service Industries Reston, VA USA
>>
>> Opinions are mine and do not necessarily reflect those of Oracle Corporation
--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'...
Current article is "Part I of V, Autonomous Transactions" updated June 21'st
Thomas Kyte tkyte_at_us.oracle.com Oracle Service Industries Reston, VA USA
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Sat Sep 25 1999 - 08:01:29 CDT
 |
 |