35/35

Index

A  B  C  D  E  F  G  H  I  J  K  L  M  N  O  P  Q  R  S  T  U  V  W  Z 

---

Numerics

3NF, 2.3

---

A

advantages

In-Memory Column Store, 2.5.1

aggregates, 5.2.1, 11.7.5

computability check, 11.1.3.4

aggregation

In-Memory Column Store, 19.8.6

VECTOR GROUP BY, 19.8.6

ALTER MATERIALIZED VIEW statement, 5.3

altering dimensions, 9.6

amortization

calculating, 21.5.6

analytic functions, 21.3.5

concepts, 18.1

analytic processing

materialized views, 6.2

applications

decision support systems (DSS), 4.1.2

architecture

data warehouse, 1.4

attribute clustering, 12.1

adding to existing table, 12.2.4.1

advantages, 12.1.2

data dictionary views, 12.3

dropping, 12.2.4.3

guidelines, 12.1.1.4

methods, 12.1

modifying, 12.2.4.2

privileges, 12.2.1

types, 12.1.1

interleaved ordering, 12.1.1.2

linear ordering, 12.1.1.1, 12.2.2, 12.2.3

using hints for DML, 12.2.4.4

attribute-clustered tables, 12.1

attributes, 2.2, 9.2

automatic big table caching

about, 2.6

---

B

bind variables

with query rewrite, 11.3.11

bitmap indexes, 4.1.1

nulls and, 4.1.2.1.2

on partitioned tables, 4.1.2.1.3

parallel query and DML, 4.1.2, 4.1.2

bitmap join indexes, 4.1.2.2

B-tree indexes, 4.1.3

bitmap indexes versus, 4.1.2.1

build methods, 5.3.4

business intelligence, 1.1

queries, 22

business rules

violation of, 17.4.1

---

C

cardinality

degree of, 4.1.2.1

CASE expressions, 18.7.3

cell referencing, 21.2.4

columns

cardinality, 4.1.2.1

common joins, 11.1.3.1.1

common tasks

in data warehouses, 1.3

COMPLETE clause, 5.3.7

complete refresh, 7.1.1

complex queries

snowflake schemas, 2.4.3

composite

columns, 19.6

compression

See data segment compression, 5.3.3

concatenated groupings, 19.7

concatenated ROLLUP, 19.10.1

configuring

In-Memory Column Store, 2.5

constraints, 4.2, 9.4

foreign key, 4.2.2.2

RELY, 4.2.2.3

states, 4.2.1

unique, 4.2.2.1

view, 4.2.2.7, 11.3.6

with partitioning, 4.2.2.6

with query rewrite, 11.7.1

cost-based rewrite, 11.1.1

CREATE DIMENSION statement, 9.2

CREATE MATERIALIZED VIEW statement, 5.3

enabling query rewrite, 10.2

creating

zone maps, 13.2.2.2

with attribute clustering, 13.2.2.1

CUBE clause, 19.3

partial, 19.3.3

when to use, 19.3.1

cubes

hierarchical, 6.2.1

materialized views, 6.2.1

CUME_DIST function, 18.2.1.3

---

D

data

nonvolatile, 1.1

purging, 7.5.3, 7.5.3

sufficiency check, 11.1.3.2

transformation, 17.3

transportation, 16.2.1

data compression, 4.4.1

See data segment compression, 5.3.3

data cubes

hierarchical, 19.7.1

data densification, 18.5

time series calculation, 18.6

with sparse data, 18.5.2

data marts, 1.4.3

data rules

violation of, 17.4.2

data segment compression, 3.2.1.2

materialized views, 5.3.3

partitioning, 3.2.1.2

data transformation

multistage, 17.1.1.1

pipelined, 17.1.1.2

data warehouse, 5.1

architectures, 1.4

dimension tables, 5.1.6

fact tables, 5.1.6

physical design, 3.1

refresh tips, 7.1.10

data warehouses

common tasks, 1.3

key characteristics of, 1.1.1

database

staging, 5.1

date folding

with query rewrite, 11.3.5

DBMS_ADVISOR

TUNE_MVIEW procedure, 5.3.5

DBMS_ERROR package, 17.4.2.2

DBMS_MVIEW package, 7.1.6, 7.1.6

EXPLAIN_MVIEW procedure, 5.8.1

EXPLAIN_REWRITE procedure, 11.6.2

REFRESH procedure, 7.1, 7.1.7

REFRESH_ALL_MVIEWS procedure, 7.1

REFRESH_DEPENDENT procedure, 7.1

DBMS_STATS package, 11.1.1

DBMS_SYNC_REFRESH package, 8

