All language subtitles for [SubtitleTools.com] Partitions - Learning Oracle 12c [Video]

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.