Oracle8i Data Warehousing Guide -- Index

adaptive multi-user

ALTER TABLE statement

NOLOGGING clause, 18-60

altering dimensions, 9-14

analytic functions, 17-21

ANALYZE statement, 18-51

ANALYZE TABLE statement, 15-3

analyzing data

for parallel processing, 18-69

APPEND hint, 18-60

applications

data warehouses

star queries, 16-2

decision support, 18-2

decision support systems, 6-3

ARCH processes

multiple, 18-56

asynchronous I/O, 18-26

backups

on partitioned tables, 6-6

parallel query and DML, 6-3

block range granules, 5-3

BOTTOM_N function, 17-31

B-tree indexes, 6-6

bitmap indexes versus, 6-3

buffer pools

setting for parallel operations, 18-36

build methods, 8-18

cardinality, 6-3

CASE expressions, 17-52

change

columns

COMPATIBLE parameter, 14-13, 19-5

COMPLETE clause, 8-20

complete refresh, 14-18

complex materialized views, 14-27

complex queries

snowflake schemas, 16-3

composite partitioning, 5-6

performance considerations, 5-9

concurrent users

increasing the number of, 18-9

constraints, 7-2, 9-13

foreign key, 7-5

RELY, 7-5

unique, 7-3

with partitioning, 7-6

with query rewrite, 19-29

CORR function, 17-48

cost-based optimizations, 18-64

parallel execution, 18-64

star queries, 16-2

cost-based rewrite, 19-3

COVAR_POP function, 17-47

COVAR_SAMP function, 17-48

CPU

utilization, 5-2, 18-2

CREATE DIMENSION statement, 9-6

CREATE INDEX statement, 18-58

CREATE MATERIALIZED VIEW statement, 8-17

enabling query rewrite, 19-5

CREATE SNAPSHOT statement, 8-3

CREATE TABLE AS SELECT statement, 18-50, 18-68

CUBE, 17-10

partial, 17-13

when to use, 17-14

CUBE/ROLLUP

with query rewrite, 19-20

CUME_DIST function, 17-31

data

partitioning, 5-4

purging, 14-7

substitutions, 13-5

sufficiency check, 19-15

data marts

definition of, 20-2

dependent, 20-2

differences from data warehouse, 20-2

independent, 20-2

data warehouses, 8-2

definition of, 20-2

differences from data marts, 20-2

dimension tables

(lookup tables), 8-7

dimensions, 16-2

fact tables (detail tables), 8-7

partitioned tables, 5-6

refresh tips, 14-22

refreshing, 14-2

star queries, 16-2

database writer process (DBWn)

tuning, 18-56

databases

layout, 5-3

staging, 8-2

date folding

with query rewrite, 19-24

DB_BLOCK_SIZE parameter, 18-25

and parallel query, 18-25

DB_FILE_MULTIBLOCK_READ_COUNT parameter, 18-26

DBA_DATA_FILES view, 18-71

DBA_EXTENTS view, 18-71

DBMS_MVIEW package, 14-18

DBMS_MVIEW.REFRESH procedure, 14-17, 14-19

DBMS_MVIEW.REFRESH_ALL_MVIEWS procedure, 14-17

DBMS_MVIEW.REFRESH_DEPENDENT procedure, 14-17

DBMS_OLAP package, 8-35, 15-2, 15-3, 15-5

DBMS_OLAP.RECOMMEND_MV procedure, 8-35

DBMS_STATISTICS package, 19-3

DBMS_STATS package, 15-3

decision support

processes, 18-35

decision support systems

bitmap indexes, 6-3

degree of parallelism

and adaptive multi-user, 18-5

setting, 18-5

DEMO_DIM package, 9-12

denormalized

star schemas, 2-4

DENSE_RANK function, 17-24

dependent data marts, 20-2

detail tables, 8-7

dimension tables, 8-8, 16-2

normalized, 9-10

dimension wizard, 9-11

dimensions, 2-5, 9-2, 9-13

altering, 9-14

creating, 9-6

definition, 9-2

dimension tables (lookup tables), 8-7

dropping, 9-15

hierarchies, 2-6

hierarchies overview, 2-6

with query rewrite, 19-29

direct-load inserts

external fragmentation, 18-44

disk affinities

