Oracle by Example brandingRunning a Query on Sample Data in Autonomous Data Warehouse

section 0Before 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.

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.

section 1 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.
    connection for adwc_user
    Description of the illustration connection_for_adwc_user
  2. Proceed with sections 2 and 3 of this tutorial.

section 1 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;
    result of SH query
    Description of the illustration result_of_sh_query

section 1Query 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. create new connection as adwc_user with service high
    Description of the illustration create_new_connection_as_adwc_user_service_level_high
  3. 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;
  4. Your SQL Developer worksheet and script output will appear similar to the following:

    SSB query resuls
    Description of the illustration ssb_query_result

    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 stepNext Tutorial

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

more informationWant to Learn More?