This example shows how to populate a PL/SQL table from a cursor and then how to display all the values within that PL/SQL table array.

--in sql*plus, assuming you have scott/tiger schema

SET SERVEROUTPUT ON

CREATE OR REPLACE PROCEDURE TESTSP AS

TYPE DEPTARR IS TABLE OF DEPT%ROWTYPE INDEX BY BINARY_INTEGER;
D_ARR DEPTARR;
TYPE D_CUR IS REF CURSOR RETURN DEPT%ROWTYPE;

C1 D_CUR;
I NUMBER := 1;
BEGIN
        -- POPULATE THE PL/SQL TABLE FROM THE CURSOR
        OPEN C1 FOR SELECT * FROM DEPT;
        LOOP
                EXIT WHEN C1%NOTFOUND;
                FETCH C1 INTO D_ARR(I);
                I := I+1;
        END LOOP;
        CLOSE C1;
       
        -- DISPLAY THE ENTIRE PL/SQL TABLE
        FOR I IN 1..D_ARR.LAST
        LOOP
                DBMS_OUTPUT.PUT_LINE('DEPTNO : '||D_ARR(I).DEPTNO);
                DBMS_OUTPUT.PUT_LINE('DNAME : '||D_ARR(I).DNAME);
                DBMS_OUTPUT.PUT_LINE('LOC : '||D_ARR(I).LOC);
                DBMS_OUTPUT.PUT_LINE('');
        END LOOP;
END;
/
EXECUTE TESTSP;

Oracle Tricks - Spell Numbers in Thousands and Million

create or replace
function spell_number( p_number in number )
return varchar2
-- original by Tom Kyte
-- modified to include decimal places
as
    type myArray is table of varchar2(255);
    l_str    myArray := myArray( '',
                           ' thousand ', ' million ',
                           ' billion ', ' trillion ',
                           ' quadrillion ', ' quintillion ',
                           ' sextillion ', ' septillion ',
                           ' octillion ', ' nonillion ',
                           ' decillion ', ' undecillion ',
                           ' duodecillion ' );
    l_num varchar2(50) default trunc( p_number );
    l_return varchar2(4000);
begin
    for i in 1 .. l_str.count
    loop
        exit when l_num is null;
        if ( substr(l_num, length(l_num)-2, 3) <> 0 )
        then
            l_return := to_char(
                            to_date(
                             substr(l_num, length(l_num)-2, 3),
                               'J' ),
                        'Jsp' ) || l_str(i) || l_return;
        end if;
        l_num := substr( l_num, 1, length(l_num)-3 );
    end loop;
   l_return := l_return || ' Dhirams';
    -- beginning of section added to include decimal places:
    if to_char( p_number ) like '%.%'
    then
        l_num := substr( p_number, instr( p_number, '.' )+1 );
        if l_num > 0
        then
            l_return := l_return || ' And ';
          for i in 1 .. length (l_num)
            loop
                exit when l_num is null;
                if substr( l_num, 1, 1 ) = '0'
                then
                    l_return := l_return || ' zero';
                else
                    l_return := l_return
                    || ' '
                    || to_char(
                           to_date(
                           substr( l_num, 1, 1),
                             'j' ),
                       'jsp' );
                end if;
                l_num := substr( l_num, 2 );
            end loop;

--       L_Return := L_Return || ' ' || to_Char(To_Date(L_Num,'J'),'JSP');
        end if;
    end if;
    -- end of section added to include decimal places
    return l_return;
end spell_number;

Oracle Trick - Return Numbers in Words

