CODEWITHSUNDEEP
databaseoracle-databaseoptimizationindexingdatabase-design
satcha
satcha

Reputation: 129

Create and use an index to improve performance

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

Answers (1)

Atif
Atif

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

Related Questions