CODEWITHSUNDEEP
oracle-databaseindexing
Stig
Stig

Reputation: 2086

Oracle function based indexes

Maybe a silly question, but is it safe to use a function based indexes. I mean, when the column changes is the index changed right away?

Upvotes: 2

Views: 2796

Answers (1)

xQbert
xQbert

Reputation: 35323

Assuming they are setup correctly, Yes.

OVERVIEW: Oracle creates a hidden column on the table with the function based index which is updated each time the columns on which the function based index are changed; assuming the values are deterministic (See oracle link for more info). You can see these hidden columns by querying the system object all_tab_cols your table on which the function based index was created. They typically contain a $ and look something like SYS_NC00005$.

They are often used when you want to handle case sensitivity or handle repeated functions calls on which the overhead of executing the function call is with such frequency that the value up front time of computing the value once and retaining it results in a performance gain.

Additional reading:

Upvotes: 12

Related Questions