this is the detail :
CREATE TABLE A (
ID INTEGER NOT NULL,
DATEDOC TIMESTAMP NOT NULL,
WHEREDOC VARCHAR(22) NOT NULL
);
ALTER TABLE A ADD CONSTRAINT PK_A PRIMARY KEY (ID);
CREATE INDEX A_IDX1 ON A (DATEDOC);
CREATE INDEX A_IDX2 ON A (WHEREDOC);
CREATE TABLE B (
REFA INTEGER NOT NULL,
NBWORD INTEGER NOT NULL,
REFWROD VARCHAR(50) NOT NULL,
DATEWORD TIMESTAMP NOT NULL
);
CREATE INDEX B_IDX1 ON B (REFA);
CREATE INDEX B_IDX2 ON B (NBWORD);
CREATE INDEX B_IDX3 ON B (REFWROD);
CREATE INDEX B_IDX4 ON B (DATEWORD);
REQUEST :
select * from a inner join b on ID = refa
where a.datedoc >= '01.01.2000' and a.datedoc <= '12.12.2053' and
b.nbword > 4 and a.wheredoc = ''
and b.refwrod = '2' and b.dateword > '01.01.2030'
PLAN RESULT :
PLAN JOIN (B INDEX (B_IDX4, B_IDX2, B_IDX3), A INDEX (PK_A))
SELECTIVITY :
A_IDX1 => 0.2
A_IDX2 => 0.0024333
B_IDX1 => 0.0000999
B_IDX2 => 0.0000999
B_IDX3 => 0.5
B_IDX4 => 0.0000999
Le 13/08/2012 18:54, Dmitry Yemanov a écrit :
> 13.08.2012 19:15, arnaud le roy wrote:
>>
>> it's normal that only the indexes on one table is used during an inner
>> join ?
>>
>> for exemple :
>>
>> table a
>>
>> ID => pk_index
>> DATE => index a_date => selectivity : 0.1
>> NAME => index a_name selectivity :0.1
>>
>> table b :
>>
>> REFIDA => fk_index
>> REFPRODUIT => index b_produit => selectivity : 0.2
>> NAME => index b_name => selectivity : 0.2
>>
>> REQUEST :
>>
>> select name from a inner join b on (REFIDA = ID) WHERE a.DATE >
>> '01.01.2000' and b.name = 'fb_server' and b.refproduit = 'azaza';
>>
>> in the plan analyser only the index of a.date is used (a_date) not the
>> b.name (b_name) and b.refproduit(b_produit) indexes.
>
> Start with showing us the plan.
>
>
> Dmitry
>
>
> ------------------------------------------------------------------------------
> Live Security Virtual Conference
> Exclusive live event will cover all the ways today's security and
> threat landscape has changed and how IT managers can respond. Discussions
> will include endpoint security, mobile security and the latest in malware
> threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/
> Firebird-Devel mailing list, web interface at
> https://lists.sourceforge.net/lists/listinfo/firebird-devel
>
------------------------------------------------------------------------------
Live Security Virtual Conference
Exclusive live event will cover all the ways today's security and
threat landscape has changed and how IT managers can respond. Discussions
will include endpoint security, mobile security and the latest in malware
threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
