==============================
TPCH Database creation
==============================
C:\>sqlplus / as sysdba
SQL> CREATE DATABASE TPCH
MAXDATAFILES 100
MAXLOGFILES 6
MAXLOGHISTORY 100
MAXINSTANCES 1
ARCHIVELOG
LOGFILE
GROUP 1 ('/home/oracle/DB11G/redo01.dbf') size 10M,
GROUP 2 ('/home/oracle/DB11G/redo02.dbf') size 10M
DATAFILE '/home/oracle/DB11G/system01.dbf' size 512M AUTOEXTEND ON MAXSIZE UNLIMITED
SYSAUX DATAFILE '/home/oracle/DB11G/sysaux01.dbf' size 512M AUTOEXTEND ON MAXSIZE 1024M
DEFAULT TEMPORARY TABLESPACE TEMP tempfile '/home/oracle/DB11G/temp01.tmp' size 128M AUTOEXTEND OFF
UNDO TABLESPACE UNDO1 DATAFILE '/home/oracle/DB11G/undo101.dbf' size 128M AUTOEXTEND ON
CHARACTER SET UTF8
NATIONAL CHARACTER SET UTF8;
SQL> create user tpch identified by tpch;
SQL> grant connect,resource to tpch;
=========================================================
Step 2
========================================================
Download and uncompress the tpc-h source code from http://www.tpc.org
Edit the makefile.suite as follows and Compile/make tpc-h source usning make –f command.
CC = gcc
DATABASE = SQLSERVER
MACHINE = LINUX
WORKLOAD = TPCH
$ make -f makefile.suite
=====================================================================================
Step 3
====================================================================================
Create tpc-h’s data files using dbgen
[root@s01 tpch]# ./dbgen -f -v -s 1 # where 1 means = 1g of data
TPC-H Population Generator (Version 2.7.0 build 47)
Copyright Transaction Processing Performance Council 1994 - 2008
Generating data for suppliers table [pid: 4566]/
Preloading text ... 100%
done.
Generating data for customers table [pid: 4566]done.
Generating data for orders/lineitem tables [pid: 4566]done.
Generating data for part/partsupplier tables [pid: 4566]done.
Generating data for nation table [pid: 4566]done.
Generating data for region table [pid: 4566]done.
=====================================================================================
Step 3
=============================================================================
Create the tpc-h schema using the dss.ddl for Oracle 11g
-- Sccsid: @(#)dss.ddl 2.1.8.1
CREATE TABLE NATION ( N_NATIONKEY NUMBER NOT NULL,
N_NAME CHAR(25) NOT NULL,
N_REGIONKEY NUMBER NOT NULL,
N_COMMENT VARCHAR2(152));
CREATE TABLE REGION ( R_REGIONKEY NUMBER NOT NULL,
R_NAME CHAR(25) NOT NULL,
R_COMMENT VARCHAR2(152));
CREATE TABLE PART ( P_PARTKEY NUMBER NOT NULL,
P_NAME VARCHAR2(55) NOT NULL,
P_MFGR CHAR(25) NOT NULL,
P_BRAND CHAR(10) NOT NULL,
P_TYPE VARCHAR2(25) NOT NULL,
P_SIZE NUMBER NOT NULL,
P_CONTAINER CHAR(10) NOT NULL,
P_RETAILPRICE NUMBER(15,2) NOT NULL,
P_COMMENT VARCHAR2(23) NOT NULL );
CREATE TABLE SUPPLIER ( S_SUPPKEY NUMBER NOT NULL,
S_NAME CHAR(25) NOT NULL,
S_ADDRESS VARCHAR2(40) NOT NULL,
S_NATIONKEY NUMBER NOT NULL,
S_PHONE CHAR(15) NOT NULL,
S_ACCTBAL NUMBER(15,2) NOT NULL,
S_COMMENT VARCHAR2(101) NOT NULL);
CREATE TABLE PARTSUPP ( PS_PARTKEY NUMBER NOT NULL,
PS_SUPPKEY NUMBER NOT NULL,
PS_AVAILQTY NUMBER NOT NULL,
PS_SUPPLYCOST NUMBER(15,2) NOT NULL,
PS_COMMENT VARCHAR2(199) NOT NULL );
CREATE TABLE CUSTOMER ( C_CUSTKEY NUMBER NOT NULL,
C_NAME VARCHAR2(25) NOT NULL,
C_ADDRESS VARCHAR2(40) NOT NULL,
C_NATIONKEY NUMBER NOT NULL,
C_PHONE CHAR(15) NOT NULL,
C_ACCTBAL NUMBER(15,2) NOT NULL,
C_MKTSEGMENT CHAR(10) NOT NULL,
C_COMMENT VARCHAR2(117) NOT NULL);
CREATE TABLE ORDERS ( O_ORDERKEY NUMBER NOT NULL,
O_CUSTKEY NUMBER NOT NULL,
O_ORDERSTATUS CHAR(1) NOT NULL,
O_TOTALPRICE NUMBER(15,2) NOT NULL,
O_ORDERDATE DATE NOT NULL,
O_ORDERPRIORITY CHAR(15) NOT NULL,
O_CLERK CHAR(15) NOT NULL,
O_SHIPPRIORITY NUMBER NOT NULL,
O_COMMENT VARCHAR2(79) NOT NULL);
CREATE TABLE LINEITEM ( L_ORDERKEY NUMBER NOT NULL,
L_PARTKEY NUMBER NOT NULL,
L_SUPPKEY NUMBER NOT NULL,
L_LINENUMBER NUMBER NOT NULL,
L_QUANTITY NUMBER(15,2) NOT NULL,
L_EXTENDEDPRICE NUMBER(15,2) NOT NULL,
L_DISCOUNT NUMBER(15,2) NOT NULL,
L_TAX NUMBER(15,2) NOT NULL,
L_RETURNFLAG CHAR(1) NOT NULL,
L_LINESTATUS CHAR(1) NOT NULL,
L_SHIPDATE DATE NOT NULL,
L_COMMITDATE DATE NOT NULL,
L_RECEIPTDATE DATE NOT NULL,
L_SHIPINSTRUCT CHAR(25) NOT NULL,
L_SHIPMODE CHAR(10) NOT NULL,
L_COMMENT VARCHAR2(44) NOT NULL);
================================================================================
Step 4
=================================================================================
Loading benchmark’s data using sqlldr
***** Ensure that your NLS_DATE_FORMAT="YYYY-MM_DD" *******************(ANSI)
sqlldr tpch_usr/tpch_usr control=customer.tbl
sqlldr tpch_usr/tpch_usr control=lineitem.tbl
sqlldr tpch_usr/tpch_usr control=nation.tbl
sqlldr tpch_usr/tpch_usr control=orders.tbl
sqlldr tpch_usr/tpch_usr control=partsupp.tbl
sqlldr tpch_usr/tpch_usr control=part.tbl
sqlldr tpch_usr/tpch_usr control=region.tbl
sqlldr tpch_usr/tpch_usr control=supplier.tbl
Example of modification
LOAD DATA
INFILE *
TRUNCATE
INTO TABLE "NATION"
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS (
N_NATIONKEY,
N_NAME,
N_REGIONKEY,
N_COMMENT
)
begindata
0|ALGERIA|0| haggle. carefully final deposits detect slyly agai
1|ARGENTINA|1|al foxes promise slyly according to the regular accounts. bold requests alon
2|BRAZIL|1|y alongside of the pending deposits. carefully special packages are about the ironic forges. slyly special
3|CANADA|1|eas hang ironic, silent packages. slyly regular packages are furiously over the tithes. fluffily bold
===================================================================
Step 5
==================================================================
When you complete the TPC-H installation (create of data and database loading) , run the following java class (A multithreaded application that contains TPC-H's queries) that execute TPC-H queries randomly. The class allows you to define the number of connections. For example if you define 5 threads, then 5 connections are established to your database, which run randomly 20 TPC-H queries.
package mypackage;
import java.sql.*;
import java.util.Random;
import java.util.Vector;
import oracle.sql.*;
import oracle.jdbc.*;
public class Action implements Runnable
{
int action_id;
int query_id;
String query_text="";
Vector temp = new Vector();
String thread_status= "IDLE";
private Connection conn=null;
private Statement stm=null;
///Database connection
private String server="192.168.10.3";
private String service_name="orcl";
private String port="1521";
private String username="tpch";
private String password="tpch";
public Action()
{
}
public Action(int action_id)
{
this.action_id=action_id;
}
private void datasourse() throws Exception
{
try
{
DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
this.conn=DriverManager.getConnection("jdbc:oracle:thin:@"+server+":"+port+":"+service_name, username,password);
this.stm = conn.createStatement();
}
catch (SQLException e)
{
throw new Exception("Sql error: "+e.getMessage());
}
}
public void executeQuery(String query)
{
ResultSet rst = null;
try
{
rst = this.stm.executeQuery(query);
while (rst.next()){}
}
catch (Exception e1)
{
System.out.println(e1.getMessage());
}
}
public void run()
{
try
{
datasourse();
}
catch (Exception e)
{
System.out.println(e.getMessage());
}
this.thread_status = "RUNNING";
temp.clear();
for (int i=1; i <= 20 ; i++)
{
Random g = new Random();
this.query_id=g.nextInt(10)+1;
while ( exist(new Integer(this.query_id)))
{
this.query_id=g.nextInt(10)+1;
}
temp.add(new Integer(this.query_id));
System.out.println(" The Worker ["+this.action_id+"] executed the query "+this.query_id);
this.query_text=getQueryText();
//System.out.println(" Query Text "+this.query_text);
executeQuery(this.query_text);
this.thread_status = "CLOSED";
}
try
{
conn.close();
}
catch (Exception e)
{
System.out.println(e.getMessage());
}
}
public String getThreadStatus()
{
return this.thread_status;
}
public int getThreadID()
{
return this.getThreadID();
}
public String getThreadQueryText()
{
return this.query_text;
}
public boolean exist( Integer a)
{
int b = 1;
Integer c = new Integer(-1);
for (int i=0; i
//System.out.println((Integer)temp.elementAt(i));
c=(Integer)temp.elementAt(i);
if (c.equals(a))
{
//System.out.println("equal");
b = 0;
}
}
if (b==0)
{
return true;
}
else
{
return false;
}
}
private String getQueryText()
{
if (query_id==1)
{
return "\n"+
" select "+
" l_returnflag, " +
" l_linestatus, " +
" sum(l_quantity) as sum_qty, " +
" sum(l_extendedprice) as sum_base_price, " +
" sum(l_extendedprice * (1 - l_discount)) as sum_disc_price, " +
" sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge, " +
" avg(l_quantity) as avg_qty, " +
" avg(l_extendedprice) as avg_price, " +
" avg(l_discount) as avg_disc, " +
" count(*) as count_order " +
" from " +
" lineitem " +
" where " +
" l_shipdate <= to_date('1998-12-01','YYYY-MM-DD') - interval '3' day " +
" group by " +
" l_returnflag, " +
" l_linestatus " +
" order by " +
" l_returnflag, " +
" l_linestatus " ;
}
else if (query_id==2)
{
return "\n"+
" select " +
" s_acctbal, " +
" s_name, " +
" n_name, " +
" p_partkey, " +
" p_mfgr, " +
" s_address, " +
" s_phone, " +
" s_comment " +
" from " +
" part, " +
" supplier, " +
" partsupp, " +
" nation, " +
" region " +
" where " +
" p_partkey = ps_partkey " +
" and s_suppkey = ps_suppkey " +
" and p_size = 41 " +
" and p_type like 'PROMO%' " +
" and s_nationkey = n_nationkey " +
" and n_regionkey = r_regionkey " +
" and r_name = 'AFRICA' " +
" and ps_supplycost = ( " +
" select " +
" min(ps_supplycost) " +
" from " +
" partsupp, " +
" supplier, " +
" nation, " +
" region " +
" where " +
" p_partkey = ps_partkey " +
" and s_suppkey = ps_suppkey " +
" and s_nationkey = n_nationkey " +
" and n_regionkey = r_regionkey " +
" and r_name = 'AFRICA' " +
" ) " +
" order by " +
" s_acctbal desc, " +
" n_name, " +
" s_name, " +
" p_partkey ";
}
else if (query_id==3)
{
return "\n"+
" select " +
" l_orderkey, " +
" sum(l_extendedprice * (1 - l_discount)) as revenue, " +
" o_orderdate, " +
" o_shippriority " +
" from " +
" customer, " +
" orders, " +
" lineitem " +
" where " +
" c_mktsegment = 'BUILDING' " +
" and c_custkey = o_custkey " +
" and l_orderkey = o_orderkey " +
" and o_orderdate < to_date('1998-08-02','YYYY-MM-DD') " +
" and l_shipdate > to_date('1992-01-02','YYYY-MM-DD') " +
" and rownum <= 10 " +
" group by " +
" l_orderkey, " +
" o_orderdate, " +
" o_shippriority " +
" order by " +
" revenue desc, " +
" o_orderdate " ;
}
else if (query_id==4)
{
return "\n"+
" select " +
" o_orderpriority, " +
" count(*) as order_count " +
" from " +
" orders " +
" where " +
" o_orderdate >= to_date('1992-01-01','YYYY-MM-DD') " +
" and o_orderdate < to_date('1998-08-02','YYYY-MM-DD') + interval '3' month " +
" and exists ( " +
" select " +
" * " +
" from " +
" lineitem " +
" where " +
" l_orderkey = o_orderkey " +
" and l_commitdate < l_receiptdate " +
" ) " +
" and rownum <= 10 " +
" group by " +
" o_orderpriority " +
" order by " +
" o_orderpriority " ;
}
else if (query_id==5)
{
return "\n"+
" select " +
" n_name, " +
" sum(l_extendedprice * (1 - l_discount)) as revenue " +
" from " +
" customer, " +
" orders, " +
" lineitem, " +
" supplier, " +
" nation, " +
" region " +
" where " +
" c_custkey = o_custkey " +
" and l_orderkey = o_orderkey " +
" and l_suppkey = s_suppkey " +
" and c_nationkey = s_nationkey " +
" and s_nationkey = n_nationkey " +
" and n_regionkey = r_regionkey " +
" and r_name = 'EUROPE' " +
" and o_orderdate >= to_date('1992-01-01','YYYY-MM-DD') " +
" and o_orderdate < to_date('1996-01-01','YYYY-MM-DD') + interval '1' year " +
" and rownum <= 10 " +
" group by " +
" n_name " +
" order by " +
" revenue desc ";
}
else if (query_id==6)
{
return "\n"+
" select " +
" sum(l_extendedprice * l_discount) as revenue " +
" from " +
" lineitem " +
" where " +
" l_shipdate >= to_date('1993-01-01','YYYY-MM-DD') " +
" and l_shipdate < to_date('1998-01-01','YYYY-MM-DD') + interval '1' year " +
" and l_discount between - 0.01 and + 0.01 " +
" and l_quantity < 2 " ;
}
else if (query_id==7)
{
return "\n"+
" select " +
" supp_nation, " +
" cust_nation, " +
" l_year, " +
" sum(volume) as revenue " +
" from " +
" ( " +
" select " +
" n1.n_name as supp_nation, " +
" n2.n_name as cust_nation, " +
" extract(year from l_shipdate) as l_year, " +
" l_extendedprice * (1 - l_discount) as volume " +
" from " +
" supplier, " +
" lineitem, " +
" orders, " +
" customer, " +
" nation n1, " +
" nation n2 " +
" where " +
" s_suppkey = l_suppkey " +
" and o_orderkey = l_orderkey " +
" and c_custkey = o_custkey " +
" and s_nationkey = n1.n_nationkey " +
" and c_nationkey = n2.n_nationkey " +
" and ( " +
" (n1.n_name = 'RUSSIA' and n2.n_name = 'FRANCE') " +
" or (n1.n_name = 'CANADA' and n2.n_name = 'RUSSIA') " +
" ) " +
" and l_shipdate between to_date('1995-01-01','YYYY-MM-DD') " +
" and to_date('1996-12-31','YYYY-MM-DD') " +
" ) shipping " +
" where rownum <= 10 " +
" group by " +
" supp_nation, " +
" cust_nation, " +
" l_year " +
" order by " +
" supp_nation, " +
" cust_nation, " +
" l_year " ;
}
else if (query_id==8)
{
return "\n"+
" select " +
" o_year, " +
" sum(case " +
" when nation = '' then volume " +
" else 0 " +
" end) / sum(volume) as mkt_share " +
" from " +
" ( " +
" select " +
" extract(year from o_orderdate) as o_year, " +
" l_extendedprice * (1 - l_discount) as volume, " +
" n2.n_name as nation " +
" from " +
" part, " +
" supplier, " +
" lineitem, " +
" orders, " +
" customer, " +
" nation n1, " +
" nation n2, " +
" region " +
" where " +
" p_partkey = l_partkey " +
" and s_suppkey = l_suppkey " +
" and l_orderkey = o_orderkey " +
" and o_custkey = c_custkey " +
" and c_nationkey = n1.n_nationkey " +
" and n1.n_regionkey = r_regionkey " +
" and r_name = 'EUROPE' " +
" and s_nationkey = n2.n_nationkey " +
" and o_orderdate between to_date('1995-01-01','YYYY-MM-DD') " +
" and to_date('1996-12-31','YYYY-MM-DD') " +
" and p_type = 'STANDARD PLATED BRASS' " +
" )all_nations " +
" where rownum <= 10 " +
" group by " +
" o_year " +
" order by " +
" o_year " ;
}
else if (query_id==9)
{
return "\n"+
" select " +
" nation, " +
" o_year, " +
" sum(amount) as sum_profit " +
" from " +
" ( " +
" select " +
" n_name as nation, " +
" extract(year from o_orderdate) as o_year, " +
" l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount " +
" from " +
" part, " +
" supplier, " +
" lineitem, " +
" partsupp, " +
" orders, " +
" nation " +
" where " +
" s_suppkey = l_suppkey " +
" and ps_suppkey = l_suppkey " +
" and ps_partkey = l_partkey " +
" and p_partkey = l_partkey " +
" and o_orderkey = l_orderkey " +
" and s_nationkey = n_nationkey " +
" and p_name like '%lemon%' " +
" )profit " +
" where rownum <= 10 " +
" group by " +
" nation, " +
" o_year " +
" order by " +
" nation, " +
" o_year desc " ;
}
else if (query_id==10)
{
return "\n"+
" select " +
" c_custkey, " +
" c_name, " +
" sum(l_extendedprice * (1 - l_discount)) as revenue, " +
" c_acctbal, " +
" n_name, " +
" c_address, " +
" c_phone, " +
" c_comment " +
" from " +
" customer, " +
" orders, " +
" lineitem, " +
" nation " +
" where " +
" c_custkey = o_custkey " +
" and l_orderkey = o_orderkey " +
" and o_orderdate >= to_date('1992-01-01','YYYY-MM-DD') " +
" and o_orderdate < to_date('1996-01-01','YYYY-MM-DD') + interval '3' month " +
" and l_returnflag = 'R' " +
" and c_nationkey = n_nationkey " +
" and rownum <= 10 " +
" group by " +
" c_custkey, " +
" c_name, " +
" c_acctbal, " +
" c_phone, " +
" n_name, " +
" c_address, " +
" c_comment " +
" order by " +
" revenue desc " ;
}
else if (query_id==11)
{
return "\n"+
" select " +
" ps_partkey, " +
" sum(ps_supplycost * ps_availqty) as value " +
" from " +
" partsupp, " +
" supplier, " +
" nation " +
" where " +
" ps_suppkey = s_suppkey " +
" and s_nationkey = n_nationkey " +
" and n_name = 'UNITED KINGDOM' " +
" and rownum <= 10 " +
" group by " +
" ps_partkey having " +
" sum(ps_supplycost * ps_availqty) > ( " +
" select " +
" sum(ps_supplycost * ps_availqty) * 0.00015 " +
" from " +
" partsupp, " +
" supplier, " +
" nation " +
" where " +
" ps_suppkey = s_suppkey " +
" and s_nationkey = n_nationkey " +
" and n_name = 'UNITED KINGDOM' " +
" ) " +
" order by " +
" value desc " ;
}
else if (query_id==12)
{
return "\n"+
" select " +
" l_shipmode, " +
" sum(case " +
" when o_orderpriority = '1-URGENT' " +
" or o_orderpriority = '2-HIGH' " +
" then 1 " +
" else 0 " +
" end) as high_line_count, " +
" sum(case " +
" when o_orderpriority <> '1-URGENT' " +
" and o_orderpriority <> '2-HIGH' " +
" then 1 " +
" else 0 " +
" end) as low_line_count " +
" from " +
" orders, " +
" lineitem " +
" where " +
" o_orderkey = l_orderkey " +
" and l_shipmode in ('MAIL', 'AIR') " +
" and l_commitdate < l_receiptdate " +
" and l_shipdate < l_commitdate " +
" and l_receiptdate >= to_date('1996-01-01','YYYY-MM-DD') " +
" and l_receiptdate < to_date('1997-01-01','YYYY-MM-DD') + interval '1' year " +
" and rownum <= 10 " +
" group by " +
" l_shipmode " +
" order by " +
" l_shipmode ";
}
else if (query_id==13)
{
return "\n"+
" select " +
" c_count, " +
" count(*) as custdist " +
" from " +
" ( " +
" select " +
" c_custkey, " +
" count(o_orderkey) as c_count " +
" from " +
" customer left outer join orders on " +
" c_custkey = o_custkey " +
" and o_comment not like '%deposits%' " +
" group by " +
" c_custkey " +
" ) c_orders " +
" group by " +
" c_count " +
" order by " +
" custdist desc, " +
" c_count desc; " ;
}
else if (query_id==14)
{
return "\n"+
" select " +
" 100.00 * sum(case " +
" when p_type like 'PROMO%' " +
" then l_extendedprice * (1 - l_discount) " +
" else 0 " +
" end) / sum(l_extendedprice * (1 - l_discount)) as promo_revenue " +
" from " +
" lineitem, " +
" part " +
" where " +
" l_partkey = p_partkey " +
" and l_shipdate >= to_date('1996-01-01','YYYY-MM-DD') " +
" and l_shipdate < to_date('1997-01-01','YYYY-MM-DD') + interval '1' month ";
}
else if (query_id==15)
{
return "\n"+
" create view revenue0 (supplier_no, total_revenue) as " +
" select " +
" l_suppkey, " +
" sum(l_extendedprice * (1 - l_discount)) " +
" from " +
" lineitem " +
" where " +
" l_shipdate >= to_date('1996-01-01','YYYY-MM-DD') " +
" and l_shipdate < to_date('1996-01-01','YYYY-MM-DD') + interval '3' month " +
" group by " +
" l_suppkey; " +
" " +
" " +
" select " +
" s_suppkey, " +
" s_name, " +
" s_address, " +
" s_phone, " +
" total_revenue " +
" from " +
" supplier, " +
" revenue0 " +
" where " +
" s_suppkey = supplier_no " +
" and total_revenue = ( " +
" select " +
" max(total_revenue) " +
" from " +
" revenue0 " +
" ) " +
" order by " +
" s_suppkey; " +
" drop view revenue0 " ;
}
else if (query_id==16)
{
return "\n"+
" select " +
" p_brand, " +
" p_type, " +
" p_size, " +
" count(distinct ps_suppkey) as supplier_cnt " +
" from " +
" partsupp, " +
" part " +
" where " +
" p_partkey = ps_partkey " +
" and p_brand <> 'Brand#24' " +
" and p_type not like 'PROMO%' " +
" and p_size in (34,36,44,48,6,32,11,21) " +
" and ps_suppkey not in ( " +
" select " +
" s_suppkey " +
" from " +
" supplier " +
" where " +
" s_comment like '%Customer%Complaints%' " +
" ) " +
" group by " +
" p_brand, " +
" p_type, " +
" p_size " +
" order by " +
" supplier_cnt desc, " +
" p_brand, " +
" p_type, " +
" p_size ";
}
else if (query_id==17)
{
return "\n"+
" Select sysdate from dual ";
/*
* select
sum(l_extendedprice) / 7.0 as avg_yearly
from
lineitem,
part
where
p_partkey = l_partkey
and p_brand = 'Brand#24'
and p_container = 'SM BOX'
and l_quantity < (
select
0.2 * avg(l_quantity)
from
lineitem
where
l_partkey = p_partkey
);
*/
}
else if (query_id==18)
{
return "\n"+
" select " +
" c_name, " +
" c_custkey, " +
" o_orderkey, " +
" o_orderdate, " +
" o_totalprice, " +
" sum(l_quantity) " +
" from " +
" customer, " +
" orders, " +
" lineitem " +
" where " +
" o_orderkey in ( " +
" select " +
" l_orderkey " +
" from " +
" lineitem " +
" group by " +
" l_orderkey having " +
" sum(l_quantity) > 10 " +
" ) " +
" and c_custkey = o_custkey " +
" and o_orderkey = l_orderkey " +
" and rownum < 10 " +
" group by " +
" c_name, " +
" c_custkey, " +
" o_orderkey, " +
" o_orderdate, " +
" o_totalprice " +
" order by " +
" o_totalprice desc, " +
" o_orderdate; " ;
}
else if (query_id==19)
{
return "\n"+
" select "+
" sum(l_extendedprice* (1 - l_discount)) as revenue " +
" from " +
" lineitem, " +
" part " +
" where " +
" ( " +
" p_partkey = l_partkey " +
" and p_brand = 'Brand#24' " +
" and p_container in ('SM CASE', 'SM BOX', 'SM PACK', 'SM PKG') " +
" and l_quantity >=9.00 and l_quantity <=9.00 + 10 " +
" and p_size between 1 and 5 " +
" and l_shipmode in ('AIR', 'AIR REG') " +
" and l_shipinstruct = 'DELIVER IN PERSON' " +
" ) " +
" or " +
" ( " +
" p_partkey = l_partkey " +
" and p_brand = 'Brand#33' " +
" and p_container in ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK') " +
" and l_quantity >=13.00 and l_quantity <=13.00 + 10 " +
" and p_size between 1 and 10 " +
" and l_shipmode in ('AIR', 'AIR REG') " +
" and l_shipinstruct = 'DELIVER IN PERSON' " +
" ) " +
" or " +
" ( " +
" p_partkey = l_partkey " +
" and p_brand = 'Brand#13' " +
" and p_container in ('LG CASE', 'LG BOX', 'LG PACK', 'LG PKG') " +
" and l_quantity >=47.00 and l_quantity <=47.00 + 10 " +
" and p_size between 1 and 15 " +
" and l_shipmode in ('AIR', 'AIR REG') " +
" and l_shipinstruct = 'DELIVER IN PERSON' " +
" ) " +
" and rownum < 10 " ;
}
else if (query_id==20)
{
return "\n"+
" select " +
" cntrycode, " +
" count(*) as numcust, " +
" sum(c_acctbal) as totacctbal " +
" from " +
" ( " +
" select " +
" substr(c_phone,1,2) as cntrycode, " +
" c_acctbal " +
" from " +
" customer " +
" where " +
" substr(c_phone,1,2) in " +
" ('25', '23', '11', '14', '12', '20', '16') " +
" and c_acctbal > ( " +
" select " +
" avg(c_acctbal) " +
" from " +
" customer " +
" where " +
" c_acctbal > 0.00 " +
" and substr(c_phone,1,2) in " +
" ('25', '23', '11', '14', '12', '20', '16') " +
" ) " +
" and not exists ( " +
" select " +
" * " +
" from " +
" orders " +
" where " +
" o_custkey = c_custkey " +
" ) " +
" ) custsale " +
" group by " +
" cntrycode " +
" order by " +
" cntrycode";
}
else
{
return "\n"+
" Select sysdate from dual ";
}
}
public static void main(String[] args)
{
int max_thread_number = 1;
for (int i = 0; i <= max_thread_number ; i++)
{
Runnable task = new Action(i);
Thread worker = new Thread(task);
worker.start();
}
}
}
