0

Using NVl in a query didn't considering indexes . But after creating below index it using newly created index.Please enlighten me.

CREATE INDEX IDX_3 ON TEST_TABLE (NVL(ID,1));

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

INSERT INTO TEST_TABLE VALUES(1,'A');
INSERT INTO TEST_TABLE VALUES(2,'A');
INSERT INTO TEST_TABLE VALUES(3,'A');
INSERT INTO TEST_TABLE VALUES(4,'A');
INSERT INTO TEST_TABLE VALUES(5,'A');
INSERT INTO TEST_TABLE VALUES(6,'A');
INSERT INTO TEST_TABLE VALUES(7,'A');
INSERT INTO TEST_TABLE VALUES(8,'A');
INSERT INTO TEST_TABLE VALUES(9,'A');
INSERT INTO TEST_TABLE VALUES(10,'A');


SELECT * FROM TEST_TABLE 
WHERE NVL(ID,1) = 1;

CREATE INDEX IDX_1 ON TEST_TABLE (ID);

CREATE INDEX IDX_3 ON TEST_TABLE (NVL(ID,1));

Thanks,

3

Whenever you use functions on columns that are associated to indexes, the engine MIGHT NOT use the indexes, due to the computation it needs on top of the column values. If you create a function -based index (like you made), it's being created with the already computed values from the function. The caveat with this, is that this index is now usable only when the column is also being used with the function.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.