Running a Query on Sample Data in Autonomous Data Warehouse

Before You Begin

This tutorial shows you how to use SQL Developer to query sample data that comes with Autonomous Data Warehouse. This tutorial takes approximately 10 minutes to complete.

Oracle Autonomous Data Warehouse Cloud Service Tutorial Series

This is the fourth in a series of tutorials for the Autonomous Data Warehouse Cloud. Perform the tutorials sequentially.

• Provisioning Autonomous Data Warehouse Cloud
• Connecting SQL Developer and Creating a Table
• Loading Your Data
• Running a Query on Sample Data in Autonomous Data Warehouse
• Using Oracle Machine Learning with Autonomous Data Warehouse Cloud (set of additional tutorials)

Background

If you want to start using the Autonomous Data Warehouse service without creating your own tables, the service provides the read-only Sales History and Star Schema Benchmark data sets. These data sets are provided as Oracle Database schemas SH and SSB, respectively.

What Do You Need?

• Access to an instance of Autonomous Data Warehouse (see previous tutorials in this series and the documentation: Using Oracle Autonomous Data Warehouse).
• Oracle SQL Developer (see Oracle Technology Network download site). Version 18.3 or later. Versions 18.2 or later contain enhancements for key Autonomous Data Warehouse features.
  Note:
  If you are a Windows user on 64-bit platform, download the 'Windows 64-bit with JDK 8 included' distribution as it includes the files necessary to run SQL Developer and connect to your Autonomous Data Warehouse database.
  
  If you are using a SQL Developer version earlier than 18.2, see the documentation topic Connecting with Oracle SQL Developer (earlier than Version 18.2).
• A client credentials zip file and its keystore password, which you defined in a previous tutorial or obtained from your Autonomous Data Warehouse administrator. This file contains security credentials to connect SQL Developer to your Autonomous Data Warehouse database.

Connect SQL Developer to Your Autonomous Data Warehouse Database

You can query the sample Sales History (SH) and Star Schema Benchmark (SSB) data sets using one of the following SQL Developer connections to your Autonomous Data Warehouse database:

• The connection you created for the user admin that you created in the first tutorial in this series, Provisioning Autonomous Data Warehouse.
• The connection you created for the user adwc_user that you created in the second tutorial in this series, Connecting SQL Developer and Creating a Table.

Note: You can run the SH and SSB queries from the admin user or any user that you define in Autonomous Data Warehouse. However, the SH and SSB schemas are read-only, so you cannot log in to those schemas. In your queries, always reference their tables prepended with the schema, as in sh.tablename or ssb.tablename.

1. Open SQL Developer using the connection you defined for admin or adwc_user.



2. Proceed with sections 2 and 3 of this tutorial.

Query the Sample Sales History Data Set

The SH schema provides a small data set you can use to run the sample queries in the Oracle Database Data Warehousing Guide. For example, the following query shows you how the SQL function RANK() works:

1. In a SQL Developer worksheet, perform the following SH query.
   
   

   SELECT channel_desc, TO_CHAR(SUM(amount_sold),'9,999,999,999') SALES$,
      RANK() OVER (ORDER BY SUM(amount_sold)) AS default_rank,
      RANK() OVER (ORDER BY SUM(amount_sold) DESC NULLS LAST) AS custom_rank
   FROM sh.sales, sh.products, sh.customers, sh.times, sh.channels, sh.countries
   WHERE sales.prod_id=products.prod_id AND sales.cust_id=customers.cust_id
     AND customers.country_id = countries.country_id AND sales.time_id=times.time_id
     AND sales.channel_id=channels.channel_id
     AND times.calendar_month_desc IN ('2000-09', '2000-10')
     AND country_iso_code='US'
   GROUP BY channel_desc;

   

Query the Sample Star Schema Benchmark Data Set

Oracle Autonomous Data Warehouse comes with another sample data set, the Star Schema Benchmark (SSB) data set. This data set is in the SSB schema in your database. The SSB schema contains the tables: lineorder, customer, supplier, part, and dwdate.

The SSB schema provides a well-known large sample data set. The SSB schema in Autonomous Data Warehouse contains 1 TB of data. You can use this schema to test the performance of your service. You can run the sample queries on this schema with the different database services, HIGH, MEDIUM, and LOW, and with different number of OCPUs to test the performance of different Autonomous Data Warehouse configurations.

1. Open SQL Developer and create a new connection to your Autonomous Data Warehouse database as the user you defined in the first tutorial, adwc_user.
   Be sure to select the High Service level resource group.
   Note: See the related tutorial, Provisioning Autonomous Data Warehouse, for steps to create SQL Developer connections to your Autonomous Data Warehouse database. 



2. In a SQL Developer worksheet, perform any of the following subset of SSB queries.
   

   set timing on
   set echo on
   
   select sum(lo_extendedprice*lo_discount) as revenue
   from ssb.lineorder, ssb.dwdate where lo_orderdate = d_datekey
   and d_year = 1993
   and lo_discount between 1 and 3
   and lo_quantity < 25;
   
   select c_nation, s_nation, d_year, sum(lo_revenue) as revenue
   from ssb.customer, ssb.lineorder, ssb.supplier, ssb.dwdate
   where lo_custkey = c_custkey
   and lo_suppkey = s_suppkey
   and lo_orderdate = d_datekey
   and c_region = 'ASIA' and s_region = 'ASIA'
   and d_year >= 1992 and d_year <= 1997
   group by c_nation, s_nation, d_year
   order by d_year asc, revenue desc;
   
   select c_city, s_city, d_year, sum(lo_revenue) as revenue
   from ssb.customer, ssb.lineorder, ssb.supplier, ssb.dwdate
   where lo_custkey = c_custkey
   and lo_suppkey = s_suppkey
   and lo_orderdate = d_datekey
   and c_nation = 'UNITED STATES'
   and s_nation = 'UNITED STATES'
   and d_year >= 1992 and d_year <= 1997
   group by c_city, s_city, d_year
   order by d_year asc, revenue desc;
   
   select c_city, s_city, d_year, sum(lo_revenue) as revenue
   from ssb.customer, ssb.lineorder, ssb.supplier, ssb.dwdate
   where lo_custkey = c_custkey
   and lo_suppkey = s_suppkey
   and lo_orderdate = d_datekey
   and (c_city='UNITED KI1'
   or c_city='UNITED KI5')
   and (s_city='UNITED KI1'
   or s_city='UNITED KI5')
   and d_year >= 1992 and d_year <= 1997
   group by c_city, s_city, d_year
   order by d_year asc, revenue desc;

Your SQL Developer worksheet and script output will appear similar to the following:



For the full set of sample queries for the SSB schema, see Appendix D of Using Oracle Autonomous Data Warehouse: Sample Star Schema Benchmark (SSB) Queries.

Next Tutorial

Using Oracle Machine Learning with Autonomous Data Warehouse Cloud (set of additional tutorials)

Want to Learn More?

• Autonomous Data Warehouse website