Create and use an index to improve performance

Question

As a follow up to What columns generally make good indexes? where I am attempting to know what columns are good index candidates for my query ?

using ROWNUM for my query, which columns I should add to an index to improve performance of my query for oracle Database ? I already create and index on startdate and enddate .

SELECT
ID,
startdate,
enddate,
a,
b,
c,
d,
e,
f, /*fk in another table*/
g /*fk in another table*/
FROM tab
WHERE  (enddate IS NULL )  AND  ROWNUM <= 1;

below is the plan table output:

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes  | Cost (%CPU)  | Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     2 |    64 |     2336  (2)| 00:00:01 |
|*  1 |  COUNT STOPKEY    |      |       |       |              |          |
|*  2 |  TABLE ACCESS FULL| tab  |     2 |    64 |     2336  (2)| 00:00:01 |

--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
 1 - filter(ROWNUM<=1)
 2 - filter("tab"."enddate " IS NULL)

thanks for help.

In general Oracle won't index null values, so your `enddate is null` predicate effectively makes the use of indexes impossible and will force the full table scan. indexes mostly help when you have a specific value of a column to search for, or on key fields of tables referenced through joins. when searching for nulls or negative conditions (column != value) indexes are ignored, except possibly in some very specific use cases. the use of `rownum` will limit the size of the result set, but otherwise won't affect the execution plan of the query. — pmdba, Jul 08 '21 at 18:04
Column is not a problem. How many total rows in this table. As per the explain plan FTS is taking place but if table size is small then FTS is the best choice so first you have decide whether you really need an index and to answer that you also need to check what other operations are performed on this table like DMLs(Insert/Update/Delete) because adding index means some cost on DML operations as well. — Atif, Jul 08 '21 at 18:08
If the table has too few rows, the optimizer may also decide that a full table scan is more efficient than scanning an index and then accessing individual rows. It is important to have current table statistics generated so that the optimizer understands as accurately as possible the implications of each predicate. — pmdba, Jul 08 '21 at 18:08
@Atif sorry for the rows, total is 3592413 rows in the table — satcha, Jul 08 '21 at 18:21
@pmdba in this case the `enddate is null` predicate is my performance issue, if it will force the full table scan what is the solution please ? — satcha, Jul 08 '21 at 18:44
A B*Tree index does not index NULL values. You could use a [Bitmap-Index](https://docs.oracle.com/en/database/oracle/oracle-database/18/cncpt/indexes-and-index-organized-tables.html#GUID-B15C4817-7748-456D-9740-8B9628AF9F47), however you should have a careful look at its drawbacks and limitations. — Wernfried Domscheit, Jul 08 '21 at 18:53
Yes - if your table processes a lot of random transactions a bitmap index could hurt application performance. Ideally the table should be read-only. — pmdba, Jul 08 '21 at 18:56

score 1 · Answer 1 · edited Jul 10 '21 at 05:02

One workaround for NULL values is to create function based index as below:

CREATE TABLE TEST_INDEX(ID NUMBER, NAME VARCHAR2(20));

INSERT INTO TEST_INDEX
SELECT LEVEL, NULL
FROM DUAL CONNECT BY LEVEL<= 1000;

--SELECT * FROM TEST_INDEX WHERE NAME IS NULL AND ROWNUM<=1;

CREATE INDEX TEST_INDEX_IDX ON TEST_INDEX(NVL(NAME, 'TEST'));

SELECT * FROM TEST_INDEX WHERE NVL(NAME,'TEST')= 'TEST' AND ROWNUM<=1;

Another common workaround is to index both the column and a literal. NULLs are indexed if at least one of the columns in an index is not NULL. A multi-column index would be a little larger than a function based index, but it has the advantage of working with the NAME IS NULL predicate.

CREATE INDEX TEST_INDEX_IDX ON TEST_INDEX(NAME, 1);

Create and use an index to improve performance

1 Answers1