Oracle9i Data Warehousing Guide -- Index

access

controlling to change data, 15-3

adaptive multiuser

algorithm for, 21-47

definition, 21-47

ADD PARTITION clause, 5-32

affinity

parallel DML, 21-76

partitions, 21-75

aggregates, 8-13, 22-64

computability check, 22-42

ALL_SOURCE_TABLES view, 15-15

ALTER MATERIALIZED VIEW statement, 8-22

enabling query rewrite, 22-7

ALTER SESSION statement

ENABLE PARALLEL DML clause, 21-21

FORCE PARALLEL DDL clause, 21-42, 21-45

create or rebuild index, 21-43, 21-46

create table as select, 21-44, 21-45

move or split partition, 21-43, 21-46

FORCE PARALLEL DML clause

insert, 21-41, 21-42, 21-45

update and delete, 21-39, 21-40, 21-45

ALTER TABLE statement

NOLOGGING clause, 21-88

altering dimensions, 9-13

analytic functions

concepts, 19-3

analyzing data

for parallel processing, 21-68

APPEND hint, 21-88

applications

data warehouses

star queries, 17-4

decision support, 21-2

decision support systems (DSS), 6-3

parallel SQL, 21-14

direct-path INSERT, 21-21

parallel DML, 21-20

ARCH processes

multiple, 21-84

architecture

data warehouse, 1-5

MPP, 21-76

SMP, 21-76

asynchronous I/O, 21-64

attributes, 2-3, 9-6

backups

disk mirroring, 4-11

bandwidth, 5-2, 21-2

bitmap indexes, 6-2

nulls and, 6-5

on partitioned tables, 6-6

parallel query and DML, 6-3

bitmap join indexes, 6-6

block range granules, 5-3

B-tree indexes, 6-10

bitmap indexes versus, 6-3

build methods, 8-23

cardinality

degree of, 6-3

CASE expressions, 19-44

change data

controlling access to, 15-3

publishing, 15-3

Change Data Capture, 11-5

change sets

definition, 15-7

SYNC_SET, 15-7

change source

definition, 15-6

SYNC_SOURCE, 15-6

change tables

contain published data, 15-3

definition, 15-7

importing for Change Data Capture, 15-21

CLUSTER_DATABASE_INSTANCES initialization parameter

and parallel execution, 21-57

columns

cardinality, 6-3

common joins, 22-32

COMPATIBLE initialization parameter, 13-28, 22-8

COMPLETE clause, 8-26

complete refresh, 14-13

complex queries

snowflake schemas, 17-5

composite

columns, 18-21

partitioning, 5-9

partitioning methods, 5-9

performance considerations, 5-12, 5-15

compression

See data segment compression, 8-23

concatenated groupings, 18-24

concatenated ROLLUP, 8-43

concurrent users

increasing the number of, 21-50

CONSIDER FRESH clause, 14-30

constraints, 7-2, 9-11

foreign key, 7-5

parallel create table, 21-43

RELY, 7-6

states, 7-3

unique, 7-4

view, 7-7, 22-14

with partitioning, 7-7

with query rewrite, 22-63

cost-based optimization, 21-92

parallel execution, 21-92

cost-based rewrite, 22-3

CPU

utilization, 5-2, 21-2

CREATE DIMENSION statement, 9-4

CREATE INDEX statement, 21-86

rules of parallelism, 21-43

CREATE MATERIALIZED VIEW statement, 8-22

enabling query rewrite, 22-7

CREATE SNAPSHOT statement, 8-3

CREATE TABLE AS SELECT statement, 21-67, 21-78

rules of parallelism

index-organized tables, 21-14

CREATE TABLE statement

AS SELECT

decision support systems, 21-14

rules of parallelism, 21-43

space fragmentation, 21-16

temporary storage space, 21-16

parallelism, 21-14

index-organized tables, 21-14

CUBE clause, 18-10

partial, 18-12

when to use, 18-10

cubes

hierarchical, 8-42

CUME_DIST function, 19-13

data

integrity of

parallel DML restrictions, 21-26

partitioning, 5-4

purging, 14-11