create function  inwords(n IN NUMBER) return varchar2                                              
is                                                                                                
words varchar2(500);                                                                              
begin                                                                                              
select distinct  SUBSTR(' '||                                                                      
DECODE(MOD(TRUNC(n/100000000,0),10),2,'Twenty ',                                                  
           3,'Thirty ',                                                                        
   4,'Forty ',                                                                                
   5,'Fifty ',                                                                                
   6,'Sixty ',                                                                                
                                    7,'Seventy ',                                                  
                                    8,'Eighty ',                                                  
                                    9,'Ninety ',                                                  
         1,DECODE(MOD(TRUNC(n/10000000,0),10),0,'Ten ',                                    
                                              1,'Eleven ',                            
                                              2,'Twelve ',                            
                                              3,'Thirteen ',                          
                                              4,'Fourteen ',                          
                                              5,'Fifteen ',                            
                                              6,'Sixteen ',                            
                                              7,'Seventeen ',                          
                                              8,'Eighteen ',                          
                                              9,'Nineteen ',''),'')                  
  || DECODE(MOD(TRUNC(n/100000000,0),10),1,'',                                                    
     DECODE(MOD(TRUNC(n/10000000,0),10),1,'One ',                                  
                                        2,'Two ',                              
                                        3,'Three ',                            
                                        4,'Four ',                              
                                        5,'Five ',                              
                                        6,'Six ',                              
                                        7,'Seven ',                            
                                        8,'Eight ',                            
                                        9,'Nine ', ''))
 ||
 DECODE( MOD(TRUNC(n/10000000,0),10),                                              
     0,DECODE(MOD(TRUNC(n/10000000,0),10), 0,'','Crore '),'Crore ')
 ||                          
 DECODE(MOD(TRUNC(n/1000000,0),10),2,'Twenty ',                                                    
                                   3,'Thirty ',                                                
                                   4,'Forty ',                                                  
                                   5,'Fifty ',                                                  
                                   6,'Sixty ',                                                  
                                   7,'Seventy ',                                                
                                   8,'Eighty ',                                                
                                   9,'Ninety ',                                                
     1,DECODE(MOD(TRUNC(n/100000,0),10),1,'Eleven ',                            
                                        2,'Twelve ',                            
                                        3,'Thirteen ',                          
                                        4,'Fourteen ',                          
                                        5,'Fifteen ',                            
                                        6,'Sixteen ',                            
                                        7,'Seventeen ',                          
                                        8,'Eighteen ',                          
                                        9,'Nineteen ',                          
                                        0,'Ten ',''),'')
 ||                      
 DECODE(MOD(TRUNC(n/1000000,0),10),1,'',                                                          
       DECODE(MOD(TRUNC(n/100000,0),10),1,'One ',                                      
                                        2,'Two ',                                  
                                        3,'Three ',                                
                                        4,'Four ',                                  
                                        5,'Five ',                                  
                                        6,'Six ',                                  
                                        7,'Seven ',                                
                                        8,'Eight ',                                
                                        9,'Nine ', ''))
 ||                          
 DECODE(MOD(TRUNC(n/1000000,0),10),                                                                
        0,DECODE(MOD(TRUNC(n/100000,0),10), 0,'','Lakh '),'Lakh ')
 ||                              
 DECODE(MOD(TRUNC(n/10000,0),10),2,'Twenty ',                                                      
                                    3,'Thirty ',                                                  
                                    4,'Forty ',                                                    
                                    5,'Fifty ',                                                    
                                    6,'Sixty ',                                                    
                                    7,'Seventy ',                                                  
                                    8,'Eighty ',                                                  
                                    9,'Ninety ',                                                  
       1,DECODE(MOD(TRUNC(n/1000,0),10),1,'Eleven ',                            
                                        2,'Twelve ',                            
                                        3,'Thirteen ',                          
                                        4,'Fourteen ',                          
                                        5,'Fifteen ',                            
                                        6,'Sixteen ',                            
                                        7,'Seventeen ',                          
                                        8,'Eighteen ',                          
                                        9,'Nineteen ',                          
                                        0,'Ten ',''), '')
 ||                      
 DECODE(MOD(TRUNC(n/10000,0),10),1,'',                                                            
          DECODE(MOD(TRUNC(n/1000,0),10),1,'One ',                                    
                                         2,'Two ',                                  
                                         3,'Three ',                                
                                         4,'Four ',                                
                                         5,'Five ',                                
                                         6,'Six ',                                  
                                         7,'Seven ',                                
                                         8,'Eight ',                                
                                         9,'Nine ', ''))
 ||                          
 DECODE(MOD(TRUNC(n/10000,0),10),0,
  DECODE(MOD(TRUNC(n/1000,0),10), 0,'','thousand '),'thousand ')
 ||                        
 DECODE(MOD(TRUNC(n/100,0),10),1,'One ',                                                          
                               2,'Two ',                                                        
                               3,'Three ',                                                      
                               4,'Four ',                                                      
                               5,'Five ',                                                      
                               6,'Six ',                                                        
                               7,'Seven ',                                                      
                               8,'Eight ',                                                      
                               9,'Nine ','')
 ||                                                  
 DECODE(MOD(TRUNC(n/100,0),10),0,'','Hundred ')
 ||                                                  
 DECODE(MOD(TRUNC(n/10,0),10),2,'Twenty ',                                                        
                              3,'Thirty ',                                                      
                              4,'Forty ',                                                      
                              5,'Fifty ',                                                      
                              6,'Sixty ',                                                      
                              7,'Seventy ',                                                    
                              8,'Eighty ',                                                      
                              9,'Ninety ',                                                      
                              1,DECODE(MOD(TRUNC(n,0),10),                                      
                                                 1,'Eleven ',                                
                                                 2,'Twelve ',                                
                                                 3,'Thirteen ',                              
                                                 4,'Fourteen ',                              
                                                 5,'Fifteen ',                              
                                                 6,'Sixteen ',                              
                                                 7,'Seventeen ',                            
                                                 8,'Eighteen ',                              
                                                 9,'Nineteen ',                              
                                                 0,'Ten ',''),'')
 ||                        
 DECODE(MOD(TRUNC(n/10,0),10),1,'',                                                                
             DECODE(MOD(TRUNC(n,0),10),1,'One ',                                                  
                                          2,'Two ',                                                
                                          3,'Three ',                                              
                                          4,'Four ',                                              
                                          5,'Five ',                                              
                                          6,'Six ',                                                
                                          7,'Seven ',                                              
                                          8,'Eight ',                                              
                                          9,'Nine ', ''))
 ||                                        
 DECODE(MOD(12434567,1),0,'','and ')
 ||                                                            
 DECODE(MOD(TRUNC(n*10,0),10),2,'Twenty ',                                                        
                                 3,'Thirty ',                                                      
                                 4,'Forty ',                                                      
                                 5,'Fifty ',                                                      
                                 6,'Sixty ',                                                      
                                 7,'Seventy ',                                                    
                                 8,'Eighty ',                                                      
                                 9,'Ninety ',                                                      
                                 1,DECODE(MOD(TRUNC(n*100,0),10),                                  
                                               1,'Eleven ',                                        
                                               2,'Twelve ',                                        
                                               3,'Thirteen ',                                      
                                               4,'Fourteen ',                                      
                                               5,'Fifteen ',                                      
                                               6,'Sixteen ',                                      
                                               7,'Seventeen ',                                    
                                               8,'Eighteen ',                                      
                                               9,'Nineteen ',                                      
                                               0,'Ten ',''), '')
 ||                                
 DECODE(MOD(TRUNC(n*10,0),10),1,'',                                                                
 DECODE(MOD(TRUNC(n*100,0),10),1,'One ',                                      
                               2,'Two ',                                  
                               3,'Three ',                                
                               4,'Four ',                                  
                               5,'Five ',                                  
                               6,'Six ',                                  
                               7,'Seven ',                                
                               8,'Eight ',                                
                               9,'Nine ', ''))
 ||                          
 DECODE(MOD(n,1),0,'','  ')||'only ',1,120)                                                        
 INTO words FROM  DUAL;                                                                            
