Partitioning

Partitioning
Local Indexes
Index is partitioned for each partition. So one Index Partition will store index keys for only one partition
Global Partitioned Indexes
Partitioning Key for Index is independent of Partitioning Key for table. It can be applied to Regular table, Index Organized Tables, Partitioned Tables
Global Non Partitioned Indexes
It's regular non Partitioned Index. And can be applied to any table

Three Types of Basic Partitioning:
----------------------------------
Range : Partition For Feb 2010, Mar 2010, ...
List : Partition For say America, India, US, Russia
Hash : Partition using Hash Algorithm (I guess Oracle does not publish the algorithm)


Single Level Partitioning: Only one set of partitions
-------------------------
Composite Partitioning: Two level of partitions and can be combination of 3 basic
----------------------
paritioning type. Available composite partitioning techniques are range-hash, range-list,
range-range, list-range, list-list, and list-hash.

Partitioning Extension in Oracle 11g:
------------------------------------
Interval Partitioning: Define Interval and first partition. Oracle will create new partition when data is inserted for first time in new partition

REF Partitioning: Parent-Child Relationship: Child partitions will be created automatically based on parents partition and will have same charecteristics as parent partitions. In Child partitions, Oracle will not store index keys as data

Virtual Column Based Partitioning:
Paritioning based on metadata instead of column data. Let's say account number has first three digit as branch code, then we can have partitions for branch and account level data will go in respective branch partitions

Parallel Hint

SELECT /*+ PARALLEL(a) */ COUNT(*) FROM ABCD a ;

Parallel Hint makes operation faster. It does so by providing more threads to the current SQL statement. Not advisable to run in Production Environment during Peak Hours.

When We are Joyful

WHEN WE ARE JOYFUL

 

When we are joyful, we don't look for perfection. If you are looking for perfection then you are not at the source of joy. Joy is the realization that there is no vacation from wisdom. The world appears imperfect on the surface but underneath, all is perfect. Perfection hides; imperfection shows off.

 

The wise will not stay on the surface but will probe into the depth. Things are not blurred; your vision is blurred. Infinite actions prevail in the wholeness of consciousness. And yet the consciousness remains perfect, untouched. As Satsangees, realize this now and be at Home.

 

Regards,
Arpit Shah

---

See the Web's breaking stories, chosen by people like you. Check out Yahoo! Buzz.

Smart Choice for Cursor Processing

It's nice article desribing when to use
CURSOR FOR Loops
Avoid using it
SELECT INTO
Use when query can return atmost one row. Also put SELECT statements in seperate PROCEDURES/FUNCTION. Which can be optimized/cached in Oracle 11g.
CURSOR BULK COLLECT VARRAY [Fixed number of rows or less]
Use when SELECT query will return multiple rows but you know upper limit. If upper limit is very high say 10000, you may want to go for next approach. As it will consume lots of memory.
CURSOR BULK COLLECT NESTED ARRAY with LIMITS
Use when SELECT query will fetch multiple rows and you don't know the upper limit or you know the upper limit but it is very high.

More Detail with examples at:
http://www.oracle.com/technology/oramag/oracle/08-nov/o68plsql.html

Unix to Dos [How to write Control Character in Unix File]

Suppose following is your file. If you move the file from Windows to Unix, you will see Control M (^M) character at the end of each line.
File in Windows:
export USERNAME
export PASSWORD
export CTLPATH

Same File when you move it to Unix
export USERNAME^M
export PASSWORD^M
export CTLPATH^M
^M

You can see the special characters because Windows has [Carriage Return Line Feed] CRLF (\r\n) as line terminating sequence. Where as Unix has only [Line Feed] LF (\n) as line terminator.

So, when we move file from Dos (Windows) to Unix, we need some kind of special processing which will convert CRLF into LF.

One is to open the file and replace Control M character visible in file to blank. To replace Control M character one should know how to create Control M character in Unix.

Folloing is the way to create Control M character in Unix.

Open File in vi editor.

Come in Instert mode by pressing Esc i
Press Control V
Then Press Control M
Then Esc.

It will generate Control M.

Whatever control character you want to generate, first press Control + V and then Control +

So convert file from Dos to Unix follow the steps mentioned below:

Open file in vi.
Type following command.
:0,$s/^M//

The above command will remove Control M (^M) character from entire file.

I will mention other utilities to convert from Dos to Unix sometime later.