sufficiency check, 22-37

transformation, 13-9

transportation, 12-2

data compression

See data segment compression, 8-23

data cubes

hierarchical, 18-26

data manipulation language

parallel DML, 21-18

transaction model for parallel DML, 21-22

data marts, 1-7

data mining, 20-4

data segment compression, 3-5

bitmap indexes, 5-18

materialized views, 8-23

partitioning, 3-5, 5-17

data transformation

multistage, 13-2

pipelined, 13-4

data warehouse, 8-2

architectures, 1-5

dimension tables, 8-7

dimensions, 17-4

fact tables, 8-7

logical design, 2-2

partitioned tables, 5-10

physical design, 3-2

refresh tips, 14-18

refreshing table data, 21-20

star queries, 17-4

database

extraction

with and without Change Data Capture, 15-2

scalability, 21-20

staging, 8-2

database writer process (DBWn)

tuning, 21-84

date folding

with query rewrite, 22-18

DB_BLOCK_SIZE initialization parameter, 21-63

and parallel query, 21-63

DB_FILE_MULTIBLOCK_READ_COUNT initialization parameter, 21-63

DBA_DATA_FILES view, 21-70

DBA_EXTENTS view, 21-70

DBMS_LOGMNR_CDC_PUBLISH package, 15-3

DBMS_LOGMNR_CDC_SUBSCRIBE package, 15-3

DBMS_MVIEW package, 14-14

EXPLAIN_MVIEW procedure, 8-53

EXPLAIN_REWRITE procedure, 22-57

REFRESH procedure, 14-12, 14-15

REFRESH_ALL_MVIEWS procedure, 14-12

REFRESH_DEPENDENT procedure, 14-12

DBMS_OLAP package, 16-3, 16-4, 16-5

ADD_FILTER_ITEM procedure, 16-18

LOAD_WORKLOAD_TRACE procedure, 16-12

PURGE_FILTER procedure, 16-23

PURGE_RESULTS procedure, 16-32

PURGE_WORKLOAD procedure, 16-18

SET_CANCELLED procedure, 16-32

DBMS_STATS package, 16-6, 22-3

decision support systems (DSS)

bitmap indexes, 6-3

disk striping, 21-75

parallel DML, 21-20

parallel SQL, 21-14, 21-20

performance, 21-20

scoring tables, 21-21

default partition, 5-8

degree of cardinality, 6-3

degree of parallelism, 21-32, 21-38, 21-40

and adaptive multiuser, 21-47

between query operations, 21-9

parallel SQL, 21-34

DELETE statement

parallel DELETE statement, 21-39

DEMO_DIM package, 9-10

DENSE_RANK function, 19-5

design

logical, 3-2

physical, 3-2

detail tables, 8-7

dimension tables, 2-5, 8-7, 17-4

normalized, 9-9

Dimension Wizard, 9-14

dimensional modeling, 2-3

dimensions, 2-6, 9-2, 9-11

altering, 9-13

analyzing, 18-3

creating, 9-4

definition, 9-2

dimension tables, 8-7

dropping, 9-14

hierarchies, 2-6

hierarchies overview, 2-6

multiple, 18-3

star joins, 17-4

star queries, 17-4

validating, 9-12

with query rewrite, 22-63

direct-path INSERT

restrictions, 21-24

disk affinity

disabling with MPP, 4-6

parallel DML, 21-76

partitions, 21-75

disk striping

affinity, 21-75

DISK_ASYNCH_IO initialization parameter, 21-64

distributed transactions

parallel DDL restrictions, 21-11

parallel DML restrictions, 21-11, 21-27

DML statements

captured by Change Data Capture, 15-4

DML_LOCKS initialization parameter, 21-61

drilling down, 9-2

hierarchies, 9-2

DROP MATERIALIZED VIEW statement, 8-22

prebuilt tables, 8-33

DROP PARTITION clause, 5-33

dropping

dimensions, 9-14

materialized views, 8-52

ENFORCED mode, 22-10

ENQUEUE_RESOURCES initialization parameter, 21-61

entity, 2-2

estimating materialized view size, 16-38

