specjplans.out

derby database source code.good for you.

	o_entry_date	timestamp,
	o_ship_date	date
);
0 rows inserted/updated/deleted
ij> CREATE UNIQUE INDEX O_ords_idx ON O_orders (o_id);
0 rows inserted/updated/deleted
ij> CREATE INDEX O_oc_idx ON O_orders (o_c_id);
0 rows inserted/updated/deleted
ij> DROP TABLE O_orderline;
ERROR 42Y55: 'DROP TABLE' cannot be performed on 'O_ORDERLINE' because it does not exist.
ij> CREATE TABLE O_orderline
(
	ol_id		integer not null,
	ol_o_id		integer not null,
	ol_i_id		char(15),
	ol_qty		integer,
	ol_status	integer,
	ol_ship_date	date
);
0 rows inserted/updated/deleted
ij> CREATE UNIQUE INDEX O_ordl_idx ON O_orderline (ol_o_id, ol_id);
0 rows inserted/updated/deleted
ij> CREATE INDEX O_ordl_idx2 ON O_orderline (ol_o_id, ol_i_id);
0 rows inserted/updated/deleted
ij> CREATE INDEX O_ordl_idx3 ON O_orderline (ol_o_id);
0 rows inserted/updated/deleted
ij> DROP TABLE O_item;
ERROR 42Y55: 'DROP TABLE' cannot be performed on 'O_ITEM' because it does not exist.
ij> CREATE TABLE O_item
(
	i_id			char(15) not null,
	i_name			char(20),
	i_desc			varchar(100),
	i_price			numeric(9,2),
	i_discount		numeric(6,4)
);
0 rows inserted/updated/deleted
ij> CREATE UNIQUE INDEX O_i_idx ON O_item (i_id);
0 rows inserted/updated/deleted
ij> DROP TABLE S_component;
ERROR 42Y55: 'DROP TABLE' cannot be performed on 'S_COMPONENT' because it does not exist.
ij> CREATE TABLE S_component
(
	comp_id			char(15) not null,
	comp_name		char(10),
	comp_desc		varchar(100),
	comp_unit		char(10),
	comp_cost		numeric(9,2),
	qty_on_order		integer,
	qty_demanded		integer,
	lead_time		integer,
	container_size		integer
);
0 rows inserted/updated/deleted
ij> CREATE UNIQUE INDEX S_comp_idx ON S_component (comp_id);
0 rows inserted/updated/deleted
ij> DROP TABLE S_supp_component;
ERROR 42Y55: 'DROP TABLE' cannot be performed on 'S_SUPP_COMPONENT' because it does not exist.
ij> CREATE TABLE S_supp_component
(
	sc_p_id			char(15) not null,
	sc_supp_id		integer not null,
	sc_price		numeric(9,2),
	sc_qty			integer,
	sc_discount		numeric(6,4),
	sc_del_date		integer
);
0 rows inserted/updated/deleted
ij> CREATE UNIQUE INDEX S_sc_idx ON S_supp_component (sc_p_id, sc_supp_id);
0 rows inserted/updated/deleted
ij> DROP TABLE S_supplier;
ERROR 42Y55: 'DROP TABLE' cannot be performed on 'S_SUPPLIER' because it does not exist.
ij> CREATE TABLE S_supplier
(
	supp_id			integer not null,
	supp_name		char(16),
	supp_street1	char(20),	
	supp_street2	char(20),	
	supp_city		char(20),	
	supp_state		char(2),	
	supp_country	char(10),	
	supp_zip		char(9),	
	supp_phone		char(16),
	supp_contact	char(25)
);
0 rows inserted/updated/deleted
ij> CREATE UNIQUE INDEX S_supp_idx ON S_supplier (supp_id);
0 rows inserted/updated/deleted
ij> DROP TABLE S_site;
ERROR 42Y55: 'DROP TABLE' cannot be performed on 'S_SITE' because it does not exist.
ij> CREATE TABLE S_site
(
	site_id			integer not null,
	site_name		char(16),
	site_street1	char(20),	
	site_street2	char(20),	
	site_city		char(20),	
	site_state		char(2),	
	site_country	char(10),	
	site_zip		char(9)
);
0 rows inserted/updated/deleted
ij> CREATE UNIQUE INDEX S_site_idx ON S_site (site_id);
0 rows inserted/updated/deleted
ij> DROP TABLE S_purchase_order;
ERROR 42Y55: 'DROP TABLE' cannot be performed on 'S_PURCHASE_ORDER' because it does not exist.
ij> CREATE TABLE S_purchase_order
(
	po_number		integer not null,
	po_supp_id		integer,
	po_site_id		integer
);
0 rows inserted/updated/deleted
ij> CREATE UNIQUE INDEX S_po_idx ON S_purchase_order (po_number);
0 rows inserted/updated/deleted
ij> DROP TABLE S_purchase_orderline;
ERROR 42Y55: 'DROP TABLE' cannot be performed on 'S_PURCHASE_ORDERLINE' because it does not exist.
ij> CREATE TABLE S_purchase_orderline
(
	pol_number		integer not null,
	pol_po_id		integer not null,
	pol_p_id		char(15),
	pol_qty			integer,
	pol_balance		numeric(9,2),
	pol_deldate		date,
	pol_message		varchar(100)
);
0 rows inserted/updated/deleted
ij> CREATE UNIQUE INDEX S_pol_idx ON S_purchase_orderline (pol_po_id, pol_number);
0 rows inserted/updated/deleted
ij> DROP TABLE U_sequences;
ERROR 42Y55: 'DROP TABLE' cannot be performed on 'U_SEQUENCES' because it does not exist.
ij> CREATE TABLE U_sequences
(
	s_id		varchar(50) not null,
	s_nextnum	integer,
	s_blocksize	integer
);
0 rows inserted/updated/deleted
ij> CREATE UNIQUE INDEX U_s_idx ON U_sequences (s_id);
0 rows inserted/updated/deleted
ij> -- set the runtimestatistics to check the query plans generated
call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
0 rows inserted/updated/deleted
ij> maximumdisplaywidth 3500;
ij> SELECT T1.O_STATUS, T1.O_SHIP_DATE, T1.O_ENTRY_DATE, T1.O_TOTAL, T1.O_DISCOUNT, T1.O_OL_CNT, T1.O_C_ID, T1.O_ID 
 FROM O_ORDERS  T1 WHERE o_c_id = 0 FOR UPDATE ;
