db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <rick.hille...@oracle.com>
Subject Re: Is there a way to disable the change made by DERBY-3790
Date Wed, 21 Aug 2013 13:13:10 GMT
On 8/21/13 5:20 AM, Bergquist, Brett wrote:
> 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?
Hi Brett,

You may be able to coax some information out of the optimizer by loading 
the data into a 10.11 (development trunk) database and then using the 
new xml-based optimizer tracing. To get an xml trace of the optimizer's 
reasoning, do the following:

call syscs_util.syscs_register_tool( 'optimizerTracing', true, 'xml' );

-- put  your query here, e.g.:
select * from sys.systables t, sys.syscolumns c
where t.tableid = c.referenceid
and 1=2;

call syscs_util.syscs_register_tool( 'optimizerTracing', false, 
'optimizerTrace.xml' );

This will dump an xml trace of the optimizer's reasoning to the file 
optimizerTrace.xml. I find that these files are easy to read with my 
Firefox browser. The following elements in the trace file may be salient:

o <joinOrder> This element starts out with an ordered list of tables, a 
partial or full join order.

o <decoration> This element is nested under <joinOrder>. It describes a 
candidate conglomerate plus join strategy for a given slot in the join 
order.

o <decConglomerateCost> This element is nested under <decoration>. This 
is probably the key element you are looking for. This element describes 
what the optimizer thinks it will cost to scan that conglomerate at that 
slot in the decorated join order.

o <planCost> This element is nested under <joinOrder>. It describes what 
the optimizer thinks is the cheapest cost of that (partial or full) join 
order.

Hope this helps,
-Rick
>
> 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