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

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.