Hi Brian,
Regarding the deadlock on SYSTABLE, I used derby.language.logQueryPlan=true to try to see what was going on. The following excerpt are related to SYSTABLES. This might be related to my use of jdbc's Connection.getMetaData. I used "==========" to separate the two main groups of SYSTABLES-related log-stuff. The second group occurs right before the deadlock.
2008-05-18 19:24:52.764 GMT Thread[btpool0-4,5,main] (XID = 8142), (SESSIONID = 2), EXECUTE STATEMENT SYS."getTables" ******* Sort ResultSet:
Number of opens = 1
Rows input = 13
Rows returned = 13
Eliminate duplicates = false
In sorted order = false
Sort information:
Number of rows input=13
Number of rows output=13
Sort type=internal
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 0.24
optimizer estimated cost: 5.71
Source result set:
Project-Restrict ResultSet (20):
Number of opens = 1
Rows seen = 13
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: 0.24
optimizer estimated cost: 5.71
Source result set:
Nested Loop Join ResultSet:
Number of opens = 1
Rows seen from the left = 13
Rows seen from the right = 13
Rows filtered = 0
Rows returned = 13
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 0.24
optimizer estimated cost: 5.71
Left result set:
Nested Loop Exists Join ResultSet:
Number of opens = 1
Rows seen from the left = 13
Rows seen from the right = 13
Rows filtered = 0
Rows returned = 13
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 0.06
optimizer estimated cost: 5.71
Left result set:
Project-Restrict ResultSet (5):
Number of opens = 1
Rows seen = 32
Rows filtered = 19
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: 0.06
optimizer estimated cost: 5.43
Source result set:
Index Row to Base Row ResultSet for SYSTABLES:
Number of opens = 1
Rows seen = 32
Columns accessed from heap = {1, 2, 3}
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 0.06
optimizer estimated cost: 5.43
Index Scan ResultSet for SYSTABLES using index SYSTABLES_INDEX1 at read uncommitted isolation level using share row locking chosen by the optimizer
Number of opens = 1
Rows seen = 32
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, 2}
Number of columns fetched=2
Number of deleted rows visited=8
Number of pages visited=1
Number of rows qualified=32
Number of rows visited=40
Scan type=btree
Tree height=1
start position:
>= on first 1 column(s).
Ordered null semantics on the following columns:
stop position:
>= on first 1 column(s).
Ordered null semantics on the following columns:
qualifiers:
None
optimizer estimated row count: 0.06
optimizer estimated cost: 5.43
Right result set:
Project-Restrict ResultSet (8):
Number of opens = 13
Rows seen = 13
Rows filtered = 0
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: 0.06
optimizer estimated cost: 0.27
Source result set:
Index Row to Base Row ResultSet for SYSSCHEMAS:
Number of opens = 13
Rows seen = 13
Columns accessed from heap = {1}
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 0.06
optimizer estimated cost: 0.27
Index Scan ResultSet for SYSSCHEMAS using index SYSSCHEMAS_INDEX2 at read uncommitted isolation level using share row locking chosen by the optimizer
Number of opens = 13
Rows seen = 13
Rows filtered = 0
Fetch Size = 1
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=All
Number of columns fetched=2
Number of deleted rows visited=0
Number of pages visited=13
Number of rows qualified=13
Number of rows visited=13
Scan type=btree
Tree height=1
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.06
optimizer estimated cost: 0.27
Right result set:
Project-Restrict ResultSet (19):
Number of opens = 13
Rows seen = 52
Rows filtered = 39
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: 0.24
optimizer estimated cost: 0.00
Source result set:
Union ResultSet:
Number of opens = 13
Rows seen from the left = 39
Rows seen from the right = 13
Rows returned = 52
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 0.24
optimizer estimated cost: 0.00
Left result set:
Union ResultSet:
Number of opens = 13
Rows seen from the left = 26
Rows seen from the right = 13
Rows returned = 39
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 0.18
optimizer estimated cost: 0.00
Left result set:
Union ResultSet:
Number of opens = 13
Rows seen from the left = 13
Rows seen from the right = 13
Rows returned = 26
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 0.12
optimizer estimated cost: 0.00
Left result set:
Normalize ResultSet:
Number of opens = 13
Rows seen = 13
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 0.12
optimizer estimated cost: 0.00
Source result set:
Row ResultSet:
Number of opens = 13
Rows returned = 13
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 0.06
optimizer estimated cost: 0.00
Right result set:
Row ResultSet:
Number of opens = 13
Rows returned = 13
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 0.06
optimizer estimated cost: 0.00
Right result set:
Normalize ResultSet:
Number of opens = 13
Rows seen = 13
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 0.18
optimizer estimated cost: 0.00
Source result set:
Row ResultSet:
Number of opens = 13
Rows returned = 13
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 0.06
optimizer estimated cost: 0.00
Right result set:
Normalize ResultSet:
Number of opens = 13
Rows seen = 13
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 0.24
optimizer estimated cost: 0.00
Source result set:
Row ResultSet:
Number of opens = 13
Rows returned = 13
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 0.06
optimizer estimated cost: 0.00
2008-05-18 19:24:52.848 GMT Thread[btpool0-4,5,main] (XID = 8145), (SESSIONID = 2), EXECUTE STATEMENT SYS."getColumns" ******* Sort ResultSet:
Number of opens = 1
Rows input = 6
Rows returned = 6
Eliminate duplicates = false
In sorted order = false
Sort information:
Number of rows input=6
Number of rows output=6
Sort type=internal
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 0.18
optimizer estimated cost: 7.30
Source result set:
Project-Restrict ResultSet (12):
Number of opens = 1
Rows seen = 6
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: 0.18
optimizer estimated cost: 7.30
Source result set:
Nested Loop Join ResultSet:
Number of opens = 1
Rows seen from the left = 1
Rows seen from the right = 6
Rows filtered = 0
Rows returned = 6
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 0.18
optimizer estimated cost: 7.30
Left result set:
Nested Loop Join ResultSet:
Number of opens = 1
Rows seen from the left = 15
Rows seen from the right = 1
Rows filtered = 0
Rows returned = 1
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 0.05
optimizer estimated cost: 6.22
Left result set:
Project-Restrict ResultSet (5):
Number of opens = 1
Rows seen = 15
Rows filtered = 0
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: 0.43
optimizer estimated cost: 4.99
Source result set:
Index Row to Base Row ResultSet for SYSSCHEMAS:
Number of opens = 1
Rows seen = 15
Columns accessed from heap = {0, 1}
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 0.43
optimizer estimated cost: 4.99
Index Scan ResultSet for SYSSCHEMAS using index SYSSCHEMAS_INDEX1 at read uncommitted isolation level using share row locking chosen by the optimizer
Number of opens = 1
Rows seen = 15
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=All
Number of columns fetched=2
Number of deleted rows visited=12
Number of pages visited=1
Number of rows qualified=15
Number of rows visited=27
Scan type=btree
Tree height=1
start position:
>= on first 1 column(s).
Ordered null semantics on the following columns:
stop position:
>= on first 1 column(s).
Ordered null semantics on the following columns:
qualifiers:
None
optimizer estimated row count: 0.43
optimizer estimated cost: 4.99
Right result set:
Project-Restrict ResultSet (8):
Number of opens = 15
Rows seen = 13
Rows filtered = 12
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: 0.05
optimizer estimated cost: 1.23
Source result set:
Index Row to Base Row ResultSet for SYSTABLES:
Number of opens = 15
Rows seen = 13
Columns accessed from heap = {0, 1, 3}
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 0.05
optimizer estimated cost: 1.23
Index Scan ResultSet for SYSTABLES using index SYSTABLES_INDEX1 at read uncommitted isolation level using share row locking chosen by the optimizer
Number of opens = 15
Rows seen = 13
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=All
Number of columns fetched=3
Number of deleted rows visited=120
Number of pages visited=15
Number of rows qualified=13
Number of rows visited=330
Scan type=btree
Tree height=1
start position:
>= on first 2 column(s).
Ordered null semantics on the following columns:
1
stop position:
>= on first 1 column(s).
Ordered null semantics on the following columns:
qualifiers:
Column[0][0] Id: 1
Operator: =
Ordered nulls: false
Unknown return value: false
Negate comparison result: false
optimizer estimated row count: 0.05
optimizer estimated cost: 1.23
Right result set:
Project-Restrict ResultSet (11):
Number of opens = 1
Rows seen = 6
Rows filtered = 0
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: 0.18
optimizer estimated cost: 1.08
Source result set:
Index Row to Base Row ResultSet for SYSCOLUMNS:
Number of opens = 1
Rows seen = 6
Columns accessed from heap = {0, 1, 2, 3, 4, 7, 8}
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 0.18
optimizer estimated cost: 1.08
Index Scan ResultSet for SYSCOLUMNS using index SYSCOLUMNS_INDEX1 at read uncommitted isolation level using share row locking chosen by the optimizer
Number of opens = 1
Rows seen = 6
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=All
Number of columns fetched=3
Number of deleted rows visited=0
Number of pages visited=2
Number of rows qualified=6
Number of rows visited=7
Scan type=btree
Tree height=2
start position:
>= on first 2 column(s).
Ordered null semantics on the following columns:
0
stop position:
>= on first 2 column(s).
Ordered null semantics on the following columns:
0
qualifiers:
None
optimizer estimated row count: 0.18
optimizer estimated cost: 1.08
2008-05-18 19:24:52.891 GMT Thread[btpool0-4,5,main] (XID = 8148), (SESSIONID = 3), SELECT CONSTRAINTNAME FROM SYS.SYSCONSTRAINTS ******* Index Scan ResultSet for SYSCONSTRAINTS using index SYSCONSTRAINTS_INDEX2 at read uncommitted isolation level using share row locking chosen by the optimizer
Number of opens = 1
Rows seen = 24
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}
Number of columns fetched=1
Number of deleted rows visited=20
Number of pages visited=3
Number of rows qualified=24
Number of rows visited=44
Scan type=btree
Tree height=-1
start position:
None
stop position:
None
qualifiers:
None
optimizer estimated row count: 43.00
optimizer estimated cost: 61.46
==========================
2008-05-18 19:40:06.154 GMT Thread[btpool0-4,5,main] (XID = 8189), (SESSIONID = 2), EXECUTE STATEMENT SYS."getTables" ******* Sort ResultSet:
Number of opens = 1
Rows input = 13
Rows returned = 13
Eliminate duplicates = false
In sorted order = false
Sort information:
Number of rows input=13
Number of rows output=13
Sort type=internal
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 0.24
optimizer estimated cost: 5.71
Source result set:
Project-Restrict ResultSet (20):
Number of opens = 1
Rows seen = 13
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: 0.24
optimizer estimated cost: 5.71
Source result set:
Nested Loop Join ResultSet:
Number of opens = 1
Rows seen from the left = 13
Rows seen from the right = 13
Rows filtered = 0
Rows returned = 13
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 0.24
optimizer estimated cost: 5.71
Left result set:
Nested Loop Exists Join ResultSet:
Number of opens = 1
Rows seen from the left = 13
Rows seen from the right = 13
Rows filtered = 0
Rows returned = 13
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 0.06
optimizer estimated cost: 5.71
Left result set:
Project-Restrict ResultSet (5):
Number of opens = 1
Rows seen = 32
Rows filtered = 19
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: 0.06
optimizer estimated cost: 5.43
Source result set:
Index Row to Base Row ResultSet for SYSTABLES:
Number of opens = 1
Rows seen = 32
Columns accessed from heap = {1, 2, 3}
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 0.06
optimizer estimated cost: 5.43
Index Scan ResultSet for SYSTABLES using index SYSTABLES_INDEX1 at read uncommitted isolation level using share row locking chosen by the optimizer
Number of opens = 1
Rows seen = 32
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, 2}
Number of columns fetched=2
Number of deleted rows visited=8
Number of pages visited=1
Number of rows qualified=32
Number of rows visited=40
Scan type=btree
Tree height=1
start position:
>= on first 1 column(s).
Ordered null semantics on the following columns:
stop position:
>= on first 1 column(s).
Ordered null semantics on the following columns:
qualifiers:
None
optimizer estimated row count: 0.06
optimizer estimated cost: 5.43
Right result set:
Project-Restrict ResultSet (8):
Number of opens = 13
Rows seen = 13
Rows filtered = 0
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: 0.06
optimizer estimated cost: 0.27
Source result set:
Index Row to Base Row ResultSet for SYSSCHEMAS:
Number of opens = 13
Rows seen = 13
Columns accessed from heap = {1}
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 0.06
optimizer estimated cost: 0.27
Index Scan ResultSet for SYSSCHEMAS using index SYSSCHEMAS_INDEX2 at read uncommitted isolation level using share row locking chosen by the optimizer
Number of opens = 13
Rows seen = 13
Rows filtered = 0
Fetch Size = 1
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=All
Number of columns fetched=2
Number of deleted rows visited=0
Number of pages visited=13
Number of rows qualified=13
Number of rows visited=13
Scan type=btree
Tree height=1
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.06
optimizer estimated cost: 0.27
Right result set:
Project-Restrict ResultSet (19):
Number of opens = 13
Rows seen = 52
Rows filtered = 39
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: 0.24
optimizer estimated cost: 0.00
Source result set:
Union ResultSet:
Number of opens = 13
Rows seen from the left = 39
Rows seen from the right = 13
Rows returned = 52
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 0.24
optimizer estimated cost: 0.00
Left result set:
Union ResultSet:
Number of opens = 13
Rows seen from the left = 26
Rows seen from the right = 13
Rows returned = 39
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 0.18
optimizer estimated cost: 0.00
Left result set:
Union ResultSet:
Number of opens = 13
Rows seen from the left = 13
Rows seen from the right = 13
Rows returned = 26
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 0.12
optimizer estimated cost: 0.00
Left result set:
Normalize ResultSet:
Number of opens = 13
Rows seen = 13
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 0.12
optimizer estimated cost: 0.00
Source result set:
Row ResultSet:
Number of opens = 13
Rows returned = 13
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 0.06
optimizer estimated cost: 0.00
Right result set:
Row ResultSet:
Number of opens = 13
Rows returned = 13
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 0.06
optimizer estimated cost: 0.00
Right result set:
Normalize ResultSet:
Number of opens = 13
Rows seen = 13
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 0.18
optimizer estimated cost: 0.00
Source result set:
Row ResultSet:
Number of opens = 13
Rows returned = 13
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 0.06
optimizer estimated cost: 0.00
Right result set:
Normalize ResultSet:
Number of opens = 13
Rows seen = 13
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 0.24
optimizer estimated cost: 0.00
Source result set:
Row ResultSet:
Number of opens = 13
Rows returned = 13
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 0.06
optimizer estimated cost: 0.00
2008-05-18 19:40:06.201 GMT Thread[btpool0-4,5,main] (XID = 8191), (SESSIONID = 2), EXECUTE STATEMENT SYS."getColumns" ******* Sort ResultSet:
Number of opens = 1
Rows input = 6
Rows returned = 6
Eliminate duplicates = false
In sorted order = false
Sort information:
Number of rows input=6
Number of rows output=6
Sort type=internal
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 0.18
optimizer estimated cost: 7.30
Source result set:
Project-Restrict ResultSet (12):
Number of opens = 1
Rows seen = 6
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: 0.18
optimizer estimated cost: 7.30
Source result set:
Nested Loop Join ResultSet:
Number of opens = 1
Rows seen from the left = 1
Rows seen from the right = 6
Rows filtered = 0
Rows returned = 6
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 0.18
optimizer estimated cost: 7.30
Left result set:
Nested Loop Join ResultSet:
Number of opens = 1
Rows seen from the left = 15
Rows seen from the right = 1
Rows filtered = 0
Rows returned = 1
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 0.05
optimizer estimated cost: 6.22
Left result set:
Project-Restrict ResultSet (5):
Number of opens = 1
Rows seen = 15
Rows filtered = 0
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: 0.43
optimizer estimated cost: 4.99
Source result set:
Index Row to Base Row ResultSet for SYSSCHEMAS:
Number of opens = 1
Rows seen = 15
Columns accessed from heap = {0, 1}
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 0.43
optimizer estimated cost: 4.99
Index Scan ResultSet for SYSSCHEMAS using index SYSSCHEMAS_INDEX1 at read uncommitted isolation level using share row locking chosen by the optimizer
Number of opens = 1
Rows seen = 15
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=All
Number of columns fetched=2
Number of deleted rows visited=12
Number of pages visited=1
Number of rows qualified=15
Number of rows visited=27
Scan type=btree
Tree height=1
start position:
>= on first 1 column(s).
Ordered null semantics on the following columns:
stop position:
>= on first 1 column(s).
Ordered null semantics on the following columns:
qualifiers:
None
optimizer estimated row count: 0.43
optimizer estimated cost: 4.99
Right result set:
Project-Restrict ResultSet (8):
Number of opens = 15
Rows seen = 13
Rows filtered = 12
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: 0.05
optimizer estimated cost: 1.23
Source result set:
Index Row to Base Row ResultSet for SYSTABLES:
Number of opens = 15
Rows seen = 13
Columns accessed from heap = {0, 1, 3}
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 0.05
optimizer estimated cost: 1.23
Index Scan ResultSet for SYSTABLES using index SYSTABLES_INDEX1 at read uncommitted isolation level using share row locking chosen by the optimizer
Number of opens = 15
Rows seen = 13
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=All
Number of columns fetched=3
Number of deleted rows visited=120
Number of pages visited=15
Number of rows qualified=13
Number of rows visited=330
Scan type=btree
Tree height=1
start position:
>= on first 2 column(s).
Ordered null semantics on the following columns:
1
stop position:
>= on first 1 column(s).
Ordered null semantics on the following columns:
qualifiers:
Column[0][0] Id: 1
Operator: =
Ordered nulls: false
Unknown return value: false
Negate comparison result: false
optimizer estimated row count: 0.05
optimizer estimated cost: 1.23
Right result set:
Project-Restrict ResultSet (11):
Number of opens = 1
Rows seen = 6
Rows filtered = 0
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: 0.18
optimizer estimated cost: 1.08
Source result set:
Index Row to Base Row ResultSet for SYSCOLUMNS:
Number of opens = 1
Rows seen = 6
Columns accessed from heap = {0, 1, 2, 3, 4, 7, 8}
constructor time (milliseconds) = 0
open time (milliseconds) = 0
next time (milliseconds) = 0
close time (milliseconds) = 0
optimizer estimated row count: 0.18
optimizer estimated cost: 1.08
Index Scan ResultSet for SYSCOLUMNS using index SYSCOLUMNS_INDEX1 at read uncommitted isolation level using share row locking chosen by the optimizer
Number of opens = 1
Rows seen = 6
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=All
Number of columns fetched=3
Number of deleted rows visited=0
Number of pages visited=3
Number of rows qualified=6
Number of rows visited=7
Scan type=btree
Tree height=2
start position:
>= on first 2 column(s).
Ordered null semantics on the following columns:
0
stop position:
>= on first 2 column(s).
Ordered null semantics on the following columns:
0
qualifiers:
None
optimizer estimated row count: 0.18
optimizer estimated cost: 1.08
2008-05-18 19:40:06.219 GMT Thread[btpool0-4,5,main] (XID = 8193), (SESSIONID = 3), SELECT CONSTRAINTNAME FROM SYS.SYSCONSTRAINTS ******* Index Scan ResultSet for SYSCONSTRAINTS using index SYSCONSTRAINTS_INDEX2 at read uncommitted isolation level using share row locking chosen by the optimizer
Number of opens = 1
Rows seen = 24
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}
Number of columns fetched=1
Number of deleted rows visited=24
Number of pages visited=3
Number of rows qualified=24
Number of rows visited=48
Scan type=btree
Tree height=-1
start position:
None
stop position:
None
qualifiers:
None
optimizer estimated row count: 26.00
optimizer estimated cost: 59.49