RE: Optimizer question in 7.3

I was looking for some help with some optimization questions that I had and I found this thread. Please let me know if you can help me with any of the following questions.

Thanks in advance,
Niloufar

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?
   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.

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. 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"?
   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?

X wrote:
>
> >The query ran horribly slow until we either changed the optimizer to run in
> >RULES mode or placed a hint to use RULES mode on this query.
>
> You should use RULE optimizer unless you have up to date statistics on all
> your schema. Otherwise, the COST optimizer won't do correct decisions.
>
> If you want to check the difference between the plans, download a TunaSQL
> demo from DBE site (www.dbesoftware.com) and run it with your SQL.
>
> If you need any assistance, I will be happy to provide it at
> morgan_at_cardume.com .
>
> CardumeSoftware
> www.cardume.com
Received on Thu Nov 06 1997 - 00:00:00 CST