db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bergquist, Brett" <BBergqu...@canoga.com>
Subject Is there a way to disable the change made by DERBY-3790
Date Wed, 21 Aug 2013 12:20:43 GMT
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" <BBergquist@canoga.com<mailto:BBergquist@canoga.com>>
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




Mime
View raw message