specjplans.sql

derby database source code.good for you.

-- 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;

drop table t1;
create table t1(c1 int, c2 int);
-- 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);
create unique index i12 on t1(c1);
call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1);
maximumdisplaywidth 3500;

-- 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;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- 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;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- The following select should use TABLE SCAN, no predicate at all, and index not covering, no reason
-- to use index!!!
select * from t1;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

-- 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;
values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

drop table t1;
-- end of something light...

DROP TABLE C_customer;
CREATE TABLE C_customer
(
	c_id		integer not null,
	c_first		char(16),
	c_last		char(16),
	c_street1	char(20),	
	c_street2	char(20),	
	c_city		char(20),	
	c_state		char(2),	
	c_country	char(10),	
	c_zip		char(9),	
	c_phone		char(16),
	c_contact	char(25),	
	c_since		date,
	c_balance	numeric(9,2),
	c_credit	char(2),
	c_credit_limit	numeric(9,2),
	c_ytd_payment	numeric(9,2)
);

CREATE UNIQUE INDEX C_c_idx ON C_customer (c_id);

DROP TABLE C_supplier;
CREATE TABLE C_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)
);

CREATE UNIQUE INDEX C_supp_idx ON C_supplier (supp_id);

DROP TABLE C_site;
CREATE TABLE C_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)
);

CREATE UNIQUE INDEX C_site_idx ON C_site (site_id);

DROP TABLE C_parts;
CREATE TABLE C_parts
(
	p_id			char(15) not null,
	p_name			char(10),
	p_desc			varchar(100),
	p_rev			char(6),
	p_unit			char(10),
	p_cost			numeric(9,2),
	p_price			numeric(9,2),
	p_planner		integer,
	p_type			integer,
	p_ind			integer,
        p_lomark                integer,
        p_himark                integer
);

CREATE UNIQUE INDEX C_p_idx ON C_parts (p_id);

DROP TABLE C_rule;
CREATE TABLE C_rule
(
	r_id		varchar(20) not null,
	r_text		long varchar
);

CREATE UNIQUE INDEX C_r_idx on C_rule (r_id);

DROP TABLE C_discount;
CREATE TABLE C_discount
(	
	d_id		varchar(64) not null,
	d_percent	integer
);



CREATE UNIQUE INDEX C_d_idx on C_discount (d_id);
DROP TABLE M_parts;
CREATE TABLE M_parts
(
	p_id			char(15) not null,
	p_name			char(10),
	p_desc			varchar(100),
	p_rev			char(6),
	p_planner		integer,
	p_type			integer,
	p_ind			integer,
	p_lomark		integer,
	p_himark		integer
);

CREATE UNIQUE INDEX M_parts_idx ON M_parts (p_id);

DROP TABLE M_bom;
CREATE TABLE M_bom
(
	b_comp_id		char(15) not null,
	b_assembly_id		char(15) not null,
	b_line_no		integer,
	b_qty			integer,
	b_ops			integer,	
	b_eng_change		char(10),	
	b_ops_desc		varchar(100)
);

CREATE UNIQUE INDEX M_bom_idx ON M_bom (b_assembly_id, b_comp_id, b_line_no);

DROP TABLE M_workorder;
CREATE TABLE M_workorder
(
	wo_number		integer not null,
	wo_o_id			integer,
	wo_ol_id		integer,
	wo_status		integer,
	wo_assembly_id	char(15),
	wo_orig_qty		integer,
	wo_comp_qty		integer,
	wo_due_date		date,
	wo_start_date		timestamp
);

CREATE UNIQUE INDEX M_wo_idx ON M_workorder (wo_number);

DROP TABLE M_largeorder;
CREATE TABLE M_largeorder
(
	lo_id			integer not null,
	lo_o_id			integer,
	lo_ol_id		integer,
	lo_assembly_id	char(15),
	lo_qty			integer,
	lo_due_date		date
);

CREATE UNIQUE INDEX M_lo_idx ON M_largeorder (lo_id);
CREATE UNIQUE INDEX M_OL_O_idx ON M_largeorder (lo_o_id, lo_ol_id);

DROP TABLE M_inventory;
CREATE TABLE M_inventory
(
	in_p_id			char(15) not null,
	in_qty			integer,
	in_ordered		integer,
	in_location		char(20),	
	in_acc_code		integer,
	in_act_date		date
);

CREATE UNIQUE INDEX M_inv_idx ON M_inventory (in_p_id);
DROP TABLE O_customer;
CREATE TABLE O_customer
(
	c_id		integer not null,
	c_first		char(16),
	c_last		char(16),
	c_street1	char(20),	
	c_street2	char(20),	
	c_city		char(20),	
	c_state		char(2),	
	c_country	char(10),	
	c_zip		char(9),	
	c_phone		char(16),
	c_contact	char(25),
	c_since		date
);

CREATE UNIQUE INDEX O_c_idx ON O_customer (c_id);

DROP TABLE O_orders;
CREATE TABLE O_orders
(
	o_id		integer not null,
	o_c_id		integer,
	o_ol_cnt	integer,
	o_discount	numeric(4,2),
	o_total		numeric(9,2),
	o_status	integer,
	o_entry_date	timestamp,
	o_ship_date	date
);

CREATE UNIQUE INDEX O_ords_idx ON O_orders (o_id);

CREATE INDEX O_oc_idx ON O_orders (o_c_id);

DROP TABLE O_orderline;
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
);

CREATE UNIQUE INDEX O_ordl_idx ON O_orderline (ol_o_id, ol_id);
CREATE INDEX O_ordl_idx2 ON O_orderline (ol_o_id, ol_i_id);
CREATE INDEX O_ordl_idx3 ON O_orderline (ol_o_id);

DROP TABLE O_item;
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)
);

CREATE UNIQUE INDEX O_i_idx ON O_item (i_id);

DROP TABLE S_component;
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
);

CREATE UNIQUE INDEX S_comp_idx ON S_component (comp_id);