Fragmentation and Optimizer
Hi people,
I'm with some kind of problem using fragmentation. Here is the situation:
I've send this DDL to my database server (Informix Universal Server 9.14
TC1 - WIndows NT).
-------------------------------------------------------------------
create table fragmenta
(
id integer,
desc varchar(4)
)
fragment by expression
((desc = 'aaaa' ) AND (mod(id , 2 ) = 0 ) ) in frag0 ,
((desc = 'aaaa' ) AND (mod(id , 2 ) = 1 ) ) in frag1 ,
((desc != 'aaaa' ) AND (mod(id , 2 ) = 0 ) ) in frag2 ,
((desc != 'aaaa' ) AND (mod(id , 2 ) = 1 ) ) in frag3;
create procedure p_carga()define i integer;
for i=1 to 10000
if i<5000
then insert into fragmenta values (i, 'aaaa');
else insert into fragmenta values (i, 'bbbb');
end if;
end for;
end procedure;
-------------------------------------------------------------------
This procedure load some data in the table. All four fragments will receive
2500 rows. I run 'update statistics high', 'set explain on' and 'set
PDQPRIORIT 100', then make some queries:
QUERY:
------
select * from fragmenta
where desc = 'bbbb'
Estimated Cost: 86
Estimated # of Rows Returned: 5001
Maximum Threads: 2
1) leonardo.fragmenta: SEQUENTIAL SCAN (Parallel, fragments: 2, 3)
Filters: leonardo.fragmenta.desc = 'bbbb'
************************************************************
Correct plan!
************************************************************
QUERY:
------
select * from fragmenta
where mod (id,2) = 1
Estimated Cost: 85
Estimated # of Rows Returned: 1000
Maximum Threads: 4
1) leonardo.fragmenta: SEQUENTIAL SCAN (Parallel, fragments: ALL)
Filters: mod(leonardo.fragmenta.id , 2 ) = 1
************************************************************
Incorrect plan! Why scan all fragments if the where clause
says one part of the fragmentarion scheme?
************************************************************
QUERY:
------
select * from fragmenta
where mod (id,2) = 0
Estimated Cost: 85
Estimated # of Rows Returned: 1000
Maximum Threads: 4
1) leonardo.fragmenta: SEQUENTIAL SCAN (Parallel, fragments: ALL)
Filters: mod(leonardo.fragmenta.id , 2 ) = 0
************************************************************
Incorrect plan! Why scan all fragments if the where clause
says one part of the fragmentarion scheme?
************************************************************
QUERY:
------
select * from fragmenta
where mod (id,2) = 0 and desc = 'bbbb'
Estimated Cost: 86
Estimated # of Rows Returned: 500
Maximum Threads: 2
1) leonardo.fragmenta: SEQUENTIAL SCAN (Parallel, fragments: 2, 3)
Filters: (mod(leonardo.fragmenta.id , 2 ) = 0 AND
leonardo.fragmenta.desc = 'bbbb' )
************************************************************
Incorrect plan! Why don't scan only fragment 2?
************************************************************
QUERY:
------
select * from fragmenta
where mod (id,2) = 1 and desc = 'aaaa'
Estimated Cost: 85
Estimated # of Rows Returned: 500
Maximum Threads: 4
1) leonardo.fragmenta: SEQUENTIAL SCAN (Parallel, fragments: ALL)
Filters: (mod(leonardo.fragmenta.id , 2 ) = 1 AND
leonardo.fragmenta.desc = 'aaaa' )
************************************************************
Incorrect plan! All fragments? The where clause is EXACTLY
the same of the fragmentation scheme! Must be fragment 1 only
************************************************************
QUERY:
------
select * from fragmenta
where mod (id,2) = 1
Estimated Cost: 85
Estimated # of Rows Returned: 1000
Maximum Threads: 4
1) leonardo.fragmenta: SEQUENTIAL SCAN (Parallel, fragments: ALL)
Filters: mod(leonardo.fragmenta.id , 2 ) = 1
************************************************************
Incorrect plan! Once again a bad plan! Fragments 0 and 2 don't
contain any rows of this query.
************************************************************
QUERY:
------
select * from fragmenta
where desc = 'aaaa'
Estimated Cost: 85
Estimated # of Rows Returned: 4999
Maximum Threads: 4
1) leonardo.fragmenta: SEQUENTIAL SCAN (Parallel, fragments: ALL)
Filters: leonardo.fragmenta.desc = 'aaaa'
************************************************************
Incorrect plan! Must be fragments 0 and 1! Stupid way!
************************************************************
QUERY:
------
select * from fragmenta
where desc = 'cccc'
Estimated Cost: 86
Estimated # of Rows Returned: 12
Maximum Threads: 2
1) leonardo.fragmenta: SEQUENTIAL SCAN (Parallel, fragments: 2, 3)
Filters: leonardo.fragmenta.desc = 'cccc'
************************************************************
Correct plan!
************************************************************
QUERY:
------
select * from fragmenta
where mod (id,2)=3
Estimated Cost: 85
Estimated # of Rows Returned: 1000
Maximum Threads: 4
1) leonardo.fragmenta: SEQUENTIAL SCAN (Parallel, fragments: ALL)
Filters: mod(leonardo.fragmenta.id , 2 ) = 3
************************************************************
Incorrect plan! The where clause is allways false in this
fragmentation scheme. Should Informix return no rows directly?
************************************************************
Am I making some mistake? OPTCOMPIND is set to 2! Suggestions?
Thanks!
LEO Cardoso
lfcardoso@hotmail.com
---
In article <7n2rtl$5hr2@www.na.informix.com>, Leo Cardoso
<lfcardoso@hotmail.com> writes
>Hi people,
>
> I'm with some kind of problem using fragmentation. Here is the situation:
>
> I've send this DDL to my database server (Informix Universal Server 9.14
>TC1 - WIndows NT).
>
>-------------------------------------------------------------------
>create table fragmenta
> (
> id integer,
> desc varchar(4)
> )
> fragment by expression
> ((desc = 'aaaa' ) AND (mod(id , 2 ) = 0 ) ) in frag0 ,
> ((desc = 'aaaa' ) AND (mod(id , 2 ) = 1 ) ) in frag1 ,
> ((desc != 'aaaa' ) AND (mod(id , 2 ) = 0 ) ) in frag2 ,
> ((desc != 'aaaa' ) AND (mod(id , 2 ) = 1 ) ) in frag3;>
>create procedure p_carga()>define i integer;
>for i=1 to 10000
>if i<5000
> then insert into fragmenta values (i, 'aaaa');
> else insert into fragmenta values (i, 'bbbb');
>end if;
>end for;
>end procedure;
>-------------------------------------------------------------------
>
> This procedure load some data in the table. All four fragments will receive
>2500 rows. I run 'update statistics high', 'set explain on' and 'set
>PDQPRIORIT 100', then make some queries:
>
>QUERY:
>------
>select * from fragmenta
>where desc = 'bbbb'>
>Estimated Cost: 86
>Estimated # of Rows Returned: 5001
>Maximum Threads: 2
>
>1) leonardo.fragmenta: SEQUENTIAL SCAN (Parallel, fragments: 2, 3)
>
> Filters: leonardo.fragmenta.desc = 'bbbb'
>************************************************************
>Correct plan!
>************************************************************
>
>
>QUERY:
>------
>select * from fragmenta
>where mod (id,2) = 1>
>Estimated Cost: 85
>Estimated # of Rows Returned: 1000
>Maximum Threads: 4
>
>1) leonardo.fragmenta: SEQUENTIAL SCAN (Parallel, fragments: ALL)
>
> Filters: mod(leonardo.fragmenta.id , 2 ) = 1
>************************************************************
>Incorrect plan! Why scan all fragments if the where clause
>says one part of the fragmentarion scheme?
>************************************************************
>
> Am I making some mistake? OPTCOMPIND is set to 2! Suggestions?
>
The engine is not intelligent to use mod() expressions to do fragment
elimination! The optimizer only has a certain amount of intelligence!
> Thanks!
>
> LEO Cardoso
> lfcardoso@hotmail.com
>
>---
>
>
>
--
David Williams
The optimizer has trouble doing fragment elimination if the
fragmentation scheme includes functions. This is a long standing
problem. The same happens using say MONTH(application_date) etc. The
workaround is to add a one character or smallint column containing the
results of the modulus function operating on the id column and make the
fragmentation scheme simple based on that column:
create table fragmenta2 (
id integer,
desc varchar(4),
fragcode smallint
) fragment by expression
((desc = 'aaaa') and (fragcode = 0)) in frag0,
((desc = 'aaaa') and (fragcode = 1)) in frag1,
((desc != 'aaaa') and (fragcode = 0)) in frag2,
((desc != 'aaaa') and (fragcode = 1)) in frag3;
This has the added advantage that you can most easily re fragment the
database by adding say two new fragments:
ALTER FRAGMENT FOR TABLE fragmenta2 ADD
((desc = 'aaaa') and (fragcode = 2)) in frag4 AFTER frag3;
ALTER FRAGMENT FOR TABLE fragmenta2 ADD
((desc != 'aaaa') and (fragcode = 2)) in frag5 AFTER frag4;
And these ALTERs will be quick since no rows need to be moved until
you:
UPDATE fragmenta2 SET fragcode = mod( id, 3 );
Art S. Kagel
Leo Cardoso wrote:
>
> Hi people,
>
> I'm with some kind of problem using fragmentation. Here is the situation:
>
> I've send this DDL to my database server (Informix Universal Server 9.14
> TC1 - WIndows NT).
>
> -------------------------------------------------------------------
> create table fragmenta
> (
> id integer,
> desc varchar(4)
> )
> fragment by expression
> ((desc = 'aaaa' ) AND (mod(id , 2 ) = 0 ) ) in frag0 ,
> ((desc = 'aaaa' ) AND (mod(id , 2 ) = 1 ) ) in frag1 ,
> ((desc != 'aaaa' ) AND (mod(id , 2 ) = 0 ) ) in frag2 ,
> ((desc != 'aaaa' ) AND (mod(id , 2 ) = 1 ) ) in frag3;>
> create procedure p_carga()> define i integer;
> for i=1 to 10000
> if i<5000
> then insert into fragmenta values (i, 'aaaa');
> else insert into fragmenta values (i, 'bbbb');
> end if;
> end for;
> end procedure;
> -------------------------------------------------------------------
>
> This procedure load some data in the table. All four fragments will receive
> 2500 rows. I run 'update statistics high', 'set explain on' and 'set
> PDQPRIORIT 100', then make some queries:
>
> QUERY:
> ------
> select * from fragmenta
> where desc = 'bbbb'>
> Estimated Cost: 86
> Estimated # of Rows Returned: 5001
> Maximum Threads: 2
>
> 1) leonardo.fragmenta: SEQUENTIAL SCAN (Parallel, fragments: 2, 3)
>
> Filters: leonardo.fragmenta.desc = 'bbbb'
> ************************************************************
> Correct plan!
> ************************************************************
>
> QUERY:
> ------
> select * from fragmenta
> where mod (id,2) = 1>
> Estimated Cost: 85
> Estimated # of Rows Returned: 1000
> Maximum Threads: 4
>
> 1) leonardo.fragmenta: SEQUENTIAL SCAN (Parallel, fragments: ALL)
>
> Filters: mod(leonardo.fragmenta.id , 2 ) = 1
> ************************************************************
> Incorrect plan! Why scan all fragments if the where clause
> says one part of the fragmentarion scheme?
> ************************************************************
>
> QUERY:
> ------
> select * from fragmenta
> where mod (id,2) = 0>
> Estimated Cost: 85
> Estimated # of Rows Returned: 1000
> Maximum Threads: 4
>
> 1) leonardo.fragmenta: SEQUENTIAL SCAN (Parallel, fragments: ALL)
>
> Filters: mod(leonardo.fragmenta.id , 2 ) = 0
> ************************************************************
> Incorrect plan! Why scan all fragments if the where clause
> says one part of the fragmentarion scheme?
> ************************************************************
>
> QUERY:
> ------
> select * from fragmenta
> where mod (id,2) = 0 and desc = 'bbbb'>
> Estimated Cost: 86
> Estimated # of Rows Returned: 500
> Maximum Threads: 2
>
> 1) leonardo.fragmenta: SEQUENTIAL SCAN (Parallel, fragments: 2, 3)
>
> Filters: (mod(leonardo.fragmenta.id , 2 ) = 0 AND
> leonardo.fragmenta.desc = 'bbbb' )
> ************************************************************
> Incorrect plan! Why don't scan only fragment 2?
> ************************************************************
>
> QUERY:
> ------
> select * from fragmenta
> where mod (id,2) = 1 and desc = 'aaaa'>
> Estimated Cost: 85
> Estimated # of Rows Returned: 500
> Maximum Threads: 4
>
> 1) leonardo.fragmenta: SEQUENTIAL SCAN (Parallel, fragments: ALL)
>
> Filters: (mod(leonardo.fragmenta.id , 2 ) = 1 AND
> leonardo.fragmenta.desc = 'aaaa' )
> ************************************************************
> Incorrect plan! All fragments? The where clause is EXACTLY
> the same of the fragmentation scheme! Must be fragment 1 only
> ************************************************************
>
> QUERY:
> ------
> select * from fragmenta
> where mod (id,2) = 1>
> Estimated Cost: 85
> Estimated # of Rows Returned: 1000
> Maximum Threads: 4
>
> 1) leonardo.fragmenta: SEQUENTIAL SCAN (Parallel, fragments: ALL)
>
> Filters: mod(leonardo.fragmenta.id , 2 ) = 1
> ************************************************************
> Incorrect plan! Once again a bad plan! Fragments 0 and 2 don't
> contain any rows of this query.
> ************************************************************
>
> QUERY:
> ------
> select * from fragmenta
> where desc = 'aaaa'>
> Estimated Cost: 85
> Estimated # of Rows Returned: 4999
> Maximum Threads: 4
>
> 1) leonardo.fragmenta: SEQUENTIAL SCAN (Parallel, fragments: ALL)
>
> Filters: leonardo.fragmenta.desc = 'aaaa'
> ************************************************************
> Incorrect plan! Must be fragments 0 and 1! Stupid way!
> ************************************************************
>
> QUERY:
> ------
> select * from fragmenta
> where desc = 'cccc'>
> Estimated Cost: 86
> Estimated # of Rows Returned: 12
> Maximum Threads: 2
>
> 1) leonardo.fragmenta: SEQUENTIAL SCAN (Parallel, fragments: 2, 3)
>
> Filters: leonardo.fragmenta.desc = 'cccc'
> ************************************************************
> Correct plan!
> ************************************************************
>
> QUERY:
> ------
> select * from fragmenta
> where mod (id,2)=3>
> Estimated Cost: 85
> Estimated # of Rows Returned: 1000
> Maximum Threads: 4
>
> 1) leonardo.fragmenta: SEQUENTIAL SCAN (Parallel, fragments: ALL)
>
> Filters: mod(leonardo.fragmenta.id , 2 ) = 3
> ************************************************************
> Incorrect plan! The where clause is allways false in this
> fragmentation scheme. Should Informix return no rows directly?
> ************************************************************
>
> Am I making some mistake? OPTCOMPIND is set to 2! Suggestions?
>
> Thanks!
>
> LEO Cardoso
> lfcardoso@hotm
Hi... >> fragment by expression >> ((desc = 'aaaa' ) AND (mod(id , 2 ) = 0 ) ) in frag0 , >> ((desc = 'aaaa' ) AND (mod(id , 2 ) = 1 ) ) in frag1 , >> ((desc != 'aaaa' ) AND (mod(id , 2 ) = 0 ) ) in frag2 , >> ((desc != 'aaaa' ) AND (mod(id , 2 ) = 1 ) ) in frag3; > The engine is not intelligent to use mod() expressions to do fragment > elimination! The optimizer only has a certain amount of intelligence! Oh my... this is logics! :) The same bad plan occurs when the expression looks like this: create table "informix".paciente ( codpaciente integer , codconvenio varchar(4) , nomepaciente varchar(60), idade integer , sexo char(1) ) fragment by expression ( (codconvenio = 'amil') and (sexo = 'F') and (idade > 65) ) in fragl7, ((codconvenio = 'amil')and(sexo = 'F')and(idade <= 65)) in fragl8, ((codconvenio = 'amil')and(sexo = 'M')and(idade > 65)) in fragl9, ((codconvenio = 'amil')and(sexo = 'M')and(idade <= 65)) in fragl10, ((codconvenio <> 'amil')and(sexo = 'F')and(idade > 65)) in fragl11, ((codconvenio <> 'amil')and(sexo = 'F')and(idade <= 65)) in fragl12, ((codconvenio <> 'amil')and(sexo = 'M')and(idade > 65)) in fragl13, ((codconvenio <> 'amil')and(sexo = 'M')and(idade <= 65)) in fragl14; No functions... just 3 "AND's" in a logic expression! Too bad! []ao LEO Cardoso ---