Home > Workload Solutions > Oracle > White Papers > Oracle Big Data SQL on Dell EMC PowerFlex > TPC-H Query 4 test
This query used all the tables except PARTSUPP and is the most comprehensive test because it pulls data from all four data sources: Oracle Big Data SQL, ONDB, Cloudera Hadoop, and Microsoft SQL Server.
/* TPC_H Query 8 - National Market Share */
SELECT O_YEAR,
SUM(CASE WHEN NATION = 'CHINA' 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,
EXT_HDFS_LINEITEM,
EXT_HDFS_ORDERS,
EXT_HDFS_CUSTOMER,
EXT_ORA_NATION N1,
EXT_ORA_NATION N2,
EXT_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 = 'ASIA' AND
S_NATIONKEY = N2.N_NATIONKEY AND
O_ORDERDATE BETWEEN '1995-01-01' AND '1996-12-31' AND
P_TYPE = 'SMALL BRUSHED COPPER'
) AS ALL_NATIONS
GROUP BY O_YEAR
ORDER BY O_YEAR
Modifying the four queries used to test data virtualization provided insights into the value of using Oracle Big Data SQL. There is a minor learning curve to understand how to connect disparate data sources. For example, in this solution we used Oracle Big Data SQL access drivers to connect to Cloudera Hadoop. To connect to the Microsoft SQL Server database, we used Oracle Database Gateway. The two different access methods meant less consistency in connecting disparate data sources.
The capability to quickly define external tables and start creating queries is beneficial. When the external tables are available, developers can start creating reports and performing analysis immediately, especially if the developer has Oracle and SQL or PL/SQL programming experience. Table 13 shows the four modified queries used to validate Oracle Big Data SQL data virtualization. The goal in running these queries was to test accessing all tables across all database systems.
Database Source |
Table name |
Query 1 |
Query 2 |
Query 3 |
Query 4 |
Oracle |
ORDERS |
✔ |
✔ |
|
✔ |
Oracle |
PART |
|
|
|
✔ |
ONDB |
NATION |
|
|
✔ |
✔ |
ONDB |
REGION |
|
|
|
✔ |
Cloudera Hadoop |
SUPPLIERS |
|
|
✔ |
✔ |
Cloudera Hadoop |
LINEITEM |
✔ |
✔ |
|
✔ |
SQL Server |
CUSTOMER |
✔ |
|
|
✔ |
SQL Server |
PARTSUPP |
|
|
✔ |
|