How SQL gets executed in Oracle

First the SQL is passed into a Hashing Algorithm and a Hash value is generated.The next step is Parsing of the SQL statement and generating Optimal execution plan.There are two types of parsing, Soft parse and Hard Parse.

Oracle will check whether the generated hash value already exist in the memory buffer. if itexist it will take the plan of the existing hash and executes , this is known assoft parsing.If the hash value does not exist, the new hashvalue is stored in the memory buffer and then the sql will be parsed. Thisis called as Hard Parsing.

Parsing involves Syntax check and Semantic Check.Once the Sql is parsed, the Optimizer willgenerate a Optimal Execution plan.Once the plan is generated, oracle SQL engine will execute the SQL based on the optimalexecution plan.

The Order of the various SQL Clause in a statement is as follows

1. First, the WHERE clause is applied as a filter to the data which comes the individual tables.

2. then if there is a JOIN, then the Join are executed in the most optimized way

3. then if there is a GROUP BY clause, the data is grouped based on the grouping column and aggregate functions are applied on the data and if a HAVING clause is there, it is applied to filter the grouped data

4. then SELECT is used to get the desired columns

5. if there is any analytical functions, it will be applied in the output

6. finally, ORDER BY Clause is applied to sort the data

Differences between the different Oracle Editions

Oracle currently comes in two major Editions, Standard Edition (SE) and Enterprise Edition (EE). The Enterprise Edition has features which are not available to users of the Standard Edition. There is an additional Personal Edition (PE) which has the same feature set as Enterprise Edition except that, since it is aimed at a single user machine, options such as Real Application Clusters (RAC) are not available.
In addition there is a Standard Edition One which was originally aimed at single processor servers (hence the name), but which is currently licensable for dual processor machines. It has the same feature set as SE but is cheaper.
The feature set of both Editions varies between Releases, and some features which were ‘Enterprise’ only in an earlier release have become available in ‘Standard Edition’ in later releases.
Some features within an Edition are options, where the availabilty is governed by license. In other words, you have to pay extra to use these particular features on top of the Enterprise Edition license itself -the Partitioning Option is a classic example of this sort of thing. It is notable with the 10g release of the database that RAC which is the much touted clustering solution for Oracle databases and which has a rather high list price is available for free with the Standard Edition (an edition which historically had no options available to it). This maybe an indicator of a future commoditisation of the RAC technology.

For the current release (10GR2) the list of features is available here:
http://download-uk.oracle.com/docs/cd/B19306_01/license.102/b14199/editions.htm

Standard Edition does NOT include the following :

* Oracle Data Guard
* Fast-start fault recovery
* Online index maintenance
* Online table organization
* Online table redefinition
* Block-level media recovery
* Parallel backup and recovery
* Duplexed backup sets (backup sets can be written to multiple devices in parallel)
* Point-in-time tablespace recovery (a tablespace can be recovered up to a specified point in time after a failure or inadvertent transaction execution)
* Trial recovery (redo is applied in memory only but is not written to disk and is easily rolled back)
* RMAN Backup Encryption
* Unused Block Compression
* Oracle Flashback Table
* Oracle Flashback Database
* Oracle Flashback Transaction Query
* Restore Points
* Oracle Advanced Security
* Oracle Label Security
* Virtual Private Database
* Fine-grained auditing
* Enterprise User Security
* Application roles
* N-tier authentication authorization
* Oracle Change Management Pack
* Oracle Configuration Management Pack
* Oracle Diagnostic Pack
* Oracle Tuning Pack
* Database Resource Manager
* Oracle Partitioning
* Oracle OLAP
* Oracle Data Mining
* Data compression
* Bitmapped index and bitmapped join index
* Star Query Transformation
* Export transportable tablespace
* Asynchronous Change Data Capture
* Summary management
* Parallel query
* Parallel DML
* Parallel index build
* Parallel statistics gathering
* Parallel Data Pump export and import
* Parallel text index creation
* Parallel backup and recovery
* Oracle Streams
* Advanced Replication (multimaster)
* Oracle Messaging Gateway
* Connection Manager
* Multiprotocol connectivity
* Oracle Spatial
* Partitioned Tables

Oracle has introduced an Express Edition (XE) which broadly has the same feature set as the Standard Edition. The major difference in features is the lack of the Java Virtual Machine (JVM) within the Express Edition database. The Express Edition is also limited to 1 processor, 1 GB of RAM and 4 GB of User Data. There is a big difference in the licensing for this edition: it is free to develop, deploy, and distribute.

The latest information can be obtained via:
http://www.oracle.com/technology/products/database/xe/index.html

Windows Tips & Tricks - Tip 1

Deleting Files with a Pattern or Extension within a folder

If you wish to delete all files or a certain file within a folder and all subfolders,
1. simply go to Start -> Run -> "cmd."
2. When the command prompt appears, go to the directory that you wish to delete from.
using cd command
3. Type in DEL /F /S [*.type]

For example, if I wanted to delete all MP3 files from my PC, I would type in "CD C:\ " and then type in "DEL /F /S *.mp3". All of the MP3 files on my C: drive would then be deleted.
Note that if you do not want to delete files within subfolders, don't type the "/S."

