How to get an index in Oracle 10g

One of the best features of Oracle 10g is the consultant Framework (Advisory framework), a collection of tuning consultants that can help you make the most of your database. One of the consultants,--sql Access Advisor (Access Advisor), recommends changing indexes and materialized views in the schema to improve performance.

However, because the consultant Framework (Advisory framework) is generalized to support a wide variety of consultants, it is cumbersome to navigate all stored procedures and functions in the Dbms_advisor package. A create_task process Initializes a new optimization task, a set_task_parameter process modifies the default parameters, a CTEATE_SQLWKLD process creates a load object, and so on.

Oracle 10g Second Edition adds a quite_tune process to the Dbms_advisor package. This process defaults to most of the parameters needed to create a consultant to adjust the interface. In addition to creating a load object (that is, an optimized QSL statement table), it also submits a separate SQL statement directly to the procedure.

To use the Quite_tune procedure-or any other tool in the Dbms_advisor package-the user must have advisor privileges. In addition, the user who is profiling must have at least a choice of permissions for the tables referenced in the query. Then, just simply call the procedure on the line. As shown in listing a.

You can view the results of a run in various dba_ views. Dba_adivsor_log lists the tasks you are doing and the number of recommendations it generates. These suggestions can then be seen in the dba_advisor_recommendations view, and individual commands can be found in the Dba_advisor_actions view.

Also, you can have dbms_advisor generate the full SQL script needed to perform the recommended behavior. The Get_task_script function returns a slob containing the script.

List B shows the results of the run of List A. Because the Amount_sold column used by the WHERE clause is not indexed, a comprehensive table scan is required to process the statement. In addition, it implies that this is a bitmap index, because the data in the column has many duplicate values.