Re: Where are the "unique" constraint indexes?

stevec_at_zimmer.CSUFresno.EDU (Steve Cosner) wrote:

>In article <3q00a8$m57_at_fg1.plk.af.mil> wilsons_at_mis4.plk.af.mil writes:
>>When one creates a primary key or a unique constraint, an index is
>>automatically created by the Oracle engine when that constraint is
>>enabled. My question is can those indexes be "analyzed" and used by
>>the cost based optimizer. Certainly I can't find them in dba_indexes.
>
>They go into user_indexes. refer to appendix B in Oracle7 Server
>admin guide. I assume that they are used by the optimizer.
 

>I'm not sure what effect Analyze Index has. I have observed that
>Analyze Index puts only one row into the INDEX_STATS table, replacing
>whatever row was there before. This implies to me that Analyze Index
>is not required or used by the optimizer.
 

>Can anyone else explain more? I'm new at this.
 

>Steve Cosner (stevec_at_zimmer.csufresno.edu)

First of all, if you do not specify otherwise when you create the constraint, the indexes go into your DEFAULT_TABLESPACE which is displayed when you examine the USER_USERS view. If you do not explicitly name the index, it has the same name as the constraint. If you do not name the constraint, it has a name of the form SYS_Cnnnnnn, where nnnnnn is an Oracle assigned sequence number.

Second, when you ANALYZE TABLE, all associated indices are also analyzed. The results of the analysis are visible in columns of the USER_TABLES, USER_INDEXES, and USER_TAB_COLUMNS views (and possibly elsewhere).

From my experience, the cost-based optimizer cannot function without the results of the analysis, so it falls back to rule-based optimization. Thus, if you have never analyzed your tables, you get rule-based behavior. As soon as you analyze the table the cost-based optimization will kick-in, possibly with VERY unexpected results.

I recently analyzed a table, and found the performance of a relatively simple query drop from around 5 seconds to about 5 minutes. I believe the plan switched from using an index to a full table scan (but they won't let me test that theory on the production system).

If you know how to analyze a table, be prepared to un-analyze it. Simply issue
ANALYZE TABLE my_table DELETE STATISTICS; and likewise for the indexes.

--
Doug Henderson, Glen Coulee Consulting, Calgary, Alberta
[Sent by Free Agent 0.55]