Hi Bryan and all, On Sat, 17 Apr 2010, Bryan Pendleton wrote:
> > I think this could be a case where the query optimizer could use some work? > > Is it useful for me to file a bug report somewhere, e.g. in JIRA? > > Yes, it would be particularly helpful if you could provide a complete > standalone test case which demonstrates the problem. I believe I have done so; the link that I provided in my email to the sources of the test case from Subversion should run on any Unix system with Bash, Sun Java and the Derby libraries available. It has four source files (a shell script and three SQL scripts) and a copy of the Derby database to save you developers the time of reloading the fixture data used in the test. > If that's not possible, then perhaps you could gather query plan output > and post that; perhaps the Derby optimizer is choosing a particularly > poor query plan. OK, this is interesting. With the default JVM settings, it crashes with OOM before outputting the query plan. However, with -Xmx1024m, it finishes in reasonable time, and does output the plan, which I've attached. I guess that means that the optimiser is just taking a lot of memory to optimise the query, and it spends forever in GC before finally hitting OOM and giving up when using the default settings? Does this bear looking into? Should I file a JIRA issue? Also the recommended page at (http://wiki.apache.org/db-derby/PerformanceDiagnosisTips) contains a broken link to "Working with Derby properties" (http://db.apache.org/derby/docs/dev/tuning/ctunsetprop34818.html) which is currently 404. Cheers, Chris. -- Aptivate | http://www.aptivate.org | Phone: +44 1223 760887 The Humanitarian Centre, Fenner's, Gresham Road, Cambridge CB1 2ES Aptivate is a not-for-profit company registered in England and Wales with company number 04980791.
2010-04-17 16:35:00.914 GMT Thread[main,5,main] (XID = 158237), (SESSIONID =
1), -- recreate 'O' records for completed outgoing lines
insert into movement_complete
(move_site_id, owner_code, request_site_id,
request_line_id, is_deleted, record_version)
select
-- can't just use move_site_id, as the first movement is special and
-- has the same move_site_id for both sides, so we'd count O records twice
-- (once for customer promise, once for the real dispatch) and the lines
-- would never balance when they're supposed to and vice versa.
CASE WHEN MX.owner_code = 'O'
THEN LOX.parent_site_id
ELSE LDX.parent_site_id
END AS mx_move_site_id,
'O' AS owner_code,
RL.request_site_id,
RL.id,
0 AS is_deleted,
0 AS record_version
-- SUM(CASE MX.owner_code WHEN 'D' THEN MX.ltu_qty ELSE 0 END) AS qty_in,
-- SUM(CASE MX.owner_code WHEN 'O' THEN MX.ltu_qty ELSE 0 END) AS qty_out,
-- MI.id AS mi_id,
-- MO.id AS mo_id,
-- MX.id AS mx_id,
-- MX.owner_code AS mx_owner, MX.ltu_qty,
-- JI.destination_location_site_id AS idlsid,
-- JI.destination_location_id AS idlid,
-- LOX.location_site_id AS xolsid, LOX.id AS xolid, LOX.parent_site_id AS
xopsid,
-- LDX.location_site_id AS xdlsid, LDX.id AS xdlid, LDX.parent_site_id AS
xdpsid
from request_line as RL
left join movement as MI -- Movement In
on MI.request_site_id = RL.request_site_id
and MI.request_line_id = RL.id
-- and MI.move_site_id = 2641
and MI.owner_code = 'D'
-- Shipment for movement In
left join shipment SI
on SI.id = MI.shipment_id
and SI.journey_site_id = MI.journey_site_id
left join journey JI
on JI.id = SI.journey_id
and JI.journey_site_id = SI.journey_site_id
-- MO = Movement Out, may not exist, used to find waybill
left join movement as MO
on MO.request_site_id = RL.request_site_id
and MO.request_line_id = RL.id
and MO.owner_code = 'O'
-- Shipment for movement Out
left join shipment SO
on SO.id = MO.shipment_id
and SO.journey_site_id = MO.journey_site_id
left join journey JO
on JO.id = SO.journey_id
and JO.journey_site_id = SO.journey_site_id
left join location LOO -- movement out origin location
on LOO.id = JO.origin_location_id
and LOO.location_site_id = JO.origin_location_site_id
left join location LOD -- movement out destination location
on LOD.id = JO.destination_location_id
and LOD.location_site_id = JO.destination_location_site_id
inner join movement as MX -- Movement, Any (in and out of site, for SUM)
on MX.request_site_id = RL.request_site_id
and MX.request_line_id = RL.id
inner join shipment SX
on SX.id = MX.shipment_id
and SX.journey_site_id = MX.journey_site_id
inner join journey JX
on JX.id = SX.journey_id
and JX.journey_site_id = SX.journey_site_id
inner join location LOX -- movement via X, origin location
on LOX.id = JX.origin_location_id
and LOX.location_site_id = JX.origin_location_site_id
inner join location LDX -- movement via X, destination location
on LDX.id = JX.destination_location_id
and LDX.location_site_id = JX.destination_location_site_id
where
(
-- Either MX is a move Out from the Destination of the Movement In (MI)
(
MX.owner_code = 'O' AND
JX.origin_location_site_id = JI.destination_location_site_id AND
JX.origin_location_id = JI.destination_location_id
)
-- Or MX is a move In to the Destination of the Movement In (MI)
OR
(
MX.owner_code = 'D' AND
JX.destination_location_site_id = JI.destination_location_site_id AND
JX.destination_location_id = JI.destination_location_id
)
-- Or MX is a move Out from the Origin of the Movement Out (MO)
OR
(
MX.owner_code = 'O' AND
JX.origin_location_site_id = JO.origin_location_site_id AND
JX.origin_location_id = JO.origin_location_id
)
-- Or MX is a move In to the Origin of the Movement Out (MO)
OR
(
MX.owner_code = 'D' AND
JX.destination_location_site_id = JO.origin_location_site_id AND
JX.destination_location_id = JO.origin_location_id
)
)
-- When detecting in-progress lines, exclude MX out from a customer
-- location, as there is no movement into it. When detecting completed
-- lines, instead pretend that it balances, and include it.
-- AND (MX.owner_code <> "O" OR LOX.location_type_code IN ('S','W'))
-- AND RL.request_site_id = 2641
-- AND RL.id = 35501
group by
-- mx_move_site_id -- cannot group by a SELECT column on Derby,
-- so we need to repeat the expression?
CASE WHEN MX.owner_code = 'O'
THEN LOX.parent_site_id
ELSE LDX.parent_site_id
END,
-- MI.owner_code,
RL.request_site_id, -- MI.request_site_id,
RL.id -- MI.request_line_id
having
-- When detecting in-progress lines, exclude MX out from a customer
-- location, as there is no movement into it. When detecting completed
-- lines, zero them instead, to ensure that the line balances.
(
SUM(CASE WHEN MX.owner_code = 'D' THEN MX.ltu_qty ELSE 0 END)
=
SUM(
CASE
WHEN MX.owner_code = 'O'
-- AND LOX.location_type_code NOT IN ('S','W')
THEN MX.ltu_qty
ELSE 0
END)
)
-- AND MI.request_site_id = 12
-- AND MI.request_line_id = 101
-- limit 10
******* Insert ResultSet using row locking:
deferred: false
insert mode: normal
Rows inserted = 2038
Indexes updated = 2
Execute Time = 0
Normalize ResultSet:
Number of opens = 1
Rows seen = 2038
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 625276676.63
optimizer estimated cost: 84887.91
Source result set:
Project-Restrict ResultSet (32):
Number of opens = 1
Rows seen = 10067
Rows filtered = 8029
restriction = true
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
optimizer estimated row count: 625276676.63
optimizer estimated cost: 84887.91
Source result set:
Grouped Aggregate ResultSet:
Number of opens = 1
Rows input = 191158
Has distinct aggregate = false
In sorted order = false
Sort information:
Number of rows input=191158
Number of rows output=10067
Sort type=internal
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 625276676.63
optimizer estimated cost: 84887.91
Source result set:
Project-Restrict ResultSet (31):
Number of opens = 1
Rows seen = 191158
Rows filtered = 0
restriction = false
projection = true
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
optimizer estimated row count:
625276676.63
optimizer estimated cost:
84887.91
Source result set:
Hash Exists Join ResultSet:
Number of opens = 1
Rows seen from the left = 191158
Rows seen from the right = 191158
Rows filtered = 0
Rows returned = 191158
constructor time (milliseconds)
= 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count:
625276676.63
optimizer estimated cost:
84887.91
Left result set:
Hash Exists Join ResultSet:
Number of opens = 1
Rows seen from the left = 191158
Rows seen from the right =
191158
Rows filtered = 0
Rows returned = 191158
constructor time
(milliseconds) = 0
open time
(milliseconds) = 0
next time
(milliseconds) = 0
close time
(milliseconds) = 0
optimizer estimated row
count: 625276676.63
optimizer estimated
cost: 79074.94
Left result set:
Hash Exists Join
ResultSet:
Number of opens = 1
Rows seen from the left
= 274966
Rows seen from the
right = 191158
Rows filtered = 0
Rows returned = 191158
constructor
time (milliseconds) = 0
open time
(milliseconds) = 0
next time
(milliseconds) = 0
close time
(milliseconds) = 0
optimizer
estimated row count: 625276676.63
optimizer
estimated cost: 73261.96
Left result set:
Hash Exists
Join ResultSet:
Number of opens
= 1
Rows seen from
the left = 274966
Rows seen from
the right = 274966
Rows filtered = 0
Rows returned =
274966
constructor time (milliseconds) = 0
open
time (milliseconds) = 0
next
time (milliseconds) = 0
close
time (milliseconds) = 0
optimizer estimated row count: 625276676.63
optimizer estimated cost: 69735.68
Left result set:
Hash
Join ResultSet:
Number
of opens = 1
Rows
seen from the left = 29969
Rows
seen from the right = 274966
Rows
filtered = 0
Rows
returned = 274966
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 625276676.63
optimizer estimated cost: 67775.78
Left
result set:
Hash Left Outer Join ResultSet:
Number of opens = 1
Rows seen from the left = 29969
Rows seen from the right = 29969
Empty right rows returned = 0
Rows filtered = 0
Rows returned = 29969
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 2871666.56
optimizer estimated cost: 51138.40
Left result set:
Hash Left Outer Join ResultSet:
Number of opens = 1
Rows seen from the left = 29969
Rows seen from the right = 29969
Empty right rows returned = 0
Rows filtered = 0
Rows returned = 29969
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 2871666.56
optimizer estimated cost: 48622.82
Left result set:
Hash Left Outer Join ResultSet:
Number of opens = 1
Rows seen from the left = 29969
Rows seen from the right = 29969
Empty right rows returned = 0
Rows filtered = 0
Rows returned = 29969
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 2871666.56
optimizer estimated cost: 46107.24
Left result set:
Hash Left Outer Join ResultSet:
Number of opens = 1
Rows seen from the left = 29969
Rows seen from the right = 29969
Empty right rows returned = 0
Rows filtered = 0
Rows returned = 29969
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 2871666.56
optimizer estimated cost: 42580.96
Left result set:
Hash Left Outer Join ResultSet:
Number of opens = 1
Rows seen from the left = 11537
Rows seen from the right = 29969
Empty right rows returned = 0
Rows filtered = 0
Rows returned = 29969
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count:
2871666.56
optimizer estimated cost:
40621.06
Left result set:
Hash Left Outer Join ResultSet:
Number of opens = 1
Rows seen from the left = 11537
Rows seen from the right = 11537
Empty right rows returned = 0
Rows filtered = 0
Rows returned = 11537
constructor time (milliseconds)
= 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count:
131885.12
optimizer estimated cost:
23983.67
Left result set:
Hash Left Outer Join ResultSet:
Number of opens = 1
Rows seen from the left = 11537
Rows seen from the right = 11537
Empty right rows returned = 0
Rows filtered = 0
Rows returned = 11537
constructor time
(milliseconds) = 0
open time
(milliseconds) = 0
next time
(milliseconds) = 0
close time
(milliseconds) = 0
optimizer estimated row
count: 131885.12
optimizer estimated
cost: 20457.39
Left result set:
Hash Left Outer Join
ResultSet:
Number of opens = 1
Rows seen from the left
= 6051
Rows seen from the
right = 11537
Empty right rows
returned = 0
Rows filtered = 0
Rows returned = 11537
constructor
time (milliseconds) = 0
open time
(milliseconds) = 0
next time
(milliseconds) = 0
close time
(milliseconds) = 0
optimizer
estimated row count: 131885.12
optimizer
estimated cost: 18497.49
Left result set:
Project-Restrict ResultSet (16):
Number of opens
= 1
Rows seen = 6051
Rows filtered = 0
restriction =
false
projection =
true
constructor time (milliseconds) = 0
open
time (milliseconds) = 0
next
time (milliseconds) = 0
close
time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
optimizer estimated row count: 6057.00
optimizer estimated cost: 1860.11
Source result
set:
Index
Scan ResultSet for REQUEST_LINE using constraint PK_REQUEST_LINE at read
committed isolation level using instantaneous share row locking chosen by the
optimizer
Number
of opens = 1
Rows
seen = 6051
Rows
filtered = 0
Fetch
Size = 16
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
next time in milliseconds/row = 0
scan
information:
Bit set of columns fetched={0, 1}
Number of columns fetched=2
Number of deleted rows visited=0
Number of pages visited=64
Number of rows qualified=6051
Number of rows visited=6051
Scan type=btree
Tree height=2
start position:
None
stop position:
None
qualifiers:
None
optimizer estimated row count: 6057.00
optimizer estimated cost: 1860.11
Right result set:
Hash Scan
ResultSet for MOVEMENT at read committed isolation level using instantaneous
share row locking:
Number of opens
= 6051
Hash table size
= 6051
Hash keys are
column numbers (6,7)
Rows seen =
11537
Rows filtered = 0
constructor time (milliseconds) = 0
open
time (milliseconds) = 0
next
time (milliseconds) = 0
close
time (milliseconds) = 0
next
time in milliseconds/row = 0
scan
information:
Bit set
of columns fetched={2, 3, 6, 7, 8}
Number
of columns fetched=5
Number
of pages visited=770
Number
of rows qualified=11537
Number
of rows visited=23074
Scan
type=heap
start
position:
null
stop
position:
null
scan
qualifiers:
Column[0][0] Id: 8
Operator: =
Ordered nulls: false
Unknown return value: false
Negate comparison result: false
next
qualifiers:
Column[0][0] Id: 6
Operator: =
Ordered nulls: false
Unknown return value: false
Negate comparison result: false
Column[0][1] Id: 7
Operator: =
Ordered nulls: false
Unknown return value: false
Negate comparison result: false
optimizer estimated row count: 131885.12
optimizer estimated cost: 16637.38
Right result set:
Hash Scan ResultSet for
SHIPMENT at read committed isolation level using instantaneous share row
locking:
Number of opens = 11537
Hash table size = 4249
Hash keys are column
numbers (0,1)
Rows seen = 11537
Rows filtered = 0
constructor
time (milliseconds) = 0
open time
(milliseconds) = 0
next time
(milliseconds) = 0
close time
(milliseconds) = 0
next time in
milliseconds/row = 0
scan information:
Bit set of
columns fetched={0, 1, 2}
Number of
columns fetched=3
Number of pages
visited=77
Number of rows
qualified=4249
Number of rows
visited=4249
Scan type=heap
start position:
null
stop position:
null
scan qualifiers:
None
next qualifiers:
Column[0][0] Id: 0
Operator: =
Ordered nulls: false
Unknown return value: false
Negate comparison result: false
Column[0][1] Id: 1
Operator: =
Ordered nulls: false
Unknown return value: false
Negate comparison result: false
optimizer
estimated row count: 131885.12
optimizer
estimated cost: 1959.90
Right result set:
Hash Scan ResultSet for JOURNEY
at read committed isolation level using instantaneous share row locking:
Number of opens = 11537
Hash table size = 4249
Hash keys are column numbers
(0,1)
Rows seen = 11537
Rows filtered = 0
constructor time
(milliseconds) = 0
open time
(milliseconds) = 0
next time
(milliseconds) = 0
close time
(milliseconds) = 0
next time in
milliseconds/row = 0
scan information:
Bit set of columns
fetched={0, 1, 7, 8}
Number of columns
fetched=4
Number of pages
visited=165
Number of rows
qualified=4249
Number of rows
visited=4249
Scan type=heap
start position:
null
stop position:
null
scan qualifiers:
None
next qualifiers:
Column[0][0] Id: 0
Operator: =
Ordered nulls: false
Unknown return value: false
Negate comparison result: false
Column[0][1] Id: 1
Operator: =
Ordered nulls: false
Unknown return value: false
Negate comparison result: false
optimizer estimated row
count: 131885.12
optimizer estimated
cost: 3526.28
Right result set:
Hash Scan ResultSet for MOVEMENT at
read committed isolation level using instantaneous share row locking:
Number of opens = 11537
Hash table size = 6051
Hash keys are column numbers (6,7)
Rows seen = 29969
Rows filtered = 0
constructor time (milliseconds)
= 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
next time in milliseconds/row = 0
scan information:
Bit set of columns fetched={2,
3, 6, 7, 8}
Number of columns fetched=5
Number of pages visited=770
Number of rows qualified=11537
Number of rows visited=23074
Scan type=heap
start position:
null
stop position:
null
scan qualifiers:
Column[0][0] Id: 8
Operator: =
Ordered nulls: false
Unknown return value: false
Negate comparison result: false
next qualifiers:
Column[0][0] Id: 6
Operator: =
Ordered nulls: false
Unknown return value: false
Negate comparison result: false
Column[0][1] Id: 7
Operator: =
Ordered nulls: false
Unknown return value: false
Negate comparison result: false
optimizer estimated row count:
2871666.56
optimizer estimated cost:
16637.38
Right result set:
Hash Scan ResultSet for SHIPMENT at read
committed isolation level using instantaneous share row locking:
Number of opens = 29969
Hash table size = 4249
Hash keys are column numbers (0,1)
Rows seen = 29969
Rows filtered = 0
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
next time in milliseconds/row = 0
scan information:
Bit set of columns fetched={0, 1, 2}
Number of columns fetched=3
Number of pages visited=77
Number of rows qualified=4249
Number of rows visited=4249
Scan type=heap
start position:
null
stop position:
null
scan qualifiers:
None
next qualifiers:
Column[0][0] Id: 0
Operator: =
Ordered nulls: false
Unknown return value: false
Negate comparison result: false
Column[0][1] Id: 1
Operator: =
Ordered nulls: false
Unknown return value: false
Negate comparison result: false
optimizer estimated row count:
2871666.56
optimizer estimated cost:
1959.90
Right result set:
Hash Scan ResultSet for JOURNEY at read committed
isolation level using instantaneous share row locking:
Number of opens = 29969
Hash table size = 4249
Hash keys are column numbers (0,1)
Rows seen = 29969
Rows filtered = 0
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
next time in milliseconds/row = 0
scan information:
Bit set of columns fetched={0, 1, 3, 4, 7, 8}
Number of columns fetched=6
Number of pages visited=165
Number of rows qualified=4249
Number of rows visited=4249
Scan type=heap
start position:
null
stop position:
null
scan qualifiers:
None
next qualifiers:
Column[0][0] Id: 0
Operator: =
Ordered nulls: false
Unknown return value: false
Negate comparison result: false
Column[0][1] Id: 1
Operator: =
Ordered nulls: false
Unknown return value: false
Negate comparison result: false
optimizer estimated row count: 2871666.56
optimizer estimated cost: 3526.28
Right result set:
Hash Scan ResultSet for LOCATION using constraint PK_LOCATION
at read committed isolation level using instantaneous share row locking:
Number of opens = 29969
Hash table size = 6633
Hash keys are column numbers (0,1)
Rows seen = 29969
Rows filtered = 0
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
next time in milliseconds/row = 0
scan information:
Bit set of columns fetched={0, 1}
Number of columns fetched=2
Number of deleted rows visited=0
Number of pages visited=90
Number of rows qualified=6633
Number of rows visited=6633
Scan type=btree
Tree height=2
start position:
None
stop position:
None
scan qualifiers:
None
next qualifiers:
Column[0][0] Id: 0
Operator: =
Ordered nulls: false
Unknown return value: false
Negate comparison result: false
Column[0][1] Id: 1
Operator: =
Ordered nulls: false
Unknown return value: false
Negate comparison result: false
optimizer estimated row count: 2871666.56
optimizer estimated cost: 2515.58
Right result set:
Hash Scan ResultSet for LOCATION using constraint PK_LOCATION at read
committed isolation level using instantaneous share row locking:
Number of opens = 29969
Hash table size = 6633
Hash keys are column numbers (0,1)
Rows seen = 29969
Rows filtered = 0
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
next time in milliseconds/row = 0
scan information:
Bit set of columns fetched={0, 1}
Number of columns fetched=2
Number of deleted rows visited=0
Number of pages visited=90
Number of rows qualified=6633
Number of rows visited=6633
Scan type=btree
Tree height=2
start position:
None
stop position:
None
scan qualifiers:
None
next qualifiers:
Column[0][0] Id: 0
Operator: =
Ordered nulls: false
Unknown return value: false
Negate comparison result: false
Column[0][1] Id: 1
Operator: =
Ordered nulls: false
Unknown return value: false
Negate comparison result: false
optimizer estimated row count: 2871666.56
optimizer estimated cost: 2515.58
Right
result set:
Hash Scan ResultSet for MOVEMENT at read committed isolation level using
instantaneous share row locking:
Number of opens = 29969
Hash table size = 6051
Hash keys are column numbers (6,7)
Rows seen = 274966
Rows filtered = 0
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
next time in milliseconds/row = 0
scan information:
Bit set of columns fetched={2, 3, 6, 7, 8, 10}
Number of columns fetched=6
Number of pages visited=770
Number of rows qualified=23074
Number of rows visited=23074
Scan type=heap
start position:
null
stop position:
null
scan qualifiers:
None
next qualifiers:
Column[0][0] Id: 6
Operator: =
Ordered nulls: false
Unknown return value: false
Negate comparison result: false
Column[0][1] Id: 7
Operator: =
Ordered nulls: false
Unknown return value: false
Negate comparison result: false
optimizer estimated row count: 625276676.63
optimizer estimated cost: 16637.38
Right result
set:
Hash
Scan ResultSet for SHIPMENT at read committed isolation level using
instantaneous share row locking:
Number
of opens = 274966
Hash
table size = 4249
Hash
keys are column numbers (0,1)
Rows
seen = 274966
Rows
filtered = 0
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
next time in milliseconds/row = 0
scan
information:
Bit set of columns fetched={0, 1, 2}
Number of columns fetched=3
Number of pages visited=77
Number of rows qualified=4249
Number of rows visited=4249
Scan type=heap
start position:
null
stop position:
null
scan qualifiers:
None
next qualifiers:
Column[0][0] Id: 0
Operator: =
Ordered nulls: false
Unknown return value: false
Negate comparison result: false
Column[0][1] Id: 1
Operator: =
Ordered nulls: false
Unknown return value: false
Negate comparison result: false
optimizer estimated row count: 625276676.63
optimizer estimated cost: 1959.90
Right result set:
Project-Restrict ResultSet (28):
Number of opens
= 274966
Rows seen =
274966
Rows filtered =
83808
restriction =
true
projection =
false
constructor time (milliseconds) = 0
open
time (milliseconds) = 0
next
time (milliseconds) = 0
close
time (milliseconds) = 0
restriction time (milliseconds) = 0
projection time (milliseconds) = 0
optimizer estimated row count: 625276676.63
optimizer estimated cost: 3526.28
Source result
set:
Hash
Scan ResultSet for JOURNEY at read committed isolation level using
instantaneous share row locking:
Number
of opens = 274966
Hash
table size = 4249
Hash
keys are column numbers (0,1)
Rows
seen = 274966
Rows
filtered = 0
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
next time in milliseconds/row = 0
scan
information:
Bit set of columns fetched={0, 1, 3, 4, 7, 8}
Number of columns fetched=6
Number of pages visited=165
Number of rows qualified=4249
Number of rows visited=4249
Scan type=heap
start position:
null
stop position:
null
scan qualifiers:
None
next qualifiers:
Column[0][0] Id: 0
Operator: =
Ordered nulls: false
Unknown return value: false
Negate comparison result: false
Column[0][1] Id: 1
Operator: =
Ordered nulls: false
Unknown return value: false
Negate comparison result: false
optimizer estimated row count: 625276676.63
optimizer estimated cost: 3526.28
Right result set:
Hash Scan ResultSet for
LOCATION at read committed isolation level using instantaneous share row
locking:
Number of opens = 191158
Hash table size = 6633
Hash keys are column
numbers (0,1)
Rows seen = 191158
Rows filtered = 0
constructor
time (milliseconds) = 0
open time
(milliseconds) = 0
next time
(milliseconds) = 0
close time
(milliseconds) = 0
next time in
milliseconds/row = 0
scan information:
Bit set of
columns fetched={0, 1, 4}
Number of
columns fetched=3
Number of pages
visited=37
Number of rows
qualified=6633
Number of rows
visited=6633
Scan type=heap
start position:
null stop position:
null scan qualifiers:
None
next qualifiers:
Column[0][0] Id: 0
Operator: =
Ordered nulls: false
Unknown return value: false
Negate comparison result: false
Column[0][1] Id: 1
Operator: =
Ordered nulls: false
Unknown return value: false
Negate comparison result: false
optimizer
estimated row count: 625276676.63
optimizer
estimated cost: 5812.97
Right result set:
Hash Scan ResultSet for
LOCATION at read committed isolation level using instantaneous share row
locking:
Number of opens = 191158
Hash table size = 6633
Hash keys are column numbers
(0,1)
Rows seen = 191158
Rows filtered = 0
constructor time
(milliseconds) = 0
open time
(milliseconds) = 0
next time
(milliseconds) = 0
close time
(milliseconds) = 0
next time in
milliseconds/row = 0
scan information:
Bit set of columns
fetched={0, 1, 4}
Number of columns
fetched=3
Number of pages
visited=37
Number of rows
qualified=6633
Number of rows
visited=6633
Scan type=heap
start position:
null stop position:
null scan qualifiers:
None
next qualifiers:
Column[0][0] Id: 0
Operator: =
Ordered nulls: false
Unknown return value: false
Negate comparison result: false
Column[0][1] Id: 1
Operator: =
Ordered nulls: false
Unknown return value: false
Negate comparison result: false
optimizer estimated row
count: 625276676.63
optimizer estimated
cost: 5812.97
2010-04-17 16:35:00.991 GMT:
Shutting down instance a816c00e-0128-0c9f-019d-0000003b5678
----------------------------------------------------------------
