Original subtitles

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.