db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bergquist, Brett" <BBergqu...@canoga.com>
Subject Re: Is there a way to disable the change made by DERBY-3790
Date Wed, 21 Aug 2013 13:46:46 GMT
I will give that a try Rick, thanks.   Right now, I just created a brand new database with
Derby 10.10.1.1 and am using the export/import system procedures to load the data into this
new database just to make sure that it is not a corrupt database problem.

Just a question,  however, to make sure my expectations are corrects.   Basically there are
two tables here, one containing an unique ID (primary key) with some other data, and the other
containing rows with an unique instance ID and a non-unique ID that refers to the first table.
  

Basically the is the classic orders/line items setup where the orders have a unique ID, the
line items have an unique item ID and non unique order ID (in this table).   The query is
working backward, given a line item ID, return the order that it belongs to.   So should not
an primary key on the order ID in the order table be used in this type of query which is going
to find exactly row in the line times table and from that row, the order ID is obtained which
is the unique value to lookup in the orders table?

On Aug 21, 2013, at 9:13 AM, Rick Hillegas <rick.hillegas@oracle.com> wrote:

> 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