The APPEND_VALUES hint was introduced in 11.2 to allow direct path inserts with variables using the VALUES clause. i.e.
INSERT INTO XYZ (COL1, COL2) VALUES (:A, :B);
The feature was designed to allow bulk inserting via arrays of 100’s or 1000’s of records in a single insert statement. Prior to 11.2, there was no documented way to do an direct path insert other than with the APPEND hint which only worked on inserts that used the SELECT clause. i.e.
INSERT INTO XYZ SELECT * from ZYX;
There was however an undocumented hint (SYS_DL_CURSOR) which did a bulk insert of sorts. (not a full append) You may have seen this hint if you use Informatica. I was recently asked about the use of Informatica with Hybrid Columnar Compression (HCC) on Exadata. Which prompted a little research on these two methods of loading data and whether they were compatible with HCC or not. So first off, here’s a test with the APPEND_VALUES clause (using my check_row_comp.sql script):
KSO@dbm1> !cat bulk_insert1.sql
CREATE TABLE t1
(c1 NUMBER,
c2 NUMBER,
c3 VARCHAR2(50),
c4 VARCHAR2(50),
c5 DATE,
c6 DATE)
compress for query high;
DECLARE
-- Define a collection
TYPE t1_tbl_type IS TABLE OF t1%ROWTYPE;
t1_tbl t1_tbl_type := t1_tbl_type();
BEGIN
-- Populate the collection
FOR i IN 1..32000 LOOP
t1_tbl.EXTEND;
t1_tbl(i).c1 := i;
t1_tbl(i).c2 := i*i;
t1_tbl(i).c3 := 'i=' || TO_CHAR(i);
t1_tbl(i).c4 := 'i*i=' || TO_CHAR(i*i);
t1_tbl(i).c5 := SYSDATE;
t1_tbl(i).c6 := SYSDATE;
END LOOP;
-- Bulk Insert the collection into table T1
FORALL i IN 1..t1_tbl.COUNT
INSERT /*+ append_values */ INTO t1
(c1,
c2,
c3,
c4,
c5,
c6)
VALUES
(t1_tbl(i).c1,
t1_tbl(i).c2,
t1_tbl(i).c3,
t1_tbl(i).c4,
t1_tbl(i).c5,
t1_tbl(i).c6);
END;
/
KSO@dbm1> @bulk_insert1
Table created.
PL/SQL procedure successfully completed.
KSO@dbm1> select count(*) from t1;
select count(*) from t1
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel
KSO@dbm1> -- correct behavior for direct path insert
KSO@dbm1> commit;
Commit complete.
KSO@dbm1> select rowid from t1 where rownum < 10;
ROWID
------------------
AAARlqAAIAAB/krAAA
AAARlqAAIAAB/krAAB
AAARlqAAIAAB/krAAC
AAARlqAAIAAB/krAAD
AAARlqAAIAAB/krAAE
AAARlqAAIAAB/krAAF
AAARlqAAIAAB/krAAG
AAARlqAAIAAB/krAAH
AAARlqAAIAAB/krAAI
9 rows selected.
KSO@dbm1> @check_row_comp.sql
Enter value for owner: KSO
Enter value for table_namr: T1
Enter value for rowid: AAARlqAAIAAB/krAAH
OLD_ROWID COMPRESSION_TYPE
-------------------- -------------------------
8.522539.7 HCC Query High
So the APPEND_VALUES hint behaves as expected, does a proper append and applies HCC. There are a couple of corner cases worth mentioning. First, small inserts (a single row for example) will not trigger the HCC processing. See the following example.
KSO@dbm1> insert /*+ append_values */ into t1 (c1) values (-1);
1 row created.
KSO@dbm1> select count(*) from t1 where c1 = -1;
select count(*) from t1 where c1 = -1
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel
KSO@dbm1> commit;
Commit complete.
KSO@dbm1> select rowid from t1 where c1 = -1;
ROWID
------------------
AAARlqAAIAAEfDLAAA
KSO@dbm1> @check_row_comp.sql
Enter value for owner: KSO
Enter value for table_namr: T1
Enter value for rowid: AAARlqAAIAAEfDLAAA
OLD_ROWID COMPRESSION_TYPE
-------------------- -------------------------
8.1175755.0 No Compression
So a single row insert was done in direct path mode, but HCC was not applied due to the small size of the insert.
The second corner case is that objects owned by SYS (and stored in the SYSTEM tablespace) appear to be ineligible for HCC processing. See the example below which shows the same test but when T1 is owned by SYS. It also shows an alter table move does not compress the rows when the object is owned by SYS and stored in SYSTEM. Note: there could be something else that is preventing the compression but objects in SYSTEM that are not owned by SYS and objects that are owned by SYS but not stored in SYSTEM both seem to behave correctly. As Tanel says, “the rabbit hole always gets deeper”. (I’ll leave those tests as an exercise for the reader rather than clutter up this post) Here’s the simple case of creating a table in the SYSTEM tablespace that is owned by SYS.
SYS@dbm1> @bulk_insert1
Table created.
PL/SQL procedure successfully completed.
SYS@dbm1> select count(*) from t1;
select count(*) from t1
*
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel
SYS@dbm1> commit;
Commit complete.
SYS@dbm1> select rowid from t1 where rownum < 10;
ROWID
------------------
AAARltAABAAAUnRAAA
AAARltAABAAAUnRAAB
AAARltAABAAAUnRAAC
AAARltAABAAAUnRAAD
AAARltAABAAAUnRAAE
AAARltAABAAAUnRAAF
AAARltAABAAAUnRAAG
AAARltAABAAAUnRAAH
AAARltAABAAAUnRAAI
9 rows selected.
SYS@dbm1> @check_row_comp.sql
Enter value for owner: SYS
Enter value for table_namr: T1
Enter value for rowid: AAARltAABAAAUnRAAG
OLD_ROWID COMPRESSION_TYPE
-------------------- -------------------------
1.84433.6 No Compression
SYS@dbm1> @table_size
Enter value for owner: SYS
Enter value for table_name: T1
Enter value for type:
Enter value for tablespace_name:
OWNER SEGMENT_NAME TYPE TOTALSIZE_MEGS TABLESPACE_NAME
-------------------- ------------------------------ ------------------ -------------- ------------------------------
SYS T1 TABLE 2.0 SYSTEM
--------------
sum 2.0
SYS@dbm1> alter table sys.t1 move compress for archive high;
Table altered.
SYS@dbm1> @table_size
Enter value for owner: SYS
Enter value for table_name: T1
Enter value for type:
Enter value for tablespace_name:
OWNER SEGMENT_NAME TYPE TOTALSIZE_MEGS TABLESPACE_NAME
-------------------- ------------------------------ ------------------ -------------- ------------------------------
SYS T1 TABLE 2.0 SYSTEM
--------------
sum 2.0
SYS@dbm1> select rowid from t1 where rownum < 10;
ROWID
------------------
AAARluAABAAAXNRAAA
AAARluAABAAAXNRAAB
AAARluAABAAAXNRAAC
AAARluAABAAAXNRAAD
AAARluAABAAAXNRAAE
AAARluAABAAAXNRAAF
AAARluAABAAAXNRAAG
AAARluAABAAAXNRAAH
AAARluAABAAAXNRAAI
9 rows selected.
SYS@dbm1> @check_row_comp.sql
Enter value for owner: SYS
Enter value for table_namr: T1
Enter value for rowid: AAARluAABAAAXNRAAG
OLD_ROWID COMPRESSION_TYPE
-------------------- -------------------------
1.95057.6 No Compression
So again, in this case, it is clearly a direct path insert, but HCC is not applied.
So what about the older SYS_DL_CURSOR hint. Well the short story is it doesn’t do a real direct path load, so it doesn’t work with HCC. Here’s a quick demonstration (back in a regular user account).
KSO@dbm1> !cat bulk_insert2.sql
CREATE TABLE t1
(c1 NUMBER,
c2 NUMBER,
c3 VARCHAR2(50),
c4 VARCHAR2(50),
c5 DATE,
c6 DATE)
compress for query high;
DECLARE
-- Define a collection
TYPE t1_tbl_type IS TABLE OF t1%ROWTYPE;
t1_tbl t1_tbl_type := t1_tbl_type();
BEGIN
-- Populate the collection
FOR i IN 1..32000 LOOP
t1_tbl.EXTEND;
t1_tbl(i).c1 := i;
t1_tbl(i).c2 := i*i;
t1_tbl(i).c3 := 'i=' || TO_CHAR(i);
t1_tbl(i).c4 := 'i*i=' || TO_CHAR(i*i);
t1_tbl(i).c5 := SYSDATE;
t1_tbl(i).c6 := SYSDATE;
END LOOP;
-- Bulk Insert the collection into table T1
FORALL i IN 1..t1_tbl.COUNT
INSERT /*+ sys_dl_cursor */ INTO t1
(c1,
c2,
c3,
c4,
c5,
c6)
VALUES
(t1_tbl(i).c1,
t1_tbl(i).c2,
t1_tbl(i).c3,
t1_tbl(i).c4,
t1_tbl(i).c5,
t1_tbl(i).c6);
END;
/
KSO@dbm1> drop table t1;
Table dropped.
KSO@dbm1> @bulk_insert2
Table created.
PL/SQL procedure successfully completed.
KSO@dbm1> select count(*) from t1;
COUNT(*)
----------
32000
KSO@dbm1> -- Not a good sign
KSO@dbm1> -- direct path should not allow a select without a commit or rollback
KSO@dbm1> -- let's check anyway
KSO@dbm1> select rowid from t1 where rownum < 10;
ROWID
------------------
AAARlzAAIAAB/krAAA
AAARlzAAIAAB/krAAB
AAARlzAAIAAB/krAAC
AAARlzAAIAAB/krAAD
AAARlzAAIAAB/krAAE
AAARlzAAIAAB/krAAF
AAARlzAAIAAB/krAAG
AAARlzAAIAAB/krAAH
AAARlzAAIAAB/krAAI
9 rows selected.
KSO@dbm1> @check_row_comp.sql
Enter value for owner: KSO
Enter value for table_namr: T1
Enter value for rowid: AAARlzAAIAAB/krAAG
OLD_ROWID COMPRESSION_TYPE
-------------------- -------------------------
8.522539.6 No Compression
So no love with SYS_DL_CURSOR. Of course you can still do the more normal, load, alter table move, exchange partition type processing. As always, please let me know if you any questions or comments.
APPEND showed up in version 8.1, SYS_DL_CURSOR showed up in 9.1 and APPEND_VALUES didn’t show up until 11.2 (probably because of Exadata I’m guessing). SYS_DL_CURSOR was designed to allow “direct path like” inserts using a values clause – but it doesn’t behave exactly like a direct path insert (you’ll notice from the post that it does not require a commit before doing any further access to the object). The older APPEND hint does not support a values clause on the insert so you have to do an insert as select to use it. So the advantage of the SYS_DL_CURSOR when it was introduced (and until 11.2) was that you could do array inserts using the values clause. It has been used by Informatica for a long time when the user chooses the bulk loading option. The APPEND_VALUES hint actually does a true direct path insert (above the high water mark) and also correctly handles HCC processing on Exadata. So to my way of thinking, SYS_DL_CURSOR provides no benefit if you are running 11.2 or higher, but may be of use if you are on an older version.
This hint is used by the OCI Direct-Path loading API (http://docs.oracle.com/cd/E11882_01/appdev.112/e10646/oci13obn.htm#LNOCI120). This API is used by different ETL and other tools, including SQL Loader, to do direct path loading. My experience with this was with SAP’s Data Services ETL tool – and I believe Informatica uses the same API.
When using this API, the processing actually bypasses the SQL engine to do the direct path load. When running loads with the API V$SQL shows the use of the hint and actually uses NULLs a placeholders for the values. However, this is all just giving a representation of what is happening for the purposes of monitoring in V$SQL – not what really is happening, since the SQL engine is not used at all. The data does get loaded in direct path – and HCC compression does work – if going through the API. We validated this in our environments with using the bulk load option with Data Services ETL, and the data was HCC compressed. But I don’t believe the hint, as your tests show above, is an alternative for the APPEND or APPEND_VALUES hints; and if using it in SQL directly it won’t work and doesn’t direct path load at all. I think this is why the hint is undocumented.
So, I think the important thing here is to not get mixed up between using the direct path load API to do direct path loading, which shows the SYS_DL_CURSOR hint in V$SQL, and the use of the SYS_DL_CURSOR hint in a SQL statement. The API will still direct path load and compress with HCC, if enabled on the table. I haven’t done any testing with Informatica on this specifically, but I believe Informatica uses the same API for its bulk load processing. If it does, then its bulk loading should also work with HCC.
FAQ: Is it possible to use APPEND_VALUES in Bulk mode when loading tables on Oracle 11gR2 database in Informatica PowerCenter?
May 19, 2022•Knowledge 000085142
in PowerCenter it is not possible to use APPEND_VALUES in a bulk mode when loading tables on Oracle 11gR2 database. This is a known limitation and an FR 292417 has been submitted to include this feature in a future release. Furthermore the known limitation is still present in Informatica 10.1.1 (latest version at the moment) – A new (fresh) bug FR CORE-5606 was opened for the 10.1.1.
Additional Information
Oracle has changed hints for direct path and the one used by Informatica is ignored (SYS_DL_CURSOR) and instead of that hint, on versions 11.2 and above preferred hint is now APPEND_VALUES.
The hint that is added is based on the direct path load API of OCI library. In PowerCenter, for Oracle native connection, Oracle APIs are used. For Bulk mode, Oracle provides a specific set of calls (OCI) which are used in Powercenter.
But as of versions above 11.2 this hint’s deprecated .
credit: http://kerryosborne.oracle-guy.com/2013/11/10/append_values-and-sys_dl_cursor-hints-with-hcc/