ETL. See extraction, transformation, and loading (ETL), 10-2

EVALUATE_MVIEW_STRATEGY package, 16-39

EXCHANGE PARTITION statement, 7-7

execution plans

parallel operations, 21-66

star transformations, 17-9

EXPLAIN PLAN statement, 21-66, 22-56

query parallelization, 21-81

star transformations, 17-9

exporting

a source table

change data capture, 15-20

EXP utility, 11-10

expression matching

with query rewrite, 22-17

extend window

to create a new view, 15-3

extents

parallel DDL, 21-16

size, 13-28

external tables, 13-6

extraction, transformation, and loading (ETL), 10-2

overview, 10-2

process, 7-2

extractions

data files, 11-8

distributed operations, 11-11

full, 11-3

incremental, 11-3

OCI, 11-10

online, 11-4

overview, 11-2

physical, 11-4

Pro*C, 11-10

SQL*Plus, 11-8

fact tables, 2-5

star joins, 17-4

star queries, 17-4

facts, 9-2

FAST clause, 8-26

fast refresh, 14-14

restrictions, 8-27

FAST_START_PARALLEL_ROLLBACK initialization parameter, 21-60

features, new, xxxiii

FIRST_ROWS(n) hint, 21-93

FIRST_VALUE function, 19-24

FIRST/LAST functions, 19-28

FORCE clause, 8-26

foreign key

constraints, 7-5

joins

snowflake schemas, 17-5

fragmentation

parallel DDL, 21-16

FREELISTS parameter, 21-84

full partition-wise joins, 5-21

functions

COUNT, 6-5

CUME_DIST, 19-13

DENSE_RANK, 19-5

FIRST_VALUE, 19-24

FIRST/LAST, 19-28

GROUP_ID, 18-17

GROUPING, 18-13

GROUPING_ID, 18-17

LAG/LEAD, 19-27

LAST_VALUE, 19-24

linear regression, 19-31

NTILE, 19-14

parallel execution, 21-28

PERCENT_RANK, 19-14

RANK, 19-5

ranking, 19-5

RATIO_TO_REPORT, 19-27

REGR_AVGX, 19-32

REGR_AVGY, 19-32

REGR_COUNT, 19-32

REGR_INTERCEPT, 19-32

REGR_SLOPE, 19-32

REGR_SXX, 19-33

REGR_SXY, 19-33

REGR_SYY, 19-33

reporting, 19-24

ROW_NUMBER, 19-16

WIDTH_BUCKET, 19-42

windowing, 19-17

global

indexes, 21-83

striping, 4-6

granting access to change data, 15-3

granules, 5-3

block range, 5-3

partition, 5-4

GROUP_ID function, 18-17

grouping

compatibility check, 22-40

conditions, 22-64

GROUPING function, 18-13

when to use, 18-16

GROUPING_ID function, 18-17

GROUPING_SETS expression, 18-19

groups, instance, 21-37

GV$FILESTAT view, 21-68

hash partitioning, 5-7

HASH_AREA_SIZE initialization parameter

and parallel execution, 21-59

hierarchical cubes, 8-42

hierarchies, 9-2

how used, 2-6

multiple, 9-7

overview, 2-6

rolling up and drilling down, 9-2

hints

FIRST_ROWS(n), 21-93

PARALLEL, 21-34

PARALLEL_INDEX, 21-35

query rewrite, 22-8, 22-9

histograms

creating with user-defined buckets, 19-45

hypothetical rank, 19-38

importing

a change table

Change Data Capture, 15-21

a source table

Change Data Capture, 15-20

indexes

bitmap indexes, 6-6

bitmap join, 6-6

B-tree, 6-10

cardinality, 6-3

creating in parallel, 21-85

global, 21-83

local, 21-83

nulls and, 6-5

parallel creation, 21-85, 21-86

parallel DDL storage, 21-16

parallel local, 21-86

partitioned tables, 6-6

partitioning, 5-9

STORAGE clause, 21-86

index-organized tables

parallel CREATE, 21-14

parallel queries, 21-11

INITIAL extent size, 13-28

initialization parameters

