APPEND_VALUES Hint in Oracle Database 11g Release 2
轉:http://www.oracle-base.com/articles/11g/append-values-hint-11gr2.php
APPEND_VALUES Hint in Oracle Database 11g Release 2
We have been able take advantage of the performance benefits of direct-path inserts in "INSERT ... SELECT" operations for a long time using the APPEND Hint.
INSERT /*+ APPEND */ INTO dest_tab SELECT * FROM source_tab;
The APPEND_VALUES hint in Oracle 11g Release 2 now allows us to take advantage of direct-path inserts when insert statements include a VALUES clause. Typically we would only want to do this when the insert statement is part of bulk operation using the FORALL statement. We will use the following table to demonstrate the effect of the hint.
CREATE TABLE forall_test ( id NUMBER(10), code VARCHAR2(10), description VARCHAR2(50) ); ALTER TABLE forall_test ADD (CONSTRAINT forall_test_pk PRIMARY KEY (id)); ALTER TABLE forall_test ADD (CONSTRAINT forall_test_uk UNIQUE (code));
The following code populates the base table then deletes half of the rows before performing each test. This is because during a regular (conventional-path) insert, Oracle tries to use up any free space currently allocated to the table, including space left from previous delete operations. In contrast direct-path inserts ignore existing free space and append the data to the end of the table. After preparing the base table we time how long it takes to perform. conventional-path insert as part of the FORALL statement. Next, we repeat the same test, but this time use a the APPEND_VALUES hint to give us direct-path inserts.
SET SERVEROUTPUT ON DECLARE TYPE t_forall_test_tab IS TABLE OF forall_test%ROWTYPE; l_tab t_forall_test_tab := t_forall_test_tab(); l_start NUMBER; l_size NUMBER := 1000000; PROCEDURE prepare_table AS BEGIN EXECUTE IMMEDIATE 'TRUNCATE TABLE forall_test'; INSERT /*+ APPEND */ INTO forall_test SELECT level, TO_CHAR(level), 'Description: ' || TO_CHAR(level) FROM dual CONNECT BY level <= l_size; COMMIT; DELETE FROM forall_test WHERE MOD(id, 2) = 0; COMMIT; END prepare_table; BEGIN -- Populate collection. FOR i IN 1 .. (l_size/2) LOOP l_tab.extend; l_tab(l_tab.last).id := i*2; l_tab(l_tab.last).code := TO_CHAR(i*2); l_tab(l_tab.last).description := 'Description: ' || TO_CHAR(i*2); END LOOP; prepare_table; -- ---------------------------------------------------------------- -- Test 1: Time bulk inserts. l_start := DBMS_UTILITY.get_time; FORALL i IN l_tab.first .. l_tab.last INSERT INTO forall_test VALUES l_tab(i); DBMS_OUTPUT.put_line('Bulk Inserts : ' || (DBMS_UTILITY.get_time - l_start)); -- ---------------------------------------------------------------- ROLLBACK; prepare_table; -- ---------------------------------------------------------------- -- Test 2: Time bulk inserts using the APPEND_VALUES hint. l_start := DBMS_UTILITY.get_time; FORALL i IN l_tab.first .. l_tab.last INSERT /*+ APPEND_VALUES */ INTO forall_test VALUES l_tab(i); DBMS_OUTPUT.put_line('Bulk Inserts /*+ APPEND_VALUES */ : ' || (DBMS_UTILITY.get_time - l_start)); -- ---------------------------------------------------------------- ROLLBACK; END; / Bulk Inserts : 394 Bulk Inserts /*+ APPEND_VALUES */ : 267 PL/SQL procedure successfully completed. SQL>
We can see that the APPEND_VALUES hint gives us better performance by allowing us to use direct-path inserts within the FORALL statement. Remember there are factors other than performance to consider before deciding to use direct-path inserts. Make sure you read the About Direct-Path INSERT documentation.
For more information see:
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/195110/viewspace-774815/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Oracle Database 11g Release 2 RAC On LinuxOracleDatabaseLinux
- Oracle® Database Error Messages 11g Release 2 (11.2)OracleDatabaseError
- Using Oracle Database 11g Release 2 Result Cache in an Oracle RAC EnvironmentOracleDatabase
- 1 Oracle Database 11g Release 2 (11.2.0.4) New FeaturesOracleDatabase
- Oracle Database 11g Release 2 RAC On Linux Using VMware Server 2OracleDatabaseLinuxServer
- 11gr2,新增hint APPEND_VALUESAPP
- Partitioning Enhancements in Oracle Database 11g Release 1OracleDatabase
- [原創]append_values hintAPP
- 在Centos 6.5上安裝Oracle Database 11g Release 2 (11.2.0.4.0) RACCentOSOracleDatabase
- Read-Only Tables in Oracle Database 11g Release 1OracleDatabase
- Transparent Data Encryption (TDE) in Oracle 10g Database Release 2Oracle 10gDatabase
- Automatic Diagnostic Repository (ADR) in Oracle Database 11g Release 1 (ADRCI)OracleDatabase
- Database 11g Release 2 Certification Highlights [ID 1065024.1]Database
- SAP has certified Oracle Database 11g Release 2,will be Certified Sun Exadata V2 during Q2 CY 2011OracleDatabase
- Reboot-less node fencing in Oracle Clusterware 11g Release 2bootOracle
- Oracle Database 12c Release 2 (12.2) RAC On Oracle Linux 7 Using VMwareOracleDatabaseLinux
- Identifying Your Oracle Database Software Release (21)IDEOracleDatabase
- Oracle11gr2新增APPEND_VALUES提示OracleAPP
- [轉]Important ASM changes in 11g Release 2ImportASM
- Oracle Database 11g 第2版中的 Oracle Advanced SecurityOracleDatabase
- 1 Oracle Database Release 20c New FeaturesOracleDatabase
- Oracle 11G Duplicate DatabaseOracleDatabase
- Major Database Release Number (22)Database
- oracle 11g release 2 12g 不支援裸裝置的宣告Oracle
- Installing Oracle Database 10g Release 2 on Linux x86OracleDatabaseLinux
- Oracle Database 11g Launch WebcastOracleDatabaseWebAST
- Oracle 11g Release 2 RAC叢集系統——安裝後置任務Oracle
- Oracle9i Database Release 2 Enterprise/Standard Edition for Intel Linux下載地址OracleDatabaseIntelLinux
- [轉]How to release space from databaseDatabase
- Oracle 11g Database Replay 實驗OracleDatabase
- Oracle 11g Rman Active database duplicateOracleDatabase
- Oracle Database 11g索引技術OracleDatabase索引
- Cursor Sharing in Oracle Database 11gOracleDatabase
- [Oracle] oracle 11g database install(linux)OracleDatabaseLinux
- oracle 11g Oracle Database Vault 的配置方法OracleDatabase
- Oracle 19c Concepts(00):Changes in This Release for Oracle Database ConceptsOracleDatabase
- oracle hintOracle
- oracle 12c release 2 安裝Oracle