This chapter contains the following topics:
Database administrators drop indexes from tables in a physical database to increase system performance. Instead of using the actual database application to drop table indexes, you can use the Index Selection Tool (P95150). The advantage of using P95150 to drop indexes is that Oracle's JD Edwards EnterpriseOne keeps track of the indexes that are dropped, even if the indexes have been restored or rebuilt. For example, during a software update, the system restores indexes to tables that are affected by the update. Typically, after an update, you would have to perform another analysis to determine the indexes that need to be re-dropped. However, JD Edwards EnterpriseOne retains this information so that you can easily find the indexes that were previously dropped, reducing the time it takes to drop the indexes again.
Note:
P95150 should only be used by system administrators or database administrators. Use Security Workbench (P00950) to apply the proper security to this application.See "Using Security Workbench" in the JD Edwards EnterpriseOne Tools Security Administration Guide.
The P95150 program enables you to locate and display the table indexes in a tree structure. You expand the tree to view and flag the indexes that you wish to drop. You can flag a single index or multiple indexes at a time. The P95150 program only allows you to drop indexes that are not unique or primary to the table since dropping these types of indexes would disrupt the integrity of the table.
When you open a table in P95150, the program displays the table and all of its columns in a tree. Each item, or node, in the tree has an icon next to it. The Tree Node Key tab contains a description of each of these icons, which include:
Table
Data Source Override
Primary Index
Unique Index
Unflagged Index
The Unflagged Index is the only type of index that you can flag or drop. The system does not permit you to drop a primary or unique index.
Index Flagged for Drop
Column in Index
A table can reside in multiple data sources, so you must determine the data source from which you want the table index to be dropped. When you initially open a table in P95150, by default, the system displays a table in a Default node. A table in the Default node is not associated with a data source. If you flag and then try to drop an index from this node, the program prompts you to specify a data source. If you specify a data source before flagging and dropping an index, the program displays another node besides the Default node. This node is named after the data source that you selected and contains the same tree as the Default node. When you drop an index from the data source node, the index is automatically dropped.
The P95150 program contains an option that enables you to view the history of actions that have been performed using the tool. You can view the following information in the index selection logs:
Table name
Data Source
Index ID
Log Description
User
Machine
Date Updated
Time Updated
This section discusses how to:
Add a table to the Index Selection Tool.
Select a data source.
Drop a single index from a table.
Drop multiple indexes from a table.
Restore indexes to a table.
View the Index Selection Logs.
Access the Machine Search & Select form.
Click the search button in the Data Source column to find the data source master table that you want the Index Selection Tool to validate data sources against.
Once you select a table, the system automatically displays the machine in which the table resides in the Machine Name column.
Click Select to continue.
On the Index Selection Tool, click the Add button.
On the Add Table to Index Selection Tool form, select a table and click OK.
The left pane displays the table in a default node tree.
Before you drop an index from a table, you can select a specific data source location for the table. When you use this method, the Index Selection Tool tree displays the data source in a node for the table.
Access the Index Selection Tool form.
Select a table in the tree and then click the Add Data Source button in the center column.
On Choose OCM or Regular Data Source, click one of these options and then click OK to continue:
Select data source by OCM (recommended).
Use this option to ensure that the table exists in any data source that you select.
Select data source from list of all data sources.
If you select this option, you must select a data source from the Database Data Source Search and Select form, which lists all available data sources.
If you selected the "Select data source by OCM (recommended)" option, on Select Data Source by Object Configuration Management, click one of these options and then click the search button in the Data Source column:
List data sources that the table is specifically mapped to by OCM.
Use this option to select from a list of data sources that the table has been specifically mapped to.
List data sources that the table is specifically and by "DEFAULT" mapped by OCM.
Use this option to select from a list of data sources that the table has been specifically mapped to, as well as mapped to by default.
Click Select.
The Index Selection Tool displays the data source in a new node under the table.
Access the Index Selection Tool form.
The instructions in this section assume that you have already selected a table and a specific data source from which you want to drop the table index.
Expand the data source node to view the indexes for the table.
To flag an index, select an index and then click the Flag/Unflag Index button.
To drop a single index, select a flagged index and then click the Drop Single Index button.
On Drop Single Index, click one of these options and then click OK:
Use proxy user and password.
Specify user and password.
This option activates the Owner ID and Password fields, which you must complete before clicking OK.
On the dialog box, click OK to continue.
Access the Machine Search & Select form.
The instructions in this section assume that you have already selected a table and a specific data source from which you want to drop indexes.
From the data source node, select an index, and then click the Flag/Unflag Index button. Repeat until you have flagged all of the indexes that you want to drop from the table.
To drop multiple indexes, select the data source node that contains the indexes that are flagged to be dropped, and then click the Update Table Indices button.
On Update Table Indices, click one of these options and then click OK:
Use proxy user and password.
Specify user and password.
This option activates the Owner ID and Password fields, which you must complete before clicking OK.
Access the Index Selection Tool form.
Locate the table and data source that you want to restore indexes to.
Expand the data source node to view the indexes that have been dropped.
If you want to restore a single index, select the index and then click the Create Single Index button.
If you want to restore all of the indexes to the table, select the appropriate data source node and then click the Update Table Indices button.
Use the Index Selection Logs to view all of the actions that you have taken when working with table indexes.
Access the Work with Index Selection Logs form.
Enter a table name in the Table Name field or enter an asterisk (*) to view all the tables that have been acted upon in the Index Selection Tool.
Click Find.
The program displays the records for all actions taken based on the search criteria. Each record contains this information:
Table Name
Data Source
Index ID
Log Description
User
Machine
Date Updated
Time Updated