decision support systems (DSS)

bitmap indexes, 4.1.2

degree of cardinality, 4.1.2.1

DENSE_RANK function, 18.2.1.1

densification

data, 18.5

design

logical, 3.1

physical, 3.1

dimension levels

skipping, 9.2

dimension tables, 5.1.6

normalized, 9.2.3

dimensions, 3.2.6, 9.1, 9.4

altering, 9.6

analyzing, 19.1.1

creating, 9.2

definition, 9.1

dimension tables, 5.1.6

dropping, 9.7

hierarchies, 3.2.6.1

multiple, 19.1.1

skipping levels, 9.2

validating, 9.5

with query rewrite, 11.7.2

drilling down, 9.1

hierarchies, 9.1

DROP MATERIALIZED VIEW statement, 5.3

prebuilt tables, 5.5

dropping

dimensions, 9.7

materialized views, 5.7

zone maps, 13.2.4

---

E

ELT, 14.1

ENFORCED mode, 10.2.3

entity, 2.2

error logging, 17.4

table, 17.4.2.2

errors

handling, 17.4

ETL. See extraction, transformation, and loading (ETL), 14.1

examples

zone maps

join pruning, 13.4.2.2

partition and table scan pruning, 13.4.2.1

EXCHANGE PARTITION statement, 4.2.2.6

execution plans

star transformations, 4.5.2.2

EXPAND_GSET_TO_UNION hint, 11.3.8.2, 11.7.10.4

EXPLAIN PLAN statement, 11.6.1

star transformations, 4.5.2.2

EXPLAIN_REWRITE procedure, 11.6.2

exporting

EXP utility, 15.3.1.3

expression matching

with query rewrite, 11.3.10

external tables, 17.2.2

extraction, transformation, and loading (ETL), 14.1

overview, 14.1

process, 4.2

extractions

data files, 15.3.1

distributed operations, 15.3.2

full, 15.2.1

incremental, 15.2.1.2

OCI, 15.3.1.2

online, 15.2.2.1

overview, 15.1

physical, 15.2.2

Pro*C, 15.3.1.2

SQL*Plus, 15.3.1.1

---

F

facts, 9.1

FAST clause, 5.3.7

fast refresh, 7.1.2

restrictions, 5.3.7.1

with UNION ALL, 7.2.4

files

ultralarge, 3.2.1.1

filling gaps

with data, 18.5.6

FIRST_VALUE function, 18.2.5.1

FIRST/LAST functions, 18.3.2

FOR loops, 21.3.1

FORCE clause, 5.3.7

foreign key

constraints, 4.2.2.2

joins

snowflake schemas, 2.4.3

frequent itemsets, 18.7.4

functions

analytic, 21.3.5

COUNT, 4.1.2.1.2

CUME_DIST, 18.2.1.3

DENSE_RANK, 18.2.1.1

FIRST_VALUE, 18.2.5.1

FIRST/LAST, 18.3.2

GROUP_ID, 19.4.4

GROUPING, 19.4

GROUPING_ID, 19.4.3

LAG/LEAD, 18.2.4

LAST_VALUE, 18.2.5.1

linear regression, 18.3.5

LISTAGG, 18.3.1

NTH_VALUE, 18.2.5.2

NTILE, 18.2.1.5

PERCENT_RANK, 18.2.1.4

RANK, 18.2.1.1

ranking, 18.2.1

RATIO_TO_REPORT, 18.2.3.1

REGR_AVGX, 18.3.5.2

REGR_AVGY, 18.3.5.2

REGR_COUNT, 18.3.5.1

REGR_INTERCEPT, 18.3.5.3

REGR_SLOPE, 18.3.5.3

REGR_SXX, 18.3.5.5

REGR_SXY, 18.3.5.5

REGR_SYY, 18.3.5.5

reporting, 18.2.3

ROW_NUMBER, 18.2.1.6

WIDTH_BUCKET, 18.7.1, 18.7.1.1

window, 21.3.5

windowing, 18.2.2

---

G

GROUP_ID function, 19.4.4

grouping

compatibility check, 11.1.3.3

conditions, 11.7.6

GROUPING function, 19.4

when to use, 19.4.2

GROUPING_ID function, 19.4.3

GROUPING_SETS expression, 19.5

---

H

hierarchical cubes, 6.2.1, 19.10.1

in SQL, 19.10

hierarchies, 9.1

how used, 3.2.6.1

multiple, 9.2.2

overview, 3.2.6.1

rolling up and drilling down, 9.1

hints

EXPAND_GSET_TO_UNION, 11.3.8.2, 11.7.10.4