return words;                                                                                      
end;                                                                                              
/

Oracle Global Temporary Table Creation Script

create global temporary table premal
(no number(3)
 name varchar(10))
on commit preserve rows;
or
on commit delete rows;

Oracle SQL Loader With Example

SQLLoader - An overview with examples
Sqlloader is an Oracle supplied utility that can be used to load data into table(s) within an Oracle database from flat files. It is highly efficient, reliable and commonly used.
These pages will demonstrate how it can be used and provide suitable scripts that can easily be adapted.
Several files need to be prepared before successfully using sqlloader. These include a calling command file (optional), a control file, a parfile (optional) and a data file. The table must pre-exist but it may or may not contain existing data.
A simple command line syntax could look like the following

sqlldr control=chf.ctl userid=username/password

The control file would contain the following lines which indicate the name and location of the datafile, whether the table should be empty or not, the target table and the format of the input data.

load data
infile 'nov01.csv' (single quite used here is optional)
into table chf
fields terminated by ',' optionally enclosed by '"'
(
PHONE,
MARKETING_SEGMENT_CODE,
PRE_POST_FLAG,
PL_SEGMENT,
BILLING_ENGINE,
CONNECT_DATE DATE(8) "YYYYMMDD" ,
FIRST_CUST,
SELECT_CUST,
SEGMENT_CODE,
ADVERT_ID1,
ADVERT_ID2,
ADVERT_ID3,
ADVERT_ID4,
ADVERT_ID5,
ADVERT_ID6,
ADVERT_ID7,
ADVERT_ID8
)

I have never found a way of hiding the userid/password from the command line when callling a sqllloader ctl file until fairly recently.
It is possible to use a parfile in sqllloader similar to that used in export/import
The format would be as follows

