RE: Optimiser choice of index
The 1st query was after drop and creae of both indexes, 2nd query is after
update statistics high for 1st column in all indices and update statisticslow for all other columns in all indices
QUERY:
------
select
a.cust_id,
a.ccy_code,
j.cr_date,
j.jrnl_id,
j.line_id,
j.desc,
j.j_op_ref_key,
j.j_op_ref_id,
j.acct_id,
j.amount,
j.j_op_type,
DECODE(j.j_op_ref_key, 'IGF', tCgGame.name,
null) as igf_name
from tJrnl j,
tAcct a,
outer (tCgGameSummary, tCgGame)
where
j.cr_date between '2005-09-20 08:00:00' AND
'2005-09-20 16:00:00'
and
j.acct_id = a.acct_id
and
a.cust_id in (140522667)
and
tCgGameSummary.cg_game_id = j.j_op_ref_id
and
tCgGameSummary.cg_id = tCgGame.cg_id
and
j.j_op_type <> 'BXCG'
ORDER BY a.cust_id desc, j.cr_date desc, j.jrnl_id
desc
Estimated Cost: 8
Estimated # of Rows Returned: 1
Maximum Threads: 1
Temporary Files Required For: Order By
1) informix.a: INDEX PATH
(1) Index Keys: cust_id
Lower Index Filter: informix.a.cust_id = 140522667
2) informix.j: INDEX PATH
Filters: informix.j.j_op_type != 'BXCG'
(1) Index Keys: acct_id cr_date (Parallel, fragments: ALL)
Lower Index Filter: (informix.j.cr_date >= datetime(2005-09-20
08:00:00) year to second AND informix.j.acct_id = informix.a.acct_id )
Upper Index Filter: informix.j.cr_date <= datetime(2005-09-20
16:00:00) year to second
NESTED LOOP JOIN
3) openbet.tcggamesummary: INDEX PATH
(1) Index Keys: cg_game_id
Lower Index Filter: openbet.tcggamesummary.cg_game_id =
informix.j.j_op_ref_id
NESTED LOOP JOIN
4) openbet.tcggame: INDEX PATH
(1) Index Keys: cg_id
Lower Index Filter: openbet.tcggame.cg_id =
openbet.tcggamesummary.cg_id
NESTED LOOP JOIN
QUERY:
------
select
a.cust_id,
a.ccy_code,
j.cr_date,
j.jrnl_id,
j.line_id,
j.desc,
j.j_op_ref_key,
j.j_op_ref_id,
j.acct_id,
j.amount,
j.j_op_type,
DECODE(j.j_op_ref_key, 'IGF', tCgGame.name,
null) as igf_name
from tJrnl j,
tAcct a,
outer (tCgGameSummary, tCgGame)
where
j.cr_date between '2005-09-20 08:00:00' AND
'2005-09-20 16:00:00'
and
j.acct_id = a.acct_id
and
a.cust_id in (140522667)
and
tCgGameSummary.cg_game_id = j.j_op_ref_id
and
tCgGameSummary.cg_id = tCgGame.cg_id
and
j.j_op_type <> 'BXCG'
ORDER BY a.cust_id desc, j.cr_date desc, j.jrnl_id
desc
Estimated Cost: 5
Estimated # of Rows Returned: 1
Maximum Threads: 1
Temporary Files Required For: Order By
1) informix.j: INDEX PATH
Filters: informix.j.j_op_type != 'BXCG'
(1) Index Keys: cr_date (Parallel, fragments: ALL)
Lower Index Filter: informix.j.cr_date >= datetime(2005-09-20
08:00:00) year to second
Upper Index Filter: informix.j.cr_date <= datetime(2005-09-20
16:00:00) year to second
2) informix.a: INDEX PATH
Filters: informix.a.cust_id = 140522667
(1) Index Keys: acct_id
Lower Index Filter: informix.a.acct_id = informix.j.acct_id
NESTED LOOP JOIN
3) openbet.tcggamesummary: INDEX PATH
(1) Index Keys: cg_game_id
Lower Index Filter: openbet.tcggamesummary.cg_game_id =
informix.j.j_op_ref_id
NESTED LOOP JOIN
4) openbet.tcggame: INDEX PATH
(1) Index Keys: cg_id
Lower Index Filter: openbet.tcggame.cg_id =
openbet.tcggamesummary.cg_id
NESTED LOOP JOIN
Regards
Colin
There are 10 types of people in the world, those that understand binary and
those that don't
>From: "Simmons, Keith" <keith.simmons@office2office.biz>
>To: "Colin Dawson" <cjd_1955@hotmail.com>, <informix-list@iiug.org>
>Subject: RE: Optimiser choice of index
>Date: Fri, 23 Sep 2005 10:32:40 +0100
>
>Colin
>
>What was the Query? Explain Plan ?
>
>Keith
>
>-> -----Original Message-----
>-> From: Colin Dawson [mailto:cjd_1955@hotmail.com]
>-> Sent: Friday, September 23, 2005 10:00 AM
>-> To: informix-list@iiug.org
>-> Subject: Optimiser choice of index
>->
>->
>-> IDS 7.31.FD7
>-> Solaris 9
>->
>->
>-> We have a query that rans in <1 sec using a 2 column
>-> composite index (id &
>-> date). I fragmented the table (it was getting close to the
>-> 16777215 page
>-> limit) and added another index (date). The query now takes
>-> >5 mins to run, a
>-> set explain confirmed the optimiser was using the new index.
>-> Adding an
>-> optimiser directive to use the original index solved the problem.
>->
>-> BTW Update Statistics was run after the fragmentation and
>-> index build.
>->
>-> My question is this:
>-> Why would the optimiser use the new index on date only when
>-> using the index
>-> on id and date is obviously better?
>->
>-> I have a vague recollection about Informix selecting the
>-> latest created
>-> index when a column appears in more than one index but can't
>-> remember which
>-> version of OnLine it was.
>->
>-> All observations gratefully received
>->
>->
>->
>->
>-> Regards
>->
>-> Colin
>->
>-> There are 10 types of people in the world, those that
>-> understand binary and
>-> those that don't
>-> sending to informix-list
>->
>
>**********************************************************************************
>This message is sent in strict confidence for the addressee only. It may
>contain legally privileged information. The contents are not to be@@NL