NOWRITE, 11.7.10.1

query rewrite, 10.2, 11.7.10.1

REWRITE, 11.7.10.1

REWRITE_OR_ERROR, 11.7.10.2

histograms

creating with user-defined buckets, 18.7.3.1

hypothetical rank, 18.3.4

---

I

IM column store

See In-Memory Column Store, 2.5

indexes

bitmap indexes, 4.1.2.1.3

bitmap join, 4.1.2.2

B-tree, 4.1.3

cardinality, 4.1.2.1

nulls and, 4.1.2.1.2

partitioned tables, 4.1.2.1.3

initialization parameters

JOB_QUEUE_PROCESSES, 7.1.10

OPTIMIZER_MODE, 7.1.12, 10.2.1

PARALLEL_MAX_SERVERS, 7.1.12

PGA_AGGREGATE_TARGET, 7.1.12

QUERY_REWRITE_ENABLED, 10.2, 10.2.1

QUERY_REWRITE_INTEGRITY, 10.2.1

STAR_TRANSFORMATION_ENABLED, 4.5.1.1

In-Memory aggregation, 19.8.6

about, 2.7

In-Memory Column Store

about, 2.5

advantages, 2.5.1

aggregation, 19.8.6

configuring, 2.5

using, 2.5.2

VECTOR GROUP BY, 2.7.1

vector joins, 2.5.3

integrity constraints, 4.2

invalidating

materialized views, 6.4

itemsets

frequent, 18.7.4

---

J

JOB_QUEUE_PROCESSES initialization parameter, 7.1.10

join compatibility, 11.1.3.1

---

K

key lookups, 17.5.1

keys, 5.1.6

---

L

LAG/LEAD functions, 18.2.4

LAST_VALUE function, 18.2.5.1

level relationships, 3.2.6.1

purpose, 3.2.6.1.2

levels, 3.2.6, 3.2.6.1.1

levels in a dimension

skipping, 9.2

limiting rows returned, 18.8

linear regression functions, 18.3.5

LISTAGG function, 18.3.1

local indexes, 4.1.2, 4.1.2.1.3

logging

error, 17.4

logical design, 3.1

logs

materialized views, 5.4

lookup tables

See dimension tables, 5.1.6, 5.1.6

---

M

manual

refresh, 7.1.6

manual refresh

with DBMS_MVIEW package, 7.1.6

MATCH_RECOGNIZE clause, 20

materialized view logs, 5.4

materialized views

aggregates, 5.2.1

altering, 6.6

analytic processing, 6.2

build methods, 5.3.4

checking status, 7.1.14

containing only joins, 5.2.2

creating, 5.3

cubes, 6.2.1

data segment compression, 5.3.3

delta joins, 11.1.3.1.3

dropping, 5.5, 5.7

invalidating, 6.4

logs, 15.2.3.3

multiple, 11.2.8

naming, 5.3.2

nested, 5.2.3

partition change tracking (PCT), 6.1.1

partitioned tables, 7.3

partitioning, 6.1

prebuilt, 5.3

query rewrite

hints, 10.2, 11.7.10.1

matching join graphs, 5.3.6

parameters, 10.2.1

privileges, 10.2.4

refresh dependent, 7.1.9

refreshing, 5.3.7, 7.1

refreshing all, 7.1.8

registration, 5.5

restrictions, 5.3.6.1

rewrites

enabling, 10.2

schema design, 5.1.7

schema design guidelines, 5.1.7

security, 6.5, 6.5

set operators, 6.2.4

storage characteristics, 5.3.3

types of, 5.2

uses for, 5.1

with VPD, 6.5.1

measures, 5.1.6

MERGE PARTITION operation, 4.4.1

MERGE statement, 7.5.1

MODEL clause, 21.1

cell referencing, 21.2.4

data flow, 21.1.1

keywords, 21.2.3

parallel execution, 21.4.1

rules, 21.2.5

modeling

logical design, 2

physical design, 2

modifying

zone maps, 13.2.3

monitoring

refresh, 7.1.13

mortgage calculation, 21.5.6

MOVE PARTITION operation, 4.4.1

multiple hierarchies, 9.2.2

multiple materialized views, 11.2.8

MV_CAPABILITIES_TABLE table, 5.8.1.2

---

N

nested materialized views, 5.2.3

refreshing, 7.2.3

restrictions, 5.2.3.4

net present value

calculating, 21.5.3

NEVER clause, 5.3.7

nonvolatile data, 1.1

NOREWRITE hint, 10.2, 11.7.10.1

NTH_VALUE function, 18.2.5.2

