specjplans.out

derby database source code.good for you.

ij> -- Test prints query plans for SpecjAppServer2001 benchmark queries.
-- Specifically this test checks for plans generated for tables with no rows to 
-- ensure that 
-- Index scan must be chosen over table scan for searched update/delete even when there are 
-- 0 rows in table
-- Also see Beetle task id : 5006
-- Test does the following
-- 1. First creates the necessary schema (tables, indexes)
-- 2. Executes and prints the query plan for all the queries in specjappserver2001 benchmark
--    Makes sure that the insert stmts are in the end to ensure that there are no
--    rows in the tables
-- 3. Drops the tables
-- Let's start with something light...
-- by default, holdability of ResultSet objects created using this Connection object is true. Following will set it to false for this connection.
NoHoldForConnection;
ij> drop table t1;
ERROR 42Y55: 'DROP TABLE' cannot be performed on 'T1' because it does not exist.
ij> create table t1(c1 int, c2 int);
0 rows inserted/updated/deleted
ij> -- create non-unique index first, then unique, to make sure non-unique is looked at first, and so
-- in case of tie, the first (nonunique) would be chosen by the cost-based optimizer.  But we need
-- to make sure such tie never happens, and unique index is always chosen (if the only difference
-- between the two is "uniqueness").  Well the beetle bug 5006 itself is about, first of all,
-- table scan should never be chosen, no matter the index is covering ot not.
create index i11 on t1(c1);
0 rows inserted/updated/deleted
ij> create unique index i12 on t1(c1);
0 rows inserted/updated/deleted
ij> call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
0 rows inserted/updated/deleted
ij> maximumdisplaywidth 3500;
ij> -- In the following statement, optimizer thinks we have a covering index (only referenced column is
-- c1), make sure we are using unique index (I12), not table scan, not I11.
delete from t1 where c1 = 1;
0 rows inserted/updated/deleted
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name: 
	null
Statement Text: 
	-- In the following statement, optimizer thinks we have a covering index (only referenced column is
-- c1), make sure we are using unique index (I12), not table scan, not I11.
delete from t1 where c1 = 1
Parse Time: 0
Bind Time: 0
Optimize Time: 0
Generate Time: 0
Compile Time: 0
Execute Time: 0
Begin Compilation Timestamp : null
End Compilation Timestamp : null
Begin Execution Timestamp : null
End Execution Timestamp : null
Statement Execution Plan Text: 
Delete ResultSet using row locking:
deferred: false
Rows deleted = 0
Indexes updated = 2
Execute Time = 0
	Project-Restrict ResultSet (2):
	Number of opens = 1
	Rows seen = 0
	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
	Source result set:
		Project-Restrict ResultSet (1):
		Number of opens = 1
		Rows seen = 0
		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
		Source result set:
			Index Scan ResultSet for T1 using index I12 at read committed isolation level using exclusive row locking chosen by the optimizer
			Number of opens = 1
			Rows seen = 0
			Rows filtered = 0
			Fetch Size = 1
				constructor time (milliseconds) = 0
				open time (milliseconds) = 0
				next time (milliseconds) = 0
				close time (milliseconds) = 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=1
				Number of rows qualified=0
				Number of rows visited=0
				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
ij> -- In the following statement, optimizer thinks we have a non-covering index (referenced columns are
-- c1 and c2), make sure we are still using unique index (I12), not table scan, not I11.
update t1 set c2 = 1 where c1 = 1;
0 rows inserted/updated/deleted
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name: 
	null
Statement Text: 
	-- In the following statement, optimizer thinks we have a non-covering index (referenced columns are
-- c1 and c2), make sure we are still using unique index (I12), not table scan, not I11.
update t1 set c2 = 1 where c1 = 1
Parse Time: 0
Bind Time: 0
Optimize Time: 0
Generate Time: 0
Compile Time: 0
Execute Time: 0
Begin Compilation Timestamp : null
End Compilation Timestamp : null
Begin Execution Timestamp : null
End Execution Timestamp : null
Statement Execution Plan Text: 
Update ResultSet using row locking:
deferred: false
Rows updated = 0
Indexes updated = 0
Execute Time = 0
	Project-Restrict ResultSet (2):
	Number of opens = 1
	Rows seen = 0
	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
	Source result set:
		Index Row to Base Row ResultSet for T1:
		Number of opens = 1
		Rows seen = 0
		Columns accessed from heap = {1}
			constructor time (milliseconds) = 0
			open time (milliseconds) = 0
			next time (milliseconds) = 0
			close time (milliseconds) = 0
			Index Scan ResultSet for T1 using index I12 at read committed isolation level using exclusive row locking chosen by the optimizer
			Number of opens = 1
			Rows seen = 0
			Rows filtered = 0
			Fetch Size = 1
				constructor time (milliseconds) = 0
				open time (milliseconds) = 0
				next time (milliseconds) = 0
				close time (milliseconds) = 0
			scan information: 
				Bit set of columns fetched=All
				Number of columns fetched=2
				Number of deleted rows visited=0
				Number of pages visited=1
				Number of rows qualified=0
				Number of rows visited=0
				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
ij> -- The following select should use TABLE SCAN, no predicate at all, and index not covering, no reason
-- to use index!!!
select * from t1;
C1         |C2         
-----------------------
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name: 
	null
Statement Text: 
	-- The following select should use TABLE SCAN, no predicate at all, and index not covering, no reason
-- to use index!!!
select * from t1
Parse Time: 0
Bind Time: 0
Optimize Time: 0
Generate Time: 0
Compile Time: 0
Execute Time: 0
Begin Compilation Timestamp : null
End Compilation Timestamp : null
Begin Execution Timestamp : null
End Execution Timestamp : null
Statement Execution Plan Text: 
Table Scan ResultSet for T1 at read committed isolation level using instantaneous share row locking chosen by the optimizer
Number of opens = 1
Rows seen = 0
Rows filtered = 0
Fetch Size = 16
	constructor time (milliseconds) = 0
	open time (milliseconds) = 0
	next time (milliseconds) = 0
	close time (milliseconds) = 0
scan information: 
	Bit set of columns fetched=All
	Number of columns fetched=2
	Number of pages visited=1
	Number of rows qualified=0
	Number of rows visited=0
	Scan type=heap
	start position: 
null	stop position: 
null	qualifiers:
None
ij> -- This one should use index, since it is covered, really doesn't matter which one, since no predicate,
-- It will choose the first one -- I11.
select c1 from t1;
C1         
-----------
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name: 
	null
Statement Text: 
	-- This one should use index, since it is covered, really doesn't matter which one, since no predicate,
-- It will choose the first one -- I11.