Re: query optimization question

In article <93k5ta$qg2$1_at_nnrp1.deja.com>, <sybrandb_at_my-deja.com> wrote:
>In article <G6zvw8.5Kr_at_world.std.com>,
> modrall_at_world.std.com (Mark W Modrall) wrote:
>> Hi...
>>
>> we've got a couple of tables and some indexes and we're
>> having trouble getting the following query to make use of the
>> indexes:
>>
>> select * from x,y where x.a = y.a order by x.b desc;
>>
>> x.b has the index on it already...
>>
>> we had high-priced oracle consultants tell us there was
>> no way to get the index in on the act unless you add a bogus
>> where clause on x.b, e.g.
>>
>> select * from x,y where x.b is not null and x.a=y.a order by x.b;
>>
>> we'd had no luck trying with a couple of hints to get
>> the index used in the first case, but it seems ridiculous to have
>> to create a bogus where clause to get the optimizer to recognize
>> the index...
>>
>> anyone out there have better advice than the oracle
>> racketeers?
>>
>> thanks
>> -mark
>>
>>
>x.b is not null is, regrettably, incorrect
>It should be x.b > 0 or x.b > chr(0)
>However this is a typical Rule Based Optimizer tric (tm).
>Are you still using it?
>Welcome in the Wonderful World of Oracle :)
>
>BTW *my* charge is only $100 per hour.

        thanks for responding... sorry about the incorrect form... In reality, the column in question is a date column, and the term they added was "date >sysdate-10000", which, essentially, is like saying "select any date"

        what i find peculiar is that you need to add that where clause to get the optimizer to figure out it has an index it can use... SQL Server didn't seem to suffer from the same silliness...

        the oracle consultants also tried reordering the query as

select * from (select * from x order by x.date desc) a, y where   a.a = y.a;

in an effort to make it clear to the optimizer, but it still wouldn't bite... without a where clause, it wouldn't use the index, which sucks...

        i think it's configured to use rule based... Our tables are too volatile to keep analyzing them all the time, so I was given the impression that the cost based analyzer would be a good choice necessarily...

thanks
-mark Received on Thu Jan 11 2001 - 16:16:33 CET