Loading & organising data

1. Loading & organising data

2. Objectives • Loading data using direct-load insert • Loading data into oracle tables using SQL*Loader conventional and direct paths • Reorganising data using export and import

3. Overview Other applications Oracle Database SQL* Loader Export Import Oracle database Direct loader

4. Using direct-Load inserts Insert /*+append */into emmajane.emp NOLOGGING Select * from scott.emp • Note: you do not use ‘insert into values’ only ‘insert into select’ • Can be used on non-partitioned and partitioned tables • Maintains indexes and enforces constraints • Allows concurrent modification of rows while insert occurring.

5. Parallel direct-load insert Alter session enable parallel DML; • Must be executed at beginning of transaction INSERT /*+parallel(emmajane.emp2) */ INTO emmajane.emp NOLOGGING SELECT * FROM scott.emp;

6. SQL*Loader • One or more input files can be used • Several input records can be combined into one logical record for loading • Input fields can be fixed or variable length • Input data can be in any format – char, binary etc • Data can be loaded from different types of media such as disk, tape or named pipes • Data can be loaded into several tables in one run • Options are available to replace or append to existing data in tables • SQL functions can be applied on input data before row is stored in DB

7. SQL*loader files • Control file • Data files • Parameter file • Bad file • Log file

8. SQL* loader methods • Conventional • Direct path

9. Direct vs. Conventional load

10. Parallel direct loads Temporary segments Load1.dat SQL*loader Load2.dat SQL*loader Load3.dat SQL*loader HW

11. Using SQL*loader SQLLDR USERID=user/password@db_name, CONTROL='sales.ctl',DATA='sales.dat', LOG='sales.log',ROWS=100000 Sales.ctl Sales.dat SQL*loader Sales table Sales.log

12. SQL*loader input files • Parameter files • Load options • Control file • Data files • Input records LOAD DATA APPEND INTO TABLE sales FIELDS TERMINATED BY "|" ( PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, PROMO_ID, QUANTITY_SOLD, AMOUNT_SOLD)

13. SQL*loader usage guidelines • Use a parameter file to specify commonly used commands • Place data within the control file only for small one-time loads • Improve performance by • Allocating sufficient space • Sorting data on largest index • Using different files for temporary segments in parallel loads

14. SQL*loader trouble shooting • Insufficient space for table or index • Instance failure during the load • If the SORTED INDEXES clause is used and data is not in the order specified • Duplicate keys found in unique index etc • Errors or discards exceed specified limit.

15. Moving Data using EXP/IMP Export Data files O/S file Import

16. Uses of Import and Export • Re-organise tables • Move data owned by one user to another user • Move data between DBs • Development to production • OLTP system to Data Warehouse • Migrate to a different platform or release of oracle • Repeat test runs during development or upgrade • Perform logical backup.

17. Guidelines for Export and Import • Use a parameter file to specify command line options • Use CONSISTENT=Y only if exporting a small volume of data • Do not use COMPRESS=Y if there are many deleted rows • Improve performance by • Allocating large buffer size • Using direct path if using only 7.3.3 or higher