오라클 USING INDEX - olakeul USING INDEX

We have observed (both in-house and customer environments) slow running queries in some cases where the Oracle optimizer is not using the database indexes available and is performing full table scans on a CE table (e.g., DocVersion). Oracle has identified an issue with Oracle 10g in one case where the stats were incorrect when using the dbms_stats.gather_schema_stats default parameters. In another case using the DBMS_STATS.GATHER_DATABASE_STATS_JOB_PROC, which gathers stats for tables with empty or stale statistics, may have resulted in a non-optimal query plan because running a dbms_stats command with explicit parameters remedied the issue at a customer site.

Symptom

Slow queries with table scans instead of the available indexes being used.

Cause

Using default settings in dbms_stats commands, or the GATHER_DATABASE_STATS_JOB_PROC results in indexes not being used.

Environment

Oracle 10g. Similar issues may be seen on Oracle 11g.

Diagnosing The Problem

Gather a P8 Server trace with Search and DB tracing enabled for a CE search to see the execution time of the DB query. Oracle "topSQL" types of monitoring tools can also be used by the DBA. If a query that should be using an index and execute fast is not doing so, consider that stats may be the issue. Stats also can help with skewed data when histograms are used.

Resolving The Problem

Please open a ticket with Oracle to investigate why the optimizer is not using the indexes for the tables in question. Oracle should be able to provide a new stats command to execute in order to generate an optimal query plan.

Recommendation: Depending on the size of your tables and the data distribution, you may want to disable the GATHER_DATABASE_STATS_JOB_PROC and use explicit stats commands. Oracle may advise you to run stats on an as needed basis using the stats command provided by Oracle.

Examples of stats commands using explicit parameters are:
Exec dbms_stats.gather_schema_stats(ownname => 'P8User', estimate_percent => 20, method_opt => 'FOR ALL INDEXED COLUMNS', cascade => TRUE);

Exec dbms_stats.gather_table_stats( ownname=> 'P8User', tabname=> 'P8Table' , estimate_percent=> 20, cascade=> TRUE, method_opt=> 'FOR ALL INDEXED COLUMNS');

Also, please see the Performance Tuning Guide for further discussion of stats issues such as skew and using explicit parameters in dbms_stats for INSUBFOLDER searches.

[{"Product":{"code":"SSNVNV","label":"FileNet Content Manager"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":"Content Engine","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"4.0.1;4.5.0;4.5.1;5.0","Edition":"","Line of Business":{"code":"LOB45","label":"Automation"}}]

This Oracle tutorial explains how to create, rename and drop indexes in Oracle with syntax and examples.

What is an Index in Oracle?

An index is a performance-tuning method of allowing faster retrieval of records. An index creates an entry for each value that appears in the indexed columns. By default, Oracle creates B-tree indexes.

Create an Index

Syntax

The syntax for creating an index in Oracle/PLSQL is:

CREATE [UNIQUE] INDEX index_name
  ON table_name (column1, column2, ... column_n)
  [ COMPUTE STATISTICS ];

Example

Let's look at an example of how to create an index in Oracle/PLSQL.

For example:

CREATE INDEX supplier_idx
  ON supplier (supplier_name);

In this example, we've created an index on the supplier table called supplier_idx. It consists of only one field - the supplier_name field.

We could also create an index with more than one field as in the example below:

CREATE INDEX supplier_idx
  ON supplier (supplier_name, city);

We could also choose to collect statistics upon creation of the index as follows:

CREATE INDEX supplier_idx
  ON supplier (supplier_name, city)
  COMPUTE STATISTICS;

Create a Function-Based Index

In Oracle, you are not restricted to creating indexes on only columns. You can create function-based indexes.

Syntax

The syntax for creating a function-based index in Oracle/PLSQL is:

CREATE [UNIQUE] INDEX index_name
  ON table_name (function1, function2, ... function_n)
  [ COMPUTE STATISTICS ];

Example

Let's look at an example of how to create a function-based index in Oracle/PLSQL.

For example:

CREATE INDEX supplier_idx
  ON supplier (UPPER(supplier_name));

In this example, we've created an index based on the uppercase evaluation of the supplier_name field.

However, to be sure that the Oracle optimizer uses this index when executing your SQL statements, be sure that UPPER(supplier_name) does not evaluate to a NULL value. To ensure this, add UPPER(supplier_name) IS NOT NULL to your WHERE clause as follows:

SELECT supplier_id, supplier_name, UPPER(supplier_name)
FROM supplier
WHERE UPPER(supplier_name) IS NOT NULL
ORDER BY UPPER(supplier_name);

Rename an Index

Syntax

The syntax for renaming an index in Oracle/PLSQL is:

ALTER INDEX index_name
  RENAME TO new_index_name;

Example

Let's look at an example of how to rename an index in Oracle/PLSQL.

For example:

ALTER INDEX supplier_idx
  RENAME TO supplier_index_name;

In this example, we're renaming the index called supplier_idx to supplier_index_name.

Collect Statistics on an Index

If you forgot to collect statistics on the index when you first created it or you want to update the statistics, you can always use the ALTER INDEX command to collect statistics at a later date.