[原创]append_values hint

最新推荐文章于 2022-03-10 16:58:46 发布
最新推荐文章于 2022-03-10 16:58:46 发布
阅读量256 收藏
点赞数
文章标签： 数据库 大数据
原文链接：https://yq.aliyun.com/articles/283818
版权

oracle 11gR2 新出来个新提示 append_values ,他的作用是对于insert values方式可以直接路径插入, 可以减少日志量生成.在9i,10g只有insert into xx select 方式可以通过添加提示append来进行直接路径插入,但是对于insert into values的方式插入添加append是没有作用的。11R1的时候insert values添加append可以起到直接路径插入作用(insert select方式一样生效),11gR2又继续推出这个新参数了来防止人们混淆,相应的insert /*+ append*/ values的方式到11gR2对于直接路径插入又失效了,当然这个新参数依然是要结合大数据量批量加载数据的方式才可以取得理想的效果,对于每次单行插入的方式没有什么好处.原理上都是要搞成并行批量的方式直接在高水位线之上插入数据.所以需要在insert完成之后立刻提交,不提交的话再次进行相应表的事物操作会报错的.

下面简单测试一下:测试环境为oracle11R2

  这种方式可以有效减少日志量

1

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as noap
 
SQL>
SQL> drop TABLE TEST_OBJECTS_INSERT PURGE;
 
Table dropped
SQL> drop TABLE TEST_OBJECTS PURGE;
 
Table dropped
SQL> CREATE TABLE TEST_OBJECTS AS SELECT owner,object_name,object_id FROM dba_objects;
 
Table created
SQL> CREATE TABLE TEST_OBJECTS_INSERT AS SELECT owner,object_name,object_id FROM dba_objects WHERE 1=2;
 
Table created
 
SQL> set timing on
SQL>
SQL>  BEGIN
  2     FOR I IN (SELECT * FROM TEST_OBJECTS) LOOP
  3       EXECUTE IMMEDIATE 'INSERT INTO TEST_OBJECTS_INSERT VALUES(:1,:2,:3)'
  4         USING I.OWNER, I.OBJECT_NAME, I.OBJECT_ID;
  5     END LOOP;
  6     COMMIT;
  7   END;
  8 
  9  /
 
PL/SQL procedure successfully completed
 
Executed in 42.438 seconds
 
SQL>
SQL>    SELECT A.NAME, B.VALUE
  2     FROM V$MYSTAT B, V$STATNAME A
  3    WHERE A.STATISTIC# = B.STATISTIC#
  4      AND A.NAME LIKE '%redo size%';
 
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                         214693328
redo size for lost write detection                                        0
redo size for direct writes                                            7488
 
Executed in 0.047 seconds
 
SQL>

2


Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as noap
 
SQL> SET TIMING ON
SQL>
SQL>  DECLARE
  2 
  3     TYPE T_FORALL_TEST_TAB IS TABLE OF TEST_OBJECTS%ROWTYPE INDEX BY BINARY_INTEGER;
  4     L_TAB   T_FORALL_TEST_TAB;
  5     V_COUNT INTEGER := 0;
  6   BEGIN
  7     FOR I IN (SELECT * FROM TEST_OBJECTS) LOOP
  8       V_COUNT := V_COUNT + 1;
  9       L_TAB(V_COUNT).OWNER := I.OWNER;
 10       L_TAB(V_COUNT).OBJECT_NAME := I.OBJECT_NAME;
 11       L_TAB(V_COUNT).OBJECT_ID := I.OBJECT_ID;
 12     END LOOP;
 13     COMMIT;
 14 
 15     --CREATE TABLE TEST_OBJECT_1 AS SELECT OWNER,OBJECT_NAME,OBJECT_ID FROM DBA_OBJECTS;
 16     -- CREATE TABLE TEST_OBJECT_TTX AS SELECT OWNER,OBJECT_NAME,OBJECT_ID FROM DBA_OBJECTS WHERE 1=2;
 17     FORALL I IN L_TAB.FIRST .. L_TAB.LAST
 18       INSERT /*+ APPEND_VALUES(T)*/ INTO TEST_OBJECTS_INSERT T VALUES(L_TAB(I).OWNER,L_TAB(I).OBJECT_NAME,L_TAB(I).OBJECT_ID);
 19     COMMIT;
 20 
 21   END;
 22  /
 
PL/SQL procedure successfully completed
 
Executed in 4.39 seconds
 
SQL>
SQL>    SELECT A.NAME, B.VALUE
  2     FROM V$MYSTAT B, V$STATNAME A
  3    WHERE A.STATISTIC# = B.STATISTIC#
  4      AND A.NAME LIKE '%redo size%';
 
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                            132656
redo size for lost write detection                                        0
redo size for direct writes                                            7332
 
Executed in 0.047 seconds

3

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as noap
 