Finding Duplicates - Part II

The Next best method for deleting the duplicate is usingROWID (this is specific to Oracle DB).

Finding Duplicates using ROWID

The fastest way of detecting duplicate records in a table is to attempt to switch on a unique constraint.so lets create a Test Table and do with an example by adding some duplicate data.

SQL> create table t1 ( c1 int, c2 int, c3 char(1) );
Table created.
SQL> insert into t1 values ( 1, 50, 'a' );
1 row created.
SQL> insert into t1 values ( 1, 50, 'b' );
1 row created.
SQL> insert into t1 values ( 2, 89, 'x' );
1 row created.
SQL> insert into t1 values ( 2, 89, 'y' );
1 row created.
SQL> insert into t1 values ( 2, 89, 'z' );
1 row created.
SQL> commit;
Commit complete.

Now we will use the following SQL Statement to find the duplicatesand delete them

SQL> delete from T1

where rowid <> ( select max(rowid) from t1 b where b.c1 = t1.c1 and b.c2 = t1.c2 );

SQL> Commit;

So selecting the rows from the table shows that the duplicatesare eliminated

SQL> select * from t1;
C1 C2 C

---------- ---------- -

1 50 b

2 89 z

Finding Duplicates - Part I

There are various ways to Find the Duplicates in a table,but the tough
thing is to identify the correct one. so here we will discuss about
the differents methods to find the duplicates in a table.

Enabling Unique constraint

The fastest way of detecting duplicate records in a table is to attempt to switch on a unique constraint.
so lets create a Test Table and do with an example by adding some duplicate data.

SQL> create table TestDup
(
empno number,
ename varchar2(20),
sal number,
deptno number
);

Table created.

SQL> Insert into TestDup values(1000,'A',5000,10);

1 row created.

SQL> Insert into TestDup values(1001,'B',8000,20);

1 row created.

SQL> Insert into TestDup values(1000,'A',9000,20);

1 row created.

SQL> commit;

Commit complete.

Now we will try to add a Unique constraint to the Table TestDup and see
what happens. So i am creating a Unique Constraint on combination of
empno and ename column. Note: Unique Constraint will be not created as
long as duplicate rows (based on empno and ename) are there in the table.

SQL> Alter table TestDup add Constraint U_Const_01 Unique(empno,ename);

ERROR at line 1:
ORA-02299: cannot validate (SCOTT.U_CONST_01) - duplicate keys found

So it is throwing an Error saying that the table contains duplicate rows and
so unique constraint cannot be applied. so now comes the part to capture the
duplicate rows. First of all we need a table to capture the duplicate rows,
the table creation script is available in the ORACLE_HOME installation path,
as follows

$ORACLE_HOME\rdbms\admin\utlexcpt.sql

I executed the following create table command from the sql file.


SQL> Create Table Exception_table(row_id rowid,
owner varchar2(30),
table_name varchar2(30),
constraint varchar2(30));

Table created.

now we are ready to catch the duplicate keys. so now i will again add the
Constraints but with a little in the syntax;

SQL> Alter table TestDup add Constraint U_Const_01 Unique(empno,ename) exceptions into Exception_table;

ERROR at line 1:
ORA-02299: cannot validate (SCOTT.U_CONST_01) - duplicate keys found

so if you see the syntax, i was using a keyword called EXCEPTIONS INTO , to redirect the duplicate keys
into the table Exception_Table. so now to see the duplicate rows, we use the following query

SQL> select * from TestDup where rowid in (select row_id from Exception_table);

EMPNO ENAME SAL DEPTNO

---------- -------------------- ---------- ----------

1000 A 5000 10

1000 A 9000 20

so it gives the two rows which are duplicate. so now we can take corrective action to remove
the duplicates and then add the constraint to the table to avoid further duplication.

All about NULL in Oracle - Part IV

NULLs affect IN and EXISTS

An IN is functionally equivalent to the = ANY clause , which means an OR Condition.

SQL> Select 1 From Dual where 1 in (1, NULL);

1
----------
1

Because True or NULL will return True.
Same Exists is also same as IN..It returns True even if NULL is returned by the Subquery.Exists checks whether a row exists..it doesn't check whether it is NULL or Not.

SQL> select 'true' val from dual where exists (select null from dual);

VAL
----
true

The difficult part comes when you use NOT IN.
The NOT IN clause is virtually equivalent to comparing each value with = and failing if any test is FALSE or NULL. It is equivalent to AND of all conditions, even if one of the condition is false or NULL, it wont return.


SQL> select 'true' from dual where 1 not in (null,2);

no rows selected

if you understand the functionalities of IN , EXISTS and NOT IN then you can avoid common errors.

Undocumented Functions

sys_op_map_nonnull() is a undocumented Function to make NULL values equal.
It does not affect the Normal Comparisons.

SQL> Select 1 From Dual Where sys_op_map_nonnull('A') = sys_op_map_nonnull('A');

1
----------
1

SQL> Select 1 From Dual Where sys_op_map_nonnull(NULL) = sys_op_map_nonnull(NULL);

