Original subtitles

In this lesson, we're looking at function based indexes.

A function based index is another type

of index for very specific situations.

So let's take an example here.

Let's say that we have a library or a application that

has library books in it, and we have

books that have their author, maybe the number of pages.

But they have their published date.

And so, when we query, we may want

to see the book that was published on a certain date.

And, if it's a large list, a large number

of rows in the table, then we may want to put an index on it.

And so, if we index that particular

column with an index--

with a regular B-tree index--

and we look up based on the exact date,

then it will use that index.

So if it's a long list, we need an index,

we're trying to speed our query performance.

But what if we were to query for all of the books that

were published on a given year?

And so we'd actually use a two car function to find that list.

Well, in this case, the Oracle optimizer

is going to ignore the index because the values are indexed

based on their full date, that full date

value, not on just the year.

And so we would see ourselves doing

this query and the performance being significantly slower.

So the function based index is an interesting solution

to this problem.

So we use a function based index to query based on a function.

So we need the function we create in the index

to match the function that we're going

to use when we make the query.

So let's just kind of break this down

with an example in the Scott schema.

So I'm connected to my Scott user.

I'm going to do select star from EMP.

One of the values that we see here is hire date.

So sometimes we want to query based on the hire date.

So if I do select star from EMP where a hire equals--

and we'll pick one of them, 01 May 81, the value is returned.

But let's say there were a lot of values here

and this query was taking a lot of time.

And so I may want to create an index on hire date.

So, to show this, we're going to look at the explain plan

button, which is right here.

And then explain plan button is going

to execute the explain plan, which is going to break down

the structure of how Oracle will perform

the solution at the values based on our query.

All right, so we see that it's doing a full table scan.

So it's doing a full scan of every value.

And we don't want that.

We want it to do something with an index.

So we'll say create index emp_idx on emo hire date.

It's created.

And now, when we run the explained plan,

we see that it's not doing a full scan,

it's doing an index scan, an index range scan.

And so that's generally going to be much faster.

So that's exactly what we want.

However, let's say we have a different query.

We may run a report against our employees

to find all the employees that were hired in 1981.

We'll need to break this out in a function.

Two part hire date.

So a four digit year.

And we say equal to 1981.

So we get that value back, this value

is for those hired in 1981.

But let's look at the explain plan.

Now, we have an index on hire date.

And notice that it's doing a full table scan.

It's not using the index that we created.

Why?

Because we indexed based on the entire date and not

on the year.

So, if we do queries based on the year,

just the year alone, we may use a function based index.

We're going to drop emp_idx index,

and we're going to create a function based index.

fidx for function based index on emp.

And then we specify the function that we're going to use.

And it was created.

Again, just running the statement

shows that it returns.

But now, if we look at the explained plan,

notice that it's using the higher FIDX,

spelled it that way, and it's saying it's

by index row ID range scan.

So it's actually using an index to do the query now

after we've created a function based index,

whereas before it was not.