emp_job_ix nomonitoring usage;
Index altered.
SQL> select * from v$object_usage;
INDEX_NAME TABLE_NAME MON USED START_MONITORING END_MONITORING
---------- --------------- --- ---- ------------------- ---------------
EMP_JOB_IX EMPLOYEES NO YES 07/24/2007 10:04:55 07/25/2007 11:39:45
Sure enough, the index appears to be used at least once during a typical day.
On the other end of the spectrum, an index may be accessed too frequently. If key values are
inserted, updated, and deleted frequently, an index can become less efficient in terms of space
usage. The following commands can be used as a baseline for an index after it is created, and
then run periodically to see if the space usage becomes inefficient:
SQL> analyze index hr.emp_job_ix validate structure;
Index analyzed.
SQL> select pct_used from index_stats where name = 'EMP_JOB_IX';
PCT_USED
----------
78
The PCT_USED column indicates the percentage of the allocated space for the index in use.
Over time, the EMPLOYEES table is heavily used, due to the high turnover rate of employees at
the company, and this index, among others, is not using its space efficiently, as indicated by the
following analyze command and select query, so we decide that a rebuild is in order:
SQL> analyze index hr.emp_job_ix validate structure;
Index analyzed.
SQL> select pct_used from index_stats where name = 'EMP_JOB_IX';
PCT_USED
----------
26
SQL> alter index hr.emp_job_ix rebuild online;
Index altered.
Pages:
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346