Michael A. Georgiou マイケル: April 2011

Step1
==============================
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();
}
}

}

Michael A. Georgiou
マイケル

Search This Blog

Thursday, April 28, 2011

Compile Oracle forms,reports etc. on Unix

Wednesday, April 20, 2011

Set User's password never exprired in AIX 5L

Wednesday, April 13, 2011

Rolling Patch Upgrade Using RAC

Create Oracle User

Monday, April 4, 2011

Deleting Expired RMAN Backups and Archives Logs after CROSSCHECK

Saturday, April 2, 2011

Prepare a stress testing using TPC-H benchmark

Total Pageviews