query filter with calculated columns
Roger Huang asked whether to index (c2,c3,c4) to speed up a query filtering on c1 and the expression (c2-c3)+c4 > 0. John Carlson asked about c1's uniqueness and the query plan. Superboer showed, for V9, a not-variant SPL function and a functional index on (a, myfun(b,c,d)). Jonathan Leffler said to index c1; an index on c2, c3, c4 would help little unless a functional index on the expression is created.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
Hi all:
I have a table t1 with c1,c2,c3,c4 columns.
I have a query ,
SELECT * FROM t1 where c1="a121" and (c2 -c3)+ c4 > 0.To improve performance , do I need to create index on (c2,c3,c4)?
Or can you tell me any way that I can improve it ?
On 19 Sep 2004 19:30:48 -0700, roger@star2000.com.tw (roger huang)
wrote:
>Hi all:
> I have a table t1 with c1,c2,c3,c4 columns.
>I have a query ,
>SELECT * FROM t1 where c1="a121" and (c2 -c3)+ c4 > 0.>To improve performance , do I need to create index on (c2,c3,c4)?
>Or can you tell me any way that I can improve it ?
How unique is c1?
How many columns does table t1 have?
Have you been able to test this out and review the query plan?
JWC
If V9 then:
create table xx ( a int, b int, c int, d int);
insert into xx values ( 1,2,3,4);
insert into xx values ( 1,2,3,4);
insert into xx values ( 1,2,3,4);
insert into xx values ( 1,2,3,4);
create function myfun ( e int, f int, g int )
returning int
with
(
not variant
)
;
return (( e - f ) + g );
end function;
create index ixie on xx ( a, myfun(b, c,d));
set explain on;
select * from xx where a =1 and myfun(b,c,d) > 0
see you
Superboer
roger@star2000.com.tw (roger huang) wrote in message news:<856c9565.0409191830.28668a65@posting.google.com>...
> Hi all:
> I have a table t1 with c1,c2,c3,c4 columns.
> I have a query ,
> SELECT * FROM t1 where c1="a121" and (c2 -c3)+ c4 > 0.> To improve performance , do I need to create index on (c2,c3,c4)?
> Or can you tell me any way that I can improve it ?
roger@star2000.com.tw (roger huang) wrote:
> I have a table t1 with c1,c2,c3,c4 columns.
> I have a query ,
> SELECT * FROM t1 where c1="a121" and (c2 -c3)+ c4 > 0.> To improve performance , do I need to create index on (c2,c3,c4)?
> Or can you tell me any way that I can improve it ?
You need an index on c1; there isn't going to be an index that is of
much use on c2, c3 and c4 unless you are forever calculating that
expression and create a functional index based on that expression.
--
Jonathan Leffler <jleffler@us.ibm.com>
#include <disclaimer.h>
"I don't suffer from insanity - I enjoy every minute of it!"