Re[2]: SET EXPLAIN / SQL
Seemed sort of silly that Informix would be optimizing a query in the
absence of key information...so I did a test using I-4gl.
Results : If there is a '?', the query is NOT optimized until
execution.
Test details
1. 4gl with only PREPARE and no execute
database conv_dev
MAIN
DEFINE
l_command char(80)
set explain on;
let l_command = "update post_code set sys_upd_usr_nam = 'informix'
where post_cd matches ?"
prepare q1 from l_command
end main
Result : At the end of the execution, sqexplain.out file created with
nothing in it (except a new line character).
2. 4gl with PREPARE & EXECUTE and parameter aimed at indexed access
database conv_dev
MAIN
DEFINE
l_command char(80),
l_param char(6)
set explain on;
let l_command = "update post_code set sys_upd_usr_nam = 'informix'
where post_cd matches ?"
prepare q1 from l_command
let l_param = 'L6SXXX'
execute q1 using l_param
end main
Result : sqexplain.out has the following information
QUERY:
------
update post_code set sys_upd_usr_nam = 'informix' where
post_cd matches ?
Estimated Cost: 1
Estimated # of Rows Returned: 1
1) informix.post_code: INDEX PATH
(1) Index Keys: post_cd
Lower Index Filter: informix.post_code.post_cd = 'L6S5A9'
3. 4gl with PREPARE & EXECUTE and parameter aimed at non-indexed access
database conv_dev
MAIN
DEFINE
l_command char(80),
l_param char(6)
set explain on;
let l_command = "update post_code set sys_upd_usr_nam = 'informix' where
post_cd matches ?"
prepare q1 from l_command
let l_param = '*6SXXX'
execute q1 using l_param
end main
Result : sqexplain.out has the following information
QUERY:
------
update post_code set sys_upd_usr_nam = 'informix' where post_cd matches ?
Estimated Cost: 37357
Estimated # of Rows Returned: 140289
1) informix.post_code: SEQUENTIAL SCAN
Filters: informix.post_code.post_cd MATCHES '*6SXXX'
Rudy Fernandes
Purolator Courier
______________________________ Reply Separator ____________________________
Subject: Re: SET EXPLAIN / SQL
Author: kagel@bloomberg.net at Unixgate
Date: 14/09/98 5:19 PM
Stefan Weideneder wrote:
>
> Hi David,
>
> as Sujit mentioned already, the results of SET EXPLAIN are > available as
soon as you prepared your query.
> This is not always true, but in most cases. >
> If the query contains no question-mark "?", your query will > be
optimized at the time of the prepare. Otherwise it will > be optimized at
the EXECUTE or OPEN/FOREACH statement.
Actually, all SQL is optimized at the time it is prepared EVEN if it has
replacable parameters ("?"). This is why the SET EXPLAIN output for a
query with a replacable parameter on a column that is part of the
fragmentation expression of a table will always scan all fragments unless a
non-fragmented (or fragmented on another column), detached, index is
selected by the optimizer.
Art S. Kagel