Understanding Function Index

ddlindexoracle

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?

Best Answer

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.

Related Solutions

Mysql – Improve query performance with index

The original query

SELECT DISTINCT t1.* 
  FROM current_order AS t1 
    LEFT JOIN receipt AS t2 USING (paper_id,subscriber_id)
  WHERE t2.id NOT IN (
    SELECT id
      FROM receipt 
      WHERE paid_till_date > Now()
    )
UNION
  SELECT current_order.* 
    FROM receipt 
      RIGHT JOIN current_order USING (paper_id, subscriber_id)
    WHERE receipt.id IS NULL 
    ORDER BY subscriber_id, paper_id

is more complex than necessary.

The base tables are

CURRENT ORDER
  paper_id
  subscriber_id

and

RECEIPT
  id
  receipt_date
  paid_till_date
  paper_id
  subscriber_id

The OP stated that receipt.id is a primary key and I think that (paper_id,subscriber_id) is the primary key of current order.

Both queries

SELECT t1.* 
  FROM current_order AS t1 
    LEFT JOIN receipt AS t2 USING (paper_id,subscriber_id)
  WHERE t2.id NOT IN (
    SELECT id
      FROM receipt 
      WHERE paid_till_date > Now()
    )

and

SELECT t1.* 
  FROM current_order AS t1 
    LEFT JOIN receipt AS t2 USING (paper_id,subscriber_id)
  WHERE paid_till_date <= Now()
    or paid_till_date  is NULL

are equivalent: The resultset f both queries contains exactly the following tuples:

all tuples from current_order where there is a RECEIPT with the same (paper_id,subscriber_id) and paid_till_date <= Now()
all tuples from current_order where there is a RECEIPT with the same (paper_id,subscriber_id) and paid_till_date is NULL
all tuples from current_order where there is no RECEIPT with the same (paper_id,subscriber_id) (and therefore paid_till_date  is NULL)

Therefore the queries are equivalent. And they are also equivalent if an DISTINCT is added to the select clause.

The resultset of the query

  SELECT current_order.* 
    FROM receipt 
      RIGHT JOIN current_order USING (paper_id, subscriber_id)
    WHERE receipt.id IS NULL 
    ORDER BY subscriber_id, paper_id

contains the following tuples

all tuples from current_order where there is no RECEIPT with the same (paper_id,subscriber_id) (and therefore receipt.id is NULL)

So the original query can be changed to the simpler query

SELECT DISTINCT t1.* 
  FROM current_order AS t1 
    LEFT JOIN receipt AS t2 USING (paper_id,subscriber_id)
  WHERE paid_till_date <= Now()
    or paid_till_date  is NULL

Perhaps an index on (paper_id,subscriber_id,paid_till_date) will be usefull. The query uses only columns found in the index (receipt.id is not used anymore). An Index on (paper_id,subscriber_id) of "current order" already exists because this is the primary key.

Why would function based indices I’ve created lower the cost but not show up in the explain plan breakdown

From the Notes on Function-based Indexes section of the user documentation for CREATE INDEX:

When you subsequently query a table that uses a function-based index, Oracle Database will not use the index unless the query filters out nulls. However, Oracle Database will use a function-based index in a query even if the columns specified in the WHERE clause are in a different order than their order in the column_expression that defined the function-based index.

So you could try adding appropriate NOT NULL conditions to your query.

Best Answer

Related Solutions

Mysql – Improve query performance with index

Why would function based indices I’ve created lower the cost but not show up in the explain plan breakdown

Related Question