CLUSTER_DATABASE_INSTANCES, 21-57

COMPATIBLE, 13-28, 22-8

DB_BLOCK_SIZE, 21-63

DB_FILE_MULTIBLOCK_READ_COUNT, 21-63

DISK_ASYNCH_IO, 21-64

DML_LOCKS, 21-61

ENQUEUE_RESOURCES, 21-61

FAST_START_PARALLEL_ROLLBACK, 21-60

HASH_AREA_SIZE, 21-59

JOB_QUEUE_PROCESSES, 14-18

LARGE_POOL_SIZE, 21-52

LOG_BUFFER, 21-61

MULTIBLOCK_READ_COUNT, 13-28

OPTIMIZER_MODE, 14-18, 21-93, 22-8

PARALLEL_ADAPTIVE_MULTI_USER, 21-47

PARALLEL_AUTOMATIC_TUNING, 21-30

PARALLEL_EXECUTION_MESSAGE_SIZE, 21-58, 21-59

PARALLEL_MAX_SERVERS, 14-18, 21-4, 21-50

PARALLEL_MIN_PERCENT, 21-36, 21-50, 21-57

PARALLEL_MIN_SERVERS, 21-3, 21-4, 21-51

PARALLEL_THREADS_PER_CPU, 21-30

PGA_AGGREGATE_TARGET, 14-18

QUERY_REWRITE_ENABLED, 22-7, 22-8

ROLLBACK_SEGMENTS, 21-60

SHARED_POOL_SIZE, 21-52, 21-56

STAR_TRANSFORMATION_ENABLED, 17-6

TAPE_ASYNCH_IO, 21-64

TIMED_STATISTICS, 21-69

TRANSACTIONS, 21-60

INSERT statement

functionality, 21-87

parallelizing INSERT ... SELECT, 21-41

instance groups for parallel operations, 21-37

instance recovery

SMON process, 21-24

instances

instance groups, 21-37

integrity constraints, 7-2

integrity rules

parallel DML restrictions, 21-26

invalidating

materialized views, 8-50

I/O

asynchronous, 21-64

parallel execution, 5-2, 21-2

striping to avoid bottleneck, 4-2

Java

used by Change Data Capture, 15-8

JOB_QUEUE_PROCESSES initialization parameter, 14-18

join compatibility, 22-31

joins

full partition-wise, 5-21

partial partition-wise, 5-27

partition-wise, 5-21

star joins, 17-4

star queries, 17-4

key lookups, 13-33

keys, 8-7, 17-4

LAG/LEAD functions, 19-27

LARGE_POOL_SIZE initialization parameter, 21-52

LAST_VALUE function, 19-24

level relationships, 2-6

purpose, 2-7

levels, 2-6, 2-7

linear regression functions, 19-31

list partitioning, 5-7

load

parallel, 13-31

LOB datatypes

restrictions

parallel DDL, 21-14

parallel DML, 21-25

local indexes, 6-3, 6-6, 21-83

local striping, 4-5

locks

parallel DML, 21-24

LOG_BUFFER initialization parameter

and parallel execution, 21-61

LOGGING clause, 21-84

logging mode

parallel DDL, 21-14, 21-15

logical design, 3-2

lookup tables, 17-4

See dimension tables, 8-7

star queries, 17-4

manual

refresh, 14-14

striping, 4-4

massively parallel processing (MPP)

affinity, 21-75, 21-76

disk affinity, 4-6

massively parallel systems, 5-2, 21-2

materialized views

aggregates, 8-13

altering, 8-51

build methods, 8-23

containing only joins, 8-16

creating, 8-21

data segment compression, 8-23

delta joins, 22-35

dropping, 8-33, 8-52

estimating size, 16-38

invalidating, 8-50

logs, 11-7

naming, 8-22

nested, 8-18

OLAP, 8-41

OLAP cubes, 8-41

partitioned tables, 14-26

partitioning, 8-35

prebuilt, 8-22

query rewrite

hints, 22-8, 22-9

matching join graphs, 8-24

parameters, 22-8

privileges, 22-10

refresh dependent, 14-16

refreshing, 8-26, 14-12

