PARTITION BY Problems

[cpaige]

hey Guys( and Gals),
I have been trying to understand the PARTITION BY statement. I Think I have a slight understanding, but not totally. Can you please give me a description of what it does, How it works and a peice of sample code.

Thanks!

Clayton T. Paige
claytonpaige@yahoo.ca

Programmer Extraordinaire

========================================================

"Who is General Failure? and Why is he reading my disk drive?"

 

[did02]

Hi,

The partition is an oracle option that exist since oracle 8.
This option is very interesting to create (very) big tables : you define a partitionning key (with one or more fields of your table) and range values. For each range, oracle will create a "sub table" (ie a partition) with the table structure. When inserting rows, the data wiil be stored in a specific partition (regarding the partition key value). When you execute a select query on this table, if you specify the value of the partitionning key in where clauses, oracle just read the right partiton instead of the whole table.

To know :
- the database must be created with the partitionning option. Open a SQL sheet. You should read something like this :

SQL*Plus: Release 8.1.7.0.0 - Production on Thu Feb 27 08:43:17 2003
(c) Copyright 2000 Oracle Corporation. All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.0.1.3.1 - Production
With the Partitioning option
JServer Release 9.0.1.3.0 - Production
- As your table is partitionned, your index are partitionned too.

For more details and code samples, refer to the oracle doc.

Hope it helps U.
Rgds,
Did02

 

[cpaige]

Thanks,but I think I'm thinking of something different.

This Partition statement is in a Select statement.
Here is the code I found online

<function_name>() OVER
(
[PARTITION BY <exp1> [, …]]
ORDER BY <exp2> [ASC|DESC]
[NULLS FIRST|NULLS LAST]
)

Example:

SELECT RANK(amount) OVER
(PARTITION BY region ORDER BY amount)
FROM REG_SALES;

I kinda understand ,but not quite.
Does it relate to what was mentioned before?

Thanks, Clayton T. Paige
claytonpaige@yahoo.ca

Programmer Extraordinaire

========================================================

&quot;Who is General Failure? and Why is he reading my disk drive?&quot;