sqlldr parfile=example.par
The parameter file could have the following contents:
userid=scott/tiger
control=example.ctl
errors=9999
log=example.log

I am sure everybody else but me knew that already but what the hell !
Types of Load
In the example above the default value of INSERT is taken. Options are APPEND, REPLACE, INSERT or TRUNCATE.
The format would be:

load data
infile 'nov01.csv'
APPEND | INSERT | TRUNCATE

APPEND adds the data to the table if whether the table is empty or not
INSERT is the default value. The table must be empty otherwise an error message is returned.
REPLACE - uses an implicit truncate of the table to replace existing data. It does not look at specific rows but rather removes all rows and inserts new ones, even if the new data is the same as the original data.
TRUNCATE - requires all RI constraints on the table to be disabled.
This is the most efficient.
Note that the TRUNCATE command is not the same as the SQL TRUNCATE command as is more inefficient.
Files used ( required and optional)
CONTROL FILE - informs sqlloader what data is to be loaded where and any actions that are necessary.
PARFILE can be used to store the username and password so that it can be handy from a ps -ef| o/s command. Similar to the use of parfile with the export/import utility.

sqlldr parfile=example.par

The example.par could have the following contents:

userid=scott/tiger
control=example.ctl
errors=9999
log=example.log

LOG file - output file show details of records processed, timings and results. Produced automatically if not specified in the parfile, the default name will be the same as the data file but with a extension of log.
BAD file - output file produced to hold records that do not contain valid data. An example would be a delimited file that does not contain a record for a not null column. Produced automatically if not specified in the parfile, the default name will be the same as the data file but with an extension of bad. If no discards are found the file is not created.
DISCARD file - an output file that contains records that do not meet the criteria specified with in the control file. Produced when specified in the parfile, the default name will be the same as the data file but with an extension of dsc. If no discards are found the file is not created.
Direct or Normal load
To enhance performance a direct load can be used. This is specified in the control file.

Top Ten New Features in Oracle9i

 Oracle9i is the latest major version of the Oracle database. This release covers a broad range of functionality, expanding the reach and depth of the features in Oracle8i.

We have updated our Oracle Essentials book to cover the new features in Oracle9i. The following list is, in our judgment, the ten most significant new facets of the Oracle9i database--plus one more feature as a lagniappe.

1.    Real Application Clusters: If you have paid attention to any of the hype surrounding the launch of Oracle9i, you have no doubt heard about Real Application Clusters. In a nutshell, Real Application Clusters are a way to have an Oracle9i database spread across multiple machines in a cluster of servers, with each server extending both the scalability and the availability of the entire cluster.

Real Application Clusters use a technology called cache fusion to make the existence of a cluster transparent to an application. You can run any type of application, from an OLTP (online transaction processing) application to a data warehouse, on a Real Application Clusters database, without modifying your code.

2.    Dynamic Memory Pools: With Oracle9i, you can adjust the size of the memory pools (buffer pool, shared pool, and large pool) without having to stop and restart the server.

3.    Data Guard: This new feature automatically handles standby databases, from creation to maintenance, and failover.

4.    Automatic Undo Management: Rather than having to define and manage rollback segments, you can simply define an Undo tablespace and let Oracle9i take care of the rest.

5.    Flashback Query: This is a nice little feature that allows you to run a query against the database and return results as they would have been produced at an earlier time. Flashback Query uses the same mechanisms as multiversion read consistency, so it's like getting a feature for free.

6.    XMLType: This is a new datatype that lets you store native XML documents directly in the database. XMLType eliminates the need to parse the documents coming into and out of the database.

7.    List Partitioning: This feature offers an additional way to partition data, based on a list of values. If you are using partitions to isolate maintenance operations, List Partitioning could come in handy.

8.    FastStart Recovery: This new feature allows you to simply specify the amount of time you want to spend recovering a database. Oracle9i will use the indicated time to automatically issue checkpoints.

9.    Two-Pass Recovery: One of the behind-the-scenes improvements in Oracle9i is Two-Pass Recovery, which allows the database to be reopened for use as soon as roll-forward recovery is completed, without having to wait for the entire rollback process to finish.

10.                       Zero Data Loss: With Oracle9i, you can now specify Zero Data Loss, which means that all writes to the local log file will also be written to the log file of the standby database. This feature means that the standby database is always in exactly the same state as the active database, so that no data will be lost in the event of a failure.

Bonus Tip

