Oracle Illustrated:

February, 2010


February 21, 2010  9:17 AM

SPLIT ONE COLUMN WITH DELIMITER TO MULTIPLE COLUMNS



Posted by: Lakshmi Venkatesh

If a delimiter is available on a column and if that needs to be split to multiple columns, then we usually use INSTR and SUBSTR to split into multiple columns and insert into table. Following is a simple demonstration without using multiple INSTR's and SUBSTR's and insert one column with a...

February 21, 2010  9:16 AM

Not Exists vs. Analytical functions



Posted by: Lakshmi Venkatesh

Analytical Functions - Introduced in Oracle 8i. There are different families of functions available and with each release new functions are introduced to the family tree. The below is a quick demonstration of using Analytical functions in place of NOT EXISTS. Note: Similar logic can be applied...


February 21, 2010  9:16 AM

Few more examples – Analytical Functions



Posted by: Lakshmi Venkatesh

FINDING MISSING SEQUENCE / NUMBER from a table SELECT /*+ ALL_ROWS */ pre_gap+1 start_gap, max_seq_gap FROM (SELECT /*+ ALL_ROWS */ seq_column post_gap, LAG(seq_column,1,0) OVER (ORDER BY seq_column) pre_gap , ...


February 21, 2010  9:16 AM

Performance Tuning – NORMAL INSERT VS MULTI-TABLE INSERT



Posted by: Lakshmi Venkatesh

Multi-table insert is introduced in ORACLE 9i. The main advantage is, it is simple to code and easy to maintain; just using a single INSERT script multiple tables and multiple data can be inserted into single / multiple tables. 1. Multi-table inserts is not possible across db links. 2. if you...


February 21, 2010  9:15 AM

Performance Tuning – TEMPORARY TABLE VS COLLECTION



Posted by: Lakshmi Venkatesh

STEP 1 - TABLE CREATE SCRIPTS: QUICK LOOK AT THE RESULTS : Temporary table : Elapsed: 00:00:17.84 Collections : Elapsed: 00:00:41.21 drop table...


February 21, 2010  9:15 AM

Performance Tuning – MERGE BASED ON TEMPORARY TABLES VS MERGE BASED ON COLLECTIONS



Posted by: Lakshmi Venkatesh

Merge - It is always better than separate INSERT / UPDATE and DELETE operations (Delete operation is added in 11g). FORALL Merge is pretty much possible while using COLLECTIONS. FORALL Merge is obviously faster than FOR LOOP Merge, as it enables bulk processing. My opinion - If there is a choice...


February 21, 2010  9:14 AM

Performance Tuning – Collections Vs. Pipelined Table Function



Posted by: Lakshmi Venkatesh

PIPELINED Table functions enables to return sets of data when the next set of data is being processed. This is obviously better than normal TABLE functions. Because, normal TABLE functions picks and processes all the data at one shot then returns it - so its particularly useful for ETL (Extraction...


February 21, 2010  9:14 AM

Performance Tuning – FOR LOOP EXECUTE IMMEDIATE VS FORALL EXECUTE IMMEDIATE



Posted by: Lakshmi Venkatesh

Usually FORALL is possible only with DML operations (Insert / Update / Delete / Merge). From Oracle 9i it is possible with EXECUTE IMMEDIATE. This is just for example purpose only - FORALL is really useful for bulk processing. Though in the below example a direct FORALL - INSERT is possible,...


February 21, 2010  9:13 AM

Read Hexa-decimal file via External table



Posted by: Lakshmi Venkatesh

External table was introduced in Oracle 9i which can be used to read file from ORACLE DIRECTORY. Multiple files can be read at the same time. It can also read hexa-decimal files. The following example demonstrates the use of external table to read from hexa-decimal file. TEST : External table...


February 21, 2010  8:53 AM

Migrating from 9i to 11g – Table Creation scripts



Posted by: Lakshmi Venkatesh

Drop sequence dummy_seq / create sequence dummy_seq start with 1 / create table emp as select dummy_seq.nextval empno, object_name empname, object_id sal, CASE WHEN ROWNUM BETWEEN 1 and 28000 then 'CLERK' WHEN ROWNUM BETWEEN 28001 and 30000 then 'SALESMAN' WHEN ROWNUM BETWEEN 30001...


Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: