Indexes as quick sorting (III)

Part III - How to manage?

The previous parts described the necessity and ways to create an index.
On using and supporting indexes in production DB you should monitor, analyze and manage objects. Developer applications have many useful abilities for your work.

Oracle SQL Developer	SQLDetective
Lists of actions for object (INDEX) managing. Note: screen shots in this article are from Oracle SQL Developer and SQLDetective applications for developers.

You see that different developer UIs allow some useful commands for index modifying. Looks like the action lists are dependent on application price and user necessities.

Oracle SQL Developer	SQLDetective

Oracle SQL Developer allows to drop not only the whole index but it's possible to drop a partition. Don't worry about data on deleting index or its part – you'll not lose the source data (not-indexed).

Oracle SQL Developer	SQLDetective

SQLDetective rebuilds the index by Index Wizard with more abilities than in an additional UI of Oracle SQL Developer.

Oracle SQL Developer	SQLDetective

You should rename an index manually in SQL Editor of SQLDeveloper typing "ALTER INDEX … RENAME TO …". Note: it's possible to rename an index partition (in the latest Oracle DB versions) but the renaming action is not available for some domain indexes.

Oracle SQL Developer	SQLDetective

Fast action for index enabling/disabling is available in both applications.

Oracle SQL Developer	SQLDetective

Use the COALESCE action to merge the contents of index blocks where possible to free blocks for reuse. Note: this command is equal to ALTER INDEX ... SHRINK SPACE.

Oracle SQL Developer	SQLDetective

Results of index analyzing allow to modify objects for best using.

Oracle SQL Developer	SQLDetective

Switch Activity action is available for function-based indexes. The UI for enabling/disabling exists in SQLDetective only.

Oracle SQL Developer	SQLDetective

The same clause INVISIBLE from ALTER INDEX command is available for any type of index but there is additional item in object menu named "Switch Visibility" in SQLDetective.

Oracle SQL Developer	SQLDetective

SQLDetective allows to move the index from one tablespace to another with modifying segment attributes. It's possible to relocate/move an index by popup menu in Storage Manager or Object Navigator. Also several indexes may be moved to other tablespace by Group Action of Object Navigator. Unfortunately, the window GUI has some problems in caption and elements align.
As an index is a copy of table/view/cluster then some properties of parent objects are applied automatically to the index. For example, it's unnecessary to set additional grants to columns used in index formula. But as an index is a standalone object then the storage parameters can be set separately from the parent object - for example, the source table is stored in one tablespace and indexes are stored in another tablespace.

For proper and helpful using of indexes, please, learn the following articles of Oracle Documentation: "Indexes and Index-Organized Tables", "Create Index", "Alter Index", "Drop Index", "Create Table (index organized)", "Create Cluster", "Create Indextype", "Create Operator", "Create Type".
Index your data and the application life becomes faster!