slow statement
A user asked why a two-table join (tab1, tab2 joined on col1, with an ORDER BY and no other filter) does a sequential scan on tab2 despite a unique index on tab2.col1. Respondents explained this is expected: with no filtering predicate on either table, the optimizer must full-scan one table (ideally the smaller) and use the index on the other. The poster confirmed that adding a value filter on tab1.col1 made the index be used. Other suggestions: run UPDATE STATISTICS, try optimizer directives, a composite index on the ORDER BY columns to avoid a sort, a hash join, PDQ, or raising DS_NONPDQ_QUERY_MEM. Since the poster can't modify the application's SQL, no further fix was recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
hi to all,
here are a statement :
select tab1.col2, tab1.col3, tab1.col4, tab1.col5,
tab2.col3, tab1.col6, tab2.col4
from tab1, tab2
where tab1.col1=tab2.col1
order by tab1.col2, tab1.col3, tab1.col4, tab1.col5
there is 2 indexes :
ontab1 :
create index i_tab1 on tab1 (col1,col2,col3);
on tab2 :
create unique index i_tab2 on tab2 (col1);
but when i see the sqlexplain.out i see that there is a sequentiel scan on
tab2.col1 although the index i_tab2
any help ?
thanks in advance
Try to force the index i_tab1 via optimizer directive like,
SELECT {+ INDEX(tab2, i_tab2) } <columns> ....
and time the execution to prove or disprove optimizer's decision of
sequential scan being optimal.
Creation of composite index on (col2, col3, col4, col5 ) might also
improve the query performance as the complete Sort operation will be
performed thru the index.
Regards,
Srini
From: "SMITH JOHN" <daylight@webmails.com>
To: ids@iiug.org
Date: 19/04/2011 14:48
Subject: slow statement [23448]
Sent by: ids-bounces@iiug.org
hi to all,
here are a statement :
select tab1.col2, tab1.col3, tab1.col4, tab1.col5,
tab2.col3, tab1.col6, tab2.col4
from tab1, tab2
where tab1.col1=tab2.col1
order by tab1.col2, tab1.col3, tab1.col4, tab1.col5
there is 2 indexes :
ontab1 :
create index i_tab1 on tab1 (col1,col2,col3);
on tab2 :
create unique index i_tab2 on tab2 (col1);
but when i see the sqlexplain.out i see that there is a sequentiel scan on
tab2.col1 although the index i_tab2
any help ?
thanks in advance
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
You are not restricting the records on none of the tables.
This will force a full scan on one of them (ideally the smallest) and then
it can access the other one through index.
Do you imagine any other possible way to solve a query like this?
On Tue, Apr 19, 2011 at 10:13 AM, SMITH JOHN <daylight@webmails.com> wrote:
> hi to all,
>
> here are a statement :
>
> select tab1.col2, tab1.col3, tab1.col4, tab1.col5,>
> tab2.col3, tab1.col6, tab2.col4
> from tab1, tab2
> where tab1.col1=tab2.col1
> order by tab1.col2, tab1.col3, tab1.col4, tab1.col5
>
> there is 2 indexes :
>
> ontab1 :
> create index i_tab1 on tab1 (col1,col2,col3);>
> on tab2 :
> create unique index i_tab2 on tab2 (col1);>
> but when i see the sqlexplain.out i see that there is a sequentiel scan on
> tab2.col1 although the index i_tab2
>
> any help ?
>
> thanks in advance
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
--0015174bee0c73775c04a1426929
here is the restriction i need where tab1.col1=tab2.col1
And how can you solve that restriction without a sequential scan on one of the tables? :) On Tue, Apr 19, 2011 at 11:03 AM, SMITH JOHN <daylight@webmails.com> wrote: > here is the restriction i need > > where tab1.col1=tab2.col1 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --0015174c3f6e59015404a1430cf2
Hi,
I think you forgot an UPDATE STATISTICS so that the Informix optimizer
uses your created index.
Regards,
Le 19/04/2011 11:13, SMITH JOHN a écrit :
> hi to all,
>
> here are a statement :
>
> select tab1.col2, tab1.col3, tab1.col4, tab1.col5,>
> tab2.col3, tab1.col6, tab2.col4
> from tab1, tab2
> where tab1.col1=tab2.col1
> order by tab1.col2, tab1.col3, tab1.col4, tab1.col5
>
> there is 2 indexes :
>
> ontab1 :
> create index i_tab1 on tab1 (col1,col2,col3);>
> on tab2 :
> create unique index i_tab2 on tab2 (col1);>
> but when i see the sqlexplain.out i see that there is a sequentiel scan on
> tab2.col1 although the index i_tab2
>
> any help ?
>
> thanks in advance
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Franck Thomas
ConsultiX
franck.thomas@consult-ix.fr
Téléphone : 33 (0) 1 39 12 18 00
Mobile : 33 (0) 6 78 81 09 33
Fax : 33 (0) 1 39 12 18 18
you're right i specified a value for tab1.col1 and the index was used. so no way to make this statement faster, i mean indexes, because i cannot modify it, it's in a program which i'm not the owner but simply a user :)
Ok. Now that we established that, you should take into account some of the other suggestions: 1- Is the full scan done on the proper table? 2- Would a hash join give better results? 3- Would a "customized" index prevent the creation of a sort file? 4- Should you use PDQ on that query? 5- Is it using the disk for the sort? If it's small enough should you increase DS_NONPDQ_QUERY_MEM so that it's done in memory? Maybe I missed something but this should give you plenty of food for thought.... Feel free to come back with more specific doubts. Regards. On Tue, Apr 19, 2011 at 3:52 PM, SMITH JOHN <daylight@webmails.com> wrote: > you're right > > i specified a value for tab1.col1 and the index was used. > > so no way to make this statement faster, i mean indexes, because i cannot > modify it, it's in a program which i'm not the owner but simply a user :) > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --0015174c104ed27f3c04a146cc35
On 19/04/2011 10:13, SMITH JOHN wrote:
> hi to all,
>
> here are a statement :
>
> select tab1.col2, tab1.col3, tab1.col4, tab1.col5,>
> tab2.col3, tab1.col6, tab2.col4
> from tab1, tab2
> where tab1.col1=tab2.col1
> order by tab1.col2, tab1.col3, tab1.col4, tab1.col5
>
> there is 2 indexes :
>
> ontab1 :
> create index i_tab1 on tab1 (col1,col2,col3);>
> on tab2 :
> create unique index i_tab2 on tab2 (col1);>
> but when i see the sqlexplain.out i see that there is a sequentiel scan on
> tab2.col1 although the index i_tab2
If you read more than 20% of a table (or something) it will always do a
sequential scan anyway. How many rows in each table? Have you run UPDATE
STATISTICS?
--
Cheers,
Obnoxio The Clown
http://obotheclown.blogspot.com
I will now proceed to pleasure myself with this fish.
On 19/04/2011 15:52, SMITH JOHN wrote: > you're right > > i specified a value for tab1.col1 and the index was used. > > so no way to make this statement faster, i mean indexes, because i cannot > modify it, it's in a program which i'm not the owner but simply a user :) I really wouldn't expect that to ever do anything BUT a sequential scan. How many rows are in these tables? -- Cheers, Obnoxio The Clown http://obotheclown.blogspot.com I will now proceed to pleasure myself with this fish.
It looks to me like the engine is doing the right thing, since there is no
filter on tab1, unless the rows in tab2 will severely limit the number of
rows that are actually selected from tab1! Are the data distributions and
other statistics up-to-date? What version of Informix are you using?
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
organization with which I am associated either explicitly, implicitly, or by
inference. Neither do those opinions reflect those of other individuals
affiliated with any entity with which I am affiliated nor those of the
entities themselves.
On Tue, Apr 19, 2011 at 5:13 AM, SMITH JOHN <daylight@webmails.com> wrote:
> hi to all,
>
> here are a statement :
>
> select tab1.col2, tab1.col3, tab1.col4, tab1.col5,>
> tab2.col3, tab1.col6, tab2.col4
> from tab1, tab2
> where tab1.col1=tab2.col1
> order by tab1.col2, tab1.col3, tab1.col4, tab1.col5
>
> there is 2 indexes :
>
> ontab1 :
> create index i_tab1 on tab1 (col1,col2,col3);>
> on tab2 :
> create unique index i_tab2 on tab2 (col1);>
> but when i see the sqlexplain.out i see that there is a sequentiel scan on
> tab2.col1 although the index i_tab2
>
> any help ?
>
> thanks in advance
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--20cf30549f5978d0dd04a163dece