On 8/21/13 6:46 AM, Bergquist, Brett wrote:
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.
Hi Brett,
ForeignTableVTI may be a quicker way to copy the data into another Derby
database:
http://db.apache.org/derby/docs/10.10/publishedapi/jdbc4/org/apache/derby/vti/ForeignTableVTI.html
Hope this helps,
-Rick
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<[email protected]> 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"<[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