and parallel query, 18-47

disabling with MPP, 4-4

with MPP, 18-52

DISK_ASYNCH_IO parameter, 18-26

DML_LOCKS parameter, 18-23, 18-25

drilling across, 9-5

drilling down, 9-3

hierarchies, 9-3

DROP MATERIALIZED VIEW statement, 8-17

prebuilt tables, 8-30

dropping

dimensions, 9-15

materialized views, 8-36

ENFORCED mode, 19-26

ENQUEUE_RESOURCES parameter, 18-23, 18-25

estimating materialized view size, 15-7

ETT

overview, 10-2

process, 7-2, 20-5

tools, 10-2

EVALUATE_UTILIZATION_W package, 15-9

EXCHANGE PARTITION statement, 7-6

execution plans

parallel operations, 18-67

star transformations, 16-8

EXPLAIN PLAN statement, 18-67, 19-27

query parallelization, 18-54

star transformations, 16-8

exporting

EXP utility, 11-4

expression matching

with query rewrite, 19-23

extents

size, 14-13

temporary, 18-52

extractions

data files, 11-2

distributed operations, 11-5

OCI, 11-4

overview, 11-2

Pro*C, 11-4

SQL*Plus, 11-3

fact tables, 2-4, 2-5

facts, 9-2

FAST REFRESH restrictions, 8-20

FAST_START_PARALLEL_ROLLBACK parameter, 18-23

FIRST_ROWS hint, 18-21

FIRST_VALUE function, 17-42

FORCE clause, 8-20

foreign key constraints, 7-5

foreign key joins

snowflake schemas, 16-3

fragmentation

external, 18-44

FREELISTS parameter, 18-56

full partition-wise joins, 5-12

functions

LAG/LEAD, 17-46

linear regression, 17-48

GC_FILES_TO_LOCKS parameter, 18-44

GC_ROLLBACK_LOCKS parameter, 18-45

GC_ROLLBACK_SEGMENTS parameter, 18-45

global

GROUP BY clause

decreasing demand for, 18-39

grouping

compatibility check, 19-18

conditions, 19-30

GROUPING function, 17-15

when to use, 17-18

GV$FILESTAT view, 18-70

hash areas, 18-35

hash joins, 18-19, 18-35

hash partitioning, 5-5

HASH_AREA_SIZE parameter, 18-39

and parallel execution, 18-18, 18-19

relationship to memory, 18-38

HASH_MULTIBLOCK_IO_COUNT parameter, 18-26

rolling up and drilling down, 9-3

hints

query rewrite, 19-5, 19-28

histograms

creating with user-defined buckets, 17-53

I/O

asynchronous, 18-26

parallel execution, 5-2, 18-2

striping to avoid bottleneck, 4-2

independent data marts, 20-2

indexes

creating in parallel, 18-57

global, 18-55

local, 18-55

nulls and, 6-5

parallel creation, 18-57, 18-58

parallel local, 18-58

partitioned tables, 6-6

partitioning, 5-6

STORAGE clause, 18-58

INITIAL extent size, 14-13, 18-44

INSERT

functionality, 18-59

invalidating

materialized views, 8-34

JOB_QUEUE_INTERVALS parameter, 14-19

JOB_QUEUE_PROCESSES parameter, 14-19

join compatibility, 19-10

joins, 8-11

full partition-wise, 5-12

partial partition-wise, 5-17

LAG/LEAD functions, 17-46

LARGE_POOL_SIZE parameter, 18-10

LAST_VALUE function, 17-42

level relationships, 2-7

purpose, 2-7

levels, 2-6, 2-7

linear regression functions, 17-48

load

parallel, 14-15

local indexes, 6-3, 6-6, 18-55

local striping, 4-4

LOG_BUFFER parameter

and parallel execution, 18-23

LOGGING clause, 18-56

lookup tables, 8-7, 16-2

star queries, 16-2

manual

refresh, 14-18

striping, 4-3

massively parallel systems, 5-2, 18-2

materialized views

MAXEXTENTS keyword, 14-13, 18-44

measures, 8-7, 16-2

media recoveries, 18-52

memory

configure at 2 levels, 18-17

process classification, 18-35

virtual, 18-18

mirroring

disks, 4-8

monitoring

