At 10:01 06/04/2004 +0000, you wrote:

>Hi all.
>It is possible to make this query fully optimizable?
>
>select * from catalog_produse where cod_producator in
>(select cod_producator from catalog_produse where cod_original like
>?parameter)
>
>The PLAN is
>PLAN (CATALOG_PRODUSE INDEX (CATALOG_PRODUSE_IDX3))
>PLAN (CATALOG_PRODUSE NATURAL)
>
>Indexes:
>CATALOG_PRODUSE_IDX2 on cod_original
>CATALOG_PRODUSE_IDX3 on cod_producator
>
>The query is slow on a 50000 rows table, because, I think, the index
>on cod_original is not used.
>Only few rows are returned (average 5 rows)
>
>Thanks in advance
>

Hi Dorin,

The "problem" in your query is the following part:
where cod_original like ?parameter


In a prepared query with like the optimizer could not use an index for this
fields since he is unable to know if the parameter will contain:
"ABC%"
"AB%C"
"%ABC%"

the first two could use an index in a non prepared query, the last one
could not use and index.

if what you want is search by the start of the field, change that part to:

where cod_original starts with ?parameter

this will make the query use the index.

HTH


Alexandre Benson Smith
Development
THOR Software e Comercial Ltda.
Santo Andre - Sao Paulo - Brazil
www.thorsoftware.com.br

----------


---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.631 / Virus Database: 404 - Release Date: 17/03/2004


[Non-text portions of this message have been removed]