Oracle9i Data Warehousing Guide -- Index

access

controlling to change data, 15-3

adaptive multiuser

algorithm for, 21-47

definition, 21-47

affinity

parallel DML, 21-78

partitions, 21-77

aggregates, 8-7, 8-10, 22-62

computability check, 22-41

ALL_PUBLISHED_COLUMNS view, 15-10

ALL_SOURCE_TABLES view, 15-10, 15-13

ALTER MATERIALIZED VIEW statement, 8-23

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-95

altering dimensions, 9-13

analyzing data

for parallel processing, 21-71

APPEND hint, 21-95

applications

data warehouses

star queries, 17-2

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-91

architecture

MPP, 21-78

SMP, 21-78

asynchronous I/O, 21-66

attributes, 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

buffer pools

setting for parallel operations, 21-81

build methods, 8-24

cardinality, 6-3

CASE expressions, 19-44

change

capture, 11-5

data capture, 11-5

change data

controlling access to, 15-3

publishing, 15-3

Change Data Capture, 15-1

database extraction advantages, 15-2

DBMS_LOGMNR_CDC_PUBLISH package, 15-11

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

columns in, 15-8

contain published data, 15-3

definition, 15-7

importing for Change Data Capture, 15-18

CHANGE_SETS view, 15-10

CHANGE_SOURCES view, 15-10

CHANGE_TABLES view, 15-10

CLUSTER_DATABASE_INSTANCES parameter

and parallel execution, 21-57

columns

cardinality, 6-3

in a change table, 15-8

common joins, 22-32

COMPATIBLE parameter, 13-29, 22-8

COMPLETE clause, 8-27

complete refresh, 14-10

complex queries

snowflake schemas, 17-3

composite columns, 18-21

composite partitioning methods, 5-8

performance considerations, 5-12

concatenated groupings, 18-24

concurrent users

increasing the number of, 21-50

CONSIDER FRESH clause, 14-26

constraints, 7-2, 9-11

foreign key, 7-5

parallel create table, 21-43

RELY, 7-6

unique, 7-4

view, 7-7, 22-14

with partitioning, 7-7

with query rewrite, 22-61

cost

data warehousing with and without Change Data Capture, 15-2

cost-based optimizations, 21-100

parallel execution, 21-100

cost-based rewrite, 22-3

CPU

utilization, 5-2, 21-2

CREATE DIMENSION statement, 9-4

CREATE INDEX statement, 21-93

rules of parallelism, 21-43

CREATE MATERIALIZED VIEW statement, 8-23

enabling query rewrite, 22-7

CREATE SNAPSHOT statement, 8-3

CREATE TABLE AS SELECT statement, 21-70, 21-86

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

with query rewrite (and also) ROLLUP

clause with query rewrite, 22-50

CUME_DIST function, 19-13

data

integrity of

parallel DML restrictions, 21-26

partitioning, 5-4

purging, 14-8

sufficiency check, 22-37

transformation, 13-8

transportation, 12-2

data cubes

hierarchical, 18-26

data manipulation language

parallel DML, 21-18

transaction model for parallel DML, 21-22

data marts, 1-7

data warehouses, 8-2

architectures, 1-5

dimension tables

(lookup tables), 8-7

dimensions, 17-2

fact tables (detail tables), 8-7

partitioned tables, 5-9

refresh tips, 14-15

refreshing table data, 21-20

star queries, 17-2

data warehousing

refreshing table data, 21-20

database

extraction with and without Change Data Capture, 15-2

database extraction

with and without Change Data Capture, 15-2

database writer process (DBWn)

tuning, 21-92

databases

scalability, 21-20

staging, 8-2

date folding

with query rewrite, 22-18

DB_BLOCK_SIZE parameter, 21-66

and parallel query, 21-66

DB_FILE_MULTIBLOCK_READ_COUNT parameter, 21-66

DBA_DATA_FILES view, 21-72

DBA_EXTENTS view, 21-72

DBA_PUBLISHED_COLUMNS view, 15-10

DBA_SOURCE_TABLES view, 15-10

DBA_SUBSCRIBED_COLUMNS view, 15-10

DBA_SUBSCRIBED_TABLES view, 15-10

DBA_SUBSCRIPTIONS view, 15-10

DBMS_LOGMNR_CDC_PUBLISH package, 15-3

DBMS_LOGMNR_CDC_SUBSCRIBE package, 15-3

DBMS_MVIEW package, 14-11

EXPLAIN_MVIEW procedure, 8-43