1
----------
1

All about NULL in Oracle - Part III

Aggregate Functions

Aggregate Functions like SUM , COUNT will ignore NULL vaules and processes the NON NULL values alone.

SQL> Create table TestSumNull(a Number);

Table created.

SQL> Insert into TestSumNull values(16);

1 row created.

SQL> Insert into TestSumNull values(5);

1 row created.

SQL> Insert into TestSumNull values(NULL);

1 row created.

SQL> Commit;

Commit complete.

SQL> Select * from TestSumNull;

A
----------
16
5

SQL> Select NVL(A,0) From TestSumNull;

NVL(A,0)
----------
16
5
0

SQL> Select Sum(A) , Count(A) from TestSumNull;

SUM(A) COUNT(A)
---------- ----------
21 2


you can see from above result that the COUNT function has ignored the NULL value.

SQL*PLUS Commands

There is a SQL*PLUS command SET NULL .
so the String what is given will be displayed in case a NULL is returned (Alternative for NVL in a SQL*PLUS environment alone).

SQL> Set NULL EMPTY

SQL> Column Value Format a10

SQL> Select Null "Value" From Dual;

Value
----------
EMPTY

as you can see "Empty" is displayed instead of a NULL (without using NVL)...

All about NULL in Oracle - Part II

Logical Operation

When you use NULL in Logical Operations, it has some intersting result but logical.
Note : Boolean is not a Valid Data Type in SQL...it is valid only in PL/SQL

The Truth table is as follows

Op1 Operator Op2 Result
------+------------+----------+---------
True | AND | NULL | NULL
False | AND | NULL | FALSE
True | OR | NULL | TRUE
False | OR | NULL | NULL
----------------------------------------

So Logically Thinking we can come to the conclusion why two of the above four is returning Boolean.

For example, false and null is false.
This makes sense because null, being an unknown value, could in this this context either be true or false.
False And => putting Unknown as True or False...the Result is always False
hence false and null is certainly false as well.

On the other hand, false or null is null because the result is true for false or true and false for false or false, hence the expression's value is unknown, or null.

note: In the following Example, Since Boolean is not supported directly in SQL, i have manipulated it using Relational Operators.



SQL> Select 'TRUE' Res From Dual Where (1 = 1) OR (1 > NULL);

RES
----
TRUE


In the above query, '1 = 1' will return True and (1 > NULL) returns NULL ....since True or NULL is True,
whatever is there in the Select is returned.


SQL> Select 'False' Res From Dual Where Not ((1 > 1) and (1 > NULL));

RES
-----
False

The above query is for False and Null Returns False..so applying a NOT Operator will make it True and ouput is returned.

All About NULL in Oracle - Part I

NULL is nothing or unknown. It is not equal to space or zero.
Oracle treats the empty string ('') as null. This is not ANSI SQL Standard.
The length of an empty string('') is null, not 0.

SQL> select length('') from dual; LENGTH('') ---------- SQL> select NVL(to_char(length('')),'NULL') Len From Dual; LEN ---- NULL

Arthimetic Operation

Any Arthimetic Operation(+,-,*,/) on NULL will return NULL..

SQL> Select NVL(To_Char(1+2+3+4+5+NULL),'NULL') Sum from Dual; SUM ---- NULL

Note : NVL(NULL Value Handling function) is a Oracle Built in function to Covnert NULL into some Meaning full value.
Example: NVL(expr1,expr2) => if expr1 is NULL then it Returns Expr2 Else it return expr1.

NVL2 is another function to Handle NULL values.
Example : NVL2(expr1,expr2,expr3) => if expr1 is NOT NULL then it returns expr2 else if expr1 is NULL then Returns expr3.

Relational Operator

Any Comparison of NULL with Relational Operators (> , < , >= , <=, <>) will Return NULL.so if you want to find the number of rows which has a particular column value as NULL. you cannot use '=' operator to check. You need to use a Special operator 'IS' to check for NULL values.

SQL> Select 1 From Dual Where NULL = NULL; no rows selected SQL> Select 1 From Dual Where NULL is NULL; 1 ---------- 1 SQL> Select count(*) from Emp where Comm is NULL; Count ----- 8 You can generate a matrix table which shows all operations by the following code snippet, SQL> Create Table Operand 2 ( 3 num number 4 ); Table created. Insert Non NULL and NULL Values into the table. SQL> insert into operand values ( 11); 1 row created. SQL> insert into operand values ( 42); 1 row created. SQL> insert into operand values (null); 1 row created. SQL> Commit; Commit complete. The Query to Generate the matrix is as follows ANSI SQL Syntax : Select op_left.num "op left", case when op_left.num = op_right.num then ' = ' when op_left.num > op_right.num then ' > ' when op_left.num < op_right.num then ' < ' else ' ? ' end "rel", op_right.num "op right" From operand op_left CROSS JOIN operand op_right; The Output is as follows op left rel op right ---------- --- ---------- 11 = 11 11 < 42 11 ? EMPTY 42 > 11 42 = 42 42 ? EMPTY EMPTY ? 11 EMPTY ? 42 EMPTY ? EMPTY 9 rows selected.