Hi all!
I see that nobody answer my question... I'm sorry but I would like to know
if the problem has no solution or if I don't explain enough the situation.
Are there ways to filter data in a table with a variable filter using
correctly indices?
Thanks!

Gabry


----- Messaggio originale -----
Da: gabry_a73 <gabry_a73@...>
A: firebird-support@yahoogroups.com
Inviato: Martedì 8 settembre 2009, 15:13:59
Oggetto: [firebird-support] Coalesce and OR condition didn't use indices... other ways?

Hi all,

I have a procedure that should filter the records in a table, but it's too slow. In this moment I use FB 2.0.3, but I've made test on 2.1 and 2.5 (beta 2) too.

my table contains about 30 fields (some of these are under index), for
600.000 records and I would filter for one or more field, but the query doesn't use indices. I've made a small example to explain the problem:

I create 'mytable' and some indices:

CREATE TABLE mytable (
idmytable INTEGER NOT NULL,
field_a VARCHAR(10),
field_b VARCHAR(10))
^

ALTER TABLE mytable ADD CONSTRAINT pk_mytable PRIMARY KEY (idmytable)
^
CREATE UNIQUE INDEX mytable_idx1 ON mytable (field_a);
^
CREATE INDEX mytable_idx2 ON mytable (field_b);
^

I insert some record:


INSERT INTO mytable (idmytable, field_a, field_b)
VALUES (1, 'A', 'X');
^
INSERT INTO mytable (idmytable, field_a, field_b)
VALUES (2, 'B', 'X');
^
INSERT INTO mytable (idmytable, field_a, field_b)
VALUES (3, 'C', 'X');
^
INSERT INTO mytable (idmytable, field_a, field_b)
VALUES (4, 'D', 'Y');
^
INSERT INTO mytable (idmytable, field_a, field_b)
VALUES (5, 'E', 'Y');
^

now I create the procedure to filter on mytable: the procedure accepts a filter type that could be 0 (=> coalesce), 1 (=> OR condition) or 2 (=> cursor), so I can see the difference between these way to filter.

CREATE OR ALTER PROCEDURE filter_on_mytable (
i_filtertype INTEGER,
i_field_a VARCHAR(10),
i_field_b VARCHAR(20))
RETURNS (
idmytable INTEGER)
AS

DECLARE filter_a CURSOR FOR (
SELECT t.idmytable
FROM mytable t
WHERE t.field_a = :i_field_a);

DECLARE filter_b CURSOR FOR (
SELECT t.idmytable
FROM mytable t
WHERE t.field_b = :i_field_b);

BEGIN
IF (i_filtertype = 0) THEN
BEGIN
/* try with coalesce */
FOR SELECT t.idmytable
FROM mytable t
WHERE t.field_a = COALESCE(:i_field_a, t.field_a)
AND t.field_b = COALESCE(:i_field_b, t.field_b)
INTO :idmytable DO
BEGIN
SUSPEND;
END
END


IF (i_filtertype = 1) THEN
BEGIN
/* try with OR */
FOR SELECT t.idmytable
FROM mytable t
WHERE (t.field_a = :i_field_a OR :i_field_a IS NULL)
AND (t.field_b = :i_field_b OR :i_field_b IS NULL)
INTO :idmytable DO
BEGIN
SUSPEND;
END
END


IF (i_filtertype = 2) THEN
BEGIN
/* try with cursor */
IF (i_field_a IS NOT NULL) THEN
OPEN filter_a;
ELSE
OPEN filter_b;

WHILE (0=0) DO
BEGIN
IF (i_field_a IS NOT NULL) THEN
FETCH filter_a INTO :idmytable;
ELSE
FETCH filter_b INTO :idmytable;

IF (ROW_COUNT = 0) THEN
BEGIN
IF (i_field_a IS NOT NULL) THEN
CLOSE filter_a;
ELSE
CLOSE filter_b;

LEAVE;
END
ELSE
BEGIN
SUSPEND;
END
END
END
END
^


the query:

select *
from filter_on_mytable (0, 'C', null)

doesn't use indices, and I can see that fetches 5 records. The same result for

select *
from filter_on_mytable (1, 'C', null)

Now I try with

select *
from filter_on_mytable (2, 'C', null)

and I see that uses the index, and fetches 1 record. The problem is that I cannot use this way, because I have many fields in my filter, at least 10!
I know that there are problems in the plan when I use "OR" or "COALESCE", but, are there other way to "build" my where condition according to the input parameters?

thanks in advance!

Gabry