使用 create table xxx as 时，怎么转换 varchar2(10) 为 number(10)

Question

如题，原表结构类似
create table tab1
(
col1 varchar2(10)
)
我写的SQL
create table tab2
as
select col1, to_number(col1) col2 from tab1
结果类似
create table tab2
(
col1 varchar2(10),
col2 number
)
但我想要的 col2 应该是 number(10)
这个怎么实现呢？另外我说一下背景吧
tab1 是一个大表，数据有几千万吧，但和另外一个表连接时因为 col1 的数据类型没对上，导致索引无效，查询速度太慢了
为了不改变其他SQL或功能，就要增加一个新列 col2 number(10)，col2 和 col1 的数据是一模一样的
尝试过 update ，但 update 的速度无法容忍，于是就用了 create table xxx as，但新表生成后就出现了上面的问题如何让新表那个字段的类型为指定类型呢？也就是 number(10)
求助于各位大侠

Answer 1

Connected to:
Oracle Database 10g Release 10.1.0.2.0 - ProductionSQL> --原表结构:
SQL>  
SQL> create table tab1
  2  (
  3  col1 varchar2(10)
  4  )
  5  ;Table created.SQL> --转换类型，用cast函数
SQL> 
SQL> create table tab2
  2  as
  3  select col1, CAST(to_number(col1) AS NUMBER(10)) col2  --类型转换cast
  4  from tab1
  5  ;Table created.SQL> desc tab2
 Name                            Null?    Type
 ------------------------------- -------- ----
 COL1                                     VARCHAR2(10)
 COL2                                     NUMBER(10)SQL> 

Answer 2

--这样试试
create table tab2
as
select col1, to_number(col1,9999999999) col2 from tab1

Answer 3

谢谢1、2楼
忘说了，数据库是 9i，用两位的方法，还是 number 类型哦，不是 number(10)

Answer 4

你要number(10)是为了防止 ORA-01438: 值大于为此列指定的允许精度
这个错误吧 

Answer 5

---你先建表嘛
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 
Connected as SYS
SQL> 
SQL> drop table tab2;Table droppedSQL> create table tab2
  2  (
  3  col1 varchar2(10),
  4  col2 number(10)
  5  );Table createdSQL> insert into tab2 select col1 ,col1 col2 from tab1;3 rows insertedSQL> commit;Commit completeSQL> desc tab2;
Name Type         Nullable Default Comments 
---- ------------ -------- ------- -------- 
COL1 VARCHAR2(10) Y                         
COL2 NUMBER(10)   Y                         SQL> 

Answer 6

那就这样子操作
alter table tab1 add col3 number(10);
update tal1 set col3=col2;
alter tabel tab1 drop column col2;
alter tabel tab1 rename col3 as col2;

Answer 7

--我这个是在8i下的
SQL> create table test
  2  as 
  3  select cast(sal as number(10)) c from emp where rownum=1;表已创建。SQL> desc test;
 名称                                      空?      类型
 ----------------------------------------- -------- -------------------------
 C                                                  NUMBERSQL> insert into test values(9876543210);已创建 1 行。

Answer 8

--总结一下：
--两种方法：
--(1)、cast()函数转换，要求Oracle10g
create table tab2
as
select col1, CAST(to_number(col1) AS NUMBER(10)) col2 from tab1--(2)、
--直接创建一个tab2
create table tab2(col1 varchar2(10), col2 number(10))
--然后往表里塞数据
insert into tab2 select col1, to_number(col1) col2 from tab1

Answer 9

不是这个原因，是为了和连接的那个表的字段类型完全一致6楼的方法可以试试，我看看效率如何7楼的方法试过，update 效率太低了
在我开发的电脑上，1000条数据用了 7.5 秒，这种表一共有4个，初步估计弄完要十几天
虽然服务器要好得多，但这个速度也是无法容忍的

Answer 10

---在insert 最后加个append，使用末尾追加的方式，别让他排序
insert into tab2 select col1, to_number(col1) col2 from tab1 append;

Answer 11

--数据量大，可以这样一步一步做：
--(1)直接创建一个tab2
create table tab2(col1 varchar2(10), col2 number(10)) nologging;
--(2)然后往表里塞数据
insert /*+append*/ into tab2 select col1, to_number(col1) col2 from tab1
--(3) alter table tab2 logging

Answer 12

--直接创建一个tab2
create table tab2(col1 varchar2(10), col2 number(10))
--然后往表里塞数据
insert into tab2 select col1, to_number(col1) col2 from tab1那用这种方法试试!

Answer 13

先创建，后插入，效率的确不错
我的电脑上：4456048 rows inserted in 375.719 seconds
加分结贴！