parallel processing, 18-70

refresh, 14-28

MPP

disk affinity, 4-4

MULTIBLOCK_READ_COUNT parameter, 14-13

multiple archiver processes, 18-56

multiple hierarchies, 9-8

multi-threaded server, 18-35

nested loop joins, 18-35

nested materialized views, 8-23

refreshing, 14-26

restrictions, 8-24

nested queries, 18-49

NEVER clause, 8-20

NEXT extent, 18-44

NOAPPEND hint, 18-60

NOARCHIVELOG mode, 18-57

NOLOGGING clause, 18-50, 18-56, 18-58

with APPEND hint, 18-60

NOPARALLEL attribute, 18-48

NOREWRITE hint, 19-5, 19-28

NTILE function, 17-33

nulls

indexes and, 6-5

OLAP tools, 2-5

ON COMMIT clause, 8-20

ON DEMAND clause, 8-20

online transaction processing (OLTP)

processes, 18-35

operating systems

striping, 4-3

optimizations

cost-based

star queries, 16-2

query rewrite

enabling, 19-5

hints, 19-5, 19-28

matching join graphs, 8-18

query rewrites

privileges, 19-6

OPTIMIZER_MODE parameter, 14-28, 18-64, 19-5

OPTIMIZER_PERCENT_PARALLEL parameter, 14-28, 18-21, 18-67

optimizers

with rewrite, 19-2

Oracle Parallel Server

disk affinities, 18-47

parallel execution, 18-44

parallel load, 14-16

Oracle Trace, 15-3

ORDER BY clause, 8-22

decreasing demand for, 18-39

outer joins

with query rewrite, 19-29

oversubscribing resources, 18-37, 18-41

paging, 18-37

rate, 18-18

subsystem, 18-37

PARALLEL clause, 18-59, 18-60

PARALLEL CREATE INDEX statement, 18-22

PARALLEL CREATE TABLE AS SELECT statement

external fragmentation, 18-44

resources required, 18-22

parallel DML

bitmap indexes, 6-3

parallel execution

adjusting workloads, 18-40

cost-based optimization, 18-64

I/O parameters, 18-25

index creation, 18-57

introduction, 5-2

maximum processes, 18-34

method of, 18-3

parallel server, 18-44

plans, 18-67

process classification, 4-4

resource parameters, 18-17

rewriting SQL, 18-49

solving problems, 18-48

space management, 18-43

tuning, 5-1, 18-2

understanding performance issues, 18-33

PARALLEL hint, 18-48, 18-59, 18-67

parallel load

example, 14-15

Oracle Parallel Server, 14-16

using, 14-10

parallel partition-wise joins

performance considerations, 5-21

parallel query

bitmap indexes, 6-3

parallel scan operations, 4-3

parallel server

disk affinities, 18-47

parallel execution tuning, 18-44

PARALLEL_ADAPTIVE_MULTI_USER parameter, 18-6, 18-29

PARALLEL_AUTOMATIC_TUNING parameter, 18-3

PARALLEL_BROADCAST_ENABLE parameter, 18-21

PARALLEL_EXECUTION_MESSAGE_SIZE parameter, 18-20

PARALLEL_MAX_SERVERS parameter, 14-27, 18-8, 18-9, 18-38

and parallel execution, 18-8

PARALLEL_MIN_PERCENT parameter, 18-9, 18-16

PARALLEL_MIN_SERVERS parameter, 18-10

PARALLEL_SERVER_INSTANCES parameter

and parallel execution, 18-17

PARALLEL_THREADS_PER_CPU parameter, 18-3, 18-7

parallelism

degree, overriding, 18-48

enabing for tables and queries, 18-6

parameters

COMPATIBLE, 14-13, 19-5

DB_BLOCK_SIZE, 18-25

DB_FILE_MULTIBLOCK_READ_COUNT, 18-26

DISK_ASYNCH_IO, 18-26

DML_LOCKS, 18-23, 18-25

ENQUEUE_RESOURCES, 18-23, 18-25

FAST_START_PARALLEL_ROLLBACK, 18-23

FREELISTS, 18-56

GC_FILES_TO_LOCKS, 18-44

GC_ROLLBACK_LOCKS, 18-45

GC_ROLLBACK_SEGMENTS, 18-45