EXPLAIN_REWRIITE procedure, 22-56

REFRESH procedure, 14-9, 14-12

REFRESH_ALL_MVIEWS procedure, 14-9

REFRESH_DEPENDENT procedure, 14-9

DBMS_OLAP package, 16-3, 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-5, 22-3

decision support systems (DSS)

bitmap indexes, 6-3

disk striping, 21-78

parallel DML, 21-20

parallel SQL, 21-14, 21-20

performance, 21-20

processes, 21-81

scoring tables, 21-21

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

and adaptive multiuser, 21-46

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-2

normalized, 9-9

Dimension Wizard, 9-10

dimensional modeling, 2-3

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

altering, 9-13

creating, 9-4

definition, 9-2

dimension tables (lookup tables), 8-7

dropping, 9-14

hierarchies, 2-6

hierarchies overview, 2-6

multiple, 18-3

star joins, 17-3

star queries, 17-2

validating, 9-12

with query rewrite, 22-61

direct-path INSERT

external fragmentation, 21-84

restrictions, 21-24

disk affinity

disabling with MPP, 4-6

parallel DML, 21-78

partitions, 21-77

with MPP, 21-88

disk striping

affinity, 21-77

DISK_ASYNCH_IO parameter, 21-66

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 parameter, 21-63

drilling down, 9-2

hierarchies, 9-2

DROP MATERIALIZED VIEW statement, 8-23

prebuilt tables, 8-32

dropping

dimensions, 9-14

materialized views, 8-42

ENFORCED mode, 22-10

ENQUEUE_RESOURCES parameter, 21-63

estimating materialized view size, 16-37

EVALUATE_MVIEW_STRATEGY package, 16-38

EXCHANGE PARTITION statement, 7-7

execution plans

parallel operations, 21-69

star transformations, 17-7

EXPLAIN PLAN statement, 21-69, 22-55

query parallelization, 21-89

star transformations, 17-7

exporting

EXP utility, 11-10

exporting a source table

change data capture, 15-18

expression matching

with query rewrite, 22-17

extend window

to create a new view, 15-3

extents

parallel DDL, 21-16

size, 13-29

temporary, 21-87

external tables, 13-6

extraction, transformation, loading (ETL)

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

with and without Change Data Capture, 15-2

fact tables, 2-5

star joins, 17-3

star queries, 17-2

facts, 9-2

FAST clause, 8-27

fast refresh, 14-11

fast refresh restrictions, 8-27

FAST_START_PARALLEL_ROLLBACK parameter, 21-63

FIRST/LAST functions, 19-29

FIRST_VALUE function, 19-24

FORCE clause, 8-27

foreign key constraints, 7-5

foreign key joins

snowflake schemas, 17-3

fragmentation

external, 21-84

parallel DDL, 21-16

FREELISTS parameter, 21-91

full partition-wise joins, 5-15

functions

COUNT, 6-5

CUME_DIST, 19-13

DENSE_RANK, 19-5

FIRST/LAST, 19-29

FIRST_VALUE, 19-24

GROUP_ID, 18-18

GROUPING, 18-13

GROUPING_ID, 18-17

LAG/LEAD, 19-28

LAST_VALUE, 19-24

linear regression, 19-32

NTILE, 19-15

PERCENT_RANK, 19-14

RANK, 19-5

ranking, 19-5

RATIO_TO_REPORT, 19-27

REGR_AVGX, 19-33

REGR_AVGY, 19-33

REGR_COUNT, 19-32

REGR_INTERCEPT, 19-33

REGR_SLOPE, 19-33

REGR_SXX, 19-33

REGR_SXY, 19-33

REGR_SYY, 19-33

reporting, 19-24

ROW_NUMBER, 19-16

WIDTH_BUCKET, 19-43

windowing, 19-17

global

indexes, 21-90

striping, 4-5

granting access to change data, 15-3

granules, 5-3

block range, 5-3

partition, 5-4

GROUP_ID function, 18-18

grouping

compatibility check, 22-40

conditions, 22-62

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-71

hash areas, 21-81

hash joins, 21-60, 21-81

hash partitioning, 5-7

HASH_AREA_SIZE parameter

and parallel execution, 21-59, 21-60

hierarchies, 9-2

how used, 2-6

multiple, 9-7

overview, 2-6

rolling up and drilling down, 9-2

hints

PARALLEL, 21-35

PARALLEL_INDEX, 21-35

query rewrite, 22-8, 22-9

histograms

creating with user-defined buckets, 19-45

