First - how can you optimize your PL/SQL's performance:
•Disable indexes and any other constraints on target tables before you begin your load and re-enable them after you are done
•Don't commit at the very end - have commit points to free-up rollback segments
Second - don't do the insert with PL/SQL. Use BulkLoading. you can easily find lots of info on BulkLoading if you Google for "oracle sql loader"
===========================================
For this can be Use Direct Load Insert
——————————————————-
Direct Load Insert is a faster way of running an INSERT statement.Direct Load Insert
differs from Conventional Insert in that it bypasses the buffer cache.
To use Direct Load Insert, add the APPEND hint to your INSERT statement. like below…..
INSERT /*+ APPEND*/
INTO target_table
SELECT * FROM source_table
Need to know for Direct load Insert
—————————————————
1. Data is appended to the table. Existing free space is not re-used.
2. Direct Load Insert only works for INSERT INTO .. SELECT …. Inserts
3. Direct Load Insert uses rollback segments to maintain indexes as the data is loaded
4. Direct Load Insert can be run with a NOLOGGING option making it even faster.
5. Direct Load Insert locks the table in exclusive mode. No other session can insert, update, or delete data, or maintain any indexes.
6. Referential Integrity (Foreign Key) constraints and triggers must be disabled before running Direct Path Insert
7. Direct Load Insert cannot occur on:
o Index Organised Tables
o Tables with LOB Columns
o Tables with Object Columns
o Clustered Tables
DEMO
=====
SQL*Plus: Release 10.2.0.1.0 – Production on Sat Jul 31 12:28:13 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn basel2@test107
Enter password:
Connected.
SQL>
SQL>
SQL>
SQL> set timing on
SQL> set time on
12:28:41 SQL>
12:28:42 SQL>
12:28:42 SQL> create table direct_load_insert as select * from TABLE_OF_VIEW_BACKUP
12:30:57 2 where brancd=0 ;
Table created.
Elapsed: 00:00:04.03
12:31:28 SQL>
12:31:31 SQL> desc direct_load_insert
Name Null? Type
—————————————– ——– —————————-
BRANCD VARCHAR2(3)
ACTYPE VARCHAR2(3)
ACTNUM VARCHAR2(12)
CURBAL NUMBER
LONCON VARCHAR2(3)
ACTTIT VARCHAR2(60)
CUSCOD VARCHAR2(10)
SHDESC VARCHAR2(10)
OPNDAT DATE
EXPDAT DATE
SECURITY_BAL NUMBER
SECURITY_TYPE VARCHAR2(4000)
VALDAT DATE
CATGRY VARCHAR2(1)
VALPRD NUMBER(4)
LCAAMT NUMBER(16,3)
REMAMT NUMBER(16,3)
BANCOD VARCHAR2(3)
CMPIND VARCHAR2(1)
REMARK VARCHAR2(50)
12:31:48 SQL>
12:31:50 SQL>
12:31:51 SQL> INSERT /*+ APPEND*/
12:32:19 2 INTO direct_load_insert
12:32:44 3 SELECT * FROM TABLE_OF_VIEW_BACKUP ;
3706656 rows created.
Elapsed: 00:01:13.31
12:34:17 SQL>
12:34:23 SQL>
12:34:23 SQL>
12:34:23 SQL> ROLLBACK ;
Rollback complete.
Elapsed: 00:00:00.06
12:34:53 SQL>
12:34:54 SQL>
12:34:54 SQL> INSERT INTO direct_load_insert
12:35:31 2 SELECT * FROM TABLE_OF_VIEW_BACKUP ;
3706656 rows created.
Elapsed: 00:01:53.36
12:37:30 SQL>
12:38:06 SQL>
12:38:06 SQL> ROLLBACK
12:39:44 2 /
Rollback complete.
Elapsed: 00:01:25.35
12:41:12 SQL>
12:41:14 SQL>
12:41:14 SQL> DROP TABLE direct_load_insert ;
Table dropped.
Elapsed: 00:00:28.17
12:41:57 SQL>
12:41:59 SQL>
12:41:59 SQL>
分享到:
相关推荐
The fourth edition of Data Structures and Algorithm Analysis in C++ describes data structures, methods of organizing large amounts of data, and algorithm analysis, the estimation of the running time ...
C++实现数据结构最新版The fourth edition ofData Structures and Algorithm Analysis in C++describesdata structures, methods of organizing large amounts of data, andalgorithm analysis,the estimation of ...
The fourth edition of Data Structures and Algorithm Analysis in C++ describes data structures, methods of organizing large amounts of data, and algorithm analysis, the estimation of the running time ...
scale so that they can learn how to handle large data clusters in data intensive environments to build powerful machine learning models. The contents of the books have been written in a bottom-up ...
**Description:** This feature allows database administrators (DBAs) to query the history of data changes within a table, providing the ability to see past versions of rows or even entire tables....
In this tutorial, we'll cover some of the basics of Unicode-encoded text and Unicode files, and how to view and manipulate it in UltraEdit. Search and delete lines found UEStudio and UltraEdit provide...
For example, in this text students look at specific problems and see how careful implementations can reduce the time constraint for large amounts of data from 16 years to less than a second....
For example, in this text students look at specific problems and see how careful implementations can reduce the time constraint for large amounts of data from 16 years to less than a second....
Using large-capacity flash memory as a storage device enables the system to store a large amount of data and ensures data retention even during power outages. To effectively control interference, ...
Table of ContentsBuilding Stacks for Application State ManagementCreating Queues for In-Order ExecutionsUsing Sets and Maps for Faster ApplicationsUsing Trees for Faster Lookup and ...
With its ease of development (in comparison to the relative complexity of Hadoop), it’s unsurprising that it’s becoming popular with data analysts and engineers everywhere. Beginning with the ...
Another way to say this is that the data itself is part of the clustered index. A clustered index keeps the data in a table ordered around the key. The data pages in the table are kept in a doubly ...
Now, for the first time, there's a comprehensive, authoritative guide to making the most of DTrace in any supported UNIX environment--from Oracle Solaris to OpenSolaris, Mac OS X, and FreeBSD. ...
Spark is capable of handling large-scale batch and streaming data to figure out when to cache data in memory and processing them up to 100 times faster than Hadoop-based MapReduce.This means ...
The variant features in Pentaho for MongoDB are designed to empower organizations to be more agile and scalable and also enables applications to have better flexibility, faster performance, and lower...
After reading this book, you will know how to boost the performance of any Web application and make it part of what has come to be known as the Faster Web. What you will learn Install, confgure, and...
This library allows easier and faster development of clients for Database Applications. Depending on relations between your DataSets on the client side it automatically creates forms to Edit/View data...
In this comprehensive guide to Ruby optimization, you’ll learn how to write faster Ruby code–but that’s just the beginning. See exactly what makes Ruby and Rails code slow, and how to fix it. Alex...
Your queries will generate correct results, be more readable and less brittle in the face of unexpected data, and you’ll be able to adapt them quickly in the face of changing business requirements. ...