Home > Querying Dimensional Objects > Joining Cubes to Tables and...
![]() Previous |
![]() Next |
You can join cubes to other cubes and to relational objects such as:
Tables
Views including external tables and PL/SQL table functions
Other row source types, like other joins
Typically, you do not need a fully aggregated cube when joining it to a table or view, and a CUBE JOIN
operation limits the number of fetched values to improve performance automatically. The cube must be on the right side of the equation. If the query does not support CUBE JOIN
, then the more expensive HASH JOIN
, MERGE JOIN
, or NESTED LOOPS
are commonly used.
You can use hints in the query to influence the use of CUBE JOIN
:
USE_CUBE
forces a CUBE JOIN
if possible.
NO_USE_CUBE
prevents a CUBE JOIN
.
See "Viewing Execution Plans" for more information about CUBE JOIN
.
Example: Joining a Cube and a Table joins a table that contains French descriptions of the Customer dimension to a cube that supports only English. The query returns these results:
CUSTOMER SALES ---------------------------------------- ------------ La Marine des USA Washington 600.34 Monolith Motor Co. Chattanooga 17946.51 Piedmont, Inc. San Jose 24874.41 Ministere du Commerce Int. Nagano 27595.97 Depart. des commun. - Stuttgart 30706.10 Min. Env. Brit. Londres 38125.77 Departement de travail Nouvelle-Orleans 42507.50 Ministere des Finances Sorbonne 43607.58 Monolith Motor Co. Knoxville 50874.53 Serv. des USA de recherche Wyo 54497.19 Depart. des commun. - Bonn 58944.97 . . .
Joining a Cube and a Table
SELECT cu.ship_to_dsc_french customer, f.sales sales FROM time_calendar_view t, product_primary_view p, customer_dim cu, channel_primary_view ch, units_cube_view f WHERE t.dim_key = 'CY2006' AND p.level_name = 'TOTAL' AND ch.level_name = 'TOTAL' AND t.dim_key = f.TIME AND p.dim_key = f.product AND cu.ship_to_id = f.customer AND ch.dim_key = f.channel ORDER BY f.sales;