O_STATUS   |O_SHIP_DA&|O_ENTRY_DATE              |O_TOTAL     |O_DISC&|O_OL_CNT   |O_C_ID     |O_ID       
----------------------------------------------------------------------------------------------------------
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name: 
	null
Statement Text: 
	SELECT T1.O_STATUS, T1.O_SHIP_DATE, T1.O_ENTRY_DATE, T1.O_TOTAL, T1.O_DISCOUNT, T1.O_OL_CNT, T1.O_C_ID, T1.O_ID 
 FROM O_ORDERS  T1 WHERE o_c_id = 0 FOR UPDATE 
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: 
Project-Restrict ResultSet (3):
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 O_ORDERS:
	Number of opens = 1
	Rows seen = 0
	Columns accessed from heap = {0, 1, 2, 3, 4, 5, 6, 7}
		constructor time (milliseconds) = 0
		open time (milliseconds) = 0
		next time (milliseconds) = 0
		close time (milliseconds) = 0
		Index Scan ResultSet for O_ORDERS using index O_OC_IDX 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> -- MAKE SURE THE FOLLOWING USE TABLE SCAN, NO REASON TO USE INDEX AT ALL, NOT USEFUL PREDICATES!!! 
 SELECT COUNT (*) FROM O_orders WHERE o_entry_date >= '01/10/2003' AND o_entry_date <= '01/09/2003' ;
1          
-----------
0          
ij> values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();
1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Statement Name: 
	null
Statement Text: 
	-- MAKE SURE THE FOLLOWING USE TABLE SCAN, NO REASON TO USE INDEX AT ALL, NOT USEFUL PREDICATES!!! 
 SELECT COUNT (*) FROM O_orders WHERE o_entry_date >= '01/10/2003' AND o_entry_date <= '01/09/2003' 
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: 
Project-Restrict ResultSet (4):
Number of opens = 1
Rows seen = 1
Rows filtered = 0
restriction = false
projection = true
	constructor time (milliseconds) = 0