hypothetical rank, 19-39

I/O

asynchronous, 21-66

parallel execution, 5-2, 21-2

striping to avoid bottleneck, 4-2

importing a change table

Change Data Capture, 15-18

importing a source table

Change Data Capture, 15-18

indexes

bitmap indexes, 6-6

bitmap join, 6-6

B-tree, 6-10

cardinality, 6-3

creating in parallel, 21-93

global, 21-90

local, 21-90

nulls and, 6-5

parallel creation, 21-93

parallel DDL storage, 21-16

parallel local, 21-93

partitioned tables, 6-6

partitioning, 5-8

STORAGE clause, 21-94

index-organized tables

parallel CREATE, 21-14

parallel queries, 21-11

INITIAL extent size, 13-29, 21-84

INSERT statement

functionality, 21-95

parallelizing INSERT ... SELECT, 21-41

instance groups for parallel operations, 21-37

instance recovery

SMON process, 21-24

instances

instance groups, 21-37

integrity rules

parallel DML restrictions, 21-26

interface

publish and subscribe, 15-2

invalidating

materialized views, 8-41

Java

used by Change Data Capture, 15-8

JOB_QUEUE_PROCESSES parameter, 14-15

join compatibility, 22-31

joins

full partition-wise, 5-15

partial partition-wise, 5-20

partition-wise, 5-15

star joins, 17-3

star queries, 17-2

key lookups, 13-34

keys, 8-7, 17-2

LAG/LEAD functions, 19-28

LARGE_POOL_SIZE parameter, 21-51

LAST_VALUE function, 19-24

level relationships, 2-6

purpose, 2-7

levels, 2-6

linear regression functions, 19-32

load

parallel, 13-31

LOB datatypes

restrictions

parallel DDL, 21-14

parallel DML, 21-25

local indexes, 6-3, 6-6, 21-90

local striping, 4-4

locks

parallel DML, 21-24

LOG_BUFFER parameter

and parallel execution, 21-63

LOGGING clause, 21-92

logging mode

parallel DDL, 21-14, 21-15

logical design, 3-2

lookup tables, 8-7, 17-2

star queries, 17-2

manual

refresh, 14-11

striping, 4-4

massively parallel processing (MPP)

affinity, 21-77, 21-78

massively parallel systems, 5-2, 21-2

materialized views

aggregates, 8-10

altering, 8-42

build methods, 8-24

containing only joins, 8-16

creating, 8-22

delta joins, 22-35

dropping, 8-32, 8-42

estimating size, 16-37

invalidating, 8-41

logs, 11-7

naming, 8-23

nested, 8-18

partitioned tables, 14-22

partitioning, 8-34

prebuilt, 8-23

query rewrite

hints, 22-8, 22-9

matching join graphs, 8-25

parameters, 22-8

privileges, 22-10

refresh dependent, 14-13

refreshing, 8-27, 14-9

refreshing all, 14-13

registration, 8-32

restrictions, 8-25

rewrites

enabling, 22-7

schema design guidelines, 8-8

security, 8-41

storage characteristics, 8-23

types of, 8-10

uses for, 8-2

MAXEXTENTS keyword, 13-29, 21-84

MAXEXTENTS UNLIMITED storage parameter, 21-23

measures, 8-7, 17-2

media recoveries, 21-88

memory

configure at 2 levels, 21-58

process classification, 21-81

virtual, 21-58

merge, 14-5

MERGE statement, 14-5

MINIMUM EXTENT parameter, 21-17

mirroring

disks, 4-10

monitoring

data capture, 15-10

parallel processing, 21-71

refresh, 14-15

MOVE PARTITION statement

rules of parallelism, 21-43

MPP

disk affinity, 4-6

MULTIBLOCK_READ_COUNT parameter, 13-29

multiple archiver processes, 21-91

multiple hierarchies, 9-7

MV_CAPABILITIES_TABLE, 8-44

MVIEW_WORKLOAD view, 16-2

nested loop joins, 21-81

nested materialized views, 8-18

refreshing, 14-20

restrictions, 8-21

nested tables

restrictions, 21-13

NEVER clause, 8-27

NEXT extent, 21-84

NOAPPEND hint, 21-95

NOARCHIVELOG mode, 21-93

nodes

disk affinity in a Real Application Cluster, 21-77

NOLOGGING clause, 21-86, 21-92, 21-93

with APPEND hint, 21-95

NOLOGGING mode

parallel DDL, 21-14, 21-15

nonvolatile data, 1-3