HASH_AREA_SIZE, 18-18, 18-38, 18-39

HASH_MULTIBLOCK_IO_COUNT, 18-26

JOB_QUEUE_INTERVAL, 14-19

JOB_QUEUE_PROCESSES, 14-19

LARGE_POOL_SIZE, 18-10

LOG_BUFFER, 18-23

MULTIBLOCK_READ_COUNT, 14-13

OPTIMIZED_PERCENT_PARALLEL, 18-67

OPTIMIZER_MODE, 14-28, 18-64, 19-5

OPTIMIZER_PERCENT_PARALLEL, 14-28, 18-21

PARALLEL_ADAPTIVE_MULTI_USER, 18-29

PARALLEL_AUTOMATIC_TUNING, 18-3

PARALLEL_BROADCAST_ENABLE, 18-21

PARALLEL_EXECUTION_MESSAGE_SIZE, 18-20

PARALLEL_MAX_SERVERS, 14-27, 18-8, 18-9, 18-38

PARALLEL_MIN_PERCENT, 18-9, 18-16

PARALLEL_MIN_SERVERS, 18-10

PARALLEL_SERVER_INSTANCES, 18-17

PARALLEL_THREADS_PER_CPU, 18-3

QUERY_REWRITE_ENABLED, 19-5

ROLLBACK_SEGMENTS, 18-22

SHARED_POOL_SIZE, 18-10, 18-16

SORT_AREA_SIZE, 14-28, 18-19

SORT_MULTIBLOCK_READ_COUNT, 18-26

STAR_TRANSFORMATION_ENABLED, 16-4

TAPE_ASYNCH_IO, 18-26

TRANSACTIONS, 18-22

UTL_FILE_DIR, 14-19

partial partition-wise joins, 5-17

partition granules, 5-4

partitioned tables

data, 5-4

hash, 5-5

indexes, 5-6

materialized views, 8-31

prebuilt tables, 8-33

range, 5-5

partitions

bitmap indexes, 6-6

pruning, 5-10

partition-wise joins, 5-12

benefits of, 5-20

PCM lock, 18-44

PERCENT_RANK function, 17-33

physical database layouts, 5-3

pivoting, 13-7

plans

star transformations, 16-8

prebuilt materialized views, 8-16

PRIMARY KEY constraints, 18-58

processes

and memory contention in parallel processing, 18-9

classes of parallel execution, 4-4

DSS, 18-35

maximum number, 18-34

maximum number for parallel query, 18-34

OLTP, 18-35

pruning

partitions, 5-10

using DATE columns, 5-11

purging data, 14-7

queries

enabling parallelism for, 18-6

star queries, 16-2

query delta joins, 19-13

query rewrite

matching join graphs, 8-18

QUERY_REWRITE_ENABLED parameter, 19-5

RAID, 4-9, 18-52

range partitioning, 5-5

performance considerations, 5-6

RANK function, 17-24

ranking functions, 17-24

RATIO_TO_REPORT function, 17-45

RECOMMEND_MV function, 15-5

RECOMMEND_MV_W function, 15-5

recovery

media, with striping, 4-8

redo buffer allocation retries, 18-23

redundancy

star schemas, 2-4

reference tables, 8-7

refresh

monitoring, 14-28

options, 8-19

refreshing

materialized views, 14-16

nested materialized views, 14-26

partitioning, 14-2

REGR_AVGX function, 17-49

REGR_AVGY function, 17-49

REGR_COUNT function, 17-49

REGR_INTERCEPT function, 17-49

REGR_R2 function, 17-50

REGR_SLOPE function, 17-49

REGR_SXX function, 17-50

REGR_SXY function, 17-50

REGR_SYY function, 17-50

regression

detecting, 18-66

RELY constraints, 7-5

reporting functions, 17-43

resources

consumption, parameters affecting, 18-17

consumption, parameters affecting parallel DML/DDL, 18-22

limiting for users, 18-9

limits, 18-8

oversubscribing, 18-37

oversubscription, 18-41

parallel query usage, 18-17

restrictions

FAST REFRESH, 8-20

nested materialized views, 8-24

query rewrite, 8-19

REWRITE hint, 19-5, 19-28

rewrites

query optimizations

matching join graphs, 8-18

