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
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
