I am wondering if the fix for https://issues.apache.org/jira/browse/DERBY-3790 might be causing an issue or maybe I am just losing my mind ;)
As below, the query that I show will not use the primary key index when it should. To try to figure this out I created a new table CORE_V1.CONFIGURATION_BUNDLE2 and copied all of the data into it and made sure to update the statistics. I altered the query: SELECT * FROM CORE_V1.CONFIGURATION_BUNDLE2 T1, PKG_9145E_V1.COSEDDROPPROFILEDSCPTABLEBUNDLE_COSEDDROPPROFILEDSCPTABLEENTRY t0 WHERE t1.ID2 = t0.COSEDDROPPROFILEDSCPTABLEBUNDLE_ID and t0.COSEDDROPPROFILEDSCPTABLEENTRY_ID = 12344444; and this still does a table scan on CORE_V1.CONFIGURATION_BUNDLE2. This makes no sense. The one row returned from PKG_9145E_V1.COSEDDROPPROFILEDSCPTABLEBUNDLE_COSEDDROPPROFILEDSCPTABLEENTRY and the PKG_9145E_V1.COSEDDROPPROFILEDSCPTABLEBUNDLE_COSEDDROPPROFILEDSCPTABLEENTRY. COSEDDROPPROFILEDSCPTABLEBUNDLE_ID value from that row is the unique CORE_V1.CONFIGURATION_BUNDLE2.ID value of the row to look up. Why would Derby decide to do a table scan on CORE_V1.CONFIGURATION_BUNDLE2 in this case? On Aug 20, 2013, at 8:29 PM, "Bergquist, Brett" <[email protected]<mailto:[email protected]>> wrote: 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