refreshing all, 14-16

registration, 8-33

restrictions, 8-24

rewrites

enabling, 22-7

schema design, 8-8

schema design guidelines, 8-8

security, 8-50

set operators, 8-47

storage characteristics, 8-23

types of, 8-12

uses for, 8-2

MAXEXTENTS keyword, 13-28

MAXEXTENTS UNLIMITED storage parameter, 21-23

measures, 8-7, 17-4

memory

configure at 2 levels, 21-58

MERGE operation, 13-10

MERGE PARTITIONS clause, 5-35

MERGE statement, 14-9

MINIMUM EXTENT parameter, 21-17

mirroring

disks, 4-10

monitoring

parallel processing, 21-68

refresh, 14-19

MOVE PARTITION statement

rules of parallelism, 21-43

MULTIBLOCK_READ_COUNT initialization parameter, 13-28

multiple archiver processes, 21-84

multiple hierarchies, 9-7

MV_CAPABILITIES_TABLE table, 8-54

MVIEW_WORKLOAD view, 16-2

nested materialized views, 8-18

refreshing, 14-23

restrictions, 8-21

nested tables

restrictions, 21-13

NEVER clause, 8-27

new features, xxxiii

NOAPPEND hint, 21-88

NOARCHIVELOG mode, 21-85

nodes

disk affinity in Real Application Clusters, 21-75

NOLOGGING clause, 21-79, 21-84, 21-86

with APPEND hint, 21-88

NOLOGGING mode

parallel DDL, 21-14, 21-15

nonvolatile data, 1-3

NOPARALLEL attribute, 21-77

NOREWRITE hint, 22-8, 22-9

NTILE function, 19-14

nulls

indexes and, 6-5

object types

parallel query, 21-12

restrictions, 21-13

restrictions

parallel DDL, 21-14

parallel DML, 21-25

OLAP, 20-2

materialized views, 8-41

OLAP cubes

materialized views, 8-41

OLTP database

batch jobs, 21-21

parallel DML, 21-20

ON COMMIT clause, 8-26

ON DEMAND clause, 8-26

OPTIMAL storage parameter, 21-23

optimizations

parallel SQL, 21-6

query rewrite

enabling, 22-7

hints, 22-8, 22-9

matching join graphs, 8-24

query rewrites

privileges, 22-10

optimizer

with rewrite, 22-2

OPTIMIZER_MODE initialization parameter, 14-18, 21-93, 22-8

Oracle Real Application Clusters

disk affinity, 21-75

instance groups, 21-37

parallel load, 13-31

system monitor process and, 21-24

ORDER BY clause, 8-31

outer joins

with query rewrite, 22-63

PARALLEL clause, 21-87, 21-88

parallelization rules, 21-38

PARALLEL CREATE INDEX statement, 21-60

PARALLEL CREATE TABLE AS SELECT statement

resources required, 21-60

parallel DDL, 21-13

extent allocation, 21-16

parallelization rules, 21-38

partitioned tables and indexes, 21-13

restrictions

LOBs, 21-14

object types, 21-13, 21-14

parallel delete, 21-39

parallel DELETE statement, 21-39

parallel DML, 21-18

applications, 21-20

bitmap indexes, 6-3

degree of parallelism, 21-38, 21-40

enabling PARALLEL DML, 21-21

lock and enqueue resources, 21-24

parallelization rules, 21-38

recovery, 21-23

restrictions, 21-24

object types, 21-13, 21-25

remote transactions, 21-27

rollback segments, 21-23

transaction model, 21-22

parallel execution

cost-based optimization, 21-92

index creation, 21-85

interoperator parallelism, 21-9

intraoperator parallelism, 21-9

introduction, 5-2

I/O parameters, 21-63

method of, 21-31

plans, 21-66

process classification, 4-2, 4-6, 4-9, 4-12

resource parameters, 21-58

rewriting SQL, 21-78

solving problems, 21-77

tuning, 5-2, 21-2

PARALLEL hint, 21-34, 21-77, 21-87

parallelization rules, 21-38

UPDATE and DELETE, 21-39

parallel load

example, 13-31

