db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Paulo Jesus" <paulo.je...@mail.telepac.pt>
Subject Question on query optimization
Date Fri, 04 May 2007 17:09:47 GMT
Hi,

I have this query

SELECT J.i_id,J.i_thumbnail from item J where J.i_id in ((SELECT
i_related1 FROM item WHERE i_id=3556) union (SELECT i_related2 FROM
item WHERE i_id=3556) union (SELECT i_related3 FROM item WHERE
i_id=3556) union (SELECT i_related4 FROM item WHERE i_id=3556) union
(SELECT i_related5 FROM item WHERE i_id=3556));


and Derby is making a table scan over the table. You can see below the
execution plan and the list of indexes on the table.
Can I avoid this table scan by changing configuration parameters or
using hints ?
If there is no chance, does anybody see any other possibility on
rewriting this query
besides those presented below ?

rs = SELECT i_related1,i_related2,i_related3,i_related4 FROM item
WHERE i_id=3556
SELECT J.i_id,J.i_thumbnail from item J where J.i_id in (rs)

or

tmp_table = SELECT i_related1 as col FROM item WHERE i_id=3556 union
SELECT i_related2 as col FROM item WHERE i_id=3556 ...
SELECT J.i_id,J.i_thumbnail from item, tmp_table where i_id = col;
(Column i_id has a unique index thus guaranteeing that the inner joint
does not change the result set cardinality.)



Query execution plan.

...
        End Subquery Number 0
Project-Restrict ResultSet (2):
Number of opens = 1
Rows seen = 5000
Rows filtered = 4995
restriction = true
projection = false
        constructor time (milliseconds) = 0
        open time (milliseconds) = 0
        next time (milliseconds) = 0
        close time (milliseconds) = 0
        restriction time (milliseconds) = 0
        projection time (milliseconds) = 0
        optimizer estimated row count:         5005.00
        optimizer estimated cost:        15446.58

Source result set:
        Table Scan ResultSet for ITEM at read committed isolation
level using instantaneous share row locking chosen by the optimizer
        Number of opens = 1
        Rows seen = 5000
        Rows filtered = 0
        Fetch Size = 16
                constructor time (milliseconds) = 0
                open time (milliseconds) = 0
                next time (milliseconds) = 0
                close time (milliseconds) = 0
                next time in milliseconds/row = 0

        scan information:
                Bit set of columns fetched={0, 12}
                Number of columns fetched=2
                Number of pages visited=835
                Number of rows qualified=5000
                Number of rows visited=5000
                Scan type=heap
                start position:
null            stop position:
null            qualifiers:
None
                optimizer estimated row count:         5005.00
                optimizer estimated cost:        15446.58
----------------------


ij> show indexes from item;
TABLE_NAME          |COLUMN_NAME         |NON_U&|TYPE|ASC&|CARDINA&|PAGES
----------------------------------------------------------------------------
ITEM                |I_ID                |0     |3   |A   |NULL    |NULL
ITEM                |I_A_ID              |1     |3   |A   |NULL    |NULL
ITEM                |I_SUBJECT           |1     |3   |A   |NULL    |NULL
ITEM                |I_TITLE             |1     |3   |A   |NULL    |NULL

Mime
View raw message