Home > Workload Solutions > Oracle > Guides > Design Guide — Oracle Big Data SQL on Dell EMC PowerFlex > Data virtualization with Big Data SQL
Once you have created Oracle external tables that point to data sources HDFS, Oracle NoSQL, and gateways for Microsoft SQL Server by following steps 1-3 above, you can then run standard TPCH queries from the Oracle database to join the tables on different data sources to showcase data virtualization.
The following architecture diagram shows data virtualization from the Oracle database:
We ran the following TPCH standard queries on the Oracle Database:
-- Q1 : = >
----------------------------------------------------------------------------------
/* TPC_H Query 1 - Shipping Priority */ SELECT TOP 10 L_ORDERKEY, SUM(L_EXTENDEDPRICE*(1-L_DISCOUNT)) AS REVENUE, O_ORDERDATE, O_SHIPPRIORITY FROM EXT_HDFS_CUSTOMER, EXT_HDFS_ORDERS, EXT_HDFS_LINEITEM WHERE C_MKTSEGMENT = 'FURNITURE' AND C_CUSTKEY = O_CUSTKEY AND L_ORDERKEY = O_ORDERKEY AND O_ORDERDATE < '1995-03-06' AND L_SHIPDATE > '1995-03-06' GROUP BY L_ORDERKEY, O_ORDERDATE, O_SHIPPRIORITY ORDER BY REVENUE DESC, O_ORDERDATE --------------------------------------------------------------------------------------------------
Output Summary:
----------------------------------------------------------------------------------
L_ORDERKEY REVENUE O_ORDERDATE O_SHIPPRIORITY
----------------------------------------------------------------
2455724 306271.02 1995-03-06 0
-- Q2 : = >
-------------------------------------------------------------------------------------------------------------------- /* TPC_H Query 2 - Order Priority Checking */
select o_orderpriority, count(*) as ORDER_COUNT from EXT_HDFS_ORDERS where o_orderdate >= date '[1993-07-01]' and o_orderdate < date '[1995-06-01]' + interval '3' month and exists ( select * from EXT_HDFS_LINEITEM where l_orderkey = o_orderkey and l_commitdate < l_receiptdate ) group by o_orderpriority order by o_orderpriority;
---------------------------------------------------------------------------------
Output Summary:
----------------------------------------------------------------------------------
O_ORDERPRIORITY ORDER_COUNT
-------------------------------
1-URGENT 10584
-- Q3 : = >
--------------------------------------------------------------------------------------------------------- /* TPC_H Query 3 - Important Stock Identification */
SELECT PS_PARTKEY, SUM(PS_SUPPLYCOST*PS_AVAILQTY) AS VALUE FROM EXT_HDFS_PARTSUPP, SUPPLIER, EXT_ORA_NATION WHERE PS_SUPPKEY = S_SUPPKEY AND S_NATIONKEY = N_NATIONKEY AND N_NAME = 'ARGENTINA' GROUP BY PS_PARTKEY HAVING SUM(PS_SUPPLYCOST*PS_AVAILQTY) > ( SELECT SUM(PS_SUPPLYCOST*PS_AVAILQTY) * 0.0000001000 FROM EXT_HDFS_PARTSUPP, SUPPLIER, EXT_ORA_NATION WHERE PS_SUPPKEY = S_SUPPKEY AND S_NATIONKEY = N_NATIONKEY AND N_NAME = 'ARGENTINA' ) ORDER BY VALUE DESC
----------------------------------------------------------------------------------
Output Summary:
----------------------------------------------------------------------------------
PS_PARTKEY VALUE
----------------------------------------------------------------------------------
129760 17538456.86