Re: SELECT on multiple column index
Not sure what you're looking for. Are you just trying to find rows where c1 > VALUE1 and c2 > VALUE2 ?
Can you provide more information about the problem you are trying to solve? there is probably another way to do it, and with only 70,000 rows it should return in 1 or two seconds max on decent hardware.
Andr� Schr�der wrote:
> I have a multiple column index over two (actually
> n) columns and wish to find the next higher
> combination.
>
> What I thought I could do was (granted I have an
> index i0 over c1,c2):
>
> SELECT c1,c2 from foo where
> (c1,c2)>(value1,value2) order by c1,c2 limit 1
>
> This works fine but the performance is dead slow.
> EXPLAIN shows it won't use any indexes but does a
> full table scan. My table is quite big though
> (>70000 rows)
>
> If I do this:
>
> SELECT c1,c2 from foo where (c1=value1 and
> c2>value2) OR (c1>value1) order by c1,c2
> limit 1
>
> then the performance is much better. Problem is
> since I am doing this over n columns, I get HUGE
> queries that are pretty complex to handle.
>
> Is this intentional or is maybe my complete
> approach totally wrong? I really just need the
> next higher combination and only one row to
> return.
>
> Any answers are *REALLY* appreciated.
Subject
Views
Written By
Posted
10024
December 05, 2004 07:18AM
3519
December 09, 2004 09:08AM
Re: SELECT on multiple column index
3726
December 09, 2004 11:23AM
3260
December 09, 2004 07:38PM
3555
December 10, 2004 07:51AM
4529
December 10, 2004 08:42AM
3201
December 10, 2004 10:23AM
3637
December 10, 2004 11:02AM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.