Very Computer

This is maybe an index problem. If not, maybe you can help with
optimizing my query.

Before i created an index for a table the following query ran quick:

select * from table1
 where name = 'oscar'
 and city = 'amsterdam'

This table1 has -say- 10 million records
Only 10 of them have a name 'oscar'.
But 1 million of records have as city 'amsterdam'.

Again, this select retrieved quickly the result.

Then i (for whatever reason, that is not the point here) made an index
on 'city' on this table.

Now the select above takes very long to produce the same result.

I suspect the server selects the index to produce a
temporary-result-table with one million records (with amsterdam).

So, if that is the case, how can i prevent the server to use this index
for this query.

Then a related question.

I tried to optimize the query, by subquering it myself in the following
way:

select * from (select * from table1 where name = 'oscar' )
  where city = 'amsterdam'

But this did not help. It runs as slowly as the other. I cannot
understand this. Do you?

Any help appreciated on optimizing queries (maybe by assinging a
specific sequence of evaluating the where-parts of queries?).

Oscar

--
----------------------------------------------------------------
                               OMEGAM
                Environmental Research Institute
      tel: ++31 (0)20 5976 787         fax: ++31 (0)20 5976 777
               P.O.Box:  94685, 1090 GR Amsterdam
                         http://www.omegam.nl
----------------------------------------------------------------

It's using the wrong index now, it was using name before, now it is
using city.

select * from table1
 where name = 'oscar'
 and city||'' = 'amsterdam'

This should now use the name index, ignoring city.

Quote:> This is maybe an index problem. If not, maybe you can help with
> optimizing my query.

> Before i created an index for a table the following query ran quick:

> select * from table1
>  where name = 'oscar'
>  and city||'' = 'amsterdam'

> This table1 has -say- 10 million records
> Only 10 of them have a name 'oscar'.
> But 1 million of records have as city 'amsterdam'.

> Again, this select retrieved quickly the result.

> Then i (for whatever reason, that is not the point here) made an index
> on 'city' on this table.

> Now the select above takes very long to produce the same result.

> I suspect the server selects the index to produce a
> temporary-result-table with one million records (with amsterdam).

> So, if that is the case, how can i prevent the server to use this
index
> for this query.

> Then a related question.

> I tried to optimize the query, by subquering it myself in the
following
> way:

> select * from (select * from table1 where name = 'oscar' )
>   where city = 'amsterdam'

> But this did not help. It runs as slowly as the other. I cannot
> understand this. Do you?

> Any help appreciated on optimizing queries (maybe by assinging a
> specific sequence of evaluating the where-parts of queries?).

> Oscar

> --
> ----------------------------------------------------------------
>                                OMEGAM
>                 Environmental Research Institute
>       tel: ++31 (0)20 5976 787         fax: ++31 (0)20 5976 777
>                P.O.Box:  94685, 1090 GR Amsterdam
>                          http://www.omegam.nl
> ----------------------------------------------------------------

Sent via Deja.com http://www.deja.com/
Before you buy.

Great! It works for me.

Oscar

Trick of the trade, 'how to fool indexes' dept.

Generally it is advisable to:
- check your indexes
- use hints

my 1c,
Frank

Oscar,

Have you considered using bitmap indexes on column city.

--
Regards,

Zbigniew Sliwa
Oracle Programmer
Poland

Congratulations! You've been bitten by the "What hurt could it do to
add another index" bug. Doing the "city||''" trick will indeed fix this
query, but be aware that if you have another 1000 queries like this in
your application, they may all need to be fixed (or at least tested).

In the mean time, whenever you have a working system and someone
suggests adding another index, proceed with caution because you see
what can happen.

Tom Sullivan
ADI Consulting, Inc.

--
Tom Sullivan
ADI Consulting, Inc.

--
Tom Sullivan
ADI Consulting, Inc.

Sent via Deja.com http://www.deja.com/
Before you buy.