SQL> SET TIMING ON
SQL>
SQL>  DECLARE
  2 
  3     TYPE T_FORALL_TEST_TAB IS TABLE OF TEST_OBJECTS%ROWTYPE INDEX BY BINARY_INTEGER;
  4     L_TAB   T_FORALL_TEST_TAB;
  5     V_COUNT INTEGER := 0;
  6   BEGIN
  7     FOR I IN (SELECT * FROM TEST_OBJECTS) LOOP
  8       V_COUNT := V_COUNT + 1;
  9       L_TAB(V_COUNT).OWNER := I.OWNER;
 10       L_TAB(V_COUNT).OBJECT_NAME := I.OBJECT_NAME;
 11       L_TAB(V_COUNT).OBJECT_ID := I.OBJECT_ID;
 12     END LOOP;
 13     COMMIT;
 14 
 15     --CREATE TABLE TEST_OBJECT_1 AS SELECT OWNER,OBJECT_NAME,OBJECT_ID FROM DBA_OBJECTS;
 16     -- CREATE TABLE TEST_OBJECT_TTX AS SELECT OWNER,OBJECT_NAME,OBJECT_ID FROM DBA_OBJECTS WHERE 1=2;
 17     FORALL I IN L_TAB.FIRST .. L_TAB.LAST
 18       INSERT /*+ APPEND_VALUES(T)*/ INTO TEST_OBJECTS_INSERT T VALUES(L_TAB(I).OWNER,L_TAB(I).OBJECT_NAME,L_TAB(I).OBJECT_ID);
 19     COMMIT;
 20 
 21   END;
 22  /
 
PL/SQL procedure successfully completed
 
Executed in 4.39 seconds
 
SQL>
SQL>    SELECT A.NAME, B.VALUE
  2     FROM V$MYSTAT B, V$STATNAME A
  3    WHERE A.STATISTIC# = B.STATISTIC#
  4      AND A.NAME LIKE '%redo size%';
 
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                            132656
redo size for lost write detection                                        0
redo size for direct writes                                            7332
 
Executed in 0.047 seconds
 
SQL>   DECLARE
  2 
  3     TYPE T_FORALL_TEST_TAB IS TABLE OF TEST_OBJECTS%ROWTYPE INDEX BY BINARY_INTEGER;
  4     L_TAB   T_FORALL_TEST_TAB;
  5     V_COUNT INTEGER := 0;
  6   BEGIN
  7     FOR I IN (SELECT * FROM TEST_OBJECTS) LOOP
  8       V_COUNT := V_COUNT + 1;
  9       L_TAB(V_COUNT).OWNER := I.OWNER;
 10       L_TAB(V_COUNT).OBJECT_NAME := I.OBJECT_NAME;
 11       L_TAB(V_COUNT).OBJECT_ID := I.OBJECT_ID;
 12     END LOOP;
 13     COMMIT;
 14 
 15     --CREATE TABLE TEST_OBJECT_1 AS SELECT OWNER,OBJECT_NAME,OBJECT_ID FROM DBA_OBJECTS;
 16     -- CREATE TABLE TEST_OBJECT_TTX AS SELECT OWNER,OBJECT_NAME,OBJECT_ID FROM DBA_OBJECTS WHERE 1=2;
 17     FORALL I IN L_TAB.FIRST .. L_TAB.LAST
 18       INSERT /*+ APPEND */ INTO TEST_OBJECTS_INSERT T VALUES(L_TAB(I).OWNER,L_TAB(I).OBJECT_NAME,L_TAB(I).OBJECT_ID);
 19     COMMIT;
 20 
 21   END;
 22  /
 
PL/SQL procedure successfully completed
 
Executed in 3.813 seconds
 
SQL>
SQL>    SELECT A.NAME, B.VALUE
  2     FROM V$MYSTAT B, V$STATNAME A
  3    WHERE A.STATISTIC# = B.STATISTIC#
  4      AND A.NAME LIKE '%redo size%';
 
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                          36994652
redo size for lost write detection                                        0
redo size for direct writes                                               0
 
Executed in 0.047 seconds
 
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as noap
 