NTILE function, 18.2.1.5

nulls

indexes and, 4.1.2.1.2

---

O

ON COMMIT clause, 5.3.7

ON DEMAND clause, 5.3.7

optimizations

query rewrite

enabling, 10.2

hints, 10.2, 11.7.10.1

matching join graphs, 5.3.6

query rewrites

privileges, 10.2.4

optimizer

with rewrite, 10.1

OPTIMIZER_MODE initialization parameter, 7.1.12, 10.2.1

ORDER BY clause, 5.3.8

outer joins

with query rewrite, 11.7.3

out-of-place refresh, 7.1.4

---

P

packages

DBMS_ADVISOR, 5.1.4

DBMS_DIMENSION, 9.3.2

DBMS_ERROR, 17.4.2.2

DBMS_ERRORLOG, 17.4.2.2, 17.5.3

DBMS_MVIEW, 5.8.1.1, 7.1

DBMS_STATS, 11.1.1

DBMS_SYNC_REFRESH, 8

parallel DML

bitmap indexes, 4.1.2

parallel execution, 4.3

parallel query

bitmap indexes, 4.1.2

PARALLEL_MAX_SERVERS initialization parameter, 7.1.12

parallelism, 4.3

partition change tracking (PCT), 6.1.1, 7.3.1, 11.2.7

refresh, 7.1.3

with Pmarkers, 11.2.7.4

partitioned outer join, 18.5

partitioned tables

materialized views, 7.3

partitioning, 15.2.3.2

materialized views, 6.1

prebuilt tables, 6.1.3

partitions

bitmap indexes, 4.1.2.1.3

pattern matching, 20

keywords, 20.2.2

patterns

in SQL, 20

PERCENT_RANK function, 18.2.1.4

PGA_AGGREGATE_TARGET initialization parameter, 7.1.12

physical design, 3.1

structures, 3.2.1

pivoting, 17.5.4, 17.5.4, 18.4

operations, 18.4

plans

star transformations, 4.5.2.2

Pmarkers

with PCT, 11.2.7.4

populating

In-Memory Column Store, In-Memory Column Store

populating, 2.5

prebuilt materialized views, 5.3

pruning

examples, 13.4.2.1, 13.4.2.2

using zone maps, 13.4

purging data, 7.5.3

---

Q

query delta joins, 11.1.3.1.2

query rewrite

advanced, 11.4

checks made by, 11.1.3

controlling, 10.2.2

correctness, 10.2.3

date folding, 11.3.5

enabling, 10.2, 10.2

hints, 10.2, 11.7.10.1

matching join graphs, 5.3.6

methods, 11.1

parameters, 10.2.1

privileges, 10.2.4

restrictions, 5.3.6.2

using equivalences, 11.4

using GROUP BY extensions, 11.3.8.1

using nested materialized views, 11.3.1

using PCT, 11.2.7

VPD, 6.5.1.1

when it occurs, 10.1.1

with bind variables, 11.3.11

with DBMS_MVIEW package, 11.6.2

with expression matching, 11.3.10

with inline views, 11.3.2

with partially stale materialized views, 11.3.10.1

with selfjoins, 11.3.4

with set operator materialized views, 11.3.7

with view constraints, 11.3.6

QUERY_REWRITE_ENABLED initialization parameter, 10.2, 10.2.1

QUERY_REWRITE_INTEGRITY initialization parameter, 10.2.1

---

R

range-partitioned table, 4.4.1

RANK function, 18.2.1.1

ranking functions, 18.2.1

RATIO_TO_REPORT function, 18.2.3.1

reference tables

See dimension tables, 5.1.6

refresh

monitoring, 7.1.13

options, 5.3.7

out-of-place, 7.1.4

partition change tracking (PCT), 7.1.3

scheduling, 7.1.15

synchronous, 8

with UNION ALL, 7.2.4

refreshing

materialized views, 7.1

nested materialized views, 7.2.3

partitioning, 7.4

zone maps, 13.3.3

REGR_AVGX function, 18.3.5.2

REGR_AVGY function, 18.3.5.2

REGR_COUNT function, 18.3.5.1

REGR_INTERCEPT function, 18.3.5.3

REGR_R2 function, 18.3.5.4

REGR_SLOPE function, 18.3.5.3

REGR_SXX function, 18.3.5.5

REGR_SXY function, 18.3.5.5

REGR_SYY function, 18.3.5.5

RELY constraints, 4.2.2.3

reporting functions, 18.2.3

restrictions

fast refresh, 5.3.7.1

nested materialized views, 5.2.3.4

query rewrite, 5.3.6.2

