Sort indexes in Oracle9i

Factors to consider when creating an index
Indexing usually improves application performance because it reduces the I/O volume involved in the execution of individual SQL statements in the application. However, too many indexes on the application table will also have some adverse effects on performance. If you accidentally index a small table, you may actually increase the I/O required to access table data. When you perform the "data manipulation language (DML)" Operation on the application data, an unnecessary index will incur additional costs, this is because the DML operation will not only change the data in an index table, but also change the associated index items. In addition, large database environments such as data warehouses and data marketplaces require a large amount of disk space to store their indexes.

Evaluate your index Policy
So, how can we know that we adopt an effective policy to properly use indexes without causing unnecessary overhead? Oracle9i Database introduces a new data dictionary view named V $ object _ usage and a new PL/SQL package named DBMS _ metadata, they help you identify and manage indexes that are critical to an application and those that may not be needed.
Using these tools, we first identify which indexes are accessed and not accessed by the SQL statements of the application. Then, capture DDL for any unused index and delete the unused index. The complete procedure is as follows:
1. enable monitoring for the indexes you want to check, and allow users to access the application and perform standard and typical operations.
2. query the V $ object_usage view to confirm that the status is being monitored.
3. Check the content of the V $ object_usage view.
4. disable monitoring for the index you are checking.
5. Capture Data Definition Language (DDL) for indexes not used by any application during monitoring ).
6. delete unused indexes.
In the subsequent sections, we will discuss these steps and take some practical examples. They use the tables and indexes in the new order entry (OE) sample mode attached to Oracle9i database. Scripts used to build these segments are located in $ ORACLE_HOME/demo/Schema/order_entry/oe_main. SQL.
1. Enable index monitoring. If you want to know whether the application is using the prod_name_ix index in the translated_name column of the product_descriptions table, to track its usage, you must first activate the monitoring function for this index, this is implemented using the following SQL command:
SQL> alter index prod_name_ix monitoring usage;
In most cases, you monitor Multiple indexes at the same time to determine which indexes are in use and which ones should be deleted. The script shown in Listing 1 enables monitoring of all indexes in a specific mode.
After you enable the monitoring function, you can process common applications for a period of time. The duration depends on your situation.
2. Confirm the monitoring status. To confirm whether an index monitoring has been activated, You can query the V $ object_usage view. Listing 2 provides an example of a V $ object_usage query and its output for the prod_name_ix index.
Table 1 describes the View content. Note that for the content of V $ object _ usage, only the pattern with ownership of the index being monitored can be seen. Therefore, if you enable monitoring for indexes in OE mode and Log On As a user system, no data will be returned for any query next to V $ object _ usage. Because prod_name_ix is owned by user Oe, you must log on to SQL * Plus as user oe to see the data contained in the prod _ name_ix index view.
3. Check the content of V $ object _ usage. You can check v $ object_usage to determine which indexes the application has accessed since it started monitoring. Listing 3 shows the output results of the V $ object_usage query for several indexes in OE mode.
The no value in the used column indicates that the application has neither accessed the prod_name_ix index nor the item_order_ix index since monitoring began. This information helps you identify useless indexes that should be deleted. On the contrary, for other indexes, the Yes value in the used column of V $ object_usage indicates that they were accessed at least once during monitoring.
Before deciding which indexes to delete, consider whether your monitoring period accurately reflects typical user activities. If the answer is no, the index you deleted may need to be used in other cases. To avoid deleting an expected index, you must monitor the index usage in several different periods before deciding to delete an unused index. The time period you monitor should also include non-user activity access indexes (such as batch processing), so as to have a full grasp of the index usage in the application.
It is equally important that you consider the monitoring results within the framework of the overall transaction cycle of the application. You can minimize the chances of deleting an important index by monitoring the index usage in several weeks, months, or quarters, these important indexes may be used at the end of one month (or one year), but not during your monitoring.
In addition, if you use a cost-based optimization tool, make sure that the table and index statistics related to the monitored index are up-to-date before the monitoring session starts. This is very important because the cost-based optimization tool may not use this index if it does not obtain accurate information related to an important index and its basic data table, eventually, you will delete this important index.
To delete an index safely, one way is to compare the index access mode with the index access mode of the basic data table during monitoring. If the application accesses the basic data table multiple times but does not access its related indexes, you can safely assume that this index is likely not required. However, because the alter index... monitoring usage command can only be used to monitor indexes, you must also use other mechanisms to monitor the frequency of data tables accessed by applications during index monitoring. One way to solve this problem is to use the built-in audit feature of the Oracle database to learn how frequently data tables related to these unused indexes are accessed.
4. Disable index monitoring. Once the index usage statistics are collected, you can use the nomonitoring option to disable INDEX monitoring:
SQL> alter index prod_name_ix nomonitoring usage;
In Listing 4, the output result instance for querying v $ object_usage after the prod_name_ix index is disabled. The end_monitorin column shows the end date and time of the monitoring period. Prior to the next activation of monitoring on prod_name_ix, information for this index monitoring period will not be cleared from this view. Unlike the statistics in most v $ tables, the statistics in V $ object_usage are retained even after a database is shut down or restarted.
5. Capture DDL for any unused index. If you decide to delete an unused index, you may also want to capture the definition of the index, which takes the form of an SQL DDL statement-so that the index can be restored in the future. To quickly capture DDL, you can use the new Oracle DBMS_METADATA Package, which is built after the database is created and during the execution of the catproc. SQL script. At that time, the execution permission will be automatically granted to the public for the DBMS_METADATA Package. In this package, there are a total of 11 subprograms that can be used to generate DDL commands for any Mode object-either in XML format or in standard SQL DDL format. Oracle also provides metadata APIs that allow you to access metadata from your custom applications.
In this example, you use the get_ddl function with the index parameter in DBMS_METADATA to generate a complete DDL for the index to be deleted. In listing 5, a sample script is displayed for capturing DDL for all unused indexes recorded in V $ object_usage.
In Listing 6, the script in listing 5 shows the output of the two unused indexes in this example. The DBMS_METADATA package will create an output file in the directory specified by the utl_file_dir parameter of the init. ora file of the instance.
6. delete unused indexes. Once you have saved the DDL for re-indexing (if necessary), you can use the drop index command to delete unused indexes. Use the following command to delete two unused indexes in our example:
SQL> drop index prod_name_ix; index dropped. SQL> drop index item_order_ix; index dropped.
Summary
Using indexes appropriately can significantly improve the performance of Oracle Applications. However, your application may not use all the indexes available in the database at all. Use the alter index of Oracle9i database together... the monitoring usage command and the new v $ object_usage data dictionary view allow you to monitor the application's use of indexes and delete unwanted indexes. Before deleting an unused index, you can use the DBMS_METADATA Package to capture its DDL.