| -- |
| -- Licensed to the Apache Software Foundation (ASF) under one or more |
| -- contributor license agreements. See the NOTICE file distributed with |
| -- this work for additional information regarding copyright ownership. |
| -- The ASF licenses this file to You under the Apache License, Version 2.0 |
| -- (the "License"); you may not use this file except in compliance with |
| -- the License. You may obtain a copy of the License at |
| -- |
| -- http://www.apache.org/licenses/LICENSE-2.0 |
| -- |
| -- Unless required by applicable law or agreed to in writing, software |
| -- distributed under the License is distributed on an "AS IS" BASIS, |
| -- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. |
| -- See the License for the specific language governing permissions and |
| -- limitations under the License. |
| -- |
| -- 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); |
| |
| DROP TABLE S_supp_component; |
| 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 |
| ); |
| |
| CREATE UNIQUE INDEX S_sc_idx ON S_supp_component (sc_p_id, sc_supp_id); |
| |
| DROP TABLE S_supplier; |
| 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) |
| ); |
| |
| CREATE UNIQUE INDEX S_supp_idx ON S_supplier (supp_id); |
| |
| DROP TABLE S_site; |
| 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) |
| ); |
| |
| CREATE UNIQUE INDEX S_site_idx ON S_site (site_id); |
| |
| DROP TABLE S_purchase_order; |
| CREATE TABLE S_purchase_order |
| ( |
| po_number integer not null, |
| po_supp_id integer, |
| po_site_id integer |
| ); |
| |
| CREATE UNIQUE INDEX S_po_idx ON S_purchase_order (po_number); |
| |
| DROP TABLE S_purchase_orderline; |
| 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) |
| ); |
| |
| CREATE UNIQUE INDEX S_pol_idx ON S_purchase_orderline (pol_po_id, pol_number); |
| |
| DROP TABLE U_sequences; |
| CREATE TABLE U_sequences |
| ( |
| s_id varchar(50) not null, |
| s_nextnum integer, |
| s_blocksize integer |
| ); |
| |
| CREATE UNIQUE INDEX U_s_idx ON U_sequences (s_id); |
| |
| |
| |
| -- set the runtimestatistics to check the query plans generated |
| call SYSCS_UTIL.SYSCS_SET_RUNTIMESTATISTICS(1); |
| maximumdisplaywidth 3500; |
| |
| 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 ; |
| |
| values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); |
| |
| -- 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' ; |
| |
| values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); |
| |
| SELECT T1.WO_NUMBER, T1.WO_O_ID, T1.WO_OL_ID, T1.WO_STATUS, T1.WO_ORIG_QTY, T1.WO_COMP_QTY, T1.WO_ASSEMBLY_ID, |
| T1.WO_DUE_DATE, T1.WO_START_DATE FROM M_WORKORDER T1 WHERE T1.WO_NUMBER = 1 FOR UPDATE; |
| |
| values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); |
| |
| -- MAKE SURE THE FOLLOWING USE TABLE SCAN, NO REASON TO USE INDEX AT ALL, NOT USEFUL PREDICATES!!! |
| SELECT T1.LO_ID, T1.LO_O_ID, T1.LO_OL_ID, T1.LO_ASSEMBLY_ID, T1.LO_QTY, T1.LO_DUE_DATE FROM M_LARGEORDER T1 WHERE 1=1; |
| |
| values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); |
| |
| UPDATE M_INVENTORY SET IN_QTY = 1, IN_LOCATION = 'sanfrancisco', IN_ACC_CODE = 1, IN_ACT_DATE = '01/01/2003', IN_ORDERED = 1 WHERE IN_P_ID = 'abcdefghijklm'; |
| |
| values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); |
| SELECT COUNT (*) FROM S_component; |
| |
| values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); |
| SELECT T1.R_ID, T1.R_TEXT FROM C_RULE T1 WHERE T1.R_ID = 'abcdefghijlkmijklmnopqrstuvwxyz'; |
| |
| values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); |
| SELECT COUNT (*) FROM C_site; |
| |
| values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); |
| SELECT T1.IN_P_ID, T1.IN_QTY, T1.IN_LOCATION, T1.IN_ACC_CODE, T1.IN_ACT_DATE, T1.IN_ORDERED FROM M_INVENTORY T1 WHERE T1.IN_P_ID = 'abcdefghijkl' FOR UPDATE; |
| |
| values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); |
| SELECT T1.OL_SHIP_DATE, T1.OL_QTY, T1.OL_I_ID, T1.OL_O_ID, T1.OL_ID FROM O_ORDERLINE T1 WHERE ol_o_id = 1 FOR UPDATE ; |
| |
| values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); |
| DELETE FROM M_LARGEORDER WHERE LO_ID = 1; |
| |
| values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); |
| |
| -- MAKE SURE THE FOLLOWING USE TABLE SCAN, NO REASON TO USE INDEX AT ALL, NOT USEFUL PREDICATES!!! |
| SELECT COUNT (*) FROM M_workorder WHERE wo_start_date >= '01/10/2003' AND wo_start_date <= '01/10/2003'; |
| values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); |
| |
| SELECT T1.I_DISCOUNT, T1.I_DESC, T1.I_NAME, T1.I_PRICE, T1.I_ID FROM O_ITEM T1 WHERE T1.I_ID = 'abcdefghijk'; |
| |
| values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); |
| SELECT T1.P_ID, T1.P_NAME, T1.P_DESC, T1.P_REV, T1.P_PLANNER, T1.P_TYPE, T1.P_IND, T1.P_LOMARK, T1.P_HIMARK FROM M_PARTS T1 WHERE T1.P_ID = 'abcdefghijl'; |
| |
| values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); |
| SELECT COUNT (*) FROM S_purchase_orderline; |
| |
| values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); |
| SELECT T1.S_ID, T1.S_NEXTNUM, T1.S_BLOCKSIZE FROM U_SEQUENCES T1 WHERE T1.S_ID = 'abcdefghijklmnopqrstuvwxyz' FOR UPDATE; |
| |
| values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); |
| SELECT T1.SC_P_ID, T1.SC_SUPP_ID, T1.SC_PRICE, T1.SC_QTY, T1.SC_DISCOUNT, T1.SC_DEL_DATE FROM S_SUPP_COMPONENT T1 WHERE T1.SC_P_ID = 'abcdefgjikl' AND T1.SC_SUPP_ID = 1; |
| |
| values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); |
| 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 T1.O_ID = 1 FOR UPDATE; |
| |
| values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); |
| SELECT COUNT (*) FROM M_workorder; |
| |
| values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); |
| SELECT COUNT (*) FROM S_purchase_order; |
| |
| values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); |
| SELECT COUNT (*) FROM M_bom; |
| |
| values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); |
| SELECT T1.PO_NUMBER, T1.PO_SUPP_ID, T1.PO_SITE_ID FROM S_PURCHASE_ORDER T1 WHERE T1.PO_NUMBER = 1; |
| |
| values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); |
| SELECT COUNT (*) FROM O_orderline; |
| |
| values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); |
| SELECT T1.D_ID, T1.D_PERCENT FROM C_DISCOUNT T1 WHERE T1.D_ID = 'abcdefghijklmnopqrstuvwz'; |
| |
| values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); |
| UPDATE O_ORDERLINE SET OL_SHIP_DATE = '2/28/2000', OL_QTY = 10, OL_I_ID = 'abcdefghijkl' WHERE OL_O_ID = 1 AND OL_ID = 1; |
| |
| values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); |
| SELECT T1.COMP_ID, T1.COMP_NAME, T1.COMP_DESC, T1.COMP_UNIT, T1.COMP_COST, T1.QTY_ON_ORDER, T1.QTY_DEMANDED, T1.LEAD_TIME, T1.CONTAINER_SIZE FROM S_COMPONENT T1 WHERE T1.COMP_ID = 'abcdefghijk' FOR UPDATE; |
| |
| values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); |
| SELECT COUNT (*) FROM O_customer; |
| |
| values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); |
| SELECT T1.LO_ID, T1.LO_O_ID, T1.LO_OL_ID, T1.LO_ASSEMBLY_ID, T1.LO_QTY, T1.LO_DUE_DATE FROM M_LARGEORDER T1 WHERE lo_o_id = 1 AND lo_ol_id = 1; |
| |
| values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); |
| UPDATE O_ORDERS SET O_STATUS = 1, O_SHIP_DATE = '01/01/9004', O_ENTRY_DATE = NULL, O_TOTAL = 1000, O_DISCOUNT =100, O_OL_CNT = 1, O_C_ID = 1 WHERE O_ID = 2; |
| |
| values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); |
| SELECT COUNT (*) FROM C_customer; |
| |
| values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); |
| SELECT COUNT (*) FROM M_inventory; |
| |
| values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); |
| |
| -- MAKE SURE THE FOLLOWING USE TABLE SCAN, NO REASON TO USE INDEX AT ALL, NOT USEFUL PREDICATES!!! |
| SELECT T1.SUPP_ID, T1.SUPP_NAME, T1.SUPP_STREET1, T1.SUPP_STREET2, T1.SUPP_CITY, T1.SUPP_STATE, T1.SUPP_COUNTRY, T1.SUPP_ZIP, T1.SUPP_PHONE, T1.SUPP_CONTACT FROM S_SUPPLIER T1 WHERE 1=1; |
| |
| values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); |
| UPDATE U_SEQUENCES SET S_NEXTNUM = 1 , S_BLOCKSIZE = 1000 WHERE S_ID = 'abcdefghijklmnopqrstuvwxyz'; |
| |
| values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); |
| SELECT COUNT (*) FROM M_parts ; |
| |
| values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); |
| SELECT COUNT (*) FROM O_item; |
| |
| values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); |
| UPDATE M_WORKORDER SET WO_O_ID = 11, WO_OL_ID = 11, WO_STATUS = 11, WO_ORIG_QTY = 11, WO_COMP_QTY = 11, WO_ASSEMBLY_ID = 'abcdefghijk', WO_DUE_DATE = '01/01/2000', WO_START_DATE = '01/01/00' WHERE WO_NUMBER = 1; |
| |
| values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); |
| SELECT T1.POL_NUMBER, T1.POL_PO_ID, T1.POL_P_ID, T1.POL_QTY, T1.POL_BALANCE, T1.POL_DELDATE, T1.POL_MESSAGE FROM S_PURCHASE_ORDERLINE T1 WHERE T1.POL_NUMBER = 100 AND T1.POL_PO_ID = 200 FOR UPDATE; |
| |
| values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); |
| SELECT T1.OL_SHIP_DATE, T1.OL_QTY, T1.OL_I_ID, T1.OL_O_ID, T1.OL_ID FROM O_ORDERLINE T1 WHERE ol_o_id = 100 AND ol_i_id = 'abcdefgh' FOR UPDATE; |
| |
| values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); |
| SELECT T1.POL_NUMBER, T1.POL_PO_ID, T1.POL_P_ID, T1.POL_QTY, T1.POL_BALANCE, T1.POL_DELDATE, T1.POL_MESSAGE FROM S_PURCHASE_ORDERLINE T1 WHERE pol_po_id = 11 FOR UPDATE; |
| |
| values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); |
| DELETE FROM O_ORDERS WHERE O_ID = 1; |
| |
| values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); |
| SELECT COUNT (*) FROM O_orders; |
| |
| values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); |
| SELECT COUNT (*) FROM S_supplier; |
| |
| values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); |
| SELECT T1.C_ID, T1.C_SINCE, T1.C_BALANCE, T1.C_CREDIT, T1.C_CREDIT_LIMIT, T1.C_YTD_PAYMENT FROM C_CUSTOMER T1 WHERE T1.C_ID = 1111 FOR UPDATE; |
| |
| values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); |
| |
| -- MAKE SURE THE FOLLOWING DELETE STATEMENT USES UNIQUE INDEX "O_ORDL_IDX", NOT NON-UNIQUE INDEX "O_ORDL_IDX2", |
| -- EVEN THOUGH WE ARE COMPILING WITH EMPTY TABLE!!! beetle 5006. |
| DELETE FROM O_ORDERLINE WHERE OL_O_ID = 11111 AND OL_ID = 111111; |
| |
| values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); |
| SELECT COUNT (*) FROM C_supplier; |
| |
| values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); |
| SELECT T1.B_ASSEMBLY_ID, T1.B_COMP_ID, T1.B_LINE_NO, T1.B_QTY, T1.B_ENG_CHANGE, T1.B_OPS, T1.B_OPS_DESC FROM M_BOM T1 WHERE b_assembly_id = 'specjstuff'; |
| |
| values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); |
| UPDATE S_COMPONENT SET COMP_NAME = 'abc', COMP_DESC = 'book', COMP_UNIT = '100', COMP_COST = 1000, QTY_ON_ORDER = 1000, QTY_DEMANDED = 111, LEAD_TIME = 11, CONTAINER_SIZE = 11 WHERE COMP_ID = 'rudyardkipling'; |
| |
| values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); |
| SELECT T1.PO_NUMBER, T1.PO_SUPP_ID, T1.PO_SITE_ID FROM S_PURCHASE_ORDER T1 WHERE T1.PO_NUMBER = 100 FOR UPDATE; |
| |
| values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); |
| UPDATE S_PURCHASE_ORDERLINE SET POL_P_ID = 'teacup', POL_QTY = 2, POL_BALANCE = 2, POL_DELDATE = '01/01/2000', POL_MESSAGE = 'tintin shooting star' WHERE POL_NUMBER = 1 AND POL_PO_ID = 1111; |
| |
| values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); |
| SELECT COUNT (*) FROM S_site ; |
| |
| values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); |
| SELECT T1.C_SINCE, T1.C_STREET1, T1.C_STREET2, T1.C_CITY, T1.C_STATE, T1.C_COUNTRY, T1.C_ZIP, T1.C_PHONE, T1.C_CONTACT, T1.C_LAST, T1.C_FIRST, T1.C_ID FROM O_CUSTOMER T1 WHERE T1.C_ID = 23456; |
| |
| values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); |
| SELECT COUNT (*) FROM C_parts; |
| |
| INSERT INTO O_ORDERLINE (OL_O_ID, OL_ID, OL_SHIP_DATE, OL_QTY, OL_I_ID) VALUES (1, 1,NULL, 1,'abcdefghijklmn') ; |
| |
| |
| INSERT INTO M_LARGEORDER (LO_ID, LO_O_ID, LO_OL_ID, LO_ASSEMBLY_ID, LO_QTY, LO_DUE_DATE) VALUES (2, 2, 2,'id', 2, date('2000-02-29')); |
| |
| |
| INSERT INTO O_ORDERS (O_ID, O_STATUS, O_SHIP_DATE, O_ENTRY_DATE, O_TOTAL, O_DISCOUNT, O_OL_CNT, O_C_ID) VALUES (111, 11, date('1999-01-01'),timestamp('1997-06-30 01:01:01'), 10, 10, 10, 10); |
| |
| INSERT INTO M_WORKORDER (WO_NUMBER, WO_O_ID, WO_OL_ID, WO_STATUS, WO_ORIG_QTY, WO_COMP_QTY, WO_ASSEMBLY_ID, WO_DUE_DATE, WO_START_DATE) VALUES (10,10 ,10, 10,10, 10, 'abcd', date('2099-10-10'), timestamp('1997-06-30 01:01:01')); |
| |
| INSERT INTO O_CUSTOMER (C_ID, C_SINCE, C_STREET1, C_STREET2, C_CITY, C_STATE, C_COUNTRY, C_ZIP, C_PHONE, C_CONTACT, C_LAST, C_FIRST) VALUES (1, date('2000-01-01'), 'berkeley', 'berkeley','berkeley','ca', 'usofa', '94703', '01191797897', 'calvinandhobbes', 'watterson','bill'); |
| |
| INSERT INTO S_PURCHASE_ORDER (PO_NUMBER, PO_SUPP_ID, PO_SITE_ID) VALUES (100, 100, 100); |
| |
| INSERT INTO S_PURCHASE_ORDERLINE (POL_NUMBER, POL_PO_ID, POL_P_ID, POL_QTY, POL_BALANCE, POL_DELDATE, POL_MESSAGE) VALUES (121,987 ,'snowsnowsnow',11 , 999, date('2003-1-01'),'wow, it really snowed last night isnt it wonderful last calvin and hobbes'); |
| |
| INSERT INTO C_CUSTOMER (C_ID, C_SINCE, C_BALANCE, C_CREDIT, C_CREDIT_LIMIT, C_YTD_PAYMENT) VALUES (11, date('2000-10-01'), 1000, 'ab', 10000,1000.20); |
| |
| -- Cleanup : Drop all the tables created as part of this test |
| |
| DROP TABLE C_customer; |
| DROP TABLE C_supplier; |
| DROP TABLE C_site; |
| DROP TABLE C_parts; |
| DROP TABLE C_rule; |
| DROP TABLE C_discount; |
| DROP TABLE M_parts; |
| DROP TABLE M_bom; |
| DROP TABLE M_workorder; |
| DROP TABLE M_largeorder; |
| DROP TABLE M_inventory; |
| DROP TABLE O_customer; |
| DROP TABLE O_orders; |
| DROP TABLE O_orderline; |
| DROP TABLE O_item; |
| DROP TABLE S_component; |
| DROP TABLE S_supp_component; |
| DROP TABLE S_supplier; |
| DROP TABLE S_site; |
| DROP TABLE S_purchase_order; |
| DROP TABLE S_purchase_orderline; |
| DROP TABLE U_sequences; |