SQL>
SQL>  DECLARE
  2 
  3     TYPE T_FORALL_TEST_TAB IS TABLE OF TEST_OBJECTS%ROWTYPE INDEX BY BINARY_INTEGER;
  4     L_TAB   T_FORALL_TEST_TAB;
  5     V_COUNT INTEGER := 0;
  6   BEGIN
  7     FOR I IN (SELECT * FROM TEST_OBJECTS) LOOP
  8       V_COUNT := V_COUNT + 1;
  9       L_TAB(V_COUNT).OWNER := I.OWNER;
 10       L_TAB(V_COUNT).OBJECT_NAME := I.OBJECT_NAME;
 11       L_TAB(V_COUNT).OBJECT_ID := I.OBJECT_ID;
 12     END LOOP;
 13     COMMIT;
 14 
 15     --CREATE TABLE TEST_OBJECT_1 AS SELECT OWNER,OBJECT_NAME,OBJECT_ID FROM DBA_OBJECTS;
 16     -- CREATE TABLE TEST_OBJECT_TTX AS SELECT OWNER,OBJECT_NAME,OBJECT_ID FROM DBA_OBJECTS WHERE 1=2;
 17     FORALL I IN L_TAB.FIRST .. L_TAB.LAST
 18       INSERT
 19       INTO TEST_OBJECTS_INSERT T
 20       VALUES
 21         (L_TAB(I).OWNER, L_TAB(I).OBJECT_NAME, L_TAB(I).OBJECT_ID);
 22     COMMIT;
 23 
 24   END;
 25  /
 
PL/SQL procedure successfully completed
 Executed in 3.75 seconds
SQL>
SQL>    SELECT A.NAME, B.VALUE
  2     FROM V$MYSTAT B, V$STATNAME A
  3    WHERE A.STATISTIC# = B.STATISTIC#
  4      AND A.NAME LIKE '%redo size%';
 
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                          36982448
redo size for lost write detection                                        0
redo size for direct writes                                               0
 
5

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as noap
 
SQL> alter TABLE TEST_OBJECTS_INSERT NOLOGGING;
SQL>  DECLARE
  2 
  3     TYPE T_FORALL_TEST_TAB IS TABLE OF TEST_OBJECTS%ROWTYPE INDEX BY BINARY_INTEGER;
  4     L_TAB   T_FORALL_TEST_TAB;
  5     V_COUNT INTEGER := 0;
  6   BEGIN
  7     FOR I IN (SELECT * FROM TEST_OBJECTS) LOOP
  8       V_COUNT := V_COUNT + 1;
  9       L_TAB(V_COUNT).OWNER := I.OWNER;
 10       L_TAB(V_COUNT).OBJECT_NAME := I.OBJECT_NAME;
 11       L_TAB(V_COUNT).OBJECT_ID := I.OBJECT_ID;
 12     END LOOP;
 13     COMMIT;
 14 
 15     --CREATE TABLE TEST_OBJECT_1 AS SELECT OWNER,OBJECT_NAME,OBJECT_ID FROM DBA_OBJECTS;
 16     -- CREATE TABLE TEST_OBJECT_TTX AS SELECT OWNER,OBJECT_NAME,OBJECT_ID FROM DBA_OBJECTS WHERE 1=2;
 17     FORALL I IN L_TAB.FIRST .. L_TAB.LAST
 18       INSERT
 19       INTO TEST_OBJECTS_INSERT T
 20       VALUES
 21         (L_TAB(I).OWNER, L_TAB(I).OBJECT_NAME, L_TAB(I).OBJECT_ID);
 22     COMMIT;
 23 
 24   END;
 25  /
 
PL/SQL procedure successfully completed

Executed in 3.875 seconds
 
SQL> select * from redo_size_v;
 
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
redo size                                                          36976400
redo size for lost write detection                                        0
redo size for direct writes                                               0
 
SQL>

insert into values ：

time: 42.438   redo_size:214693328

insert/*+ append_values(x)*/ into table_name x values(collect) :

time: 4.39    redo_size:132656

insert/*+ append */ into table_name  values(collect) :

time: 3.813    redo_size:36994652

insert into table_name  values(collect) :

time: 3.75    redo_size:36982448

noarchive mode + table is nologging + insert into table_name value(collect)

time: 3.875   redo_size:36976400

可见日志量明显减少
 