result set, 4.5.2

REWRITE hint, 10.2, 11.7.10.1, 11.7.10.1

REWRITE_OR_ERROR hint, 11.7.10.2

rewrites

hints, 11.7.10.1

parameters, 10.2.1

privileges, 10.2.4

query optimizations

hints, 10.2, 11.7.10.1

matching join graphs, 5.3.6

rolling up hierarchies, 9.1

ROLLUP, 19.2

concatenated, 19.10.1

partial, 19.2.3

when to use, 19.2.1

root level, 3.2.6.1.1

row_limiting_clause clause, 18.8

ROW_NUMBER function, 18.2.1.6

rules

in MODEL clause, 21.2.5

in SQL modeling, 21.2.5

order of evaluation, 21.2.6

---

S

schemas

3NF, 2.3

design guidelines for materialized views, 5.1.7

snowflake, 2.2.1

star, 2.2.1

set operators

materialized views, 6.2.4

simultaneous equations, 21.5.4

SKIP WHEN NULL clause, 9.2

skipping levels in a dimension, 9.2

snowflake schemas, 2.4.3

complex queries, 2.4.3

source systems, 15.1

sparse data

data densification, 18.5.2

SPLIT PARTITION operation, 4.4.1

SQL modeling, 21.1

cell referencing, 21.2.4

keywords, 21.2.3

order of evaluation, 21.2.6

performance, 21.4

rules, 21.2.5

rules and restrictions, 21.3.7

staging

areas, 1.4.2

databases, 5.1

files, 5.1

STALE_TOLERATED mode, 10.2.3

star queries

star transformation, 4.5.2

star schemas

dimensional model, 2.4.1

star transformations, 4.5.2

restrictions, 4.5.2.6

STAR_TRANSFORMATION_ENABLED initialization parameter, 4.5.1.1

statistics, 11.7.9

storage

optimizing, 4.4

summary management

components, 5.1.5

sync refresh, 8

synchronous refresh, 8

---

T

tables

attribute-clustered, 12.1

detail tables, 5.1.6

dimension tables (lookup tables), 5.1.6

external, 17.2.2

fact tables, 5.1.6

tablespaces

transportable, 15.2.2.2, 16.2.3, 16.2.3.2

text match, 11.2.1

with query rewrite, 11.7.4

Third Normal Form, 2.3

time series calculations, 18.6

timestamps, 15.2.3.1

Top-N queries, 18.8

transformations, 17.1

scenarios, 17.5

SQL and PL/SQL, 17.3.1

SQL*Loader, 17.2.1

transportable tablespaces, 15.2.2.2, 16.2.3, 16.2.3.2

transportation

definition, 16.1

distributed operations, 16.2.2

flat files, 16.2.1

triggers, 15.2.3.3

TRUSTED mode, 10.2.3

types

attribute clustering, 12.1.1

zone maps, 13.1.3

---

U

ultralarge files, 3.2.1.1

unique

constraints, 4.2.2.1

identifier, 2.2, 3.2

update frequencies, 5.1.9

UPDATE statement, 21.2.8

update windows, 5.1.9

UPSERT ALL statement, 21.2.8

UPSERT statement, 21.2.8

using

In-Memory Column Store, 2.5.2

---

V

validating dimensions, 9.5

VECOTR GROUP BY aggregation

scenarios, 2.7.2

VECTOR GROUP BY

aggregation, 19.8.6

VECTOR GROUP BY aggregation

IM column store, 2.7.1

optimizing star queries, 4.5.4

usage in queries, 2.7.3

vector joins

In-Memory Column Store, 2.5.3

view constraints, 4.2.2.7, 11.3.6

VPD

and materialized views, 6.5.1

restrictions with materialized views, 6.5.1.2

---

W

WIDTH_BUCKET function, 18.7.1, 18.7.1.1

window functions, 21.3.5

windowing functions, 18.2.2

---

Z

zoen maps

about staleness, 13.3.1

zone maps, 13.1

about, 13.1

about refreshing, 13.3.2

automatic refresh, 13.1.6.2

benefits, 13.1.4

compiling, 13.2.5

creating, 13.2.2.2

with attribute clustering, 13.2.2.1

data dictionary views, 13.5

dropping, 13.2.4

maintaining, 13.2.7

maintenance, 13.1.6.1

modifying, 13.2.3

privileges, 13.2.1

pruning, 13.4

refreshing, 13.3.3

types, 13.1.3

usage for SQL statements, 13.2.6.2

usage for SQL workloads, 13.2.6.1

usage scenarios, 13.1.5

with attribute clustering, 13.1.2