NOPARALLEL attribute, 21-85

NOREWRITE hint, 22-8, 22-9

NTILE function, 19-15

nulls

indexes and, 6-5

object types

parallel query, 21-12

restrictions, 21-13

restrictions

parallel DDL, 21-14

parallel DML, 21-25

OLTP database

batch jobs, 21-21

parallel DML, 21-20

ON COMMIT clause, 8-26

ON DEMAND clause, 8-26

online transaction processing (OLTP)

processes, 21-81

optimization

parallel SQL, 21-6

optimizations

query rewrite

enabling, 22-7

hints, 22-8, 22-9

matching join graphs, 8-25

query rewrites

privileges, 22-10

OPTIMIZER_MODE parameter, 14-15, 21-100, 22-8

optimizers

with rewrite, 22-2

Oracle Real Application Clusters

disk affinity, 21-77

instance groups, 21-37

parallel load, 13-32

system monitor process and, 21-24

ORDER BY clause, 8-30

outer joins

with query rewrite, 22-61

oversubscribing resources, 21-82

paging, 21-82

rate, 21-59

subsystem, 21-82

PARALLEL clause, 21-95

parallelization rules, 21-38

PARALLEL CREATE INDEX statement, 21-62

PARALLEL CREATE TABLE AS SELECT statement

external fragmentation, 21-84

resources required, 21-62

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-100

I/O parameters, 21-65

index creation, 21-93

interoperator parallelism, 21-9

intraoperator parallelism, 21-9

introduction, 5-2

maximum processes, 21-80

method of, 21-31

plans, 21-69

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

resource parameters, 21-58

rewriting SQL, 21-85

solving problems, 21-84

space management, 21-83

tuning, 5-2, 21-2

understanding performance issues, 21-80

PARALLEL hint, 21-35, 21-85, 21-95

parallelization rules, 21-38

UPDATE and DELETE, 21-39

parallel load

example, 13-31

Oracle Real Application Clusters, 13-32

using, 13-26

parallel partition-wise joins

performance considerations, 5-24

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 parameter, 21-47

PARALLEL_AUTOMATIC_TUNING parameter, 21-30

PARALLEL_BROADCAST_ENABLE parameter, 21-62

PARALLEL_EXECUTION_MESSAGE_SIZE parameter, 21-61

PARALLEL_INDEX hint, 21-35

PARALLEL_MAX_SERVERS parameter, 14-15, 21-4, 21-50

and parallel execution, 21-49

PARALLEL_MIN_PERCENT parameter, 21-36, 21-49, 21-57

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

PARALLEL_THREADS_PER_CPU parameter, 21-30, 21-48

parallelism

degree, 21-32

degree, overriding, 21-84

enabing for tables and queries, 21-46

interoperator, 21-9

intraoperator, 21-9

parameters

CLUSTER_DATABASE_INSTANCES, 21-57

COMPATIBLE, 13-29, 22-8

DB_BLOCK_SIZE, 21-66

DB_FILE_MULTIBLOCK_READ_COUNT, 21-66

DISK_ASYNCH_IO, 21-66

DML_LOCKS, 21-63

ENQUEUE_RESOURCES, 21-63

FAST_START_PARALLEL_ROLLBACK, 21-63

FREELISTS, 21-91

HASH_AREA_SIZE, 21-59

JOB_QUEUE_PROCESSES, 14-15

LARGE_POOL_SIZE, 21-51

LOG_BUFFER, 21-63

MULTIBLOCK_READ_COUNT, 13-29

OPTIMIZER_MODE, 14-15, 21-100, 22-8

PARALLEL_ADAPTIVE_MULTI_USER, 21-47

PARALLEL_AUTOMATIC_TUNING, 21-30

PARALLEL_BROADCAST_ENABLE, 21-62

PARALLEL_EXECUTION_MESSAGE_SIZE, 21-61

PARALLEL_MAX_SERVERS, 14-15, 21-4, 21-50

PARALLEL_MIN_PERCENT, 21-36, 21-49, 21-57

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

PARALLEL_THREADS_PER_CPU, 21-30

PGA_AGGREGATE_TARGET, 14-15

QUERY_REWRITE_ENABLED, 22-7, 22-8

ROLLBACK_SEGMENTS, 21-63

SHARED_POOL_SIZE, 21-51, 21-56

SORT_AREA_SIZE, 21-60

STAR_TRANSFORMATION_ENABLED, 17-4

TAPE_ASYNCH_IO, 21-66

TIMED_STATISTICS, 21-72

