Index problem (?)

Index problem (?)

Post by Oscar Schoo » Thu, 26 Oct 2000 04:00:00



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

 
 
 

Index problem (?)

Post by 5defc.. » Thu, 26 Oct 2000 04:00:00


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.

 
 
 

Index problem (?)

Post by Oscar Schoo » Thu, 26 Oct 2000 04:00:00



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

Great! It works for me.

Oscar

 
 
 

Index problem (?)

Post by fran » Sat, 28 Oct 2000 02:27:33


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

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

my 1c,
Frank



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

> Great! It works for me.

> Oscar

 
 
 

Index problem (?)

Post by Zbigniew Sliw » Tue, 31 Oct 2000 18:12:53


Oscar,

Have you considered using bitmap indexes on column city.

--
Regards,

Zbigniew Sliwa
Oracle Programmer
Poland


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

 
 
 

Index problem (?)

Post by ta.. » Thu, 02 Nov 2000 06:52:32


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.




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

> Great! It works for me.

> Oscar

--
Tom Sullivan
ADI Consulting, Inc.

--
Tom Sullivan
ADI Consulting, Inc.

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

 
 
 

1. index problem (uses one index but not the other)

What's the most common value in each case?

7.2 is less likely to be fooled when the most common value is much more
common than the rest ...

                        regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

2. perl with oracle stored procedure

3. Full text indexing problem

4. Really need help on Database project!

5. SQL Server index problem on order by

6. Integrating XML, Java, databases (Oct. 20, JavaDevCon)

7. Major Index Problem

8. Looking for WinArch-1 2.0 test

9. Full text indexing problem

10. full-text indexing problems

11. Index problems with ADO 2.7

12. Please Help - Index problem

13. Functional corruption in cube / index problem?