Understanding Function Index

Question

Looking at Oracle's docs on the Function Index, what's the meaning of rivers in the following?

CREATE INDEX area_index ON rivers (area(geo));

SELECT id, geo, area(geo), desc
     FROM rivers     
     WHERE Area(geo) >5000;

What's the importance of rivers with respect to this query?

Could dual have just been used?

Answer 1

It is the name of the table that the index is created on.

This:

CREATE INDEX area_index ON rivers (area(geo));

.. creates a functional index on the rivers table, that uses the function area() on the column geo.

In simple terms, Oracle creates an index that pre-calculates the value of area(geo), thus making any lookups faster.

The query you have posted:

SELECT id, geo, area(geo), desc
FROM rivers     
WHERE Area(geo) >5000;

... can use the area_index functional index to select the pre-calculated value(s) of area(geo) without having to call the area() function for each row in the table.

Essentially, the query can look like this in pseudo-code:

SELECT id, geo, area_index_value, desc
FROM rivers     
WHERE area_index_value >5000;

The Oracle documentation explains this well.