SQL Learning

The lexical ordering is:

SELECT [ DISTINCT ]
FROM
WHERE
GROUP BY
HAVING
UNION
ORDER BY

For simplicity, not all SQL clauses are listed. This lexical ordering differs fundamentally from the logical order, i.e. from the order of execution:

FROM
WHERE
GROUP BY
HAVING
SELECT
DISTINCT
UNION
ORDER BY

Now you know why the following occurs:

SELECT A.x + A.y AS z
FROM A
WHERE z = 10 -- z is not available here!

Much more at http://tech.pro/tutorial/1555/10-easy-steps-to-a-complete-understanding-of-sql

An excellent explanation of how B-tree indexes work at http://www.orafaq.com/node/1403

SQL Plus window settings

A lot of the commands related to the login.sql for the sqlplus are mentioned in the link http://stackoverflow.com/questions/1439203/favorite-sqlplus-tips-and-tricks

Basic SQL

1. By default, Oracle sorts using a binary sort order.
ex:
Russell
SMITH
Sarchand
Sciarra

alter session set NLS_SORT='BINARY_CI'
The above statement would be able to enforce case insensitivity when sorting

2. alter session set NLS_COMP='LINGUISTIC';
This statement would enable Oracle's linguistic comparison capability which enforce case insensitivity in the where clause

3. Use ROLLUP and CUBE functions to aggregate data at multiple levels

Indexes

“An index makes the query fast” is the most basic explanation of an index .
An Oracle B-Tree index has a doubly linked list and a search tree. Each row in an indexed table corresponds to a leaf node, which is stored in a database block or page. All index blocks are of the same size and the database sorts and stores as many index entries as possible in each block. Databases use doubly linked lists to connect the so-called index leaf nodes. To identify a specific node out of the millions of index leaf nodes quickly, database creates a balanced search tree as shown below:

 

The Oracle database has three distinct operations that describe a basic index lookup:

INDEX UNIQUE SCAN 

The INDEX UNIQUE SCAN performs the tree traversal only. The Oracle database uses this operation if a unique constraint ensures that the search criteria will match no more than one entry.

INDEX RANGE SCAN 

The INDEX RANGE SCAN performs the tree traversal and follows the leaf node chain to find all matching entries. This is the fallback operation if multiple entries could possibly match the search criteria.

TABLE ACCESS BY INDEX ROWID 

The TABLE ACCESS BY INDEX ROWID operation retrieves the row from the table. This operation is (often) performed for every matched record from a preceding index scan operation.

The important point is that an INDEX RANGE SCAN can potentially read a large part of an index. If there is one more table access for each row, the query can become slow even when using an index.

A primary key does not necessarily need a unique index—you can use a non-unique index as well. In that case the Oracle database does not use an INDEX UNIQUE SCAN but instead the INDEX RANGE SCAN operation. 

If the primary key is composite, the database creates an index on all primary key columns—a so-called concatenated index. The column order of a concatenated index has great impact on its usability so it must be chosen carefully.