ssb scala=10 测试比较

显示全部楼层 · 2012-5-21 10:19:41

create user ssb identified by ssb default tablespace ts quota unlimited on ts;
grant dba to ssb;
conn ssb/ssb
CREATE TABLE CUSTOMER ( C_CUSTKEY INTEGER,

C_NAMEVARCHAR(25) NOT NULL,

C_ADDRESS VARCHAR(40) NOT NULL,

C_CITYVARCHAR(10) NOT NULL,

C_NATIONVARCHAR(15) NOT NULL,

C_REGIONVARCHAR(12) NOT NULL,

C_PHONE VARCHAR(15) NOT NULL,

C_MKTSEGMENTVARCHAR(10) NOT NULL);
CREATE TABLE DATES ( D_DATEKEY
INTEGER,

D_DATE
VARCHAR(18) NOT NULL,

D_DAYOFWEEKVARCHAR(9) NOT NULL,

D_MONTH
VARCHAR(9) NOT NULL,

D_YEAR
INTEGER NOT NULL,

D_YEARMONTHNUM INTEGER,

D_YEARMONTHVARCHAR(7) NOT NULL,

D_DAYNUMINWEEK INTEGER,

D_DAYNUMINMONTHINTEGER,

D_DAYNUMINYEAR INTEGER,

D_MONTHNUMINYEAR INTEGER,

D_WEEKNUMINYEARINTEGER,

D_SELLINGSEASONVARCHAR(12) NOT NULL,

D_LASTDAYINWEEKFLINTEGER,

D_LASTDAYINMONTHFL INTEGER,

D_HOLIDAYFLINTEGER,

D_WEEKDAYFLINTEGER);

CREATE TABLE PART( P_PARTKEY INTEGER,

P_NAMEVARCHAR(22) NOT NULL,

P_MFGRVARCHAR(6) NOT NULL,

P_CATEGORYVARCHAR(7) NOT NULL,

P_BRAND VARCHAR(9) NOT NULL,

P_COLOR VARCHAR(11) NOT NULL,

P_TYPEVARCHAR(25) NOT NULL,

P_SIZEINTEGER NOT NULL,

P_CONTAINER VARCHAR(10) NOT NULL);
CREATE TABLE SUPPLIER ( S_SUPPKEY INTEGER,

S_NAMEVARCHAR(25) NOT NULL,

S_ADDRESS VARCHAR(25) NOT NULL,

S_CITYVARCHAR(10) NOT NULL,

S_NATIONVARCHAR(15) NOT NULL,

S_REGIONVARCHAR(12) NOT NULL,

S_PHONE VARCHAR(15) NOT NULL);
CREATE TABLE LINEORDER ( LO_ORDERKEYNUMBER(15,0),

LO_LINENUMBERNUMBER(15,0),

LO_CUSTKEYINTEGER NOT NULL,

LO_PARTKEYINTEGER NOT NULL,

LO_SUPPKEYINTEGER NOT NULL,

LO_ORDERDATEINTEGER NOT NULL,

LO_ORDERPRIOTITYVARCHAR(15) NOT NULL,

LO_SHIPPRIOTITY INTEGER,

LO_QUANTITYNUMBER(15,0),

LO_EXTENDEDPRICE NUMBER(15,0),

LO_ORDTOTALPRICE NUMBER(15,0),

LO_DISCOUNTNUMBER(15,0),

LO_REVENUE NUMBER(15,0),

LO_SUPPLYCOSTNUMBER(15,0),

LO_TAX NUMBER(15,0),

LO_COMMITDATE INTEGER NOT NULL,

LO_SHIPMODE VARCHAR(10) NOT NULL);

sqlldr ssb/ssb control=part_sqlldr.ctl direct=true streamsize=4096000
...

infobht 342 ssb10
oracle 110202 ssb10
Q1.1
1 row in set (7.80 sec)
已用时间:00: 00: 09.34
Q1.2
1 row in set (4.59 sec)
已用时间:00: 00: 08.49
Q1.3
1 row in set (4.51 sec)
已用时间:00: 00: 08.48
Q2.1
280 rows in set (20.33 sec)
已用时间:00: 00: 13.22
Q2.2
56 rows in set (12.27 sec)
已用时间:00: 00: 11.38
Q2.3
7 rows in set (10.51 sec)
已用时间:00: 00: 10.85
Q3.1
150 rows in set (22.27 sec)
已用时间:00: 00: 17.89
Q3.2
600 rows in set (13.34 sec)
已用时间:00: 00: 12.36
Q3.3
24 rows in set (21.58 sec)
已用时间:00: 00: 11.50
Q3.4
4 rows in set (20.15 sec)
已用时间:00: 00: 11.34
Q4.1
35 rows in set (1 min 7.77 sec)
已用时间:00: 00: 21.36
Q4.2
100 rows in set (36.64 sec)
已用时间:00: 00: 18.06
Q4.3
800 rows in set (34.29 sec)
已用时间:00: 00: 12.95