Oracle Real Application Clusters, 13-31

using, 13-25

parallel partition-wise joins

performance considerations, 5-30

parallel query, 21-11

bitmap indexes, 6-3

index-organized tables, 21-11

object types, 21-12

restrictions, 21-13

parallelization rules, 21-38

parallel scan operations, 4-3

parallel SQL

allocating rows to parallel execution servers, 21-7

degree of parallelism, 21-34

instance groups, 21-37

number of parallel execution servers, 21-3

optimizer, 21-6

parallelization rules, 21-38

shared server, 21-4

summary or rollup tables, 21-14

parallel update, 21-39

parallel UPDATE statement, 21-39

PARALLEL_ADAPTIVE_MULTI_USER initialization parameter, 21-47

PARALLEL_AUTOMATIC_TUNING initialization parameter, 21-30

PARALLEL_EXECUTION_MESSAGE_SIZE initialization parameter, 21-58, 21-59

PARALLEL_INDEX hint, 21-35

PARALLEL_MAX_SERVERS initialization parameter, 14-18, 21-4, 21-50

and parallel execution, 21-49

PARALLEL_MIN_PERCENT initialization parameter, 21-36, 21-50, 21-57

PARALLEL_MIN_SERVERS initialization parameter, 21-3, 21-4, 21-51

PARALLEL_THREADS_PER_CPU initialization parameter, 21-30, 21-48

parallelism, 5-2

degree, 21-32

degree, overriding, 21-77

enabling for tables and queries, 21-46

interoperator, 21-9

intraoperator, 21-9

parameters

FREELISTS, 21-84

partition

default, 5-8

granules, 5-4

Partition Change Tracking (PCT), 8-35, 14-26

partitioned tables

data warehouses, 5-10

example, 13-29

partitioning, 11-7

composite, 5-9

data, 5-4

data segment compression, 5-17

bitmap indexes, 5-18

hash, 5-7

indexes, 5-9

list, 5-7

materialized views, 8-35

prebuilt tables, 8-40

range, 5-6

range-list, 5-15

partitions

adding, 5-32

affinity, 21-75

bitmap indexes, 6-6

coalescing, 5-36

dropping, 5-33

exchanging, 5-34

merging, 5-35

moving, 5-34

parallel DDL, 21-13

partition pruning

disk striping and, 21-75

pruning, 5-19

range partitioning

disk striping and, 21-75

rules of parallelism, 21-43, 21-45

splitting, 5-35

truncating, 5-35

partition-wise joins, 5-21

benefits of, 5-29

full, 5-21

partial, 5-27

PERCENT_RANK function, 19-14

performance

DSS database, 21-20

PGA_AGGREGATE_TARGET initialization parameter, 14-18

physical design, 3-2

structures, 3-4

pivoting, 13-35

plans

star transformations, 17-9

PL/SQL packages

for publish and subscribe tasks, 15-3

prebuilt materialized views, 8-22

PRIMARY KEY constraints, 21-86

process monitor process (PMON)

parallel DML process recovery, 21-23

processes

and memory contention in parallel processing, 21-50

classes of parallel execution, 4-2, 4-6, 4-9, 4-12

pruning

partitions, 5-19, 21-75

using DATE columns, 5-20

publication

definition, 15-7

publisher tasks, 15-3

publishers

capture data, 15-3

determines the source tables, 15-3

publish change data, 15-3

purpose, 15-3

purging data, 14-11

queries

ad hoc, 21-14

enabling parallelism for, 21-46

star queries, 17-4

query delta joins, 22-35

query rewrite

controlling, 22-8

correctness, 22-10

enabling, 22-7

hints, 22-8, 22-9

matching join graphs, 8-24

methods, 22-11

parameters, 22-8

privileges, 22-10

restrictions, 8-25

when it occurs, 22-4

QUERY_REWRITE_ENABLED initialization parameter, 22-7, 22-8

RAID

configurations, 4-9

range partitioning, 5-6

performance considerations, 5-9

range-list partitioning, 5-15

RANK function, 19-5

ranking functions, 19-5

RATIO_TO_REPORT function, 19-27

