Home » SQL & PL/SQL » SQL & PL/SQL » Time taken to modify a column datatype (Oracle 9i)
Time taken to modify a column datatype [message #443179] Sat, 13 February 2010 06:01 Go to next message
dancingdaisies
Messages: 9
Registered: February 2010
Junior Member
Hello,

I have a table with some 50 columns out of which I need to modify the datatype for two columns which happen to be empty. But the table has data in other columns (around 191 lakh rows) I gave an ALTER TABLE table_name modify column_name column_datatype query. But it seems to be taking forver to execute. I tried the same with a table with 5000 rows & it got done quickly.

My question is , the more the data in The table, would the time taken to alter a column datatype be more ??? Confused

Thanks in advance!

Report message to a moderator

Re: Time taken to modify a column datatype [message #443185 is a reply to message #443179] Sat, 13 February 2010 08:48 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>My question is , the more the data in The table, would the time taken to alter a column datatype be more ??? Confused

yes.

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

Report message to a moderator

Re: Time taken to modify a column datatype [message #443191 is a reply to message #443179] Sat, 13 February 2010 09:05 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
My question is , the more the data in The table, would the time taken to alter a column datatype be more

It depends on on the current datatype and the type of modification (increase, decrease, change datatype...).

Regards
Michel

Report message to a moderator

Re: Time taken to modify a column datatype [message #443241 is a reply to message #443191] Sat, 13 February 2010 22:44 Go to previous messageGo to next message
dancingdaisies
Messages: 9
Registered: February 2010
Junior Member
Michel Cadot wrote on Sat, 13 February 2010 20:35
Quote:
My question is , the more the data in The table, would the time taken to alter a column datatype be more

It depends on on the current datatype and the type of modification (increase, decrease, change datatype...).

Regards
Michel



Hey michel,

Am trying to change a column of data type DATE to VARCHAR2( 8 )

The version is Oracle 9i installed in a UNIX environment & am using SQL Navigator to run The queries. So when am attempting the above in a table with 191 lac rows , with the column in question being empty , would it take a long time ?? The query just says processing...

Thanks,
Ayesha

Report message to a moderator

Re: Time taken to modify a column datatype [message #443248 is a reply to message #443241] Sun, 14 February 2010 00:56 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
Am trying to change a column of data type DATE to VARCHAR2( 8 )

You cannot do it directly:
SQL> create table t (col date);

Table created.

SQL> insert into t values (sysdate);

1 row created.

SQL> commit;

Commit complete.

SQL> alter table t modify col varchar2(8);
alter table t modify col varchar2(8)
                     *
ERROR at line 1:
ORA-01439: column to be modified must be empty to change datatype

So post (copy and paste as I did) what you did.

Regards
Michel

Report message to a moderator

Re: Time taken to modify a column datatype [message #443256 is a reply to message #443248] Sun, 14 February 2010 04:15 Go to previous messageGo to next message
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Changing it from date to varchar2 also has a lot of my internal warning bells ringing.

So you don't want to store dates in that field anymore?

Report message to a moderator

Re: Time taken to modify a column datatype [message #443266 is a reply to message #443248] Sun, 14 February 2010 11:00 Go to previous messageGo to next message
dancingdaisies
Messages: 9
Registered: February 2010
Junior Member
Michel Cadot wrote on Sun, 14 February 2010 12:26
Quote:
Am trying to change a column of data type DATE to VARCHAR2( 8 )

You cannot do it directly:
SQL> create table t (col date);

Table created.

SQL> insert into t values (sysdate);

1 row created.

SQL> commit;

Commit complete.

SQL> alter table t modify col varchar2(8);
alter table t modify col varchar2(8)
                     *
ERROR at line 1:
ORA-01439: column to be modified must be empty to change datatype

So post (copy and paste as I did) what you did.

Regards
Michel


Hey michel,

Smile I know that we cannot change the datatype when a column has data. As mentioned in my original post, The TABLE had 191 lac rows. Then I did alter statement and added 2 columns. Now is when I realized that I need those two to be in varchar2 format after looking at the data that is being passed to them. So the COLUMNS for which I have to modify Datatype are still empty though table has original rows.

Now my point is why is it taking so long to get modified ? Is that expected ? If yes, why ? The syntax & everything else is fine..


@ Thomas,

Yes I want that field to be varchar2( 8 ) instead of date

Report message to a moderator

Re: Time taken to modify a column datatype [message #443267 is a reply to message #443266] Sun, 14 February 2010 11:28 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Now my point is why is it taking so long to get modified ?

Post Operating System (OS) name & version for DB server system.
Post results of
SELECT * from v$version

Is DB actively making changes?
Or is DB "hung" & just sitting idle.

Report message to a moderator

Re: Time taken to modify a column datatype [message #443268 is a reply to message #443267] Sun, 14 February 2010 11:40 Go to previous messageGo to next message
dancingdaisies
Messages: 9
Registered: February 2010
Junior Member
BlackSwan wrote on Sun, 14 February 2010 22:58
>Now my point is why is it taking so long to get modified ?

Post Operating System (OS) name & version for DB server system.
Post results of
SELECT * from v$version

Is DB actively making changes?
Or is DB "hung" & just sitting idle.



Hey I have posted the version above, taht is all the info I know. See the query executed finally after an hour. But I just wanted to know why it takles long just to alter a datatype !!!

Report message to a moderator

Re: Time taken to modify a column datatype [message #443271 is a reply to message #443268] Sun, 14 February 2010 11:46 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Hey I have posted the version above,
No you did NOT provide Oracle version to 4 decimal places.

>But I just wanted to know why it takles long just to alter a datatype !!!

The way to know for sure what oracle is doing "under the hood" is to use SQL_TRACE as below


ALTER SESSION SET SQL_TRACE=TRUE;
-- invoke the SQL code
ALTER SESSION SET SQL_TRACE=FALSE;

now find the trace file within ./udump folder
tkprof <trace_file.trc> trace_results.txt explain=<username>/<password>

Report message to a moderator

Re: Time taken to modify a column datatype [message #443273 is a reply to message #443266] Sun, 14 February 2010 11:52 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
dancingdaisies
Yes I want that field to be varchar2( 8 ) instead of date

May I ask why?

Report message to a moderator

Re: Time taken to modify a column datatype [message #443274 is a reply to message #443266] Sun, 14 February 2010 12:24 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
I know that we cannot change the datatype when a column has data.

How could we know that you know it?

Quote:
Now my point is why is it taking so long to get modified ? Is that expected ? If yes, why ? The syntax & everything else is fine..

1/ Why don't you post what you did as asked instead of blablabla?
2/ How do you think Oracle knows there is no value in the column?

Regards
Michel

[Updated on: Sun, 14 February 2010 12:28]

Report message to a moderator

Re: Time taken to modify a column datatype [message #443275 is a reply to message #443273] Sun, 14 February 2010 12:29 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Littlefoot wrote on Sun, 14 February 2010 18:52
dancingdaisies
Yes I want that field to be varchar2( 8 ) instead of date

May I ask why?

Because he knows it is a stupid thing. Wink

Regards
Michel

Report message to a moderator

Re: Time taken to modify a column datatype [message #443276 is a reply to message #443275] Sun, 14 February 2010 12:55 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Actually, what I meant to suggest was NOT to convert a DATE column into a CHARACTER one, but alter a table and add another (character) column and insert values from date datatype column in there. Something like
SQL> desc this_table;
 Name                                      Null?    Type
 ----------------------------------------- -------- ------------------------

 EMPNO                                     NOT NULL NUMBER(4)
 DATE_COLUMN                                        DATE

SQL> select * from this_table;

     EMPNO DATE_COLUM
---------- ----------
      7788 09.12.1982
      7876 12.01.1983
      7934 23.01.1982

SQL> alter table this_table add char_column varchar2(8);

Table altered.

SQL> update this_table set char_column = to_char(date_column, 'ddmmyyyy');

3 rows updated.

SQL> select * From this_table;

     EMPNO DATE_COLUM CHAR_COL
---------- ---------- --------
      7788 09.12.1982 09121982
      7876 12.01.1983 12011983
      7934 23.01.1982 23011982

SQL> alter table this_table drop column date_column;

Table altered.

SQL> alter table this_table rename column char_column to date_column;

Table altered.

SQL> select * from this_table;

     EMPNO DATE_COL
---------- --------
      7788 09121982
      7876 12011983
      7934 23011982

SQL> desc this_table;
 Name                                      Null?    Type
 ----------------------------------------- -------- ------------------------

 EMPNO                                     NOT NULL NUMBER(4)
 DATE_COLUMN                                        VARCHAR2(8)

SQL>


If it were me (I wouldn't do it at all, but OK ...), I'd avoid the last two steps (dropping the original column above all!) because - sooner or later - the OP might be back with another question, having problems with date arithmetic, invalid month values, etc. etc. (shortly, anything you can think of when storing dates into character columns).

As you guessed my thoughts right, this smells like a stupid idea, but we can't be sure until the OP explains the reason - maybe it is absolutely legitimate.

Report message to a moderator

Re: Time taken to modify a column datatype [message #443334 is a reply to message #443273] Mon, 15 February 2010 03:25 Go to previous messageGo to next message
dancingdaisies
Messages: 9
Registered: February 2010
Junior Member
Littlefoot wrote on Sun, 14 February 2010 23:22
dancingdaisies
Yes I want that field to be varchar2( 8 ) instead of date

May I ask why?


I dunno what to answer for that ! The business requires it to be varchar2( 8 ) . This is irrelevant to the discussion

[Updated on: Mon, 15 February 2010 03:26]

Report message to a moderator

Re: Time taken to modify a column datatype [message #443335 is a reply to message #443271] Mon, 15 February 2010 03:28 Go to previous messageGo to next message
dancingdaisies
Messages: 9
Registered: February 2010
Junior Member
BlackSwan wrote on Sun, 14 February 2010 23:16
>Hey I have posted the version above,
No you did NOT provide Oracle version to 4 decimal places.

> That's because I do not know till 4 decimal places !!

Report message to a moderator

Re: Time taken to modify a column datatype [message #443337 is a reply to message #443334] Mon, 15 February 2010 03:33 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
The business requires it to be varchar2( 8 ) .

No, the business does NOT require that.
It requires that something represents a date, it does NOT require it is VARCHAR2(8). Business does not care of implementation, it just wants feature.

Quote:
1/ Why don't you post what you did as asked instead of blablabla?
2/ How do you think Oracle knows there is no value in the column?

Please post what is asked, answer questions that are asked.

Regards
Michel
[edited by LF: disabled smilies]

[Updated on: Mon, 15 February 2010 07:31] by Moderator

Report message to a moderator

Re: Time taken to modify a column datatype [message #443339 is a reply to message #443274] Mon, 15 February 2010 03:39 Go to previous messageGo to next message
dancingdaisies
Messages: 9
Registered: February 2010
Junior Member
Michel Cadot wrote on Sun, 14 February 2010 23:54
Quote:
I know that we cannot change the datatype when a column has data.

How could we know that you know it?

Quote:
Now my point is why is it taking so long to get modified ? Is that expected ? If yes, why ? The syntax & everything else is fine..

1/ Why don't you post what you did as asked instead of blablabla?
2/ How do you think Oracle knows there is no value in the column?

Regards
Michel


> How do you think Oracle knows there is no value in the column?

How do you expect me to know what oracle knows !! I am just saying from the error thrown from queries like the one u posted above!

Michel , there is no necessity whatsoever to be rude OK. Now that i think about it, I can do without your sarcasm or your solutions ! Thanks !!!

Report message to a moderator

Re: Time taken to modify a column datatype [message #443341 is a reply to message #443337] Mon, 15 February 2010 03:49 Go to previous messageGo to next message
dancingdaisies
Messages: 9
Registered: February 2010
Junior Member
Michel Cadot wrote on Mon, 15 February 2010 15:03
Quote:
The business requires it to be varchar2( 8 ) .

No, the business does NOT require that.
It requires that something represents a date, it does NOT require it is VARCHAR2(Cool. Business does not care of implementation, it just wants feature.

Quote:
1/ Why don't you post what you did as asked instead of blablabla?
2/ How do you think Oracle knows there is no value in the column?

Please post what is asked, answer questions that are asked.

Regards
Michel


How do u know what my business requires? Don't you think we are digressing away from the issue ? why it has to be changed is irrelevant ! Am sorry I even joined this forum in the first place ! If I knew there were going to be people like you to put junior members down , I would have NEVER joined.

You please answer what is asked , do not speak irrelevant things

[Updated on: Mon, 15 February 2010 03:49]

Report message to a moderator

Re: Time taken to modify a column datatype [message #443344 is a reply to message #443341] Mon, 15 February 2010 03:56 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
1/ Oracle must scan ALL blocks to know if a column has a value => more blocks, more time.

2/ Do not attack us because we give you advices. Remember that we are trying to help in the best way. Take care that if you insult us the only result will be that you will no more get help.

3/ A developer is also a person that is able to think and MUST feedback to a requirement that seems to be wrong.

Regards
Michel

[Updated on: Mon, 15 February 2010 03:58]

Report message to a moderator

Re: Time taken to modify a column datatype [message #443346 is a reply to message #443344] Mon, 15 February 2010 04:06 Go to previous messageGo to next message
dancingdaisies
Messages: 9
Registered: February 2010
Junior Member
Michel Cadot wrote on Mon, 15 February 2010 15:26
1/ Oracle must scan ALL blocks to know if a column has a value => more blocks, more time.

2/ Do not attack us because we give you advices. Remember that we are trying to help in the best way. Take care that if you insult us the only result will be that you will no more get help.

3/ A developer is also a person that is able to think and MUST feedback to a requirement that seems to be wrong.

Regards
Michel



Advice could be given in the right way rather than blending it with sarcasm & rudeness. I honestly did not think what you gave was Advice. Was more like a mockery. And please do not say that I attacked YOU ! I merely responded to your queries & I do not think i insulted anyone. If I did , i did not intend to.

I was just trying to learn why something is happening and is there any logical reason behind that. But now that urge to learn also disappeared, seeing the reception in this forum .

I have been part of various forums & have given advice & answers to the silliest of questions -- because people learn only when they ask. But am sorry to say that, never have I seen such sarcasm & bullying in any forum.

[Updated on: Mon, 15 February 2010 04:07]

Report message to a moderator

Re: Time taken to modify a column datatype [message #443374 is a reply to message #443179] Mon, 15 February 2010 06:19 Go to previous messageGo to next message
cookiemonster
Messages: 13960
Registered: September 2008
Location: Rainy Manchester
Senior Member
@dancingdasies - despite the unfortunate tone of this thread there is a lot of good advice here on how to work out what your problem is.
Changing the datatype of an empty column in oracle should be very fast, and we can't think of any reason why it wouldn't be.
The size of the table should make some difference but not that much. (Michel I know what you mean about scanning the blocks but I just knocked up a 1.5 million row table and tried it - took 2 seconds).
The only way to know for sure what the issue is is to run a sql trace and look at the trace file. Blackswan asked you to do this earlier.
It could be a bug in your specific version of oracle, which is why we asked you for the full version number. Blackswan also gave you the query to get this - select * from v$version.

As for the comments about this not being a good idea - I'm with the others, it's never a good idea to store dates in a varchar.
I don't know who has told you to do this or why but at the end of the day business users are only ever interested in getting the right answer efficiently.
Storing dates in varchars makes that unnecessarily hard.
Oracle has all sorts of algorithms to allow it to efficiently scan dates in queries, but it'll only use them if you tell it the data is a date type.
And of course using a varchar allows people to store invalid dates, which'll mean you spend a lot of time writing code to cope with invalid dates when you really shouldn't need to.
We go on about this simply because we see a lot of questions on this site and others where people are having problems caused by these two issues and our recommendation every time is to store dates in date columns.

So if I were you I'd work out why someone wants this date stored as a varchar and see if there is another way to achieve whatever they need will keeping the data in a date column.

Report message to a moderator

Re: Time taken to modify a column datatype [message #443376 is a reply to message #443374] Mon, 15 February 2010 06:26 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
Michel I know what you mean about scanning the blocks but I just knocked up a 1.5 million row table and tried it - took 2 seconds

It does not take the same time if the table is compact with one column of number datatype or with 100 columns with varchar2(1000).
It does not take the same time if you loaded your table or if it is a table with a real life of insert, update, delete.
You cannot know how it takes for OP's table as he posted nothing about it.

Regards
Michel

Report message to a moderator

Re: Time taken to modify a column datatype [message #443378 is a reply to message #443179] Mon, 15 February 2010 06:38 Go to previous messageGo to next message
cookiemonster
Messages: 13960
Registered: September 2008
Location: Rainy Manchester
Senior Member
I'm just guessing sure. Which is why I told him to run a trace.

Report message to a moderator

Re: Time taken to modify a column datatype [message #443388 is a reply to message #443341] Mon, 15 February 2010 07:58 Go to previous message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
dancingdaisies wrote on Mon, 15 February 2010 04:49
Michel Cadot wrote on Mon, 15 February 2010 15:03
Quote:
The business requires it to be varchar2( 8 ) .

No, the business does NOT require that.
It requires that something represents a date, it does NOT require it is VARCHAR2(Cool. Business does not care of implementation, it just wants feature.

Quote:
1/ Why don't you post what you did as asked instead of blablabla?
2/ How do you think Oracle knows there is no value in the column?

Please post what is asked, answer questions that are asked.

Regards
Michel


How do u know what my business requires? Don't you think we are digressing away from the issue ? why it has to be changed is irrelevant ! Am sorry I even joined this forum in the first place ! If I knew there were going to be people like you to put junior members down , I would have NEVER joined.

You please answer what is asked , do not speak irrelevant things


I just wanted to jump in here in a nice way, really.

A business will not ask to use a VARCHAR2 column instead of a DATE. They should have no idea how a database is storing data.

They will ask for something like "give me the monthly totals" and not "give me the monthly totals but you have to use loops instead of any analytic functions."

If a manager is asking you this then they are telling you how to do your job. See how they feel if you tell them how to do their job.

[Updated on: Mon, 15 February 2010 08:01]

Report message to a moderator

Previous Topic: Script to retrieve Blobs from Database
Next Topic: calling a procedure based on a distinct resultset
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Tue Dec 03 14:04:10 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.