I need some help. I have a database that is old and has been through multiple
upgrades of Derby. Now we are using Derby 10.9.1.0.
I have the following query which is not using an index that it should and is
instead using a table scan. Here is the query:
SELECT * FROM CORE_V1.CONFIGURATION_BUNDLE T1,
PKG_9145E_V1.COSEDDROPPROFILEDSCPTABLEBUNDLE_COSEDDROPPROFILEDSCPTABLEENTRY t0
WHERE t1.ID = t0.COSEDDROPPROFILEDSCPTABLEBUNDLE_ID and
t0.COSEDDROPPROFILEDSCPTABLEENTRY_ID = 12344444
Here are the table definitions:
CREATE TABLE "CORE_V1"."CONFIGURATION_BUNDLE"
(
DTYPE varchar(64),
OPLOCK int DEFAULT 0 NOT NULL,
BUNDLE_NAME varchar(64) NOT NULL,
ID int PRIMARY KEY NOT NULL
)
;
CREATE INDEX CONFIGURATION_BUNDLE_IX_2 ON
"CORE_V1"."CONFIGURATION_BUNDLE"(BUNDLE_NAME)
;
CREATE INDEX CONFIGURATION_BUNDLE_IX_1 ON
"CORE_V1"."CONFIGURATION_BUNDLE"(DTYPE)
;
CREATE TABLE
"PKG_9145E_V1"."COSEDDROPPROFILEDSCPTABLEBUNDLE_COSEDDROPPROFILEDSCPTABLEENTRY"
(
COSEDDROPPROFILEDSCPTABLEBUNDLE_ID int NOT NULL,
COSEDDROPPROFILEDSCPTABLEENTRY_ID int NOT NULL,
CONSTRAINT COSEDDROPPROFILEDSCPTABLEBUNDLE_COSEDDROPPROFILEDSCPTABLEENTRY_PK
PRIMARY KEY
(COSEDDROPPROFILEDSCPTABLEBUNDLE_ID,COSEDDROPPROFILEDSCPTABLEENTRY_ID)
)
;
ALTER TABLE
"PKG_9145E_V1"."COSEDDROPPROFILEDSCPTABLEBUNDLE_COSEDDROPPROFILEDSCPTABLEENTRY"
ADD CONSTRAINT
COSEDDROPPROFILEDSCPTABLEBUNDLE_COSEDDROPPROFILEDSCPTABLEENTRY_FK_1
FOREIGN KEY (COSEDDROPPROFILEDSCPTABLEBUNDLE_ID)
REFERENCES "CORE_V1"."CONFIGURATION_BUNDLE"(ID)
;
ALTER TABLE
"PKG_9145E_V1"."COSEDDROPPROFILEDSCPTABLEBUNDLE_COSEDDROPPROFILEDSCPTABLEENTRY"
ADD CONSTRAINT
COSEDDROPPROFILEDSCPTABLEBUNDLE_COSEDDROPPROFILEDSCPTABLEENTRY_FK_2
FOREIGN KEY (COSEDDROPPROFILEDSCPTABLEENTRY_ID)
REFERENCES "PKG_9145E_V1"."COS_ED_DROP_PROFILE_DSCP_QMAPPING"(ID)
;
The count of the values in the table are:
SELECT COUNT(*) FROM CORE_V1.CONFIGURATION_BUNDLE;
7003481
SELECT COUNT(*) FROM
PKG_9145E_V1.COSEDDROPPROFILEDSCPTABLEBUNDLE_COSEDDROPPROFILEDSCPTABLEENTRY
58248128
When I run the above query, it uses the correct index on
PKG_9145E_V1.COSEDDROPPROFILEDSCPTABLEBUNDLE_COSEDDROPPROFILEDSCPTABLEENTRY
(the backing index for the
COSEDDROPPROFILEDSCPTABLEBUNDLE_COSEDDROPPROFILEDSCPTABLEENTRY_FK_2 constraint)
but then when it queries CORE_V1.CONFIGURATION_BUNDLE, it does a table scan and
does not use the primary key index so it iterates over 7 million records. If I
force the query to use the primary key index using the optimizer overrides like:
SELECT * FROM CORE_V1.CONFIGURATION_BUNDLE T1 --DERBY-PROPERTIES
index=SQL100922215131580
,
PKG_9145E_V1.COSEDDROPPROFILEDSCPTABLEBUNDLE_COSEDDROPPROFILEDSCPTABLEENTRY t0
WHERE t1.ID = t0.COSEDDROPPROFILEDSCPTABLEBUNDLE_ID and
t0.COSEDDROPPROFILEDSCPTABLEENTRY_ID = 12344444;
Then the query is instantaneous as expected. Below is the execution plan.
Any idea on how to get the Derby to pick the correct plan as the query is
generated from JPA and I cannot put in optimizer overrides. I have tried
using "syscs_util.syscs_drop_statistics" and
"syscs_util.syscs_update_statistics" and also tried
"syscs_util.syscs_compress_table", just trying to get Derby to use the correct
index.
Execution plan:
Statement Name:
SQL_CURLH000C6
Statement Text:
select * FROM
PKG_9145E_V1.COSEDDROPPROFILEDSCPTABLEBUNDLE_COSEDDROPPROFILEDSCPTABLEENTRY t0
JOIN CORE_V1.CONFIGURATION_BUNDLE t1 on t0.COSEDDROPPROFILEDSCPTABLEBUNDLE_ID =
t1.ID
and t0.COSEDDROPPROFILEDSCPTABLEENTRY_ID = 12344444
Parse Time: 1
Bind Time: 2
Optimize Time: 5
Generate Time: 3
Compile Time: 11
Execute Time: 9964
Begin Compilation Timestamp : 2013-08-20 20:15:49.296
End Compilation Timestamp : 2013-08-20 20:15:49.307
Begin Execution Timestamp : 2013-08-20 20:23:05.544
End Execution Timestamp : 2013-08-20 20:23:15.51
Statement Execution Plan Text:
Nested Loop Join ResultSet:
Number of opens = 1
Rows seen from the left = 1
Rows seen from the right = 1
Rows filtered = 0
Rows returned = 1
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 9964
close time (milliseconds) = 0
optimizer estimated row count: 0.00
optimizer estimated cost: 6.29
Left result set:
Index Row to Base Row ResultSet for
COSEDDROPPROFILEDSCPTABLEBUNDLE_COSEDDROPPROFILEDSCPTABLEENTRY:
Number of opens = 1
Rows seen = 1
Columns accessed from heap = {0, 1}
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 1
optimizer estimated row count: 0.00
optimizer estimated cost: 6.29
Index Scan ResultSet for
COSEDDROPPROFILEDSCPTABLEBUNDLE_COSEDDROPPROFILEDSCPTABLEENTRY using constraint
COSEDDROPPROFILEDSCPTABLEBUNDLE_COSEDDROPPROFILEDSCPTABLEENTRY_FK_2 at read
committed isolation level using instantaneous share row locking chosen by the
optimizer
Number of opens = 1
Rows seen = 1
Rows filtered = 0
Fetch Size = 16
constructor time (milliseconds)
= 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 1
next time in milliseconds/row = 0
scan information:
Bit set of columns fetched=All
Number of columns fetched=2
Number of deleted rows visited=0
Number of pages visited=4
Number of rows qualified=1
Number of rows visited=2
Scan type=btree
Tree height=4
start position:
>= on first 1
column(s).
Ordered null
semantics on the following columns:
0
stop position:
> on first 1
column(s).
Ordered null
semantics on the following columns:
0
qualifiers:
None
optimizer estimated row count:
0.00
optimizer estimated cost: 6.29
Right result set:
Table Scan ResultSet for CONFIGURATION_BUNDLE at read committed
isolation level using instantaneous share row locking chosen by the optimizer
Number of opens = 1
Rows seen = 1
Rows filtered = 0
Fetch Size = 16
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 9964
close time (milliseconds) = 0
next time in milliseconds/row = 9964
scan information:
Bit set of columns fetched=All
Number of columns fetched=4
Number of pages visited=197281
Number of rows qualified=1
Number of rows visited=7003481
Scan type=heap
start position:
null
stop position:
null
qualifiers:
Column[0][0] Id: 3
Operator: =
Ordered nulls: false
Unknown return value: false
Negate comparison result: false
optimizer estimated row count: 0.00
optimizer estimated cost: 0.00
