All language subtitles for [SubtitleTools.com] B-tree Indexes - Learning Oracle 12c [Video]

In this lesson, we'll be
looking at B-tree indexes.

So an index, in general,
is used for speeding up

the query performance
of certain queries.

That is to say, we're
querying on a certain column.

And the way a B-tree index works
is by organizing a column value

into a tree structure.

So this goes back to the whole
full table scan versus index

scan issue.

So if we have a table
with a million rows.

Let's say, it's an employee ID
that goes from 1 to 1,000,000.

And we search for
the employee ID,

let's say, in the column of 500.

What has to occur?

Well, it starts at the first row
and says, does one equal 500?

No, go to the next one.

Does two equal 500?

No, go to the next one.

So on and so forth
until it gets to 500.

Does 500 equal 500?

Yes.

And so that row is returned.

But that's not enough, because
then it has to go to 501.

Is 501 equal to 500?

No, and continues to go down the
entire structure of the table.

So that's a full table scan.

And a full table scan can
be very resource intensive

and really time consuming.

Because all of those decisions
have to be made as the scan

occurs.

When we structure a
table into an index,

or basically create
an index on the table,

then what we're doing is saying
that this column has values

that we will query on, so
we want them structured

in a more efficient manner.

So a B-tree index can be
created on one or more columns.

But we can't really understand
how a B-tree index works

until we discuss the ROWID.

So the ROWID is what's
called a pseudocolumn.

It's present in
every table, but you

don't see it, although you
can specifically select it.

And the ROWID,
for the most part,

is going to be a value
that's meaningless to us.

It would just be a series
of numbers and letters.

But the ROWID is important,
because it uniquely identifies

a row in an entire database.

So whereas a primary
key might uniquely

identify a row in
a table, a ROWID

is almost like a primary
key for every row

in the database in every table.

So the ROWID has
important information

about the exact location
of that row of data,

what block it's in, what table
it's in, what segment it's in,

all the way down to that level.

So the fastest way to find a row
would be if you knew its ROWID.

However, ROWIDs can
change over time

if data is moved,
so on, and so forth.

So let's look at what
we mean by a B-tree.

So if we take seven values,
the numbers 1 through 7,

and we decide that we
want to be able to find

any value between 1 and
7, and that we ask it

be in the most efficient
manner possible,

we could structure
it into a B-tree.

So how does a B-tree work?

Well, it's a
decision-making construct

that we can put data into.

So we've created
these seven values

and structured
them into a B-tree.

So let's say we're
looking for the value 5.

So how does a B-tree operate?

So we start at the top
of the tree, what's

actually known as the root, and
we say, does four equal five?

No, it does not.

And at this point, it can branch
in one direction or the other.

If the value is less than four,
it'll branch in this direction.

If it is greater than 4, it'll
branch in this direction.

So is five less than four?

No.

Is five greater than four?

Yes, and so it moves
down this branch.

So what we've already done is
exclude almost half the values

in our seven number series.

And so it goes to six.

Is five equal to six?

No.

Is it less than?

Yes.

And it branches
down this direction.

Is five equal to five?

Yes, and then it knows
it has found that value.

So it's a way of
structuring values

to be able to
quickly access them.

So how would this
possibly apply to a table?

Well let's take those
same seven values

and let's say that
they are an employee

ID in an employee table.

Every one of those rows
associated with those seven

values has a ROWID.

And that ROWID is
the exact location

of that particular row.

So now we want
employee ID number five

and with it, it's ROWID.

So we go through the
B-tree structure,

because we've created a
B-tree index on this table

on that particular column.

So we start with value four.

Is four equal to five?

No.

Less than?

No.

Greater than.

Down to six.

Equal to six? .

No Greater than six?

No.

Less than six, yes.

And so now we have found
employee ID number five

and with it, its ROWID.

So now we have the exact
location of the row.

We didn't have to take all
of the values in the row

and structure them in this way.

We only had to structure
the column value.

So a table has the
employee ID column,

and we create that index on it.

So let's create some indexes.

So we'll start with a table.

Create table location.

We'll have a location ID,
location description, employee

name, and arrival date.

So our table is created.

So let's say that we do a number
of queries against this table

based on location ID.

And we want to create
an index on the location

table, the location ID
column, for faster access.

We type create index.

Then give the index a
name, location_id_idx.

On, then the table name, and
in parentheses, the column

that we're indexing.

We can stop here.

We can also direct what
tablespace to put it into.

And our location ID IDX
index has been created.

So we also said that
we can do an index

on more than one column.

So let's say that we
query the location table,

but we don't always
query by location ID.

We might have other queries
that query location based

on E name and arrival date.

So we need a different
index for those.

So essentially we want
to take those values

and structure them separately.

Oracle can use
that index, and we

can get the benefit, the
performance benefit, when

we use those
queries, rather than

the queries that would benefit
from an index on location IV.

Again, the same.

Call this comp_idx,
because we refer

to this as a composite index.

And we simply say column,
comma, second column.

On table, and then
if we're querying

on E name and arrival date,
we put E name, comma, arrival

date.

And composite B-tree
index has been created.