the difference should not be that big ...



first query - 15 sec

select distinct
id_cont_debit as id_repartitor
from articol_doc
inner join document on document.id_document=articol_doc.id_document
inner join cont on cont.id_cont=articol_doc.id_cont_debit
where
(cont.repartitor is not null) and
(cont.repartitor = 1) and
(document.id_agent_economic = 253)



PLAN SORT (JOIN (CONT NATURAL, DOCUMENT INDEX
(FK_DOCUMENT_REFERENCE_AGENT_EC), ARTICOL_DOC INDEX
(FK_ARTICOL__REFERENCE_CONT1, FK_ARTICOL__REFERENCE_DOCUMENT))):
PLAN SORT (JOIN (CONT NATURAL, DOCUMENT INDEX
(FK_DOCUMENT_REFERENCE_AGENT_EC), ARTICOL_DOC INDEX
(FK_ARTICOL__REFERENCE_CONT1, FK_ARTICOL__REFERENCE_DOCUMENT)))

Adapted plan:
PLAN SORT (JOIN (CONT NATURAL, DOCUMENT INDEX
(FK_DOCUMENT_REFERENCE_AGENT_EC), ARTICOL_DOC INDEX
(FK_ARTICOL__REFERENCE_CONT1, FK_ARTICOL__REFERENCE_DOCUMENT)))



second query - exactly 500 ms

select distinct
id_cont as id_repartitor
from cont
where
(cont.repartitor is not null) and
(cont.repartitor = 1) and
exists(
select articol_doc.id_cont_debit
from articol_doc
where
articol_doc.id_cont_debit = cont.id_cont and
exists(
select id_document
from document
where
(document.id_agent_economic = 253) and
document.id_document = articol_doc.id_document
)
)


PLAN (DOCUMENT INDEX (PK_DOCUMENT))
PLAN (ARTICOL_DOC INDEX (FK_ARTICOL__REFERENCE_CONT1))
PLAN SORT ((CONT NATURAL)):
PLAN (DOCUMENT INDEX (PK_DOCUMENT))
PLAN (ARTICOL_DOC INDEX (FK_ARTICOL__REFERENCE_CONT1))
PLAN SORT ((CONT NATURAL))

Adapted plan:
PLAN (DOCUMENT INDEX (PK_DOCUMENT))
PLAN (ARTICOL_DOC INDEX (FK_ARTICOL__REFERENCE_CONT1))
PLAN SORT ((CONT NATURAL))