How Much do You Know About Oracle Indexing?

Question 5

This question is different. First consider the following index and query:

CREATE INDEX tbl_idx ON tbl (a, date_column)

SELECT date_column, count(*)
  FROM tbl
 WHERE a = 38
 GROUP BY date_column

Let’s say this query returns at least a few rows and that there is no other index on this table.

To implement a new functional requirement, another condition (b = 1) is added to the where clause:

SELECT date_column, count(*)
  FROM tbl
 WHERE a = 38
   AND b = 1
 GROUP BY date_column

How will the change affect performance:

• Same: Query performance stays about the same

  Wrong! The query will be slower.

  The index happened to have all required data (columns) for the original query. It can run as so-called index-only scan, which doesn’t need to access the actual table at all.

  Accessing any column that is not part of the index prevents this optimization so that the engine must look into the actual table for each row that qualifies the original where clause to see if it also satisfies the new filter. Even if the new filter removes all rows, it does so after incurring additional work. Although the grouping has fewer rows to aggregate, this cannot compensate for the additional table look-ups.

  Tip

  Use an index-only scan for queries that access many rows but only a few columns.

  Avoid select * to increase chances for an index-only scan.

  See also: Index-Only Scan and Myth: select * is bad

• Not enough information: Definite answer cannot be given

  Wrong. The provided information is sufficient to tell that the query will be slower.

  The index happened to have all required data (columns) for the original query. It can run as so-called index-only scan, which doesn’t need to access the actual table at all.

  Accessing any column that is not part of the index prevents this optimization so that the engine must look into the actual table for each row that qualifies the original where clause to see if it also satisfies the new filter. Even if the new filter removes all rows, it does so after incurring additional work. Although the grouping has fewer rows to aggregate, this cannot compensate for the additional table look-ups.

  Tip

  Use an index-only scan for queries that access many rows but only a few columns.

  Avoid select * to increase chances for an index-only scan.

  See also: Index-Only Scan and Myth: select * is bad

• Slower: Query takes more time

  Right: the query will be slower.

  The index happened to have all required data (columns) for the original query. It can run as so-called index-only scan, which doesn’t need to access the actual table at all.

  Accessing any column that is not part of the index prevents this optimization so that the engine must look into the actual table for each row that qualifies the original where clause to see if it also satisfies the new filter. Even if the new filter removes all rows, it does so after incurring additional work. Although the grouping has fewer rows to aggregate, this cannot compensate for the additional table look-ups.

  Tip

  Use an index-only scan for queries that access many rows but only a few columns.

  Avoid select * to increase chances for an index-only scan.

  See also: Index-Only Scan and Myth: select * is bad

• Faster: Query take less time

  Wrong: the query will be slower!

  The index happened to have all required data (columns) for the original query. It can run as so-called index-only scan, which doesn’t need to access the actual table at all.

  Accessing any column that is not part of the index prevents this optimization so that the engine must look into the actual table for each row that qualifies the original where clause to see if it also satisfies the new filter. Even if the new filter removes all rows, it does so after incurring additional work. Although the grouping has fewer rows to aggregate, this cannot compensate for the additional table look-ups.

  Tip

  Use an index-only scan for queries that access many rows but only a few columns.

  Avoid select * to increase chances for an index-only scan.

  See also: Index-Only Scan and Myth: select * is bad

Show Results

Question 4

Is the following index a good fit for the query?

CREATE INDEX tbl_idx ON tbl (text)

SELECT *
  FROM tbl
 WHERE text LIKE 'TJ%'

Your answer:

• Good fit: No need to change anything

  Although like expressions starting with a wild card character (% or _) cannot use this index efficiently, a pattern that has the wild card character at the very end can! Even if the wild card character is in the middle, the index is still useful.

  See also: A visual explanation when SQL’s like is slow

• Bad fit: Changing the index or query could improve performance

  Although like expressions starting with a wild card character (% or _) cannot use this index efficiently, a pattern that has the wild card character at the very end can! Even if the wild card character is in the middle, the index is still useful.

  See also: A visual explanation when SQL’s like is slow

Next Question

Question 3

Is the following index a good fit for both queries?

CREATE INDEX tbl_idx ON tbl (a, b)

SELECT *
  FROM tbl
 WHERE a = 38
   AND b = 1

SELECT *
  FROM tbl
 WHERE b = 1

Your answer:

• Good fit: No need to change anything

  The index covers the first query only, the second query cannot use the index efficiently.

  Note that the engine could still read the full index end to end. Although this can be faster than reading the full table end to end, it is still not very efficient and considered not solution to this problem.

  Changing the index column order makes the index suitable for both queries—without additional overhead. The index should therefore look like this (columns exchanged):

  CREATE INDEX tbl_idx ON tbl (b, a)

  Tip

  Indexes can only be used from left to right side. If the first index column is not in the where clause, the index is of little help.

  Learn more about multi-column indexes.

• Bad fit: Changing the index or a query could improve performance

  The index covers the first query only, the second query cannot use the index efficiently.

  Note that the engine could still read the full index end to end. Although this can be faster than reading the full table end to end, it is still not very efficient and considered not solution to this problem.

  Changing the index column order makes the index suitable for both queries—without additional overhead. The index should therefore look like this (columns exchanged):

  CREATE INDEX tbl_idx ON tbl (b, a)

  Tip

  Indexes can only be used from left to right side. If the first index column is not in the where clause, the index is of little help.

  Learn more about multi-column indexes.

Next Question

Question 2

Is the following index a good fit for the query?

CREATE INDEX tbl_idx ON tbl (a, date_column)

SELECT *
  FROM tbl
 WHERE a = 12
 ORDER BY date_column DESC
 FETCH FIRST 1 ROW ONLY

Your answer:

• Good fit: No need to change anything

  The statement can run as an indexed top-N query. It performs just a B-tree traversal (log(n)) and a single table access.

  Tip

  Use this pattern to optimize queries for the latest, best, ….

  The trick is that the index supports the where clause as well as the order by clause. The engine uses the index to find the last entry that matches the where clause and takes it as result. Even though there is an order by clause, there is no need to sort any rows.

  Learn More

  • Indexing the order by clause

  • Finding the first N rows quickly

  • fetch first is the standard for limit or top

• Bad fit: Changing the index or query could improve performance

  The statement can run as an indexed top-N query. It performs just a B-tree traversal (log(n)) and a single table access.

  Tip

  Use this pattern to optimize queries for the latest, best, ….

  The trick is that the index supports the where clause as well as the order by clause. The engine uses the index to find the last entry that matches the where clause and takes it as result. Even though there is an order by clause, there is no need to sort any rows.

  Learn More

  • Indexing the order by clause

  • Finding the first N rows quickly

  • fetch first is the standard for limit or top

Next Question