REBUILD INDEX PARTITION statement

rules of parallelism, 21-43

REBUILD INDEX statement

rules of parallelism, 21-43

recovery

instance recovery

parallel DML, 21-24

SMON process, 21-24

media, with striping, 4-10

parallel DML, 21-23

redo buffer allocation retries, 21-61

reference tables

See dimension tables, 8-7

refresh

monitoring, 14-19

options, 8-25

refreshing

materialized views, 14-12

nested materialized views, 14-23

partitioning, 14-2

REGR_AVGX function, 19-32

REGR_AVGY function, 19-32

REGR_COUNT function, 19-32

REGR_INTERCEPT function, 19-32

REGR_R2 function, 19-32

REGR_SLOPE function, 19-32

REGR_SXX function, 19-33

REGR_SXY function, 19-33

REGR_SYY function, 19-33

regression

detecting, 21-66

RELY constraints, 7-6

remote transactions

parallel DML and DDL restrictions, 21-11

replication

restrictions

parallel DML, 21-25

reporting functions, 19-24

resources

consumption, parameters affecting, 21-58, 21-60

limiting for users, 21-51

limits, 21-49

parallel query usage, 21-58

restrictions

direct-path INSERT, 21-24

fast refresh, 8-27

nested materialized views, 8-21

nested tables, 21-13

parallel DDL, 21-14

remote transactions, 21-11

parallel DML, 21-24

remote transactions, 21-11, 21-27

query rewrite, 8-25

result set, 17-7

revoking access to change data, 15-3

REWRITE hint, 22-8, 22-9

rewrites

hints, 22-9

parameters, 22-8

privileges, 22-10

query optimizations

hints, 22-8, 22-9

matching join graphs, 8-24

rollback segments, 21-60

MAXEXTENTS UNLIMITED, 21-23

OPTIMAL, 21-23

parallel DML, 21-23

ROLLBACK_SEGMENTS initialization parameter, 21-60

rolling up hierarchies, 9-2

ROLLUP, 18-6

concatenated, 8-43

partial, 18-8

when to use, 18-7

root level, 2-7

ROW_NUMBER function, 19-16

RULE hint, 21-93

sar UNIX command, 21-74

scalability

batch jobs, 21-21

parallel DML, 21-20

scalable operations, 21-81

schemas, 17-2

design guidelines for materialized views, 8-8

snowflake, 2-3

star, 2-3, 17-4

third normal form, 17-2

SELECT privilege

granting and revoking for access to change data, 15-3

sessions

enabling parallel DML, 21-21

set operators

materialized views, 8-47

shared server

parallel SQL execution, 21-4

SHARED_POOL_SIZE initialization parameter, 21-56

SHARED_POOL_SIZE parameter, 21-52

single table aggregate requirements, 8-15

skewing parallel DML workload, 21-37

SMP architecture

disk affinity, 21-76

snowflake schemas, 17-5

complex queries, 17-5

SORT_AREA_SIZE initialization parameter

and parallel execution, 21-59

source systems, 11-2

definition, 15-6

source tables

definition, 15-6

exporting for Change Data Capture, 15-20

importing for Change Data Capture, 15-20

space management

MINIMUM EXTENT parameter, 21-17

parallel DDL, 21-16

SPLIT PARTITION clause, 5-32, 5-35

rules of parallelism, 21-43

SQL statements

parallelizing, 21-3, 21-6

SQL*Loader, 13-25

staging

areas, 1-6

databases, 8-2

files, 8-2

STALE_TOLERATED mode, 22-10

star joins, 17-4

star queries, 17-4

star transformation, 17-7

star schemas

advantages, 2-4

defining fact tables, 2-6

dimensional model, 2-4, 17-4

star transformations, 17-7

restrictions, 17-12

STAR_TRANSFORMATION_ENABLED initialization parameter, 17-6

statistics, 22-65

estimating, 21-67

operating system, 21-74

storage

fragmentation in parallel DDL, 21-16

STORAGE clause

parallel execution, 21-16

parallel query, 21-86

storage parameters

MAXEXTENTS UNLIMITED, 21-23

