Partition By Clause in Oracle

An introductory discussion about how to use Partition by Clause in Oracle.

What is a “partition by” clause in Oracle?

It is used to break the data into small partitions and is been separated by a boundary or in simple dividing the input into logical groups. The analytical functions are performed within this partitions.  So when the boundaries are crossed then the function get restarted to segregate the data. The “partition by” clause is similar to the “GROUP BY” clause that is used in aggregate functions. They are also known as query partition clause in Oracle.

The syntax of PARTITION BY CLAUSE:

# ANALYTIC FUNCTION or FUNCTION NAME () OVER ( PARTITION BY COLUMN NAME )

Any arguments that need to be passed to the functions then it has to be passed within the parenthesis and after which we have to write the keyword OVER where the partition by will be used.   The column name is used where we want to use clause.

Example of PARTITION BY CLAUSE:

Let us try to identify the total or cumulative cost of each department when it comes to expenditure on salary.  To show this let us take the example from the Employee table which stores the data of the employee, department ID and salary etc..………continue reading from my original article that was published on Experts-Exchange.com and provides about how to use Partition by Clause in Oracle.

 

Thank you for reading my blog, the reference is taken from one of my article that was published by Experts-Exchange.

Please feel free to leave me some feedback or to suggest any future topics.

Looking forward to hearing from you – Swadhin Ray (Sloba) -( LinkedIn ) ( Twitter )

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s