To execute the create dimension command, you must have the
CREATE DIMENSION system privilege. In this example, countries are part of continents, so you
can create tables and dimensions to support this hierarchy:
create dimension GEOGRAPHY
level COUNTRY_ID is COUNTRY.Country
level CONTINENT_id is CONTINENT.Continent
hierarchy COUNTRY_ROLLUP (
COUNTRY_ID child of
CONTINENT_ID
join key COUNTRY.Continent references CONTINENT_id);
To enable a materialized view for query rewrite, you must place all the master tables for the
materialized view in the materialized view??™s schema, and you must have the QUERY REWRITE
system privilege. In general, you should create materialized views in the same schema as the
tables on which they are based; otherwise, you will need to manage the permissions and grants
required to create and maintain the materialized views.
NOTE
You can enable or disable query rewrite at the SQL statement level via
the REWRITE and NOREWRITE hints. When using the REWRITE hint,
you can specify materialized views for the optimizer to consider.
630 Oracle Database 11g DBA Handbook
NOTE
Query rewrite decisions are based on the costs of the different
execution paths, so your statistics should be kept up to date.
For query rewrite to be enabled, you must set the following initialization parameters:
OPTIMIZER_MODE = ALL_ROWS or FIRST_ROWS or FIRST_ROWS_n
QUERY_REWRITE_ENABLED = TRUE
QUERY_REWRITE_INTEGRITY = STALE_TOLERATED, TRUSTED, or ENFORCED
By default, QUERY_REWRITE_INTEGRITY is set to ENFORCED; in this mode, all constraints
must be validated.
Pages:
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927