Reputation: 129
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.
Upvotes: 1
Views: 1004
Reputation: 2210
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);
Upvotes: 1