TRANSACTIONS, 21-62

partial partition-wise joins, 5-20

Partition Change Tracking (PCT), 8-34, 14-22

partition granules, 5-4

partitioned tables

data warehouses, 5-9

example, 13-29

partitioning, 11-7

composite, 5-8

data, 5-4

hash, 5-7

indexes, 5-8

materialized views, 8-34

prebuilt tables, 8-39

range, 5-6

partitions

affinity, 21-77

bitmap indexes, 6-6

parallel DDL, 21-13

partition pruning

disk striping and, 21-78

pruning, 5-13

range partitioning

disk striping and, 21-78

rules of parallelism, 21-43, 21-45

partition-wise joins, 5-15

benefits of, 5-23

PERCENT_RANK function, 19-14

performance

DSS database, 21-20

PGA_AGGREGATE_TARGET parameter, 14-15

physical design, 3-2

pivoting, 13-35

PL/SQL packages

for publish and subscribe tasks, 15-3

plans

star transformations, 17-7

prebuilt materialized views, 8-23

PRIMARY KEY constraints, 21-94

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

DSS, 21-81

maximum number, 21-80

maximum number for parallel query, 21-80

OLTP, 21-81

pruning

partitions, 5-13, 21-78

using DATE columns, 5-14

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-8

queries

ad hoc, 21-14

enabling parallelism for, 21-46

star queries, 17-2

query delta joins, 22-34

query rewrite

controlling, 22-8

correctness, 22-10

enabling, 22-7

hints, 22-8, 22-9

matching join graphs, 8-25

methods, 22-11

parameters, 22-8

privileges, 22-10

restrictions, 8-25

when it occurs, 22-4

QUERY_REWRITE_ENABLED parameter, 22-7, 22-8

RAID, 21-88

configurations, 4-9

range partitioning, 5-6

performance considerations, 5-9

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-63

reference tables, 8-7

refresh

monitoring, 14-15

options, 8-26

refreshing

materialized views, 14-9

nested materialized views, 14-20

partitioning, 14-2

REGR_AVGX function, 19-33

REGR_AVGY function, 19-33

REGR_COUNT function, 19-32

REGR_INTERCEPT function, 19-33

REGR_R2 function, 19-33

REGR_SLOPE function, 19-33

REGR_SXX function, 19-33

REGR_SXY function, 19-33

REGR_SYY function, 19-33

regression

detecting, 21-68

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

consumption, parameters affecting parallel DML/DDL, 21-62

limiting for users, 21-50

limits, 21-49

oversubscribing, 21-82

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-5

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-25

rollback segments, 21-63

MAXEXTENTS UNLIMITED, 21-23

OPTIMAL, 21-23

parallel DML, 21-23

ROLLBACK_SEGMENTS parameter, 21-63

rolling up hierarchies, 9-2

ROLLUP, 18-7

partial, 18-8

when to use, 18-7

root level, 2-6

ROW_NUMBER function, 19-16

RULE hint, 21-100

sar UNIX command, 21-77

scalability

batch jobs, 21-21

parallel DML, 21-20

scalable operations, 21-88

schemas, 17-2

design guidelines for materialized views, 8-8

snowflake, 2-3

star, 2-3

star schemas, 17-2

third-normal form, 17-2

security

Change Data Capture, 15-8

subscriber access to change data, 15-8

SELECT privilege

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

sessions

enabling parallel DML, 21-21

SGA size, 21-58

shared server

parallel SQL execution, 21-4

SHARED_POOL_SIZE parameter, 21-51, 21-56

single table aggregate requirements, 8-13

skewing parallel DML workload, 21-37

SMP architecture

disk affinity, 21-78

snowflake schemas, 17-3

complex queries, 17-3

SORT_AREA_SIZE parameter, 21-60

and parallel execution, 21-60

source systems, 11-2

definition, 15-6

source tables

definition, 15-6

exporting for Change Data Capture, 15-18

importing for Change Data Capture, 15-18

space management, 21-87

MINIMUM EXTENT parameter, 21-17

parallel DDL, 21-16

parallel execution, 21-83

reducing transactions, 21-84

SPLIT PARTITION statement

rules of parallelism, 21-43

SQL statements

parallelizing, 21-3, 21-6

SQL*Loader, 13-26

staging

areas, 1-6

databases, 8-2

files, 8-2

with and without Change Data Capture, 15-2

STALE_TOLERATED mode, 22-10

star joins, 17-3

star queries, 17-2

star transformation, 17-5

star schemas

