OPTCOMPIND      2
create temp table t1(a varchar(50), b  serial8 );
create temp table t2(a varchar(50), b  serial8 );

insert into t1 select name, 0 from  sysobjstate; -- 10 раз 
24110 rows
insert into t2 select * from  t1;
24110 rows

1.1.
select{+ Explain} count(*)
from t1, t2
where t1.a=t2.a and t1.b=t2.b

DIRECTIVES FOLLOWED: 
EXPLAIN 
DIRECTIVES NOT FOLLOWED: 

Estimated Cost: 2
Estimated # of Rows Returned: 1

  1) informix.t1: SEQUENTIAL SCAN

  2) informix.t2: SEQUENTIAL SCAN


DYNAMIC HASH JOIN 
    Dynamic Hash Filters: (informix.t1.a = informix.t2.a AND informix.t1.b = informix.t2.b ) 

24110 rows -  16  sec.


1.2.  Set PDQPriority 100; 

select{+ Explain} count(*)
from t1, t2
where t1.a=t2.a and t1.b=t2.b

DIRECTIVES FOLLOWED: 
EXPLAIN 
DIRECTIVES NOT FOLLOWED: 

Estimated Cost: 2
Estimated # of Rows Returned: 1
 Maximum Threads: 2 

  1) informix.t1: SEQUENTIAL SCAN

  2) informix.t2: SEQUENTIAL SCAN


DYNAMIC HASH JOIN 
    Dynamic Hash Filters: (informix.t1.a = informix.t2.a AND informix.t1.b = informix.t2.b ) 

24110 rows -  0.300  sec.


1.3. Set PDQPriority 0;
create index i1 on t1(a,b); -- 2 sec
create index i2 on t2(a,b); -- 2 sec один из индексов всегда лишний
Update statistics medium for table t1;
Update statistics medium for table t2;


select{+ Explain} count(*)
from t1, t2
where t1.a=t2.a and t1.b=t2.b

DIRECTIVES FOLLOWED: 
EXPLAIN 
DIRECTIVES NOT FOLLOWED: 

Estimated Cost: 12752
Estimated # of Rows Returned: 1

  1) informix.t1: SEQUENTIAL SCAN

  2) informix.t2: INDEX PATH

    (1) Index Keys: a b   (Serial, fragments: ALL)
        Lower Index Filter: (informix.t1.b = informix.t2.b AND informix.t1.a = informix.t2.a ) 
NESTED LOOP JOIN

24110 rows -  0.700  sec.


-----------------------------------------------------------------------------------------------
create temp table t1(a varchar(50), b int8);
create temp table t2(a varchar(50), b int8);

insert into t1 select name, tabid from  sysobjstate; -- 10 раз 
24110 rows

insert into t2 select * from  t1;
24110 rows


2.1.

select{+ Explain} count(*)
from t1, t2
where t1.a=t2.a and t1.b=t2.b

DIRECTIVES FOLLOWED: 
EXPLAIN 
DIRECTIVES NOT FOLLOWED: 

Estimated Cost: 2
Estimated # of Rows Returned: 1

  1) informix.t1: SEQUENTIAL SCAN

  2) informix.t2: SEQUENTIAL SCAN


DYNAMIC HASH JOIN 
    Dynamic Hash Filters: (informix.t1.a = informix.t2.a AND informix.t1.b = informix.t2.b ) 

277500 rows -  16  sec.


2.2.  Set PDQPriority 100; 

select{+ Explain} count(*)
from t1, t2
where t1.a=t2.a and t1.b=t2.b

DIRECTIVES FOLLOWED: 
EXPLAIN 
DIRECTIVES NOT FOLLOWED: 

Estimated Cost: 2
Estimated # of Rows Returned: 1

  1) informix.t1: SEQUENTIAL SCAN

  2) informix.t2: SEQUENTIAL SCAN


DYNAMIC HASH JOIN 
    Dynamic Hash Filters: (informix.t1.a = informix.t2.a AND informix.t1.b = informix.t2.b ) 

277500 rows -  0.700  sec.  Поток один что удивляет. 

2.3. Set PDQPriority 0;
create index i1 on t1(a,b);
create index i2 on t2(a,b);
Update statistics medium for table t1;
Update statistics medium for table t2;


select{+ Explain} count(*)
from t1, t2
where t1.a=t2.a and t1.b=t2.b

DIRECTIVES FOLLOWED: 
EXPLAIN 
DIRECTIVES NOT FOLLOWED: 

Estimated Cost: 12994
Estimated # of Rows Returned: 1

  1) informix.t1: SEQUENTIAL SCAN

  2) informix.t2: SEQUENTIAL SCAN

DYNAMIC HASH JOIN 
    Dynamic Hash Filters: (informix.t1.a = informix.t2.a AND informix.t1.b = informix.t2.b ) 

277500 rows -  16  sec. Из-за неуникальности во втором поле он не хочет NL.

2.4.
select{+ Explain,  use_nl(t1) } count(*)
from t1, t2
where t1.a=t2.a and t1.b=t2.b

DIRECTIVES FOLLOWED: 
EXPLAIN 
USE_NL ( t1 )
DIRECTIVES NOT FOLLOWED: 

Estimated Cost: 27663
Estimated # of Rows Returned: 1

  1) informix.t2: SEQUENTIAL SCAN

  2) informix.t1: INDEX PATH

    (1) Index Keys: a b   (Serial, fragments: ALL)
        Lower Index Filter: (informix.t1.a = informix.t2.a AND informix.t1.b = informix.t2.b ) 
NESTED LOOP JOIN

277500 rows -  2.5  sec.

2.5.  OPTCOMPIND      0 

select{+ Explain} count(*)
from t1, t2
where t1.a=t2.a and t1.b=t2.b

DIRECTIVES FOLLOWED: 
EXPLAIN 
DIRECTIVES NOT FOLLOWED: 

Estimated Cost: 27663
Estimated # of Rows Returned: 1

  1) informix.t2: SEQUENTIAL SCAN

  2) informix.t1: INDEX PATH

    (1) Index Keys: a b   (Serial, fragments: ALL)
        Lower Index Filter: (informix.t1.a = informix.t2.a AND informix.t1.b = informix.t2.b ) 
NESTED LOOP JOIN

277500 rows -  2.5  sec.!!!!!!!!!!!!!


amd64 3000+, 512Mb, ide-hdd
winxp prof sp1
IDS 9.30.TC3

BUFFERS         2000
MAX_PDQPRIORITY 100
DS_MAX_QUERIES  32
DS_TOTAL_MEMORY 4096
DS_MAX_SCANS    1048576
OPTCOMPIND      2