Label Security: This feature gives you the ability to specify security, based on values, which gives you a finer grain of control that is useful if you are creating virtual private databases.

Query To Find Index Name and Index Column Name on Oracle Table

SELECT
SUBSTR(INDEX_NAME,1,30) INDEX_NM,
TABLE_NAME TABLE_NM,
COLUMN_NAME COLUMN_NM,COLUMN_POSITION
FROM  ALL_IND_COLUMNS
WHERE TABLE_NAME =UPPER('&TABLE_NAME')
ORDER BY INDEX_NAME,COLUMN_POSITION

Basic Oracle SQL Tuning

ORACLE SQL TUNING

This document is meant to explain the points to be considered, while
formulating your SQLs. Some of this is meant at the DBA level, but most of
these can be noted, for programmer level.

Before tuning your SQL you should understand the phases of SQL query
processing. There are 5 phases of processing the SQL query :
1) Open
2) Parse
3) Fetch
4) Execute
5) Close
In Open phase, one cursor area (context area) will be opened for your query
In Parse phase, the following processes will take place
i) SQL statement is verified, whether valid or not (Syntax checking).
ii) The tables, columns and privileges are verified (Symantec checking)
iii) A Parse tree is prepared, based on the execution plan
iv) The parse tree into SQL area (reuse to identical query).
In Fetch phase, rows are selected from the corresponding data files and put
into the data block buffer, by server process.
In Execute phase, user process sends the records to the corresponding users.
In Close phase the opened cursor will be closed and temporary memory will be
released.

The following points are to be noted, when you write SQL statements.
# When you write conditions that compare columns with constants, wherever
possible, don't use expressions.
Eg:
Sal>(24000/12) - Not optimized
Sal >2000 - Optimized

# While using LIKE operator, if you are not using wildcards, then don't use
LIKE. Use equality operator instead.
Eg.
desig Like 'DBA' - Not optimized
desig='DBA' - Optimized
desig like 'DB%' - Correct. But if you know the full string, better to use
it.

# When the number of condition in IN operator is less, use OR and =
operator
Eg.
WHERE dno IN (10,20,30) - Not optimized
dno=10 OR dno=20 OR dno=30 - Optimized.

#When you use ANY or ALL or BETWEEN operators, the query optimizer expands
the condition .So, it's better that you expand the condition as far as
possible using OR operator .
Eg.1
Where Sal=ANY(5000,6000) - Not optimized
Where sal=5000 OR sal=6000 - Optimized
Eg2.
Where sal >ALL(5000,6000) - Not optimized
Where sal >5000 AND sal>6000 - Optimized
Eg3.
Where sal BETWEEN 2000 AND 5000 - Not optimized
Where sal >=2000 AND sal<=5000 - Optimized

# Try to avoid using the NOT logical operator as far as possible. Use <> or
!= or ^= relational operators instead.
Eg.
NOT dno = (SELECT dno FROM emp WHERE ename = 'TAYLOR') - Not optimized
deptno <> (SELECT deptno FROM emp WHERE ename = 'TAYLOR') - Optimized

# If your WHERE conditions have more OR operators, then you have to rewrite
the query with SET operators because SET operators execute more efficiently
then OR (Relational Operators).
Eg.
SELECT * FROM emp WHERE job = 'CLERK' OR deptno = 10; - Not optimized

SELECT * FROM emp WHERE job = 'CLERK'
UNION ALL SELECT * FROM emp WHERE deptno = 10 AND job <> 'CLERK';
(Make sure you are having INDEX) - Optimized

# Complex queries have to be changed to join.
Eg.
SELECT * FROM accounts WHERE custno
IN (SELECT custno FROM customers); - Not optimized
SELECT accounts.* FROM accounts, customers
WHERE accounts.custno = customers.custno; - Optimized
(Make sure 'custno' column is the primary key or indexed.)

Optimizer Modes
There are three types of optimizer modes.
1. CHOOSE (Default).
2. COST (Using Resource cost)
3. RULE (Using Access path)

Default optimizer mode is CHOOSE, now Oracle check recourse to set cost
based mode if you are not enabled resource_limit It will set rule based
optimizer mode.

When you set COST based optimizer mode, Oracle considers only CPU resource.
Eg.
Logical reads per call
Logical reads per session
CPU per session
And etc. But you have to set recourse_limit=true in init parameter file to
set cost based optimizer mode.