advantages, 2-4

defining fact tables, 2-5

dimensional model, 2-4, 17-2

star transformations, 17-2, 17-5

restrictions, 17-10

STAR_TRANSFORMATION_ENABLED parameter, 17-4

statistics, 22-63

estimating, 21-69

operating system, 21-77

storage

fragmentation in parallel DDL, 21-16

STORAGE clause

parallel execution, 21-16

parallel query, 21-94

storage parameters

MAXEXTENTS UNLIMITED, 21-23

OPTIMAL (in rollback segments), 21-23

striping

analyzing, 4-6

example, 13-26

local, 4-4

manual, 4-4

media recovery, 4-10

temporary tablespace, 21-88

subqueries

in DDL statements, 21-14

subscriber

definition, 15-5

subscriber views

definition, 15-7

dropping, 15-3

removing, 15-3

subscribers

access to change data, 15-8

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-6

summary management, 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

synchronous data capture, 15-11

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-73

tables

detail tables, 8-7

dimension tables (lookup tables), 8-7

dimensions

star queries, 17-2

enabling parallelism for, 21-46

external, 13-6

fact tables, 8-7

star queries, 17-2

historical, 21-21

lookup tables (dimension tables), 17-2

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

dedicated temporary, 21-87

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

TAPE_ASYNCH_IO parameter, 21-66

temporary extents, 21-87

temporary segments

parallel DDL, 21-16

temporary tablespaces

striping, 21-88

text match, 22-12

with query rewrite, 22-62

third-normal-form schemas, 17-2

TIMED_STATISTICS parameter, 21-72

timestamps, 11-6

transactions

distributed

parallel DDL restrictions, 21-11

parallel DML restrictions, 21-11, 21-27

rate, 21-83

TRANSACTIONS parameter, 21-62

transformations, 13-2

scenarios, 13-26

SQL and PL/SQL, 13-9

SQL*Loader, 13-5

star, 17-2

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

TRUSTED mode, 22-10

two-phase commit, 21-62

unique constraints, 7-4, 21-94

UNLIMITED extents, 21-23

update frequencies, 8-50

UPDATE statement

parallel UPDATE statement, 21-39

update windows, 8-50

upsert (now merge), 13-11

user resources

limiting, 21-50

USER_PUBLISHED_COLUMNS view, 15-10

USER_SOURCE_TABLES view, 15-10

USER_SUBSCRIBED_COLUMNS view, 15-10

USER_SUBSCRIBED_TABLES view, 15-10

USER_SUBSCRIPTIONS view, 15-10

V$FILESTAT view

and parallel query, 21-72

V$PARAMETER view, 21-73

V$PQ_SESSTAT view, 21-70, 21-72

V$PQ_SYSSTAT view, 21-70

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

V$PX_PROCESS view, 21-71, 21-72

V$PX_SESSION view, 21-71

V$PX_SESSTAT view, 21-71

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

V$SORT_SEGMENT view, 21-84

V$SYSSTAT view, 21-63, 21-74, 21-92

validating dimensions, 9-12

view constraints, 7-7, 22-14

views

ALL_PUBLISHED_COLUMNS, 15-10

ALL_SOURCE_TABLES, 15-10, 15-13

CHANGE_SETS, 15-10

CHANGE_SOURCES, 15-10

CHANGE_TABLES, 15-10

DBA_DATA_FILES, 21-72

DBA_EXTENTS, 21-72

DBA_PUBLISHED_COLUMNS, 15-10

DBA_SOURCE_TABLES, 15-10

DBA_SUBSCRIBED_COLUMNS, 15-10

DBA_SUBSCRIBED_TABLES, 15-10

DBA_SUBSCRIPTIONS, 15-10

USER_PUBLISHED_COLUMNS, 15-10

USER_SOURCE_TABLES, 15-10

USER_SUBSCRIBED_COLUMNS, 15-10

USER_SUBSCRIBED_TABLES, 15-10

USER_SUBSCRIPTIONS, 15-10

V$FILESTAT, 21-72

V$PARAMETER, 21-73

V$PQ_SESSTAT, 21-72

V$PQ_TQSTAT, 21-73

V$PX_PROCESS, 21-72

V$SESSTAT, 21-74, 21-77

V$SYSSTAT, 21-74

virtual memory, 21-58

vmstat UNIX command, 21-77

wait times, 21-83

WIDTH_BUCKET function, 19-43

windowing functions, 19-17

workloads

distribution, 21-70

exceeding, 21-82

skewing, 21-37