Index_S

---

S

Sample databases, xvi

Sample scripts:

constraints, 721

count rows, 721

EXPLAIN PLAN, 719–20

indexes, 721–22

space in the database, 722–23

statistics generation, 720–21

trace file scripts, 723–25

Shared Global Area, 635, 638

Sorting in SQL, 301–5

Spotlight on Oracle, 634–36, 637, 638

SQL (structured query language), 97–199, 237–326, 365 see also Hardware resource usage tuning; Indexing; Referential integrity; Tuning the data model

Access Advisor, 382

accessing data in the database see Data access methods

code,

definition, xiii–xiv, 99–100

tuning, xiv, 8–9, 14–16

comparison conditions, 102–4, 154–61

equi, anti, and range, 155–56

groups, 161

like pattern matching, 156–57

set membership, 157–60

concatenation, 305–8

DELETE replaced by TRUNCATE, 195

functions, 113–14, 143–52

COUNT function, 143–45

datatype conversions, 146–49

DECODE function, 145–46

in the GROUP BY clause, 151–52

in the ORDER BY clause, 150–51

in the SELECT statement, 149–50

in the WHERE clause, 150

INSERT statement, 114–15

INTERSECT operator, 313

joins, 161–72

anti-joins, 171–72

cartesian products, 170

efficient joins, 165–66

equi joins and range joins, 168–69

inefficient joins, 169–70

intersections, 166–68

join formats, 161–65

mutable and complex joins, 172

outer joins, 170–71

self joins, 168

tuning, 172

IN list operator, 309–10

MERGE statement, 115–17

MINUS operator, 312–13

object and relational conflicts, 195–97

large binary objects, 195–96

object-relational collections, 196–97

optimization see Optimization

parallel queries, 119–20

parser, 237–39

PL/SQL, use of, 188–91

RETURNING INTO clause, 191

use of JAVA, 193–95

when to use with cursors, 192–93

pseudocolumns, 113, 152–54

ROWID pointers, 153

ROWNUM, 154

sequences, 152–54

SELECT statement, 100–114, 122–43

comparison conditions, 102–4

count of rows, 126

DISTINCT, use of, 139

DUAL table, 112

GROUP BY clause for sorting, 138–39

grouping result sets, 137–38

HAVING clause, 139–41

joining tables, 104–7

logical operators, 102

NULLS, 112–13

ORDER BY clause for sorting, 133–35

overriding WHERE with ORDER BY, 135–137

ROLLUP, CUBE, and GROUPING SETS, 141–42

FOR UPDATE clause, 142

WHERE clause for filtering, 126–33

set operators, 110–11

sorting, 301–5

aggregate sort, 305

GROUP BY sort, 304

ORDER BY sort, 303

sort merge join sort, 304–5

types of sort, 301–2

unique sort, 302–3

subqueries, 107–9, 172–82

FROM clause subquery nesting, 178–82

correlated versus noncorrelated, 173

IN versus EXISTS, 173

nested, 173–74

remove tables using EXISTS, 175–78

replacing joins with subqueries, 174–75

synonyms, use of, 182

temporary tables, 188

Trace, 336–40

finding trace files, 338

session level tracing, 338

setting up, 337–38

using, 339–40

transaction control, 117–19

COMMIT versus ROLLBACK, 117–18

UNION operator, 310–12

views, use of, 182–88

SQL*Loader, 639–41

SQL*Net:

buffer unit, 534

tuning, 527

STATSPACK:

and bottlenecks, 642–43

example report, 669–93

snapshots, 643–44

uses, 642–45

Structured query language see SQL

Subqueries in SQL, 107–9, 172–82

---