千问 · 2012-5-21 10:19:41

PROMPT Q1.1
SELECT SUM(LO_EXTENDEDPRICE*LO_DISCOUNT) AS
REVENUE
FROMLINEORDER, DATES
WHERELO_ORDERDATE = D_DATEKEY
AND D_YEAR = 1993
AND LO_DISCOUNT BETWEEN 1 AND 3
AND LO_QUANTITY = 1992 AND D_YEAR = 1992 AND D_YEAR = 1992 AND D_YEARset timi on
SQL> PROMPT Q1.1
Q1.1
SQL> SELECT SUM(LO_EXTENDEDPRICE*LO_DISCOUNT) AS
2REVENUE
3FROMLINEORDER, DATES
4WHERELO_ORDERDATE = D_DATEKEY
5AND D_YEAR = 1993
6AND LO_DISCOUNT BETWEEN 1 AND 3
7AND LO_QUANTITYSQL>
SQL> /
REVENUE
----------
4.4682E+12
已用时间:00: 00: 12.70
SQL> SELECT SUM(LO_EXTENDEDPRICE*LO_DISCOUNT) AS
2REVENUE
3FROMLINEORDER--- , DATES
4WHERELO_ORDERDATE
5 BETWEEN (SELECT MIN(D_DATEKEY) FROM DATES WHERE D_YEAR = 1993)
6
AND (SELECT MAX(D_DATEKEY) FROM DATES WHERE D_YEAR = 1993)
7AND LO_DISCOUNT BETWEEN 1 AND 3
8AND LO_QUANTITYSQL> /
REVENUE
----------
4.4682E+12
已用时间:00: 00: 11.67
SQL> alter table lineorder parallel;
表已更改。
已用时间:00: 00: 00.01
SQL> SELECT SUM(LO_EXTENDEDPRICE*LO_DISCOUNT) AS
2REVENUE
3FROMLINEORDER--- , DATES
4WHERELO_ORDERDATE
5 BETWEEN (SELECT MIN(D_DATEKEY) FROM DATES WHERE D_YEAR = 1993)
6
AND (SELECT MAX(D_DATEKEY) FROM DATES WHERE D_YEAR = 1993)
7AND LO_DISCOUNT BETWEEN 1 AND 3
8AND LO_QUANTITYSQL> /
REVENUE
----------
4.4682E+12
已用时间:00: 00: 01.28
SQL> PROMPT Q1.1
Q1.1
SQL> SELECT SUM(LO_EXTENDEDPRICE*LO_DISCOUNT) AS
2REVENUE
3FROMLINEORDER, DATES
4WHERELO_ORDERDATE = D_DATEKEY
5AND D_YEAR = 1993
6AND LO_DISCOUNT BETWEEN 1 AND 3
7AND LO_QUANTITY/
REVENUE
----------
4.4682E+12
已用时间:00: 00: 01.18
SQL> show parallel ^H^H^H
SP2-0158: 未知的 SHOW 选项 "parallel"
SP2-0158: 未知的 SHOW 选? "
SQL> show parameter cpu
NAME
TYPEVALUE
------------------------------------ ----------- ------------------------------
cpu_count
integer 8
parallel_threads_per_cpu
integer 2
resource_manager_cpu_allocationinteger 8
S

千问 · 2012-5-21 10:19:41

tpch压缩比
导入前
[root@redflag11012501 Calpont]# du --max-depth=1 -h data1
28K data1/systemFiles
4.2Gdata1/000.dir
4.2Gdata1
[root@redflag11012501 tpch2]# du -sh *tbl
236Mcustomer.tbl
7.4Glineitem.tbl
4.0Knation.tbl
1.7Gorders.tbl
1.2Gpartsupp.tbl
235Mpart.tbl
4.0Kregion.tbl
14M supplier.tbl
[root@redflag11012501 ~]# du -sh /usr/local/Calpont/data1
12G /usr/local/Calpont/data1

千问 · 2012-5-21 10:19:41

其中有一个37.40秒运行第二次变成1.28, 就是因为物理读？
试过在Q1.2 Q1.3用我的BETWEEN方法吗？

千问 · 2012-5-21 10:19:41

并行的比较

千问 · 2012-5-21 10:19:41

ssb10
oracle parallel
Q1.1
1.97
Q1.2
1.12
Q1.3
1.25
Q2.1
2.17
Q2.2
3.81
Q2.3
1.64
Q3.1
2.67
Q3.2
1.84
Q3.3
1.72
Q3.4
2.04
Q4.1
2.82
Q4.2
2.57
Q4.3
1.99