生成从2007年到2010年的所有购买行为的数据,到另张表!
insert into t_behavior_buylot
select f_username,trunc(f_optime,'dd'),t.f_lottype,t.f_expect,sum(t.f_paymoney),count(t.f_id),SYSTIMESTAMP
from t_gather_userpaylog t
where t.f_busisort =1
and t.f_expect is not null
and t.f_optime > to_date('2007-01-01','yyyy-mm-dd')
and t.f_optime < to_date('2008-01-01','yyyy-mm-dd')
group by f_username,trunc(f_optime,'dd'),t.f_lottype,t.f_expect;
一年一年运算过去 要46分钟!
后来取消掉唯一索引和关键字,再一个个月导入 全部导完花费了1个小时!
1 索引检查
2 goup by 要大内存排序,否则写磁盘
3 写大量的UNDO 大约7个G
很多时候都明白道理,做起来就忘,白白耗费一整天的时间!
分享到:
相关推荐
1. 复制表结构及其数据: 代码如下:create table table_name_new as select * from table_name_old2. 只复制表结构: 代码...如果两个表结构一样: 代码如下:insert into table_name_new select * from table_name_o
第一句:SELECT * INTO [To...第一句(SELECT INTO FROM)要求目标表[ToTable]不存在,因为在插入时会自动创建。 第二句(INSERT INTO SELECT FROM)要求目标表[ToTable]存在,由于目标表已经存在,所以我们除了插入源
1.INSERT INTO SELECT语句 语句形式为:Insert into Table2(field1,field2,…) select value1,value2,… from Table1 要求目标表Table2必须存在,由于目标表Table2已经存在,所以我们除了插入源表Table1的字段外,还...
插入:insert into table(field1,field2) values(value1,value2) 3 删除:delete from table where 范围 4 更新:update table set field1=value1 where 范围 5 查找:select * from table where ...
将数据库数据转换成insert sql语句,目前只在sql server测试过。 在SqlData2Insert.exe.config文件中修改连接数据库, <add name="ConnectionString" connectionString="Data Source=192.168.1.110;Initial ...
SQL>select table_name,cache from user_tables where instr(cache,'Y')>0; 3、索引 查看索引个数和类别 SQL>select index_name,index_type,table_name from user_indexes order by table_name; 查看索引被...
insert into truntab1 select * from SYS.TRUNTAB1$$2 第八步:验证数据是否完全恢复 select count(*) from truntab1; --72622 至此,truncate掉的数据成功恢复,并且此方法也可以恢复drop table ...
select * from ( select rownum row_id,b.* from (select a.* from sys_oper a) b ) where row_id between 15 and 20 15、对公共授予访问权 grant select on 表名 to public; create public synonym 同义词名 ...
select a.ml_id,a.parent_id,@level from dbo.T_TEMP_MuLu a left join @t b on a.parent_id=b.ml_id where b.level=@level-1 end return end delete dbo.T_TEMP_MuLu where ml_id in (select ml_id ...
使用insert into table(field, …)values(value, …),insert into table(field, …)values(value, …)…的情况 使用insert into table(field, …)select(value,…) union all select(value,…) union all ...
insert into tablea(cola,colb) select cola,colb from tableb; 2.1.3.2 创建相同的表结构并插入数据(备份表数据) create tab_new as select * from tab_old; 2.1.3.3 创建相同的表结构不插入数据 create tab_new ...
INSERT INTO m1_sales_flat_order SELECT * FROM sales_flat_order; CREATE TABLE m1_sales_flat_order_address LIKE sales_flat_order_address; INSERT INTO m1_sales_flat_order_address SELECT * FROM sales_...
g1.DataSource = SqlServerHelper.ReadTable(conn, CommandType.Text, "select * from [students]", null); g1.DataBind(); } 简单方式: SqlServerHelper.ExecuteNonQuery(CommandType.Text,"insert into ...
命令窗口下进行连接Oracle 运行 -->打开的输入框中输入 telnet 172.16.0.233 [localhost~]输入UserName... 插入数据 insert into table_name values(字段值1,字段值2); 查看表记录 select * from table_name;
INSERT INTO t_student VALUES(1,'张小红',8,'育才小学','一班',92) INSERT INTO t_student VALUES(2,'王丽丽',8,'育才小学','一班',90) INSERT INTO t_student VALUES(3,'张燕',7,'云华小学','二班',86) INSERT ...
或者:Insert into Table2 select * from Table1 注意:(1)要求目标表Table2必须存在,并且字段field,field2…也必须存在 (2)注意Table2的主键约束,如果Table2有主键而且不为空,则 field1, field2…中必须...
select seq_link_id.nextval into:new.id from dual; end; --测试插入 insert into customerLink_table(customer_id,Link_Name,Sex,Job,Phone,Mobile,Memo) values ('KH071202015','tcy',2,'总经理','021-...
--插入数据 INSERT INTO dept_sal VALUES(v_emp.deptno,v_emp.total_emp,v_emp.total_sal); END LOOP; END; / --对emp表进行DML操作 INSERT INTO emp(empno,deptno,sal) VALUES('123','10',10000); SELECT * FROM ...
if exists (select * from tempdb.sys.all_objects where name like '%#dbsize%') drop table #dbsize create table #dbsize (Dbname varchar(30),dbstatus varchar(20),Recovery_Model varchar(10) default ('NA'),...
当使用者用INSERT INTO <table> SELECT * FROM <external_table>这样的语句开始装载时,INSERT语句会被Master解析并且分布给主Segment。Segment连接到gpfdist服务器并且并行检索数据,解析并验证数据,从分布键数据...