OPTIMAL (in rollback segments), 21-23

striping, 4-2

analyzing, 4-6

automatic, 4-3

example, 13-25

global, 4-5

local, 4-5

manual, 4-4

media recovery, 4-10

subpartition

mapping, 5-14

template, 5-14

subqueries

in DDL statements, 21-14

subscriber views

definition, 15-7

dropping, 15-3

removing, 15-3

subscribers

definition, 15-5

drop the subscriber view, 15-3

drop the subscription, 15-3

extend the window to create a new view, 15-3

purge the subscription window, 15-3

purpose, 15-3

removing subscriber views, 15-3

retrieve change data from the subscriber views, 15-3

subscribe to source tables, 15-3

tasks, 15-3

subscription window

purging, 15-3

Summary Advisor, 16-2

Wizard, 16-40

summary management

components, 8-5

summary tables, 2-5

symmetric multiprocessors, 5-2, 21-2

SYNC_SET change set

system-generated change set, 15-7

SYNC_SOURCE change source

system-generated change source, 15-6

system monitor process (SMON)

Oracle Real Application Clusters and, 21-24

parallel DML instance recovery, 21-24

parallel DML system recovery, 21-24

table queues, 21-71

tables

detail tables, 8-7

dimension tables (lookup tables), 8-7

dimensions

star queries, 17-4

enabling parallelism for, 21-46

external, 13-6

fact tables, 8-7

star queries, 17-4

historical, 21-21

lookup tables (dimension tables), 17-4

parallel creation, 21-14

parallel DDL storage, 21-16

refreshing in data warehouse, 21-20

STORAGE clause with parallel execution, 21-16

summary or rollup, 21-14

tablespaces

creating, example, 13-27

transportable, 11-5, 12-3, 12-6

TAPE_ASYNCH_IO initialization parameter, 21-64

temporary segments

parallel DDL, 21-16

text match, 22-12

with query rewrite, 22-63

third normal form

queries, 17-3

schemas, 17-2

TIMED_STATISTICS initialization parameter, 21-69

timestamps, 11-6

transactions

distributed

parallel DDL restrictions, 21-11

parallel DML restrictions, 21-11, 21-27

TRANSACTIONS initialization parameter, 21-60

transformations, 13-2

scenarios, 13-25

SQL and PL/SQL, 13-9

SQL*Loader, 13-5

transportable tablespaces, 11-5, 12-3, 12-6

transportation

definition, 12-2

distributed operations, 12-2

flat files, 12-2

triggers, 11-7

restrictions, 21-27

parallel DML, 21-25

TRUNCATE PARTITION clause, 5-35

TRUSTED mode, 22-10

two-phase commit, 21-60

unique

constraints, 7-4, 21-86

identifier, 2-3, 3-2

UNLIMITED extents, 21-23

update frequencies, 8-12

UPDATE statement

parallel UPDATE statement, 21-39

update windows, 8-12

user resources

limiting, 21-51

V$FILESTAT view

and parallel query, 21-69

V$PARAMETER view, 21-70

V$PQ_SESSTAT view, 21-67, 21-69

V$PQ_SYSSTAT view, 21-67

V$PQ_TQSTAT view, 21-68, 21-70

V$PX_PROCESS view, 21-69

V$PX_SESSION view, 21-68

V$PX_SESSTAT view, 21-69

V$SESSTAT view, 21-71, 21-74

V$SYSSTAT view, 21-61, 21-71, 21-84

validating dimensions, 9-12

view constraints, 7-7, 22-14

views

ALL_SOURCE_TABLES, 15-15

DBA_DATA_FILES, 21-70

DBA_EXTENTS, 21-70

V$FILESTAT, 21-69

V$PARAMETER, 21-70

V$PQ_SESSTAT, 21-69

V$PQ_TQSTAT, 21-70

V$PX_PROCESS, 21-69

V$SESSTAT, 21-71, 21-74

V$SYSSTAT, 21-71

vmstat UNIX command, 21-74

WIDTH_BUCKET function, 19-42

windowing functions, 19-17

workloads

distribution, 21-67

skewing, 21-37