Query Question
Hi Informix Experts,
I have the following table and corresponding
index:
create table "owner".feedback
(
abid int8 not null ,
idid2 int8,
idid1 int8,
pos smallint not null ,
name varchar(20) not null,
unique (abid,idid2,idid1,pos,name) ,
check ((idid1 IS NULL ) OR ((idid2 IS NULL ) AND ((idid2 IS NOT NULL
) OR (idid1 IS NOT NULL ) ) ) );
alter table "owner".feedback add constraint (foreign key (idid2)
references "owner".usadmin on delete cascade);
alter table "owner".feedback add constraint (foreign key (idid1)
references "owner".usadmin on delete cascade);
When I execute a query with set explain on
it gives the following analysis of the query:
QUERY:
------
select * from owner.feedback
where idid1 = 44 or idid2 = 44
Estimated Cost: 144
Estimated # of Rows Returned: 400
1) owner.feedbackreputation: INDEX PATH
(1) Index Keys: idid1 (Serial, fragments: ALL)
Lower Index Filter: owner.feedback.idid1 = 44
(2) Index Keys: idid2 (Serial, fragments: ALL)
Lower Index Filter: owner.feedback.idid2 = 44
From my analysis of the query it actually uses
two indexes for a query on a single table.
Is this expected behavior for a query path?
Or is it something to do with having an OR
as a condition? Because when I change the
filter to an AND it only uses the index on
column idid1.
BTW, I am using IDS 9.20.UC2 on a Solaris 7.
Any explanation will be greatly appreciated.
Thanks,
Don
Join 18 million Eudora users by signing up for a free Eudora Web-Mail account at http://www.eudoramail.com
Your assumption that the OR is causing the two-index access is very likely
correct. The alternative would be to sequentially scan the table and check
the two fields.
When you use the AND it chooses one of the indexes, accesses rows with that
value in the column and then checks the other column.
As an aside, did you mis-type the check statement? It is logically
equivalent to:
(idid1 NULL) OR ((idid1 NOT NULL) AND (idid2 NULL)) .
HTH,
Doug Agnew
"Don Ureta Ignacio" <dignacio@eudoramail.com> wrote in message
news:8baml2$h0i$1@news.xmission.com...
>
> Hi Informix Experts,
>
> I have the following table and corresponding
> index:
>
> create table "owner".feedback
> (
> abid int8 not null ,
> idid2 int8,
> idid1 int8,
> pos smallint not null ,
> name varchar(20) not null,
> unique (abid,idid2,idid1,pos,name) ,
> check ((idid1 IS NULL ) OR ((idid2 IS NULL ) AND ((idid2 IS NOT NULL
> ) OR (idid1 IS NOT NULL ) ) ) );
>
> alter table "owner".feedback add constraint (foreign key (idid2)
> references "owner".usadmin on delete cascade);
>
> alter table "owner".feedback add constraint (foreign key (idid1)
> references "owner".usadmin on delete cascade);
>
> When I execute a query with set explain on
> it gives the following analysis of the query:
>
> QUERY:
> ------
> select * from owner.feedback
> where idid1 = 44 or idid2 = 44>
> Estimated Cost: 144
> Estimated # of Rows Returned: 400
>
> 1) owner.feedbackreputation: INDEX PATH
>
> (1) Index Keys: idid1 (Serial, fragments: ALL)
> Lower Index Filter: owner.feedback.idid1 = 44
>
> (2) Index Keys: idid2 (Serial, fragments: ALL)
> Lower Index Filter: owner.feedback.idid2 = 44
>
>
> From my analysis of the query it actually uses
> two indexes for a query on a single table.
>
> Is this expected behavior for a query path?
> Or is it something to do with having an OR
> as a condition? Because when I change the
> filter to an AND it only uses the index on
> column idid1.
>
> BTW, I am using IDS 9.20.UC2 on a Solaris 7.
>
> Any explanation will be greatly appreciated.
>
>
> Thanks,
>
> Don
>
>
>
>
> Join 18 million Eudora users by signing up for a free Eudora Web-Mail
account at http://www.eudoramail.com
Don Ureta Ignacio wrote:
>
> Hi Informix Experts,
>
> I have the following table and corresponding
> index:
>
> create table "owner".feedback
> (
> abid int8 not null ,
> idid2 int8,
> idid1 int8,
> pos smallint not null ,
> name varchar(20) not null,
> unique (abid,idid2,idid1,pos,name) ,
> check ((idid1 IS NULL ) OR ((idid2 IS NULL ) AND ((idid2 IS NOT NULL
> ) OR (idid1 IS NOT NULL ) ) ) );
>
> alter table "owner".feedback add constraint (foreign key (idid2)
> references "owner".usadmin on delete cascade);
>
> alter table "owner".feedback add constraint (foreign key (idid1)
> references "owner".usadmin on delete cascade);
>
> When I execute a query with set explain on
> it gives the following analysis of the query:
>
> QUERY:
> ------
> select * from owner.feedback
> where idid1 = 44 or idid2 = 44>
> Estimated Cost: 144
> Estimated # of Rows Returned: 400
>
> 1) owner.feedbackreputation: INDEX PATH
>
> (1) Index Keys: idid1 (Serial, fragments: ALL)
> Lower Index Filter: owner.feedback.idid1 = 44
>
> (2) Index Keys: idid2 (Serial, fragments: ALL)
> Lower Index Filter: owner.feedback.idid2 = 44
>
> From my analysis of the query it actually uses
> two indexes for a query on a single table.
>
> Is this expected behavior for a query path?
> Or is it something to do with having an OR
> as a condition? Because when I change the
> filter to an AND it only uses the index on
> column idid1.
>
> BTW, I am using IDS 9.20.UC2 on a Solaris 7.
>
> Any explanation will be greatly appreciated.
Yes, 9.20 has the ability to use multiple indexes. It treats the
OR condition as a UNION and makes separate parallel queries.
--
Art S. Kagel & Family
kagel@erols.com