0

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?

1
  • rivers is the table name.
    – user1822
    Nov 23, 2016 at 22:22

1 Answer 1

3

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.

2
  • What if the area Oracle Function took a second argument, y, that was not in the rivers table? Could a function index not, then, be created? Or, what if y came from another table, e.g. mountains? Nov 23, 2016 at 22:29
  • If Y was a constant, then it could be specified in the index definition. It cannot be read from a different table. Nov 23, 2016 at 23:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.