Re: mystic query (???)

From: Bricklen <bricklen-rem_at_yahoo.comz>
Date: Fri, 07 May 2004 15:28:44 GMT
Message-ID: <MWNmc.29256$U75.4321@edtnps89>

vuht2000_at_yahoo.com wrote:

> Oh but when I recreated the tables on my PC and rerun the query, it
> still uses indexes and runs fast. While on the server even when
> indexes are used, it still runs forever ?? That's the single query
> that cause me problem, all others run OK
>

>>Cost Based Optimizer (CBO) requires two things:
>>
>>1. Read the documentation
>>2. Statistics generated using DBMS_STATS.

And you're sure that the datatypes match in your WHERE clause (no implicit conversion is taking place)?

...
"a.chr_id=b.chr_id" <-- same datatype?
...

"and (a.position between b.class_start and b.class_end)" are these dates? Is it possible that you're nls settings are different, resulting in date conversions?

Are the differences between your init.ora parameter settings between the two boxes? Have you used the analyze command, or dbms_stats? Received on Fri May 07 2004 - 10:28:44 CDT