rollback segments, 18-22

ROLLBACK_SEGMENTS parameter, 18-22

rolling up hierarchies, 9-3

ROW_NUMBER function, 17-35

RULE hint, 18-64

sar UNIX command, 18-75

scalable operations, 18-53

schemas, 16-2

design guidelines for materialized views, 8-8

snowflake, 2-3

star, 2-3

star schemas, 16-2

third-normal form, 16-2

SGA size, 18-18

SHARED_POOL_SIZE parameter, 18-10, 18-16

single table aggregate requirements, 8-13

snowflake schemas, 16-3

complex queries, 16-3

SORT_AREA_SIZE parameter, 14-28, 18-19

and parallel execution, 18-19

SORT_MULTIBLOCK_READ_COUNT parameter, 18-26

source systems, 11-2

space management, 18-51

parallel execution, 18-43

reducing transactions, 18-44

SQL functions

COUNT, 6-5

SQL Loader, 14-10

SQL text match, 19-8

with query rewrite, 19-30

staging

databases, 8-2

file systems, 4-9

files, 8-2

staging databases, 8-2

STALE_TOLERATED mode, 19-26

star joins, 16-3

star queries, 16-2

star transformation, 16-5

star schemas

advantages, 2-4

defining fact tables, 2-5

dimensional model, 2-3, 16-2

redundancy, 2-4

star transformation, 16-2

star transformations, 16-5

restrictions, 16-9

STAR_TRANSFORMATION_ENABLED parameter, 16-4

statistics, 19-31

estimating, 18-67

functions, 17-46

operating system, 18-75

STDDEV_POP function, 17-47

STDDEV_SAMP function, 17-47

STORAGE clause

parallel query, 18-58

striping

analyzing, 4-5

and disk affinity, 18-47

example, 14-10

local, 4-4

manual, 4-3

media recovery, 4-8

operating systems, 4-3

temporary tablespace, 18-52

subqueries

correlated, 18-49

summary advisor, 15-2

wizard, 15-8

summary management, 8-5

symmetric multiprocessors, 5-2, 18-2

table queues, 18-71

tables

detail tables, 8-7

dimension tables (lookup tables), 8-7

dimensions

star queries, 16-2

enabling parallelism for, 18-6

fact tables, 8-7

star queries, 16-2

lookup tables (dimension tables), 16-2

tablespaces

creating, example, 14-11

dedicated temporary, 18-51

transportable, 11-5, 12-3

TAPE_ASYNCH_IO parameter, 18-26

temporary extents, 18-52

temporary tablespaces

striping, 18-52

third-normal-form schemas, 16-2

TIMED_STATISTICS parameter, 18-71

timestamps, 11-7

TOP_N function, 17-31

transactions

rate, 18-43

TRANSACTIONS parameter, 18-22

transformations, 13-2

SQL and PL/SQL, 13-4

SQL*Loader, 13-3

star, 16-2

transportable tablespaces, 11-5, 12-3

transportation

definition, 12-2

distributed operations, 12-2

flat files, 12-2

triggers, 11-7

TRUSTED mode, 19-26

tuning

parallel execution, 5-3

two-phase commit, 18-22

unique constraints, 7-3, 18-58

unusable indexes, 14-17

update frequencies, 8-37

update windows, 8-37

UPSERT statement, 14-5

user resources

limiting, 18-9

UTL_FILE_DIR parameter, 14-19

V$FILESTAT view

and parallel query, 18-71

V$PARAMETER view, 18-71

V$PQ_SESSTAT view, 18-68, 18-70

V$PQ_SYSSTAT view, 18-68

V$PQ_TQSTAT view, 18-69, 18-71

V$PX_PROCESS view, 18-70

V$PX_SESSION view, 18-70

V$PX_SESSTAT view, 18-70

V$SESSTAT view, 18-72, 18-75

V$SORT_SEGMENT view, 18-44

V$SYSSTAT view, 18-23, 18-56, 18-72

validating dimensions, 9-14

VAR_POP function, 17-47

VAR_SAMP function, 17-47

virtual memory, 18-18

vmstat UNIX command, 18-75

wait times, 18-37

windowing functions, 17-35

workloads

adjusting, 18-40

distribution, 18-68

exceeding, 18-37