When you set RULE based optimizer mode, Oracle will never worry about COST
(means resource or I/O cost). It will search the records through the Access
paths. Oracle maintains 15 access paths for each search. Each of these paths
are explained below with the corresponding rank associated with it. Among
these paths, Oracle tries to apply the least rank of the path as possible.
Your queries should apply the least rank of path.


Note:
If you are not able to understand the access path properly, send mail to me.
Why, because unless you are clear with access path, you can't tune SQL
queries those are using RULE based optimizer mode.

ACCESS PATHS (Each number below, is the rank associated with the access
path)

1. Single row returned by ROWID

Eg : SELECT * FROM emp WHERE ROWID = '00000DC5.0000.0001';

2. Single row returned by CLUSTER join
To use this access path, your join tables should be stored in the same
cluster and WHERE clause should be return one only row. To store join tables
in same cluster you have to create cluster for those tables first.

3. Single row returned by hash cluster join, with unique or primary key
To use this access path you have to create a HASH CLUSTER using HASH
function.

4. Single row returned by unique or primary key
To use this access path, the WHERE clause should have all columns in the
unique or primary key with equality condition.

5. Cluster join
To use this access path the join tables should be stored in the same
cluster and WHERE clause should contain conditions that equate each column
of the cluster key in one table with corresponding column in the other
table.

6. Hash cluster key
To use this access path, the WHERE clause should use all columns of hash
cluster key with equality condition.

7. Indexed cluster key
To use this access path, the WHERE clause should use all columns of
indexed cluster key with equality condition.

8. Composite index
To use this access path, the WHERE clause should use all columns of a
composite index in equality conditions combined with AND operators.

9. Single-column index
To use this access path, the WHERE clause should use the columns of one
or more single- column indexes in equality conditions.

10. Bound range search on indexed column
To use this access path, the WHERE clause should contains a condition
that uses either the column of a single-column index or one or more columns
that make up a leading portion of a composite index (using AND)

11. Unbound range search on indexed column.
To use this access path, the WHERE clause should contain one of the
conditions that use either the column of a single-column index or one or
more columns of the leading portion of a composite ndex (Using OR).

12. Sort merge join
To use this access path, the join tables should not be stored together
in a cluster, if the statement's WHERE clause uses columns from each table
in equality conditions.

13. MAX or MIN of indexed column
To use this access path the following conditions must be true.
i) The query uses the MAX or MIN function to select the maximum or minimum
value of either the column of a single-column index or the leading column of
a composite index. The index cannot be a cluster index.
ii) The argument to the MAX or MIN function can be any expression involving
the column, a constant, or the addition operator (+), the concatenation
operation (||), or the CONCAT function.
iii) There are no other expressions in the select list.
iv) The statement has no WHERE clause or GROUP BY clause.

14.ORDER BY on indexed column
To use this access path the following conditions must be true.
i) The query contains an ORDER BY clause that uses either the column of a
single-column index or the leading portion of a composite index. The index
cannot be a cluster index.
ii) There must be a PRIMARY KEY or NOT NULL integrity constraint that
guarantees that at least one of the indexed columns listed in the ORDER BY
clause contains no nulls.
iii) The NLS_SORT parameter is set to BINARY in init parameter file or using
statement ALTER SESSION SET NLS_SORT=BINARY;

15.Full table scan.
This access path is available for any SQL statement, regardless of
its WHERE clause conditions.

When you create a composite index for all the columns which are specified in
WHERE clause, Oracle uses the path composite index (rank 8). This will
increase the performance.
Keep in mind that full table scan is the last rank (15). So, at any point of
time try to avoid the full table scan.

Note:
Even though you are having indexes, your query will never use that index
when you write a query with the following conditions
col1 > col2
col1 < col2
col1 >= col2
col1 <= col2
col1 and col2 are in the same table.
Where Col1 is not null
Where Col1 is null
Where Col1 not in
Where col1!=<expr>
Where col1 like '% pattern'

How to make your JOINS more effective?

When you write a query with two-row source, Oracle performs one of the
following joins. I will take two tables like emp and dept to explain the
joins

1. Nested loops
When you write a query like
select * from emp,dept where emp.dno=dept.dno;
Here EMP is the Driving (outer) table and DEPT is Driven (inner) table.
Oracle performs a full table scan of emp and a rowid scan of DEPT.

2. Sort Merge
Oracle first sorts each row of source to be joined if they have not been
already sorted. Then it performs a full table scan of two tables. But sort
merge joins can perform only for equi-joins.

