Oracle Data Warehouse Tuning for 10g

466

create tablespaces, 432–34

EXPLAIN.SQL, 431–32

GENERATE.SQL, 434–43

location, 431

normalized dimensions, 443–46

SELECT statement

columns in, 164

subqueries, 344–45

Self joins, 179

SEQUENCE clause, 90

Sequences, 176–77

as counters, 176–77

in integer identifier creation, 176

SEQUENCESDIMSDW.SQL, 436–37

SEQUENCESFACTSDW.SQL, 443

SEQUENTIAL ORDER clause, 291

Session Data Unit (SDU) buffer, 393

Session layer, 199–206

defined, 192

V$SESSION_WAIT, 202

V$SYSTEM_EVENT, 200, 201, 203

See also

Wait Event Interface

Session-level events, 200

Sessions

hooking wait events to, 204

identifier (SID), 204

SQL code for, 205

Set operators, 98, 176

Single phase load, 352

Snowflake schemas, 19–20

defined, 43

in dimension organization, 21

duplication removal, 43

illustrated, 19, 114

star schemas vs., 37–44

See also

Dimensional data model

Soft parse, 185

Sorting

GROUP BY clause, 170

in OVER clause, 258

Sort merge joins, 182–83

Specialization, 12

Specialized expressions, 252

SQLAccess Advisor, 423

SQL*Loader, 354, 369–80

architecture, 370–71

control file, 372–79

executing, 379–80

input data files, 371–72

parameter file, 379–80

performance characteristics, 369–70

speed, 370

syntax, 380

SQL*Net, 390–93

SQL*Plus tool, 356

SQLTrace, 191

Star queries, 40, 62–69

defined, 62

fact table foreign key constraint indexes,

51

with heavy filtering, 69

illustrated, 67

problems with, 62–69

running, 65

SALE fact table, 66

See also

Queries

Star schemas, 39–43

defined, 17, 39

efficiency, 39

fact entities, 18–19

illustrated, 17, 20, 114

multiple, 21

snowflake schemas vs., 37–44

SQL joins and, 39

See also

Dimensional data model

Star transformation queries, 42, 69–73

bitmap join indexes, 70–73

cost, 69

dimension filtering, 67

illustrated, 70

multiple single column bitmaps, 73

occurrence, 69