此处将为大家介绍关于为什么CREATETABLEASSELECT比SELECTINSERT更快的详细内容，并且为您解答有关createtable和insert哪个快的相关问题，此外，我们还将为您介绍关
此处将为大家介绍关于为什么CREATE TABLE AS SELECT比SELECT INSERT更快的详细内容，并且为您解答有关create table和insert哪个快的相关问题，此外，我们还将为您介绍关于Create table as select、CREATE TABLE 表名 AS SELECT 语句、ctas 与 create table 后 insert、insert into table (a,b,c) select的有用信息。
本文目录一览：- 为什么CREATE TABLE AS SELECT比SELECT INSERT更快（create table和insert哪个快）
- Create table as select
- CREATE TABLE 表名 AS SELECT 语句
- ctas 与 create table 后 insert
- insert into table (a,b,c) select
为什么CREATE TABLE AS SELECT比SELECT INSERT更快（create table和insert哪个快）
我使用INNER JOIN进行查询，结果为1200万行。我喜欢把它放在桌子上。我做了一些测试，当我使用AS
SELECT子句创建表时，比先创建表并在运行SELECT之后运行INSERT更快。我不明白为什么。有人可以为我解释吗？ks
答案1
小编典典如果您使用“选择创建表”（CTAS）
CREATE TABLE new_table AS SELECT * FROM old_table您会自动对数据进行 直接路径插入 。如果你做一个
INSERT INTO new_table AS SELECT * FROM old_table您执行 常规插入操作 。如果要执行直接路径插入，则必须使用APPEND提示。所以你要做
INSERT /*+ APPEND */ INTO new_table AS SELECT * FROM old_table获得与“在创建表中进行选择时相似”的性能。
常规的常规刀片如何工作？
Oracle检查 表的
可用列表
中是否有可用空间的表段已使用块。如果该块不在缓冲区高速缓存中，则将其读入缓冲区高速缓存。最终，该块被读回到磁盘。在此过程中，将写入该块的撤消操作（此处仅需要少量数据），将更新数据结构，例如，如有必要，将更新的数据表保留在段头中，并将所有这些更改写入重做-
缓冲区也一样。
直接路径插入如何工作？
该过程在表的 高水位线上方
分配空间，即超出已使用的空间。它将数据直接写入磁盘，而无需使用缓冲区高速缓存。并且还将其写入重做缓冲区。提交会话后，高水位标记将超出新的书面数据，并且其他会话现在可以看到此数据。
如何改善CTAS和直接路径插入？
- 您可以在NOLOGGING模式下创建故事，而不写入任何重做信息。如果执行此操作，则应在插入后对包含该表的表空间进行备份，否则，如果需要，将无法恢复该表。
您可以并行选择
您可以并行插入
如果在插入操作期间必须维护索引和约束，甚至触发，这可能会大大降低插入操作的速度。因此，您应该避免这种情况，并在插入之后创建索引，并可能使用novalidata创建约束。
Create table as select
create table xxx as select
create table table1 as select * from table2 where 2=3;根据table2的表结构，创建tables1
create table table1 as select * from table2根据table2的表结构，创建tables1，同时将table2的数据插入table1
create table table1(column1_rename,column2_rename) as select column1,column2 from table2;根据table2的表结构，创建tables1,重命名列，并复制数据
CREATE TABLE 表名 AS SELECT 语句
1.新表不存在
create table new_table select * from old_talbe; 这种方法会将old_table中所有的内容都拷贝过来,用这种方法需要注意,new_table中没有了old_table中的primary key,Extra,auto_increment等属性,需要自己手动加,具体参看后面的修改表即字段属性.
只复制表结构到新表
# 第一种方法,和上面类似,只是数据记录为空,即给一个false条件
create table new_table select * from old_table where 1=2;
# 第二种方法
create table new_table like old_table;2.新表存在
复制旧表数据到新表(假设两个表结构一样)
insert into new_table select * from old_table;复制旧表数据到新表(假设两个表结构不一样)
insert into new_table(field1,field2,.....) select field1,field2,field3 from old_table;复制全部数据
select * into new_table from old_table;只复制表结构到新表
select * into new_talble from old_table where 1=2;
create table a like b;
create table c_relation as select c.memberId,m.merchantId,memb.phone from c_merchant as m inner join c_customer c on c.userId=m.userId inner join c_member memb on memb.id=c.memberId where memb.status=10;
由上面的使用 CREATE TABLE 表名 AS SELECT 语句可以看出：
1：只会复制表数据和表结构，不会有任何约束。
2：当 where 条件不成立时，只复制表结构，没有任务数据
ctas 与 create table 后 insert
| 下面是测试结论，此结论只是在本测试环境有效。 |
| 1，ctas 与 create table 后 insert 语句产生的 redo 是差不多的。 |
| 2，ctas 生成的 undo 远远小于 create table and insert 方式。 |
| 3，ctas 生成的 undo 与 create table 后 insert /*+ append */ 差不多。 |
| 4，ctas nologging 方法生成的 log 远远小于其它的方式。 |
| 5，append 方式并不一定能减少 redo 的生成，但是肯定能减少 undo 的生成。 |
| 6，append 减少 redo，前提是表在 nologging 方式下面，注意这里表上面没有索引，append 只对表有效，对索引无效。 |
| 下面是详细的测试步骤 |
| 1，ctas 方式生成的 undo 与 redo |
| SQL> SELECT a.name, b.VALUE |
| 2 FROM v$sysstat a, v$mystat b |
| 3 WHERE a.statistic# = b.statistic# |
| 4 AND a.name IN (''redo size'', ''undo change vector size''); |
| NAME VALUE |
| ---------------------------------------------------------------- ---------- |
| redo size 0 |
| 10. undo change vector size 0 |
| 11. SQL> create table scott.test_ctas as select * from dba_objects; |
| 12. SQL> /* Formatted on 2013/3/8 22:07:44 (QP5 v5.240.12305.39476) */ |
| 13. SQL> SELECT a.name, b.VALUE |
| 14. 2 FROM v$sysstat a, v$mystat b |
| 15. 3 WHERE a.statistic# = b.statistic# |
| 16. 4 AND a.name IN (''redo size'', ''undo change vector size''); |
| 17. |
| 18. NAME VALUE |
| 19. ---------------------------------------------------------------- ---------- |
| 20. redo size 5794552 |
| 21. undo change vector size 23812 |
| 22. |
| 23. 2，ctas nologging 方式生成的 undo 与 redo |
| 24. |
| 25. SQL> SELECT a.name, b.VALUE |
| 26. 2 FROM v$sysstat a, v$mystat b |
| 27. 3 WHERE a.statistic# = b.statistic# |
| 28. 4 AND a.name IN (''redo size'', ''undo change vector size''); |
| 29. |
| 30. NAME VALUE |
| 31. ---------------------------------------------------------------- ---------- |
| 32. redo size 0 |
| 33. undo change vector size 0 |
| 34. |
| 35. SQL> create table scott.test_ctas_nologging nologging as select * from dba_objects; |
| 36. |
| 37. Table created. |
| 38. |
| 39. SQL> SELECT a.name, b.VALUE |
| 40. 2 FROM v$sysstat a, v$mystat b |
| 41. 3 WHERE a.statistic# = b.statistic# |
| 42. 4 AND a.name IN (''redo size'', ''undo change vector size''); |
| 43. |
| 44. NAME VALUE |
| 45. ---------------------------------------------------------------- ---------- |
| 46. redo size 88576 |
| 47. undo change vector size 22008 |
| 48. |
| 49. 这种方式生成的 UNDO,REDO 的大小都是最好的， |
| 50. |
| 51. |
| 52. 3，普通表与 insert 生成的 undo 与 redo |
| 53. |
| 54. SQL> SELECT a.name, b.VALUE |
| 55. 2 FROM v$sysstat a, v$mystat b |
| 56. 3 WHERE a.statistic# = b.statistic# |
| 57. 4 AND a.name IN (''redo size'', ''undo change vector size''); |
| 58. |
| 59. NAME VALUE |
| 60. ---------------------------------------------------------------- ---------- |
| 61. redo size 0 |
| 62. undo change vector size 0 |
| 63. |
| 64. SQL> create table scott.test_normal as select * from dba_objects where 1=0; |
| 65. |
| 66. Table created. |
| 67. |
| 68. SQL> SELECT a.name, b.VALUE |
| 69. 2 FROM v$sysstat a, v$mystat b |
| 70. 3 WHERE a.statistic# = b.statistic# |
| 71. 4 AND a.name IN (''redo size'', ''undo change vector size''); |
| 72. |
| 73. NAME VALUE |
| 74. ---------------------------------------------------------------- ---------- |
| 75. redo size 19848 |
| 76. undo change vector size 5712 |
| 77. |
| 78. SQL> insert into scott.test_normal select * from dba_objects; |
| 79. |
| 80. 50350 rows created. |
| 81. |
| 82. SQL> SELECT a.name, b.VALUE |
| 83. 2 FROM v$sysstat a, v$mystat b |
| 84. 3 WHERE a.statistic# = b.statistic# |
| 85. 4 AND a.name IN (''redo size'', ''undo change vector size''); |
| 86. |
| 87. NAME VALUE |
| 88. ---------------------------------------------------------------- ---------- |
| 89. redo size 5725444 |
| 90. undo change vector size 208092 |
| 91. |
| 92. |
| 93. 4，普通 create 表与 insert append 方式生成的 redo 与 undo |
| 94. SQL> SELECT a.name, b.VALUE |
| 95. 2 FROM v$sysstat a, v$mystat b |
| 96. 3 WHERE a.statistic# = b.statistic# |
| 97. 4 AND a.name IN (''redo size'', ''undo change vector size''); |
| 98. |
| 99. NAME VALUE |
| 100. ---------------------------------------------------------------- ---------- |
| 101. redo size 0 |
| 102. undo change vector size 0 |
| 103. |
| 104. SQL> create table scott.test_normal_append as select * from dba_objects where 1=2; |
| 105. |
| 106. Table created. |
| 107. |
| 108. SQL> SELECT a.name, b.VALUE |
| 109. 2 FROM v$sysstat a, v$mystat b |
| 110. 3 WHERE a.statistic# = b.statistic# |
| 111. 4 AND a.name IN (''redo size'', ''undo change vector size''); |
| 112. |
| 113. NAME VALUE |
| 114. ---------------------------------------------------------------- ---------- |
| 115. redo size 21224 |
| 116. undo change vector size 6072 |
| 117. |
| 118. SQL> insert /*+ append */ into scott.test_normal_append select * from dba_objects; |
| 119. |
| 120. 50350 rows created. |
| 121. |
| 122. SQL> SELECT a.name, b.VALUE |
| 123. 2 FROM v$sysstat a, v$mystat b |
| 124. 3 WHERE a.statistic# = b.statistic# |
| 125. 4 AND a.name IN (''redo size'', ''undo change vector size''); |
| 126. |
| 127. NAME VALUE |
| 128. ---------------------------------------------------------------- ---------- |
| 129. redo size 5771092 |
| 130. undo change vector size 21072 |
| 131. |
| 132. 5，nologging 方式的 create 表与 insert append 方式生成的 undo 与 redo |
| 133. |
| 134. SQL> SELECT a.name, b.VALUE |
| 135. 2 FROM v$sysstat a, v$mystat b |
| 136. 3 WHERE a.statistic# = b.statistic# |
| 137. 4 AND a.name IN (''redo size'', ''undo change vector size''); |
| 138. |
| 139. NAME VALUE |
| 140. ---------------------------------------------------------------- ---------- |
| 141. redo size 0 |
| 142. undo change vector size 0 |
| 143. |
| 144. SQL> create table scott.test_normal_append_nologging nologging as select * from dba_objects where 1=2; |
| 145. |
| 146. Table created. |
| 147. |
| 148. SQL> SELECT a.name, b.VALUE |
| 149. 2 FROM v$sysstat a, v$mystat b |
| 150. 3 WHERE a.statistic# = b.statistic# |
| 151. 4 AND a.name IN (''redo size'', ''undo change vector size''); |
| 152. |
| 153. NAME VALUE |
| 154. ---------------------------------------------------------------- ---------- |
| 155. redo size 37568 |
| 156. undo change vector size 14204 |
| 157. |
| 158. SQL> insert /*+ append */ into scott.test_normal_append_nologging select * from dba_objects; |
| 159. |
| 160. 50351 rows created. |
| 161. |
| 162. SQL> SELECT a.name, b.VALUE |
| 163. 2 FROM v$sysstat a, v$mystat b |
| 164. 3 WHERE a.statistic# = b.statistic# |
| 165. 4 AND a.name IN (''redo size'', ''undo change vector size''); |
| 166. |
| 167. NAME VALUE |
| 168. ---------------------------------------------------------------- ---------- |
| 169. redo size 142944 |
| 170. undo change vector size 46744 |
insert into table (a,b,c) select
本文为博主原创，转载请注明出处：
在项目中，需要统计数据，从基础表中的数据进行统计，并插入到汇总 表中，
（1）语句形式为：Insert into Table2(field1,field2,...) select value1,value2,... from Table1
或者：Insert into Table2 select * from Table1
注意：（1）要求目标表Table2必须存在，并且字段field,field2...也必须存在
（2）注意Table2的主键约束，如果Table2有主键而且不为空，则 field1， field2...中必须包括主键
（3）注意语法，不要加values，和插入一条数据的sql混了，不要写成:
Insert into Table2(field1,field2,...) values (select value1,value2,... from Table1)
应用实例展示：
<insert id="addMonthStatistics" parameterType="com.statistics.model.MonthStatistics">
INSERT INTO tbl_statistics_month(month,livePlayCount,vodPlayCount,liveFlow,vodFlow,storeSpace,storeResidueSpace)
SELECT DATE_FORMAT(#{month}, ''%Y%m'') AS ''month'', SUM(t.livePlayCount) AS ''livePlayCount'', SUM(t.vodPlayCount) AS ''vodPlayCount'', SUM(t.liveFlow) AS ''liveFlow'', SUM(t.vodFlow) AS ''vodFlow''
, SUM(t.storeSpace) AS ''storeSpace'', SUM(t.storeResidueSpace) AS ''storeResidueSpace''
FROM (
SELECT CASE u.playerType WHEN ''1'' THEN u.num ELSE 0 END AS ''livePlayCount'', CASE u.playerType WHEN ''2'' THEN u.num ELSE 0 END AS ''vodPlayCount'', CASE u.playerType WHEN ''1'' THEN u.flowNum ELSE 0 END AS ''liveFlow'', CASE u.playerType WHEN ''2'' THEN u.flowNum ELSE 0 END AS ''vodFlow'', 0 AS ''storeSpace''
, 0 AS ''storeResidueSpace''
FROM (
SELECT playerType, COUNT(*) AS num, ifnull(SUM(bytesSend),0) AS ''flowNum''
FROM tbl_player_statistics
WHERE playerTime BETWEEN CONCAT(DATE_FORMAT(#{month},''%Y-%m''),''-01 00:00:00'') AND CONCAT(DATE_FORMAT(last_day(#{month}),''%Y-%m-%d''),'' 23:59:59'')
GROUP BY playerType
) u
UNION ALL
SELECT 0 AS ''livePlayCount'', 0 AS ''vodPlayCount'', 0 AS ''liveFlow'', 0 AS ''vodFlow'', ifnull(SUM(fileSize),0) AS ''storeSpace''
, 8192000 - ifnull(SUM(fileSize),0) AS ''storeResidueSpace''
FROM tbl_person_space
) t
</insert>
今天关于为什么CREATE TABLE AS SELECT比SELECT INSERT更快和create table和insert哪个快的介绍到此结束，谢谢您的阅读，有关Create table as select、CREATE TABLE 表名 AS SELECT 语句、ctas 与 create table 后 insert、insert into table (a,b,c) select等更多相关知识的信息可以在本站进行查询。
本文标签：