3. Cluster join
Oracle can perform a cluster join only for an equi-join that equates the
cluster key columns of two tables in the same cluster. Then Oracle performs
a full table scan for dept and cluster scan of emp.

4. Hash join
Here also Oracle performs a full table scan for dept and hash cluster
scan of emp.

Hints
1. When you use OUTER JOIN(+) operator.
The table with the outer join operator must come after the other table in
the condition.
2. When you specify more tables in FROM clause.
The first table in the join order has the most highly ranked access path
(eg. rank 5 table first and rank 7 table second and so on).
3. When you set operators UNION or UNION ALL, as per commitment, both are
same except that UNION uses SORT operation to eliminate the duplicate
records. So, if you are not using any WHERE clause in a compound query and
you are not worrying about duplicate records, then UNION ALL is advisable.

Using Plan Table

Note: The following examples are taken from actual data that is available
for the GECFS-CarPool? application.

Before describing how to use the plan table, it is better to know something
about the examples that are to be used in this document.

There is a table 'T8000_TIC_UNIT_HISTORY' that is created with the synonym
'TIC_UNIT_HISTORY'. To get to know the indexes for this table, use the
following SQL script:

select owner, index_name, table_owner, table_name from all_indexes where
table_name='T8000_TIC_UNIT_HISTORY'

This gives the following result:

OWNER INDEX_NAME TABLE_OWNER TABLE_NAME
OPNSUTIL T8000_TIC_UNIT_HISTORY_X OPNSUTIL T8000_TIC_UNIT_HISTORY

To view the columns that are used in this index, use the following script

select index_name, column_name, column_position from all_ind_columns where
index_name='T8000_TIC_UNIT_HISTORY_X'

This gives the following result

INDEX_NAME COLUMN_NAME COLUMN_POSITION
T8000_TIC_UNIT_HISTORY_X CURR_VIN 1

We can use table 'PLAN_TABLE' to explain the SQL queries. By saying
'explaining SQL queries', it is basically analyzing the queries, so as to
return rows that will say, what is I/O cost of processing the queries, which
index was used for each table in the SQL, which access path was used for the
query, etc.

I shall describe in detail about how to use the PLAN_TABLE, based on the
example above.


The structure of the PLAN_TABLE is as follows:

Name Null? Type
STATEMENT_ID VARCHAR2(30)
TIMESTAMP DATE
REMARKS VARCHAR2(80)
OPERATION VARCHAR2(30)
OPTIONS VARCHAR2(30)
OBJECT_NODE VARCHAR2(128)
OBJECT_OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(30)
OBJECT_INSTANCE NUMBER
OBJECT_TYPE VARCHAR2(30)
OPTIMIZER VARCHAR2(255)
SEARCH_COLUMNS NUMBER
ID NUMBER
PARENT_ID NUMBER
POSITION NUMBER
OTHER LONG

>From this, the main fields, which will be used for the purpose of this
document, will be the following fields:
STATEMENT_ID, OPERATION, OPTIONS, OBJECT_NAME, OBJECT_TYPE, ID, and POSITION

We use the following SQL script, which is used to explain the query.

explain plan set statement_id = 'pcs' for
select * from tic_unit_history where curr_vin='1FTFF25G9DKA45510'

Let us see what this means:

The statement_id field is the field that is set by us, to identify a unique
operation. We can use any literal to set the field. While analyzing the rows
returned, we will be using this field to identify the required rows.
The query, that is to be explained, is specified after the 'for' keyword.
So, what this means, in a sort of pseudo-code language is:
"Explain the plan for this query and set the statement_id='pcs' for all the
rows that are returned"

To analyze the rows that are returned, use the following script:

SELECT LPAD(' ',2*(LEVEL-1))||operation||' '||options||' '||object_name
||' '||DECODE(id, 0, 'Cost = '||position) "Query Plan"
FROM plan_table
START WITH id = 0 AND statement_id = 'pcs'
CONNECT BY PRIOR id = parent_id AND statement_id ='pcs';

I've highlighted the field names of the PLAN_TABLE in bold. Note that the
statement_id value given in this script is the same as that given in the
previous script (and it is case-sensitive).

This script will return, the following rows:

Query Plan
SELECT STATEMENT Cost = 2
TABLE ACCESS BY ROWID T8000_TIC_UNIT_HISTORY
INDEX UNIQUE SCAN T8000_TIC_UNIT_HISTORY_X

There's only one column that is returned and 3 rows. The "Query Plan" is the
header.

Now I'll explain each row.

