Re: Hom to force optimizer to use certaim index when doing update?

Thomas Kyte wrote in message <3464972f.27990548_at_newshost>... >On Tue, 04 Nov 1997 23:15:20 GMT, dtang_at_minn.net wrote:

|1- I am very confused about how the optimizer behaves. My understanding
| was that when the OPTIMIZER_MODE is set to CHOOSE the rule-based
|method
| is used unless the tables are analyzed and it is then that the
 cost-based
| method is used. Is this correct?

If Oracle holds stats for one or more of the tables in the query it will use the cost based method. Otherwise rule. To create stats use ANALYZE TABLE COMPUTE STATISTICS etc (see documentation)

| Also, I understood that best result is achieved with the combination
|of
| OPTIMIZE_GOAL set to ALL_ROWS and the tables analyzed. After reading
|this thread it sounds that the recommendation is to avoid the usage of the
| cost-based method.
|

Each query is different - if there were one best method then there would be no need for all the variety of hints that can be passed to the optimizer.

|2- I've been working on a query that is based on a view which is based
|on another view and twice the NOT EXISTS is used in the WHERE clauses
| and to make it short is not the best query ever written, but is the
|only way we could put it together to get the information that we needed.
|I've been playing with different plans and I finally have reduced the
|response time from 4 hr. to 16 min. However, my discovery was that the
 query
|was more efficient when the ALL_ROWS was selected and the tables were not
| analyzed!! My only answer to this is that this is all dependent on
|the size of the data that I have in the tables that I am trying to access
 and
|the FULL table scans that I get are probably more efficient than using
|the indexes.

Yes an index on a small table will cost time rather than save it. Indexes should only be placed from an inteligent consideration of how your queries can use them.

As you can tell 16 min. is still not acceptable and it gets
|even worst once I move to another database that has more rows in one of the
|tables that I access. I was wondering if there is any alternative to
 using
| "NOT EXISTS"?
Yes but I would need to see examples of your present queries. Joins should achieve the same result set.

| Also, a broader question is: what is a general approach that I should
|be taking when optimizing a query knowing that it will be executed against
|different sizes of tables? Obviously, the larger data that I have the
 longer
|it will take and different execution paths will be used. But, should I
 have
|different queries/indexes for different sizes of database?

NO - you should avoid this circumstance. By looking at each component of your query and the tables, indexes, plans involved you should be able to build an efficient query.
First identify pivot points in you data. These are the main junctions where tables intersect. In my last contract with 22 tables to join. I found two pivot points.
Build a heirachy of views from the end of the limbs back toward the pivot points.
Consider each join carefully. If a small table is joining with a large - look through the small table at the large. Each time you add tables check the query plan and performance. Hope this helps - contact me if you think I can help

Don Fraser
AOTEA SOFTWARE
Nelson
Aotearoa (NZ) Received on Wed Nov 12 1997 - 00:00:00 CST