SQL * Loader

SQL*Loader loads data from external files into tables of an Oracle database

A typical SQL*Loader session takes as input a control file, which controls the behavior of SQL*Loader, and one or more datafiles. The output of SQL*Loader is an Oracle database (where the data is loaded), a log file, a bad file, and potentially a discard file

 SQL*Loader is invoked when you specify the sqlldr command and, optionally, parameters that establish session characteristics.

Customer.txt

101,Raju,9847743994
102,Sreenu,9768443324
103,Pramod,9088765644

Customer.ctl

LOAD DATA
INFILE 'C:\DataUpload\Customers.txt'
append INTO TABLE Customer
FIELDS TERMINATED BY ','  optionally enclosed by ' " '
(Cno ,Cname ,Mobile  )

Execution : sqlldr scott/tiger control=customer.ctl   log=customer.log  parallel=true direct=true 



How to improve the performace of SQL Loader :

• Use Paralle Loads
• Use Direct path Loads instead of Conventional path loader 
• Disable indexes and constraints 
• Use ROWS=n to commit Less frequently 
• Ue Fixed width data, it saves affords when parsing the data 
• Disable archiving during Load 
• Use unrecoverable, it disables the writing of data to th redo logs 

Case Studies given by Oracle 

Schema Object Naming Rules

Names must be from 1 to 30 bytes long with these exceptions:

·         Names of databases are limited to 8 bytes.

·         Names of database links can be as long as 128 bytes.

You should use ASCII characters in database names, global database names, and database link names, because ASCII characters provide optimal compatibility across different platforms and operating systems.

Nonquoted identifiers must begin with an alphabetic character from your database character set. Quoted identifiers can begin with any character.

Nonquoted identifiers can contain only alphanumeric characters from your database character set and the underscore (_), dollar sign ($), and pound sign (#). Database links can also contain periods (.) and "at" signs (@). Oracle strongly discourages you from using $ and # in nonquoted identifiers.
Quoted identifiers can contain any characters and punctuations marks as well as spaces. However, neither quoted nor nonquoted identifiers can contain double quotation marks or the null character (\0).

The following schema objects share one namespace:

·         Tables

·         Views

·         Sequences

·         Private synonyms

·         Stand-alone procedures

·         Stand-alone stored functions

·         Packages

·         Materialized views

·         User-defined types

Each of the following schema objects has its own namespace:

·         Indexes

·         Constraints

·         Clusters

·         Database triggers

·         Private database links

·         Dimensions

Because tables and views are in the same namespace, a table and a view in the same schema cannot have the same name. However, tables and indexes are in different namespaces. Therefore, a table and an index in the same schema can have the same name.

Each schema in the database has its own namespaces for the objects it contains. This means, for example, that two tables in different schemas are in different namespaces and can have the same name.

Each of the following nonschema objects also has its own namespace:

·         User roles

·         Public synonyms

·         Public database links

·         Tablespaces

·         Profiles

·         Parameter files (PFILEs) and server parameter files (SPFILEs)

Database Anomalies

Anomalies are caused by bad database design

Example : ACTIVITY(StudentID,Activity,Fee)

StudentID	Activity	Fee
101	Swimming	1000
101	Skating	1500
102	Bungy Jump	5000
102	Swimming	1000
103	Bungy Jump	5000
104	Swimming	1000

Insertion Anomaly: Occurs when a row cannot be added to a relation, because not all data is available (Or need to insert Dummy data)
Eg: If we want to store the new activity Trekking costs 500, we don't have place to put this information until a student takes up Trekking (Unless we need to create a fake student).

Deletion Anomaly: Occurs when data is deleted from a relation, another critical data is unintentionally deleted.
Eg: If we delete the record with StudentID=100, then we lose the price of Skating

Update Anomaly: Occurs when one must make many changes to reflect the modification of a single data
Eg: If the Cost of Swimming changes, then all entries with Swimming activity must be changed

Causes of Anomalies:

Anomalies are primarily caused by :

Data Redundancy :Replication of same fields in multiple tables other than Foreign keys
Functional Dependencies : Whose determinants are not Candidate keys including Partial Dependency , Transitive Dependency

NVL and NVL2 functions

NVL

Syntax: NVL(expr1,expr2)

NVL lets you replace null (returned as a blank) with a string in the results of a query. If expr1 is null, then NVL returns expr2. If expr1 is not null, then NVLreturns expr1.

The arguments expr1 and expr2 can have any data type. If their data types are different, then Oracle Database implicitly converts one to the other. If they cannot be converted implicitly, then the database returns an error. The implicit conversion is implemented as follows:

• If expr1 is character data, then Oracle Database converts expr2 to the data type of expr1 before comparing them and returns VARCHAR2 in the character set of expr1. 

• If expr1 is numeric, then Oracle Database determines which argument has the highest numeric precedence, implicitly converts the other argument to that data type, and returns that data type.

NVL2

Syntax: NVL2(expr1,expr2,exp3)

NVL2 lets you determine the value returned by a query based on whether a specified expression is null or not null. If expr1 is not null, then NVL2 returnsexpr2. If expr1 is null, then NVL2 returns expr3.

The argument expr1 can have any data type. The arguments expr2 and expr3 can have any data types except LONG.

If the data types of expr2 and expr3 are different, then Oracle Database implicitly converts one to the other. If they cannot be converted implicitly, then the database returns an error. If expr2 is character or numeric data, then the implicit conversion is implemented as follows:

• If expr2 is character data, then Oracle Database converts expr3 to the data type of expr2 before returning a value unless expr3 is a null constant. In that case, a data type conversion is not necessary, and the database returns VARCHAR2 in the character set of expr2. 

• If expr2 is numeric data, then Oracle Database determines which argument has the highest numeric precedence, implicitly converts the other argument to that data type, and returns that data type.