Row 1: This row says that the SQL script, which was to be explained, was a
'SELECT' operation. (Remember that SQL script which we had used for
explaining is :
"select * from tic_unit_history where curr_vin='1FTFF25G9DKA45510'")
It also says that 'Cost = 2'. This means that the I/O cost or resource
access was 2. The unit of measure is unimportant. What is important, though,
is that the lessor the cost, the fast the query is going to execute.
Row 2: This row specifies which table was accessed and which access path was
used.
Row 3: This row specifies the index that was used to scan the table.

Note that, in the SQL script that was to be explained, I have used the
column 'curr_vin' in the WHERE clause. Why I used this specific column is
because, there is only one index for the table 'tic_unit_history', and that
index consists of only one column, which is 'curr_vin'. So, in order that
the query be processed faster, I specifically used the column in the WHERE
clause, which was present in the index. That is why the optimizer used the
index. When I executed the explained SQL script, it took 0.604 seconds to
return 1 record.

Let us explain another SQL query.

We have a table T4310_TAX_LOCATIONS with the synonym TAX_LOCATIONS. The
indexes and the columns in the indexes for this table is as follows :

Index Name Column Name Column Position
I4310_PK_TAX_LOCATIONS TAX_LOC_ID 1
I4310_IX2_TAX_LOCATIONS COUNTRY_CODE 1
I4310_IX2_TAX_LOCATIONS STATE_OR_PROV_ABBR 2
I4310_IX2_TAX_LOCATIONS COUNTY_NAME 3
I4310_IX2_TAX_LOCATIONS CITY_NAME 4
I4310_IX2_TAX_LOCATIONS ZIP_OR_POSTAL_CODE 5
I4310_IX3_TAX_LOCATIONS OWNER_TAX_LOC_ID 1

The script for explaining is as follows:
select * from tax_locations where tax_loc_id between 900000 and 900100

So the explain script will be:
explain plan set statement_id = 'pcs' for
select * from tax_locations where tax_loc_id between 900000 and 900100

Analysis script is the same as before. This will return the following:

Query Plan
SELECT STATEMENT Cost = 29
TABLE ACCESS BY ROWID T4310_TAX_LOCATIONS
INDEX RANGE SCAN I4310_PK_TAX_LOCATIONS

When we execute the query, it takes 0.302 seconds to return 101 records.

Suppose we modify the query a bit. Instead of using BETWEEN, we use the
relational operators <= and >=. The query will be as follows:

select * from tax_locations where tax_loc_id >= 900000 and tax_loc_id
<=900100

The explain script will be:
explain plan set statement_id = 'pcs' for
select * from tax_locations where tax_loc_id >= 900000 and tax_loc_id
<=900100

The analysis will be the same as before. But the query executes in 0.152
seconds to return the same 101 records. Note that the time reduction is 50%.

Let's try an example by using the index 'I4310_IX2_TAX_LOCATIONS'. Consider
the following query:
select * from tax_locations where state_or_prov_abbr='IL' and
county_name='ADAMS' and city_name='QUINCY'

The explain script will be:
explain plan set statement_id = 'pcs' for
select * from tax_locations where state_or_prov_abbr='IL' and
county_name='ADAMS' and city_name='QUINCY'

This analysis script will return:
Query Plan
SELECT STATEMENT Cost = 573
TABLE ACCESS FULL T4310_TAX_LOCATIONS

Note that the cost is 573 units and the access path that was used was Full
Table Scan. The execution time for this query was 2.143 seconds to return 5
records

Let's modify the query so as to include the country_code also, in the WHERE
clause. We must note that, all records in this table have country_code='US'.
So we might consider this insignificant, in the normal case. Anyway, we'll
try to explain the query.

select * from tax_locations where country_code='US' and
state_or_prov_abbr='IL' and county_name='ADAMS' and city_name='QUINCY'

The explain script is as follows:
explain plan set statement_id = 'pcs' for
select * from tax_locations where country_code='US' and
state_or_prov_abbr='IL' and county_name='ADAMS' and city_name='QUINCY'

And the analysis script returns:
Query Plan
SELECT STATEMENT Cost = 3
TABLE ACCESS BY ROWID T4310_TAX_LOCATIONS
INDEX RANGE SCAN I4310_IX2_TAX_LOCATIONS

Now, with the 'insignificant' country_code column, we have reduced the cost
from 573 units to 3 units. This is because, the optimizer found all the
columns in the index, except the last one (ZIP_OR_POSTAL_CODE) in the WHERE
clause of the query. So it used the index