Oracle9iR2 Data Warehousing

S

Scheduling jobs, 338–43

Schemas, 36–38

defined, 36

Easy Shopping Inc., 59–67

multiple, 36

name, 37

objects, 36

star, 25, 26

Scripts, Summary Advisor, 202–4

Security, 56–57

Easy Shopping Inc., 65–66

level, 389

maintaining, 389–92

of materialized views, 147–48

object privileges, 56–57

roles, 56–57

system privileges, 57

Sort-merge join, 97

Space

automated management, 393

resumable allocation, 394–95

use monitoring, 392–95

utilization, 393

Splitting partitions, 371, 373–75

defined, 371

illustrated, 374

See also Partitions

SQL Analyze, 383

SQL functions, 102–30

for aggregation, 102–9

for analytical computing, 109–25

CUBE, 102, 103

CUME_DIST(), 113, 124

DENSE_RANK(), 110

FIRST, 121

GROUP BY, 102, 104–7

GROUPING, 107–9

GROUPING_ID, 108–9

inverse percentile, 123–24

lag and lead, 109, 120–21

LAST, 121

linear regression, 122

MAX, 118

moving window aggregate, 109, 115–18

NTITLE(), 114–15

PERCENTILE_CONT(), 123–24

PERCENTILE_DISC(), 123–24

PERCENT_RANK(), 113, 124

RANK(), 110, 112, 124

RATIO_TO_REPORT, 119–20

reporting aggregates, 109, 118–20

ROLLUP, 102, 103–4

statistical, 109, 122

SUM, 118, 119

user-defined aggregates, 125–27

WIDTH_BUCKET(), 115

SQL Inspector, 324–25

SQL*Loader, 226–46

advanced options, 232

constraint violations, 245

control file, 228–29

conventional path load, 230

data definition language, 229

data file, 230

direct path load, 230

direct path load (parallel), 241–43

direct path load (single partition), 237–41

enabling constraints without validation, 246

error handling, 231–32

load exception processing, 244

log inspection, 233–35, 240–41, 243, 244, 397

modes of operation, 230–31

monitoring load operation progress, 233

performance optimization, 235–37

postload operations, 244–47

scheduling the load, 232

transformations with, 243–44

unstable indexes, checking for, 246–47

unstable indexes, rebuilding, 247

See also Loading data

SQL MERGE

defined, 253

loading dimensions with, 253–55

SQL text match, 171–72

Staging table, 268–73

Stale materialized views, 157, 274

Stale summaries, 137

Star schemas

defined, 26

illustrated, 25

Star transformation, 99–101

availability, 99

defined, 99

functioning of, 100–101

Statistical functions, 122

defined, 109

linear analysis, 122

SUBPARTITION TEMPLATE clause, 84, 85

Subscriber views

defined, 214

preparing, 219

Subscriptions

creating, 216–17

ending, 220

handle, 216

SUM function, 118, 119

Summary Advisor, 196–206, 383

with DBMS_OLAP package, 200–206

defined, 138

filters, 197–98, 201

hypothetical workload, 205

materialized view size, estimating, 206

materialized view use, monitoring, 205–6

recommendations, 198–200, 202

reports and scripts, 202–4

results, purging, 205

run methods, 197

run requirements, 196

with Wizard, 197–200

workload, 197–98, 200–201

See also Summary Management

Summary Advisor Wizard, 197–200

defined, 197

filters, 198

implement recommendations, 199

loading the workload, 197

recommendation graph, 198

view/modify recommendations, 199

Summary Management, 55, 135–207

components, 138–39

defined, 135

dimensions, 139, 162–69

with existing summary tables, 144–46

materialized views, 138, 139–48

need for, 135–38

query rewrite, 138, 170–96

Summary Advisor, 138, 196–206

Summary tables, 55, 135–39

complete refreshed, 138

defined, 135

existing, Summary Management with, 144–46

illustrated, 136

incrementally refreshed, 138

questions, 138

refreshed, 137–38, 149–57

stale, 137

Summary Wizard, 315, 316

Super Administrator, 333

Supervised learning, 475

Surrogate keys, 23, 222–24

in fact table, 224

uses, 223

Symmetric multiprocessor (SMP) systems, 133

System privileges, 57

System statistics, collecting, 381