Rank92
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
append_values.py
05-29
append_values
python-django中的APPEND_SLASH实现方法
01-01
关于django中的APPEND_SLASH APPEND_SLASH 它是啥？ 看变量名大概能知道做什么，就是添加斜线，用路由系统那里。 路由文件,只写了路由关系代码 ...... urlpatterns = [ url(r'^test/$', views.test), ] ...... ...
oracle APPEND_VALUES
西索的领域
07-11 1129
--创建测试表 create table t_test ( id number(10) not null, code varchar2(10), description varchar2(50) ); --创建测试过程 Create Or Replace Procedure sp_test As Type t_table Is Table Of
Oracle11gr2新增APPEND_VALUES提示
weixin_34238633的博客
09-30 339
在11.2中，Oracle新增了APPEND_VALUES提示，使得INSERT INTO VALUES语句也可以使用直接路径插入。 例子很简单： SQL> SELECT * FROM V$VERSION; BANNER---------------------------------------------...
oracle新增只,Oracle11gr2新增APPEND_VALUES提示
weixin_30995967的博客
04-04 138
在11.2中，Oracle新增了APPEND_VALUES提示，使得INSERT INTO VALUES语句也可以使用直接路径插入。例子很简单：SQL> SELECT * FROM V$VERSION;BANNER--------------------------------------------------------------------------------Oracle Dat...
PHP中auto_prepend_file与auto_append_file用法实例分析
10-25
主要介绍了PHP中auto_prepend_file与auto_append_file用法,较为详细的讲述了配置信息的休息以及函数的用法和注意事项,需要的朋友可以参考下
new_append_passwd.py
06-03
通过此部分小程序可实现通过代码往excel表格中记录一些个人重要的内容。只需按照提示输入内容即可，不用再人工找到表格，打开，写入，保存，关闭等。
11gr2，新增hint APPEND_VALUES
cotchte0421的博客
12-28 120
sys@SMART>insert /*+ APPEND_VALUES(a) */ into test a values(1,'ss'); 1 row created. sys@SMART>@lock lock...
海量数据插入性能测试
weixin_33971977的博客
06-29 97
11.2.0.2的RAC系统中原本有一张大的分区表，之前为了测试exchange分区的性能需要将这张分区表上的部分分区数据复制到测试用表上，因为数据量比较大所以记以录之: 磁盘不太给力 hdparm -tT /dev/sdd /dev/sdd: Timing cached reads: 13672 MB in 2.00 seconds = 6840.55 MB/s...
ABAP一次读取多条长文本记录
weixin_41886784的博客
01-11 658
CALL FUNCTION 'READ_MULTIPLE_TEXTS' EXPORTING client = sy-mandt * NAME = '*' * OBJECT = '*' * ID = '*' * LANGUAGE = '*' ...
如何快速往表里插入大量的数据行
shengshiqing的专栏
03-06 4074
数据转储过程中往往涉及到数据的插入和删除。在数据量特别大的时候往往会消耗较长时间。 不卖关子，直接介绍一个我目前知道最有效的插入例子。 declare type fid is table of dpcrm.t_cust_returnvisit.fid%type; fids fid; begin      select fid bulk collect into fids from dp
Python-Numpy多维数组--数组操作
Odyssues_lee的博客
12-26 4033
1.修改形状 序号 形状及描述 1. reshape 不改变数据的条件下修改形状 2. flat 数组上的一维迭代器 3. flatten 返回折叠为一维的数组副本 4. ravel 返回连续的展开数组 (1)numpy.reshape这个函数在不改变数据的条件下修改形状，它接受如下参数： numpy.reshape(arr, newshap...
SAP:关键字VALUE的用法
weixin_43734184的博客
03-10 1880
定义一个范围的时候可以使用VALUE去定义： REPORT zsy_zm_test03. DATA:lr_auart TYPE RANGE OF vbak-auart. lr_auart = VALUE #( sign = 'I' option = 'EQ' ( low = 'Z001' ) ( low = 'Z002' ) ( l
def remove_duplicate_columns(df): df2=df.copy() for i in range(df.shape[1]): for n in range(i+1,df.shape[1]): if df[df.columns[i]].equals([df.columns[n]])==False: continue if df[df.columns[i]].equals([df.columns[n]])==True: del df2[df.columns[i]] return df2 what is wrong with my codes in order to Write a function named remove_duplicate_columns to find the duplicate columns (all values are the same) of a given Pandas DataFrame (all column names are different) and remove the duplicated columns (keep the one that first appears in the DataFrame). The input for the function is a Pandas Dataframe and the output should be a new DataFrame with the duplicate columns removed. For example, if df = pd.DataFrame({'col1': [1, 2, 3], 'col2': [5, 8, 6], 'col3': [7, 9, 8], 'col4': [5, 8, 6]}) then remove_duplicate_columns(df) should return pd.DataFrame({'col1': [1, 2, 3], 'col2': [5, 8, 6], 'col3': [7, 9, 8]), because 'col4' has the same elements as 'col2'. Note that the input should not be modified. (hint: dataframe1.equals(dataframe2) can be used to test whether two dataframes (dataframe1 and dataframe2) contain the same elements).
最新发布
05-24
There are a couple of issues with your code: 1. In the line `if df[df.columns[i]].equals([df.columns[n]])==False:`, you are comparing the values of two columns, but you are wrapping one of them in a list. You should remove the square brackets around `df.columns[n]`. 2. You are deleting columns from `df2` inside the loop, which can cause problems with the indexing. It's better to create a list of columns to delete and then delete them outside the loop. Here is a corrected version of your code: ``` def remove_duplicate_columns(df): df2 = df.copy() columns_to_delete = [] for i in range(df.shape[1]): for n in range(i+1,df.shape[1]): if df[df.columns[i]].equals(df[df.columns[n]]) == True: columns_to_delete.append(df.columns[n]) df2 = df2.drop(columns_to_delete, axis=1) return df2 ``` This function should work correctly for the given problem statement.

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值