Oracle Magazine, Nov/Dec 2017
Database Developer and DBA ETL existing index and application performance is degraded A new index is required ORACLE MAGAZINE NOVEMBER DECEMBER 2017 80 SQL create index cust_ ix2 2 on customers upper cust_ name Index created The ability to create indexes on expressions has existed since Oracle8i Database and is commonly used to implement case insensitive queries But unless the refactoring of the application code can be done en masse it is likely that both indexes will need to coexist until all the code can be revisited Although it is trivial to add an index to a table you should never add indexes without understanding the repercussions of doing so Each index Consumes space which in turn may mean higher storage costs or more management effort for administrators Increases the resource cost of performing transactions on the underlying table The index has to be maintained and this may also increase redo and undo consumption May increase contention for resources in high throughput environments especially if the index is on a monotonically increasing value Obviously in an application that may already have hundreds of indexes across many tables there may be the perception that just one more index will hardly have an impact But similarly an airline thought that lower cost flights would not create airport boarding gate chaos The unintended side effects are not limited to just requiring a new index Once that new index is created consider what happens when a DBA attempts to perform an online space reclamation within the CUSTOMERS table
You must have JavaScript enabled to view digital editions.
