Original subtitles

In this lesson, we're going to be

looking at partitioning, or partitioned tables.

So partitioning is the act of breaking a large table

into more manageable pieces.

And so we are talking about very large tables

here, for the most part.

The most important thing to remember about a partition

table is that it must be partitioned on a partition key.

So a key value is what we would partition on.

And when we say that we're partitioning on a key value,

that means we're breaking the table into separations

based on that key value.

Partitioning is not preferable in every situation.

So if we have, let's say, an order entry table,

and it has 3 million rows, if we decide to partition the order

entry table based on the order ID key,

then we're saying we're going to split the table based

on that key.

However, if we don't query against the table based

on that key value, then the partitioning

is actually going to be more of a hindrance,

because of the overhead involved, than a help.

However, when we have large tables,

partitioning can be of huge performance benefit

because we're excluding so many other parts of the data.

So let's take a look at how this functions.

So here is our 3 million row table.

Now, 3 million rows might be a little on the small side

for a partition table.

Normally, we're talking about dozens of millions or hundreds

of millions of rows.

But it makes a good example here for a visual.

So we have the Order Entry table.

And we're partitioning based on the Order ID column.

So what that means is that the rows of the table that

have an order ID of 0 to 1 million

will be in this partition, those with 1 million to 2 million

in this partition, and 2 to 3 in this one.

So we're dividing the table.

So how does this help us?

Well, let's say we were searching for a particular row

in this 3 million row table.

So we want to do a select star from order entry

where order ID equals 1,000,001 as a value.

So ordinarily, if we had a 3 million row table,

and it was not partitioned, Oracle

would have to either do a full table scan of all

3 million rows or at least an index scan of 3 million values

in order to find that particular row.

But if a table is partition, it's

been broken up into these different pieces.

So now, if we do a select star from order entry

where order ID equals 1,000,001, our partitioning scheme

allows us to go directly to this partition.

And what we've done is exclude 2/3 of the data in the table

immediately as a part of the execution plan.

So those are excluded and we don't even

have to query against them.

We don't have to scan them.

We don't have to scan their index values.

And now, instead of scanning either 3 million rows

or 3 million index values, we're limited to only 1 million,

which is much more manageable.

Another benefit of partitioning is that we could actually

separate these partitions out into different disks,

separate them out to a D drive, an E drive, and an F drive,

for instance.

And that often will help with the performance

of the I/O that has to be done against the table as well.

So there are different types of partitioning

that we can do in Oracle.

Oracle continues to add new ways to partition data.

The first is range partitioning.

And so in range partitioning our partition scheme

is based on a range of values, just as the order entry

table we just looked at.

So the range of values of 0 to 1 million

and 1 million to 2 million and so forth,

a range of values on the partition key.

Another type is hash partitioning.

Hash partitioning is an interesting type,

because in hash partitioning we simply direct,

when we create the table, and say,

I want to have 10 partitions.

We don't give it any other specifications than that.

And so then Oracle uses a hashing algorithm

to decide how to populate those 10 different partitions.

And it normally does a pretty good job of that,

but it won't be as, you know, right on the money

accurate as range partition table would be.

But hash partitioning is much easier to manage.

List partitioning is partitioning based

on a fixed list of values.

So if we have a column that has a certain fixed

number of values in it, we may want to partition on that key.

So something like our company's location,

so company offices located in the North or the South

or the Northwest or the Southeast,

and has those fixed values.

And we can partition and put all of the values for North

in a partition, all the values for Northeast in a partition,

and so on and so forth.

Composite partitioning would be for even larger tables usually.

And in composite partitioning, we

use combinations of these types of partitioning

and into sub-partitions.

So we have a layer of partitions.

And then those partitions are broken further down

into sub-partitions.

And so we may partition based on range and then sub-partition

based on hash.

So let's take a look at a couple of examples

of partition tables.

First and foremost, we notice that the first section

is completely ordinary from a table perspective.

So if we wanted this to be a non-partitioned table,

we could simply run this section and it

would be completely legitimate.

And it would create the order entry range table.

So we have columns, data types, just like a normal table.

What makes it a partition table is the following section,

follows the definition of the table.

And we say partition by range.

Then we give it a partition key.

So we're specifying that the order ID column--

that's this here-- will be used as the partition key.

And then we define the partitions out.

Say the keyword partition.

We give the partition a name, values less than 1 million

and then values less than 2 million for the next partition

and values less than 3 million.

And so what's implied here when we

do range partitioning in this way

is values from 0 to 1 million essentially will be in par 0.

Values less than 2 million but greater than 1 million

will be in par 1, and so forth.

So let's create our partitioned table,

our range partitioned table.

Next, let's look at a hash partitioned table.

So here, we're creating the table order entry hash just

to specify it is hash partitioned.

Again, same columns.

All of the first section in the statement

is exactly like a normal table, a heap organized table.

But in a hash partitioned table, we say partition by hash.

And as always, we give it a partition key.

And we say the number of partitions.

So in this case partition is 3.

So here, we're saying we want to create this table.

We want it partitioned in three partition.

But we're using hash partitions.

So Oracle uses the hashing algorithm

to decide which values go in which partition.

So essentially here, we're saying

we want the benefits of a partition table.

We want the separation of data.

But we don't want to micromanage exactly every part of data that

goes to a particular partition.

Lastly is the list partition table.

So again, first section exactly like a normal table.

And we say partition by list.

And this time we're going to use the Order Region column, and so

the region that the order comes from.

And we say partition, partition name, so par n, and then

the values South and Southwest.

Par s is values North and Northeast.

And par m is values Midwest.

So anytime a row is put into the order entry list table

and the order region column value

is either South or Southwest, it goes in this partition.

And if it's in North or Northeast,

it goes in this partition, so on and so forth.

We'll go ahead and run that as well.

So that's order entry list.

Actually, I forgot to come up here and run our hash partition

table.

So what we can see here is we can

get the benefit of partitioning just

by doing a little more code in the initial creation

of the table.

And then we can get that performance benefit

for large tables.