You will learn
- Viewing the combined data set for data partitioned between an in-memory and a Dynamic Tiering table instance.
- Creating and using a SQL view.
- Querying against a SQL view with conditions.
Create and use a SQL view to query data from both in-memory and Dynamic Tiering tables.
You may have many 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
SELECT * FROM "TPCH"."ORDERS_CS" UNION ALL SELECT * FROM "TPCH"."ORDERS_DT"
Note: Since we are explicitly managing the data set between
ORDERS_DTto ensure that data is not duplicated between the 2 tables, we can use the
UNION ALLvariation of the
UNIONclause, 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.
CREATE VIEW "TPCH"."ORDERS_ALL_VIEW" AS SELECT * FROM "TPCH"."ORDERS_CS" UNION ALL SELECT * FROM "TPCH"."ORDERS_DT";
Verify the script executed correctly.
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)