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