| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Student SQL question
A copy of this was sent to nchan50315_at_aol.com (NChan50315)
(if that email address didn't require changing)
On 15 May 1998 13:58:13 GMT, you wrote:
>1. In what cases is there a need to convert a number to a string or a string
>to a number. Could you give some practical examples?
create table emp( empno number primary key, ..... );
So, there exists an index on EMP(empno) that could be used for fast retrieval. If we run the query:
select * from emp where empno = 'Some String';
then an index cannot be used since we cannot simply convert 'some string' into a number but rather we must convert the empno column into a string (we have implicitly applied a function to the column so indexes cannot be used).
On the other hand, if we issue:
select * from emp where empno = to_number( 'some string' )
then we are comparing numbers to numbers and we can use an index.
>2. Once a table is created, a column in that table cannot be deleted, or a
>datatype of a column cannot be changed, the tablename cannot be changed (as far
>as I know). Is there a reason? What other modifications cannot be done to a
>table structure. I guess this is where views become handy.
>
The tablename can be changed
SQL> rename T1 to T2;
the datatypes can be changed if the column is empty (either zero rows in the table or the column is always null)
SQL> alter table y modify ( x varchar2(255) );
alter table y modify ( x varchar2(255) )
ERROR at line 1:
ORA-01439: column to be modified must be empty to change datatype
SQL> update y set x = null;
800 rows updated.
SQL> alter table y modify ( x varchar2(255) ); Table altered.
the reason is that modifying the storage like that if there was data in the column would require rewriting the entire table (something you can do yourself if you want with a create table as select statement).
>Thanks very much. Please forward a copy to my address.
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Fri May 15 1998 - 11:11:26 CDT
 |
 |