SQL*Loader Part #1

SQL*Loader loads data from external files into tables of an Oracle database. It has a powerful data parsing engine that puts little limitation on the format of the data in the datafile. You can use SQL*Loader to do the following:

1. Load data across a network. This means that you can run the SQL*Loader client on a different system from the one that is running the SQL*Loader server.
2. Load data from multiple datafiles during the same load session.
3. Load data into multiple tables during the same load session.
4. Specify the character set of the data.
5. Selectively load data (you can load records based on the records' values).
6. Manipulate the data before loading it, using SQL functions.
7. Generate unique sequential key values in specified columns.
8. Use the operating system's file system to access the datafiles.
9. Load data from disk, tape, or named pipe.
10. Generate sophisticated error reports, which greatly aid troubleshooting.
11. Load arbitrarily complex object-relational data.
12. Use secondary datafiles for loading LOBs and collections.
13. Use either conventional or direct path loading. While conventional path loading is very flexible, direct path loading provides superior loading performance.
    

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 Parameters

SQL*Loader is invoked when you specify the sqlldr command and, optionally, parameters that establish session characteristics. In situations where you always use the same parameters for which the values seldom change, it can be more efficient to specify parameters using the following methods, rather than on the command line:

1. Parameters can be grouped together in a parameter file. You could then specify the name of the parameter file on the command line using the PARFILE parameter.
2. Certain parameters can also be specified within the SQL*Loader control file by using the OPTIONS clause.
   

Parameters specified on the command line override any parameter values specified in a parameter file or OPTIONS clause.


SQL*Loader Control File

The control file is a text file written in a language that SQL*Loader understands. The control file tells SQL*Loader where to find the data, how to parse and interpret the data, where to insert the data, and more. Although not precisely defined, a control file can be said to have three sections. The first section contains session-wide information, for example:

1. Global options such as bindsize, rows, records to skip, and so on.
2. INFILE clauses to specify where the input data is located.
3. Data to be loaded.
   

The second section consists of one or more INTO TABLE blocks. Each of these blocks contains information about the table into which the data is to be loaded, such as the table name and the columns of the table. The third section is optional and, if present, contains input data. Some control file syntax considerations to keep in mind are:

1. The syntax is free-format (statements can extend over multiple lines).
2. It is case insensitive; however, strings enclosed in single or double quotation marks are taken literally, including case.
3. In control file syntax, comments extend from the two hyphens (--) that mark the beginning of the comment to the end of the line. The optional third section of the control file is interpreted as data rather than as control file syntax; consequently, comments in this section are not supported.
4. The keywords CONSTANT and ZONE have special meaning to SQL*Loader and are therefore reserved. To avoid potential conflicts, Oracle recommends that you do not use either CONSTANT or ZONE as a name for any tables or columns.

Data Fields

Once a logical record is formed, field setting on the logical record is done. Field setting is a process in which SQL*Loader uses control-file field specifications to determine which parts of logical record data correspond to which control-file fields. It is possible for two or more field specifications to claim the same data. Also, it is possible for a logical record to contain data that is not claimed by any control-file field specification. Most control-file field specifications claim a particular part of the logical record. This mapping takes the following forms:

1. The byte position of the data field's beginning, end, or both, can be specified. This specification form is not the most flexible, but it provides high field-setting performance. The strings delimiting (enclosing and/or terminating) a particular data field can be specified. A delimited data field is assumed to start where the last data field ended, unless the byte position of the start of the data field is specified.
2. The byte offset and/or the length of the data field can be specified. This way each field starts a specified number of bytes from where the last one ended and continues for a specified length.
3. Length-value datatypes can be used. In this case, the first n number of bytes of the data field contain information about how long the rest of the data field is.
   

Data Conversion and Datatype Specification

During a conventional path load, data fields in the datafile are converted into columns in the database (direct path loads are conceptually similar, but the implementation is different). There are two conversion steps:

1. SQL*Loader uses the field specifications in the control file to interpret the format of the datafile, parse the input data, and populate the bind arrays that correspond to a SQL INSERT statement using that data.
2. The Oracle database accepts the data and executes the INSERT statement to store the data in the database.
   

The Oracle database uses the datatype of the column to convert the data into its final, stored form. Keep in mind the distinction between a field in a datafile and a column in the database. Remember also that the field datatypes defined in a SQL*Loader control file are not the same as the column datatypes.


Discarded and Rejected Records

Records read from the input file might not be inserted into the database. Such records are placed in either a bad file or a discard file.

The Bad File

The bad file contains records that were rejected, either by SQL*Loader or by the Oracle database. If you do not specify a bad file and there are rejected records, then SQL*Loader automatically creates one. It will have the same name as the data file, with a.bad extension. Some of the possible reasons for rejection are discussed in the next sections.

SQL*Loader Rejects

Datafile records are rejected by SQL*Loader when the input format is invalid. For example, if the second enclosure delimiter is missing, or if a delimited field exceeds its maximum length, SQL*Loader rejects the record. Rejected records are placed in the bad file.

Oracle Database Rejects

After a datafile record is accepted for processing by SQL*Loader, it is sent to the Oracle database for insertion into a table as a row. If the Oracle database determines that the row is valid, then the row is inserted into the table. If the row is determined to be invalid, then the record is rejected and SQL*Loader puts it in the bad file. The row may be invalid, for example, because a key is not unique, because a required field is null, or because the field contains invalid data for the Oracle datatype.

Please go SQL*Loader Part #2 for more details


Related Topic:

1. SQL*Loader Part #1
2. SQL*Loader Part #2

SQL*Loader Part #2

The Discard File

As SQL*Loader executes, it may create a file called the discard file. This file is created only when it is needed, and only if you have specified that a discard file should be enabled. The discard file contains records that were filtered out of the load because they did not match any record-selection criteria specified in the control file. The discard file therefore contains records that were not inserted into any table in the database. You can specify the maximum number of such records that the discard file can accept. Data written to any database table is not written to the discard file.

Log File and Logging Information

When SQL*Loader begins execution, it creates a log file. If it cannot create a log file, execution terminates. The log file contains a detailed summary of the load, including a description of any errors that occurred during the load.

Conventional Path Loads

During conventional path loads, the input records are parsed according to the field specifications, and each data field is copied to its corresponding bind array. When the bind array is full (or no more data is left to read), an array insert is executed. SQL*Loader stores LOB fields after a bind array insert is done. Thus, if there are any errors in processing the LOB field (for example, the LOBFILE could not be found), the LOB field is left empty. Note also that because LOB data is loaded after the array insert has been performed, BEFORE and AFTER row triggers may not work as expected for LOB columns. This is because the triggers fire before SQL*Loader has a chance to load the LOB contents into the column. For instance, suppose you are loading a LOB column, C1, with data and that you want a BEFORE row trigger to examine the contents of this LOB column and derive a value to be loaded for some other column, C2, based on its examination. This is not possible because the LOB contents will not have been loaded at the time the trigger fires.

Direct Path Loads

A direct path load parses the input records according to the field specifications, converts the input field data to the column datatype, and builds a column array. The column array is passed to a block formatter, which creates data blocks in Oracle database block format. The newly formatted database blocks are written directly to the database, bypassing much of the data processing that normally takes place. Direct path load is much faster than conventional path load, but entails several restrictions.

A parallel direct path load allows multiple direct path load sessions to concurrently load the same data segments (allows intrasegment parallelism). Parallel direct path is more restrictive than direct path.


Invoking SQL*Loader

When you invoke SQL*Loader, you can specify certain parameters to establish session characteristics. Parameters can be entered in any order, optionally separated by commas. You specify values for parameters, or in some cases, you can accept the default without entering a value. For example:

SQLLDR CONTROL=sample.ctl, LOG=sample.log, BAD=baz.bad, DATA=etc.dat
USERID=scott/tiger, ERRORS=999, LOAD=2000, DISCARD=toss.dsc, DISCARDMAX=5

If you invoke SQL*Loader without specifying any parameters, SQL*Loader displays a help screen similar to the following. It lists the available parameters and their default values.


Alternative Ways to Specify Parameters

If the length of the command line exceeds the size of the maximum command line on your system, you can put certain command-line parameters in the control file by using the OPTIONS clause. You can also group parameters together in a parameter file. You specify the name of this file on the command line using the PARFILE parameter when you invoke SQL*Loader.

Examples:

1. One can load data into an Oracle database by using the sqlldr utility. Invoke the utility as follows:

sqlldr username@server/password control=loader.ctl
sqlldr username/password@server control=loader.ctl

This sample control file (loader.ctl) will load an external data file containing delimited data:
-------------------------------------------------------------------------------------------------------------------------------------
load data
infile 'c:\data\mydata.csv'
into table emp
fields terminated by "," optionally enclosed by '"'
( empno, empname, sal, deptno )
-----------------------------------------------------------------------------------------------------------------------------------
The mydata.csv file may look like this:

10001,"Scott Tiger", 1000, 40
10002,"Frank Naude", 500, 20
-----------------------------------------------------------------------------------------------------------------------------------

2. Another Sample control file with in-line data formatted as fix length records. The trick is to specify "*" as the name of the data file, and use BEGINDATA to start the data section in the control file:
--------------------------------------------------------------------------------------------------------------------------------------
load data
infile *
replace
into table departments
( dept position (02:05) char(4),
deptname position (08:27) char(20)
)
begindata
COSC COMPUTER SCIENCE
ENGL ENGLISH LITERATURE
MATH MATHEMATICS
POLY POLITICAL SCIENCE
-------------------------------------------------------------------------------------------------------------------------------------

3. Data can be modified as it loads into the Oracle Database. One can also populate columns with static or derived values. However, this only applies for the conventional load path (and not for direct path loads). Here are some examples:
--------------------------------------------------------------------------------------------------------------------------------
LOAD DATA
INFILE *
INTO TABLE modified_data
( rec_no "my_db_sequence.nextval",
region CONSTANT '31',
time_loaded "to_char(SYSDATE, 'HH24:MI')",
data1 POSITION(1:5) ":data1/100",
data2 POSITION(6:15) "upper(:data2)",
data3 POSITION(16:22)"to_date(:data3, 'YYMMDD')"
)
BEGINDATA
11111AAAAAAAAAA991201
22222BBBBBBBBBB990112
--------------------------------------------------------------------------------------------------------------------------------------
LOAD DATA
INFILE 'mail_orders.txt'
BADFILE 'bad_orders.txt'
APPEND
INTO TABLE mailing_list
FIELDS TERMINATED BY ","
( addr,
city,
state,
zipcode,
mailing_addr "decode(:mailing_addr, null, :addr, :mailing_addr)",
mailing_city "decode(:mailing_city, null, :city, :mailing_city)",
mailing_state
)
------------------------------------------------------------------------------------------------------------------------------------

For more examples please go here :

Related Topic:

1. SQL*Loader Part #1
2. SQL*Loader Part #2
   

Resizing Temporary Tablespace

In many database configurations, the DBA will choose to allow their temporary tablespace (actually the tempfile(s) for the temporary tablespace) to autoextend. A runaway query or sort can easily chew up valuable space on the disk as the tempfiles(s) extends to accommodate the request for space. If the increase in size of the temporary tablespace (the tempfiles) gets exceedingly large because of a particular anomaly, the DBA will often want to resize the temporary tablespace to a more reasonable size in order to reclaim that extra space. The obvious action would be to resize the tempfiles using the following statement:

SQL> alter database tempfile '/u02/oradata/TESTDB/temp01.dbf' resize 250M;
alter database tempfile '/u02/oradata/TESTDB/temp01.dbf' resize 250M
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value

Ouch. You next bounce the database and attempt the same statement only to be greeted with the same error!

Several methods exist to reclaim the used space used for a larger than normal temporary tablespace depending on which release of Oracle you are running. The method that exists for all releases of Oracle is to simply drop and recreate the temporary tablespace back to its original (or another reasonable) size. If you are using Oracle9i or higher, you can apply another method which is to drop the large tempfile (which will drop the tempfile from the data dictionary AND the O/S file system) using the alter database tempfile '' drop including datafiles; command. Each method is explained below.

Dropping / Recreating Temporary Tablespace Method

Keep in mind that the procedures documented here for dropping and recreating your temporary tablespace should be performed during off hours with no users logged on performing work.

If you are working with a temporary tablespace in Oracle8i or a temporary tablespace in Oracle9i that is NOT the default temporary tablespace for the database, this process is straight forward. Simply drop and recreate the temporary tablespace:

SQL> DROP TABLESPACE temp;

Tablespace dropped.

SQL> CREATE TEMPORARY TABLESPACE TEMP
TEMPFILE '/u02/oradata/TESTDB/temp01.dbf' SIZE 500M REUSE
AUTOEXTEND ON NEXT 100M MAXSIZE unlimited EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

Tablespace created.

Oracle9i OR Above Default Temporary Tablespace

The procedures above document how to drop a temporary tablespace that is not the default temporary tablespace for the database. You will know fairly quickly if the tablespace is a default temporary tablespace when you are greeted with the following exception:

SQL> DROP TABLESPACE temp;
drop tablespace temp
*
ERROR at line 1:
ORA-12906: cannot drop default temporary tablespace

In cases where the temporary tablespace you want to resize (using the drop/recreate method) is the default temporary tablespace for the database, you have several more steps to perform, all documented below. The first step you need to perform is create another temporary tablespace (lets call it TEMP2). Next step is making TEMP2 the default temporary tablespace for the database. Drop / recreate the TEMP tablespace to the size you want. Finally, make the newly created TEMP tablespace your default temporary tablespace for the database and drop the TEMP2 tablespace. A full example session is provided below:

SQL> CREATE TEMPORARY TABLESPACE temp2
TEMPFILE '/u02/oradata/TESTDB/temp2_01.dbf' SIZE 5M REUSE
AUTOEXTEND ON NEXT 1M MAXSIZE unlimited
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

Tablespace created.


SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

Database altered.


SQL> DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

SQL> CREATE TEMPORARY TABLESPACE temp
TEMPFILE '/u02/oradata/TESTDB/temp01.dbf' SIZE 500M REUSE
AUTOEXTEND ON NEXT 100M MAXSIZE unlimited
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

Tablespace created.


SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

Database altered.


SQL> DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.


Related Topic :

1. Move Datafiles
   

Parse To Execute Ratio

All Oracle SQL statements must be parsed at the first time that they execute. Parsing involves a syntax check, a semantic check (against the dictionary), the creation of a decision tree, and the generation of the lowest cost execution plan. Once the execution plan is created, it is stored in the library cache (part of the shared pool) to facilitate re-execution. There are two types of parses:

Hard parse

A new SQL statement must be parsed from scratch. If the database is parsing every statement that is executing, the parse to execute ratio will be close to 1% (high hard parses), often indicating non-reentrant SQL that does not use host variables (Bind Variables).

Soft parse

A reentrant SQL statement where the only unique feature are host variables. The best-case scenario is a parse to execute ratio of 100% which would indicate an application with fully reentrant SQL that parses SQL once and executes many times.




In a real database, some SQL statements will be fully reentrant (execute to parse = 100%), while others must be re-parsed for every execution (execute to parse = 1%). You can see this is the instance efficiency of any STATSPACK and AWR report.


High parses suggests that your system has many incoming unique SQL statements, or that your SQL is not reentrant (i.e. literal values in the WHERE clause, not using bind variables). A hard parse is expensive because each incoming SQL statement must be re-loaded into the shared pool; with the associated overhead involved in shared pool RAM allocation and memory management. Once loaded, the SQL must then be completely re-checked for syntax & semantics and an executable generated. Excessive hard parsing can occur when your shared_pool_size is too small (and reentrant SQL is paged out), or when you have non-reusable SQL statements without host variables.



If the execute to parse ratio is too low, it is possible that the application is not using shareable SQL, or the database has sub-optimal parameters that are reducing the effectiveness of cursor sharing. A problem like excessive parsing is likely to manifest itself as additional network traffic between the application server and clients. The additional parse activity may also show up as a marked increase in CPU consumption on the database server.

Here is a simple sql query which describe the parse call and execution of current sql queries :

Select x.sql_text , x.parse_calls , x.executions
,round( 100*(1-( x.parse_calls / x.executions )),2) execute_to_parse_ratio
FROM v$sql x
WHERE x.parse_calls >0
AND x.executions !=0
AND x.parsing_schema_name='EMP'
ORDER BY execute_to_parse_ratio ;

SELECT x.executions ,
  x.parse_calls ,
  ROUND( 100*(1-(x.parse_calls/x.executions)),2) execute_to_parse_ratio
  , x.sql_text
FROM
  (SELECT DBMS_LOB.SUBSTR (sq.sql_text,500,1) sql_text ,
    SUM(st.executions_delta) executions ,
    SUM(st.parse_calls_delta) parse_calls
  FROM DBA_HIST_SQLSTAT st ,
    DBA_HIST_SQLTEXT sq ,
    DBA_HIST_SNAPSHOT s
  WHERE s.snap_id           = st.snap_id
  AND s.begin_interval_time > sysdate-14
  AND s.end_interval_time   < sysdate
  AND st.sql_id             = sq.sql_id
  AND st.parsing_schema_name='EMP'
  GROUP BY DBMS_LOB.SUBSTR (sq.sql_text,500,1)
  ) x
WHERE x.executions != 0
AND ROUND( 100*(1-(x.parse_calls/x.executions)),2) < 10
ORDER BY execute_to_parse_ratio ;

Related Topics :

1. Bind Variable

Top N SQL Query Run within 30 minuts

When the database load increase rapidly it is even hard to log in Enterprise Manager (EM) to see what is the actual cause by seeing top activity or generating AWR report. Most of the time bad SQl is behind such problem. Here is a script which give you Top 20 (sort by elapsed time) SQL quer run on last 30 minutes

SELECT x.sql_id,x.sql_text,x.users,x.executions,x.disk_reads
,x.buffer_gets,x.cpu_time,x.elapsed_time FROM
(select sql_id,sql_text,PARSING_SCHEMA_NAME users
,executions, disk_reads,buffer_gets
,DECODE (executions,0,0,round(((cpu_time/1000000)/executions),2)) cpu_time
,DECODE (executions,0,0,round(((elapsed_time/1000000)/executions),2)) elapsed_time
from v$sqlarea
where parsing_user_id !=3D
and
LAST_ACTIVE_TIME<(sysdate-60/2600)
)x WHERE rownum <=20
ORDER BY x.elapsed_time DESC;

Parent Table locket while inserting in child table !

The parent table lock with LMODE=3 when an insert command is issued on its child table. An index is also present on the foreign key , but still a table level lock apply on the parent table. Because there is a constraint that requires that the parent exist before the child can be inserted, Oracle locks the parent table to keep the parent record from being deleted while the child record is being inserted. This is a well known issue on Oracle.

Analytical SQL functions

Oracle has introduced some exciting extensions to ANSI SQL to allow us to quickly compute aggregations and rollups. These new statements include:

1. rollup
2. cube
   

These simple SQL operators allow us to create easy aggregations directly inside the SQL without having to employ SQL*Plus break and compute statements. Let’s start by examining the ROLLUP syntax.

Tabular aggregates with ROLLUP

ROLLUP enables an SQL statement to calculate multiple levels of subtotals across a specified group of dimensions. It also calculates a grand total. ROLLUP is a simple extension to the GROUP BY clause, so its syntax is extremely easy to use.

SELECT deptno, job,count(*), sum(sal) FROM emp GROUP BY ROLLUP(deptno,job);


DEPTNO JOB COUNT(*) SUM(SAL)
--------- --------- --------- ---------
10 CLERK 1 1300
10 MANAGER 1 2450
10 PRESIDENT 1 5000
10 3 8750
20 ANALYST 2 6000
20 CLERK 2 1900
20 MANAGER 1 2975
20 5 10875

cross-tabular reports with CUBE

In multidimensional jargon, a “cube” is a cross-tabulated summary of detail rows. CUBE enables a SELECT statement to calculate subtotals for all possible combinations of a group of dimensions. It also calculates a grand total. This is the set of information typically needed for all cross-tabular reports, so CUBE can calculate a cross-tabular report with a single select statement.

Note in the example below that totals are calculated for each department, and also for each job category.

SELECT deptno,job,count(*),sum(sal) FROM emp GROUP BY CUBE(deptno,job);

DEPTNO JOB COUNT(*) SUM(SAL)
--------- --------- --------- ---------
10 CLERK 1 1300
10 MANAGER 1 2450
10 PRESIDENT 1 5000
10 3 8750
20 ANALYST 2 6000
20 CLERK 2 1900
20 MANAGER 1 2975
20 5 10875
30 CLERK 1 950
30 MANAGER 1 2850
30 SALESMAN 4 5600
30 6 9400
ANALYST 2 6000
CLERK 4 4150
MANAGER 3 8275
PRESIDENT 1 5000
SALESMAN 4 5600
14 29025

Table Compression

Now a days Most systems usually involve large amounts of data stored in a few very large tables. As these systems evolve, the demand on disk space will grow quickly. In today's environment, data warehouses of hundreds of terabytes have become increasingly common. To manage disk capacity, the table compression feature introduced in Oracle9i Release 2 can significantly reduce the amount of disk space used by database tables and improve query performance in some cases.

Working Mechanism

The Oracle9i Release 2 table compression feature works by eliminating duplicate data values found in database tables. Compression works at the database block level. When a table is defined as compressed, the database reserves space in each database block to store single copies of data that appear in multiple places within that block. This reserved space is called the symbol table. Data tagged for compression is stored only in the symbol table and not in the database rows themselves. As data tagged for compression appears in a database row, the row stores a pointer to the relevant data in the symbol table, instead of the data itself. The space savings come from eliminating redundant copies of data values in the table. The effects of table compression are transparent to a user or an application developer. Developers access a table the same way regardless of whether a table is compressed or not, so SQL queries don't have to change once you decide to compress a table. Table compression settings are usually configured and managed by database administrators or architects, with little involvement from developers or users.

Create a Compressed Table

To create a compressed table, use the COMPRESS keyword in the CREATE TABLE statement. The COMPRESS keyword directs Oracle Database to store rows in the table in compressed format wherever possible. The following is an example of the CREATE TABLE COMPRESS statement:

CREATE TABLE EMP (
ID NUMBER NOT NULL,
NAME VARCHAR2(50) NOT NULL,
SAL NUMBER NOT NULL,
JOIN_DATE DATE NOT NULL,
ADDRESS VARCHAR2(100)
) COMPRESS;

Alternatively, you can use the ALTER TABLE statement to change the compression attribute of an existing table, as in the following:

ALTER TABLE EMP COMPRESS;

To determine whether a table has been defined using COMPRESS, query the USER_TABLES data dictionary view and look at the COMPRESSION column, as in the example below:

SELECT TABLE_NAME, COMPRESSION FROM USER_TABLES WHERE TABLE_NAME='EMP';

TABLE_NAME COMPRESSION
------------------ -----------
EMP ENABLED


The COMPRESS attribute can also be defined at the tablespace level, either at the time of creation (by using CREATE TABLESPACE) or later (by using ALTER TABLESPACE). The COMPRESS attribute has inheritance properties similar to those of storage parameters. When a table is created in a tablespace, it inherits the COMPRESS attribute from the tablespace. To determine whether a tablespace is defined using COMPRESS, query the DBA_ TABLESPACES data dictionary view and look at the DEF_TAB_COMPRESSION column, as in the following example:

SELECT TABLESPACE_NAME,DEF_TAB_COMPRESSION FROM DBA_TABLESPACES;

TABLESPACE_NAME DEF_TAB_COMPRESSION
--------------- -------------------
DATA_TS_01 DISABLED
INDEX_TS_01 DISABLED

As you might expect, you can still explicitly compress or uncompress a table in a tablespace, regardless of the COMPRESS value at the tablespace level.

Loading Data into a Compressed Table

Note that when you specify COMPRESS as shown above, you aren't actually compressing any data. The commands above only modify a data dictionary setting. Data isn't actually compressed until you load or insert data into a table. Furthermore, to ensure that data is actually compressed, you need to use a proper method to load or insert data into the table. Data compression takes place only during a bulk load or bulk insert process, using one of the following four methods:

1. Direct path SQL*Loader
2. Serial INSERT with an APPEND hint
3. Parallel INSERT
4. CREATE TABLE ... AS SELECT


Note: If you don't use the correct loading or INSERT method, the data in the table will remain uncompressed, even if the table is defined using COMPRESS. For example, if you use conventional path SQL*Loader or regular INSERT statements, data will not be compressed.

When to Use Table Compression

The way that Oracle Database chooses to compress or not compress table data has implications for the kind of applications best suited for table compression. As described above, data in a table defined using COMPRESS gets compressed only if it is loaded using direct path mode or inserted using append or parallel mode. Data inserted through regular insert statements will remain uncompressed. In online transaction processing (OLTP) systems, data is usually inserted using regular inserts. As a result, these tables generally do not get much benefit from using table compression. Table compression works best on read-only tables that are loaded once but read many times. Furthermore, updating data in a compressed table may require rows to be uncompressed, which defeats the purpose of compression. As a result, tables that require frequent update operations are not suitable candidates for table compression.

Finally, consider the effects of row deletion on the use of table compression. When you delete a row in a compressed table, the database frees up the space occupied by the row in the database block. This free space can be reused by any future insert. However, since a row inserted in conventional mode isn't compressed, it is unlikely that it would fit in the space freed up by a compressed row. High volumes of successive DELETE and INSERT statements may cause fragmentation and waste even more space than would be saved using compression.


Compressing an Existing Uncompressed Table

ALTER TABLE EMP MOVE COMPRESS;

You can also use the ALTER TABLE ... MOVE statement to uncompress a table,

ALTER TABLE EMP MOVE NOCOMPRESS;

Note that the ALTER TABLE ... MOVE operation acquires an EXCLUSIVE lock on the table, which prevents any DML operation on the table while the statement executes. You can avoid this potential problem by using the online table redefinition feature of Oracle9i Database.


Compressing a Partitioned Table

ALTER TABLE EMP MOVE PARTITION EMP3_03 COMPRESS;

Benefits

The biggest reason to use table compression is to save storage space. A table in compressed form will usually occupy less space when compared to its uncompressed form. A compressed table can be stored in fewer blocks results in storage savings, but fewer blocks can mean performance improvements as well. Queries on a compressed table in an I/O bound environment will often complete more quickly, because they need to read fewer database blocks

Use Regular Expressions in SQL

A feature introduced in Oracle Database 10g vastly improves your ability to search and manipulate character data. This feature, regular expressions, is a notation for describing textual patterns. It has long been available in many programming languages and a number of UNIX utilities and now available in Oracle. Oracle's implementation of regular expressions comes in the form of various SQL functions and a WHERE clause operator.

What Is a Regular Expression?

A regular expression comprises one or more character literals and/or metacharacters. In its simplest format, a regular expression can consist only of character literals, such as the regular expression cat. It is read as the letter c followed by the letters a and t and this pattern matches strings such as cat. When you understand the meaning of the various metacharacters, you will see that regular expressions are powerful for isolating and replacing specific textual data. You can apply them in order to validate the formats of phone numbers, zip codes, email addresses, Social Security numbers, IP addresses, filenames and pathnames, and so on. Furthermore, you can locate patterns such as HTML tags, numbers, dates, or anything that fits any pattern within any textual data and replace them with other patterns.

To learn Regular Expression you can go here

Using Regular Expressions With Oracle Database 10g

To utilize the power of regular expressions, you can exploit the newly introduced Oracle SQL REGEXP_LIKE operator and the REGEXP_INSTR, REGEXP_SUBSTR, and REGEXP_REPLACE functions. You will see how this new functionality supplements the existing LIKE operator and the INSTR, SUBSTR, and REPLACE functions. In fact, they are similar to the existing operator and functions but now offer powerful pattern-matching capabilities. The searched data can be simple strings or large volumes of text stored in the database character columns. Regular expressions let you search, replace, and validate data in ways you have never thought of before, with a high degree of flexibility.

The REGEXP_LIKE Operator

The following SQL query's WHERE clause shows the REGEXP_LIKE operator, which searches the ZIP column for a pattern that satisfies the regular expression [^[:digit:]]. It will retrieve those rows in the ZIPCODE table for which the ZIP column values contain any character that is not a numeric digit.

SQL> SELECT zipFROM zipcode WHERE REGEXP_LIKE(zip, '[^[:digit:]]');

ZIP
-----
ab123
123xy
007ab
abcxy

This example of a regular expression consists only of metacharacter more specifically the POSIX character class digit delimited by colons and square brackets. The second set of brackets (as in [^[:digit:]]) encloses a character class list. As previously mentioned, this is required because you can use POSIX character classes only for constructing a character list.

For More Understanding go Here

The REGEXP_INSTR Function

This function returns the starting position of a pattern, so it works much like the familiar INSTR function. The main difference between the two functions is that REGEXP_INSTR lets you specify a pattern instead of a specific search string; thus providing greater versatility. The next example uses REGEXP_INSTR to return the starting position of the five-digit zip-code pattern within the string Joe Smith, 10045 Berry Lane, San Joseph, CA 91234. If the regular expression is written as [[:digit:]]{5}, you will get the house number's starting position instead of the zip code's, because 10045 is the first occurrence of five consecutive digits. Therefore, you must anchor the expression to the end of the line, as indicated with the $ metacharacter, and the function will display the starting position of the zip code regardless of the number of digits for the house number.

SQL> SELECT REGEXP_INSTR('Joe Smith, 10045 Berry Lane, San Joseph, CA 91234','[[:digit:]]{5}$') AS rx_instr FROM dual;

RX_INSTR
----------
45

Writing More Complex Patterns Let's expand on the zip code pattern of the previous example to include an optional four digits. Your pattern may now look like this: [[:digit:]]{5}(-[[:digit:]]{4})?$. If your source string ends in either the 5-digit zip code or the 5-digit + 4 zip-code format, you'll be able to show the pattern's starting position.

SQL> SELECT REGEXP_INSTR('Joe Smith, 10045 Berry Lane, San Joseph, CA 91234-1234', ' [[:digit:]]{5}(-[[:digit:]]{4})?$') AS starts_at FROM dual;

STARTS_AT
----------
44

In this example the parenthesized subexpression (-[[:digit:]]{4}) is repeated zero or one times, as indicated by the ? repetition operator. Again, attempting to use the traditional SQL functions to accomplish the same result poses a challenge even to SQL experts. To better explain the various components of the regular expression please go Here

The REGEXP_SUBSTRFunction

The REGEXP_SUBSTR function, much like the SUBSTR function, extracts part of a string. In the following example, the string that matches the pattern , [^,]*, is returned. The regular expression searches for a comma followed by a space; then zero or more characters that are not commas, as indicated by [^,]*; and lastly looks for another comma. The pattern will look somewhat similar to a comma-separated values string.

SQL> SELECT REGEXP_SUBSTR('first field, second field , third field',', [^,]*,') FROM dual;

REGEXP_SUBSTR('FIR
------------------
, second field ,

The REGEXP_REPLACE Function

Let's first look at the traditional REPLACE SQL function, which substitutes one string with another. Assume your data has extraneous spaces in the text and you would like to replace them with a single space. With the REPLACE function, you need to list exactly how many spaces you want to replace. However, the number of extra spaces may not be the same everywhere in the text. The next example has three spaces between Joe and Smith. The REPLACE function's parameter specifies that two spaces should be replaced with one space. In this case, the result leaves an extra space where there were three spaces in the original string between Joe and Smith.

SQL> SELECT REPLACE('Joe Smith',' ', ' ')AS replace FROM dual;

REPLACE
---------
Joe Smith

The REGEXP_REPLACE function takes the substitution a step further. The following query replaces any two or more spaces with a single space. The ( ) subexpression contains a single space, which can be repeated two or more times, as indicated by {2,}.

SQL> SELECT REGEXP_REPLACE('Joe Smith','( ){2,}', ' ') AS RX_REPLACE FROM dual;

RX_REPLACE
----------
Joe Smith

For More Understanding go Here

Impotent SQL Queries

1. To see current Oracle Version :

SQL> SELECT version FROM v$instance;

VERSION
-----------------
10.2.0.1.0

2.

Virtual Indexes

For tuning SQL statements often requires the testing of alternate indexing strategies to see the affect on execution plans. Adding extra indexes to large tables can take a considerable amount of time and disk space. The additional indexes are available for use by other sessions, which may affect the performance of other parts of your application that you are not currently testing. This can be problematic when you are trying to identify problems on a production system.

In contrast to conventional indexes, a virtual index has no associated segment, so the creation time and associated disk space are irrelevant. In addition, it is not seen by other sessions, so it doesn't affect the normal running of your system. This article presents a simple example of how virtual indexes are used.


First, we create and populate a table

CREATE TABLE objects_tab AS SELECT * FROM user_objects;

Now we create an primary key on that table. So delete some rows where object_id is null.

DELETE from objects_tab where object_id is null;
COMMIT;

ALTER TABLE objects_tab ADD CONSTRAINT objects_tab_pk PRIMARY KEY (object_id);
EXEC DBMS_STATS.gather_table_stats('EMP', 'objects_tab', cascade=>TRUE);

If we query the table using the primary key, we can see this reflected in the execution plan.

SQL> SET AUTOTRACE TRACEONLY ;
SQL> SELECT * FROM objects_tab WHERE object_id = 10;

Execution Plan
----------------------------------------------------------
Plan hash value: 2097082964

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 87 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| OBJECTS_TAB | 1 | 87 | 2 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | OBJECTS_TAB_PK | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------


If we query the table using a non-indexed column, we see a full table scan

SQL> SELECT * FROM objects_tab WHERE object_name = 'LOGIN';


Execution Plan
----------------------------------------------------------
Plan hash value: 821620785

---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 87 | 11 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| OBJECTS_TAB | 1 | 87 | 11 (0)| 00:00:01 |
----------------------------------------------------------------------------

To create the virtual index on this column, simply add the NOSEGMENT clause to the CREATE INDEX statement.

SQL> CREATE INDEX objects_tab_object_name_vi ON objects_tab(object_name) NOSEGMENT;

Index Created

If we repeat the previous query we can see the virtual index is not visible to the optimizer.

SQL> SELECT * FROM objects_tab WHERE object_name = 'LOGIN';


Execution Plan
----------------------------------------------------------
Plan hash value: 821620785

---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 87 | 11 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| OBJECTS_TAB | 1 | 87 | 11 (0)| 00:00:01 |
----------------------------------------------------------------------------

To make the virtual index available we must set the _use_nosegment_indexes parameter.

SQL> ALTER SESSION SET "_use_nosegment_indexes" = TRUE;

Session Altered

If we repeat the query we can see that the virtual index is now used.

SELECT * FROM objects_tab WHERE object_name = 'LOGIN';


Execution Plan
----------------------------------------------------------
Plan hash value: 4006507992

----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 87 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| OBJECTS_TAB | 1 | 87 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | OBJECTS_TAB_OBJECT_NAME_VI | 1 | | 1 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------


The virtual index does not appear in the USER_INDEXES view, but it present in the USER_OBJECTS view.

SQL> SET AUTOTRACE OFF;
SQL> SELECT index_name FROM user_indexes WHERE INDEX_NAME = 'OBJECTS_TAB_OBJECT_NAME_VI';

no rows selected

SQL> SELECT object_name FROM user_objects WHERE object_type = 'INDEX'
AND OBJECT_NAME='OBJECTS_TAB_OBJECT_NAME_VI';

OBJECT_NAME
----------------------------------------------------------------------------------------------------
OBJECTS_TAB_OBJECT_NAME_VI

1 rows selected.

Statistics can be gathered on virtual indexes in the same way as regular indexes, but as we have seen previously, there will be no record of this in the USER_INDEXES view.

SQL> EXEC DBMS_STATS.gather_index_stats('EMP', 'objects_tab_object_name_vi');

PL/SQL procedure successfully completed.

Oracle will prevent us from creating another virtual index with the same column list, but it will allow us to create a real index with the same column list.

SQL> CREATE INDEX objects_tab_object_name_vi2 ON objects_tab(object_name) NOSEGMENT;

CREATE INDEX objects_tab_object_name_vi2 ON objects_tab(object_name) NOSEGMENT
*
ERROR at line 1:
ORA-01408: such column list already indexed


SQL> CREATE INDEX objects_tab_object_name_i ON objects_tab(object_name);

Index created.


Related Links

1. Create INDEX
2. Execution Plan

ALTER TABLE

Specifications of 'ALTER TABLE' are as follows

Adding Foreign key

ALTER TABLE table_name
ADD CONSTRAINT constraint_name FOREIGN KEY (col1, col2)
REFERENCES table_2 (cola,colb);

Adding unique constraint

The following example adds a unique constraint on the column column_name in the table whose name is table_name. The name of the constraint is constraint_name.

ALTER TABLE table_name
add constraint constraint_name
unique (column_name)

Disabling constraints

Integrity constraints can be disabled with the ALTER TABLE command.

ALTER TABLE table-name disable constraint-specification;
ALTER TABLE table-name disable constraint constraint-name;

Adding new Column

ALTER TABLE foo_table add bar_column char(1);
ALTER TABLE foo_table add (bar_column1 char(1),bar_column2 char(1));

Modifying a column

Renaming a column name

ALTER TABLE some_table rename column column_name to new_column_name;

Changing a column's type

A column's type can be changed if the column is empty within the entire table, otherwise Oracle gives an ORA-01440: column to be modified must be empty to decrease precision or scale.

ALTER TABLE some_table modify (column_name number);

Changing null to not null or vice versa

ALTER TABLE some_table modify (column_name not null);

ALTER TABLE some_table modify col_not_null number null;

Specifying tablespace for index

ALTER TABLE tbl add constraint pk_tbl
primary key (col_1, col_2)
using index tablespace ts_idx

Removing a constraint

ALTER TABLE table_name drop constraint constraint_name;

ORA-02082

Message

ORA-02082: a loopback database link must have a connection qualifier

Cause

Attempt to create a database link with the same name as the current database.

Action:

A loopback database link needs a trailing qualifier, that is if your db_link name is MYDB.ORACLE.COM@INST1 - then '@INST1' is the qualifier and 'MYDB.ORACLE.COM' is the current database name

Related Links
Create database link