View Data Across Both In-Memory and Dynamic Tiering Tables Using a SQL View

There are cases where you only need to access data either from the in-memory table instance (ORDERS_CS) or from the dynamic tiering table instance (ORDERS_DT). However you will also likely have use cases where you need to query the full data set across both table instances, which can be done with a union. Run the script below in a SQL Console to query data from both in-memory and dynamic tiering tables using a UNION.

SELECT * FROM "TPCH"."ORDERS_CS"
UNION ALL
SELECT * FROM "TPCH"."ORDERS_DT"

Since we are explicitly managing the data set between ORDERS_CS and ORDERS_DT to ensure that data is not duplicated between the 2 tables, we can use the UNION ALL variation of the UNION clause, which is faster because it doesn’t eliminate duplicate records in the combined result set.

Alternatively you can create an SQL view to query data from multiple tables and simplify the process. Run the script below to create a SQL view. Verify it ran successfully.

CREATE VIEW "TPCH"."ORDERS_ALL_VIEW" AS
  SELECT * FROM "TPCH"."ORDERS_CS"
  UNION ALL
  SELECT * FROM "TPCH"."ORDERS_DT";

After successfully creating a view, you can query against the "TPCH"."ORDERS_ALL_VIEW" whenever you need to access the combined data set. For example, if you want to query order records that are between 6 months and 18 months old, you can execute the query below.

SELECT "TPCH"."ORDERS_ALL_VIEW".* FROM "TPCH"."ORDERS_ALL_VIEW"
    WHERE "TPCH"."ORDERS_ALL_VIEW" ."O_ORDERDATE"
      BETWEEN ADD_YEARS(CURRENT_DATE, -1.5)
          AND ADD_YEARS(CURRENT_DATE, -0.5)