Access methods See also Index scan access methods

    full scan access methods

        full scan operations

        high-water mark

        multiblock reads

        throwaway

Active Session History (ASH)

Active transactions

    atomicity

    SCN number

ACYCLIC algorithm

ACYCLIC FAST algorithm

Adaptive cursor sharing

Advanced grouping. See GROUPBY clause

Aggregate functions

    analytic mode/nonanalytic mode

    default window specification

    granular window specifications

    MAX function

    sum(sale) function

Aggregating statistics

Analytic functions

    aggregate functions

        analytic mode/nonanalytic mode

        default window specification

        granular window specifications

        MAX function

        sum(sale) function

    anatomy of

        order-by-clause

        partition-by-clause

        windowing-clause

    conventional SQL statements

    denormalized fact table

    dense_rank function

        FIRST|LAST KEEP function

        vs. rank function

        syntax

    dynamic SQL statement

    execution plans

    first value function

    lag functions

    last_value function

    lead functions

    listagg function

    nesting analytic functions

    nth_value function

        definition

        syntax

    NTILE function

    organizational behavior

    overview

    parallelism

    percentile_cont function

        calculation

        median value

        syntax

    percentile_disc function

    percent_rank function

    PGA size

    predicates

    rank function

        calculation

        definition

        syntax

    ratio-report function

    row_number function

    stddev function

    types

    windows

ANSI isolation levels

ANSI/ISO SQL standard

Antijoins

    alternative syntax

    hints

    instance level

    nested loop antijoin

    NOT IN and NOT EXISTS queries

        LEFT OUTER statement

        MINUS operator

        NOT NULL constraint

        with NVL function

    null aware(NA)

    requirements

    restrictions

    venn diagram

Application characteristics

    compressed indexes

        benefit

        creation

        index/validate structure statement

        syntax

    descending indexes

    Function-Based Indexes

    Reverse key indexes

        creation

        issues

Autonomous transactions

    calling transaction

    order logging transaction

    use of

AUTOTRACE command

AWR and Statspack

Bind variable peeking

Bitmap Indexes

    BITMAP AND operation

    DML

Bitmap join indexes

B-tree indexes

    structure

    tree traversal algorithms

Buffer cache

    blocks

    latches

    logical and physical read

    soft and hard parse

    touch count

BUFFER SORT operation

Building logical expressions

    AND condition

    bind variables

    CASE statements

    CONCATENATION

    conditional logic

    conditional WHERE clause

    UNION ALL

    WHERE clause

Cardinality feedback. See Statistics feedback

Cartesian joins

Child operations

Clustering factor

COALESCE function

Compressed indexes

    benefit

    creation

    index/validate structure statement

    syntax

Conditional insert

Conditional logic constructs

    CASE statement

        repeat table acces elimination

        searched CASE statement

        simple CASE statement

    COALESCE function

    DECODE construct

    DECODE vs. CASE statement

    NULLIF function

    NVL and NVL2 functions

CONNECT BY clause

    CONNECT_BY_ROOT operator

    PRIOR operator

    pseudocolumn level creation

    START WITH clause

    SYS_CONNECT_BY_PATH Function

    types

Connect_by_iscycle Pseudocolumn and NOCYCLE parameter

    cycle error

    detection

    recursive subfactored query

Connect_by_isleaf pseudocolumn

    BREADTH FIRST searching

    LEAD() function

    leaf nodes finding

Cost Based Optimizer (CBO)

COUNT aggregate function

Create Table As Select (CTAS)

CUBE extension

    operation

    replace UNION ALL with CUBE

    rows returns

    SALES_HISTORY schema

    UNION ALL

CUBE operation

CYCLIC algorithm

Database

Database interface

Data definition language (DDL)

Data Manipulation Language (DML)

    DELETE

    INSERT (see also DML error logging)

        conditional processing

        direct path inserts

        multitable insert

    MERGE

        performance comparison

        syntax and usage

    truncate command

    UPDATE

        Create Table As Select

        INSERT APPEND technique

Data Warehouse Query

DBMS_METADATA package

dbms_xplan.display_cursor function

DBMS_XPLAN package

DELETE statement

Descending indexes

Dictionary cache

Direct path inserts

DISPLAY function

DISTINCT clause

DML error logging

    APPEND hint

    better insert error logging

    commit or rollback

    CREATE_ERROR_LOG procedure

    creation

    DBMS_ERRLOG.CREATE_ERROR_LOG parameters

    LOG ERRORS clause

    LOG ERRORS INTO

    SKIP_UNSUPPORTED parameter

Dynamic WHERE Clause

Embedded SQL statements

Environmental values change

Execution plans See also SQL monitor reports

    association and view

    collecting plan operations

    cost of

    dbms_xplan.display_cursor

    DBMS_XPLAN package

    definition of

    executing and fetching rows

    EXPLAIN PLAN statement

        access and filter predicates

        ACCESS_PREDICATES column

        bind variable datatypes

        breaking plan

        FILTER_PREDICATES column

        iterative operations

        narrative

        parent-child relationships

        pass-thru operations

        PLAN_TABLE

        producing plan output

        reading

        row-source execution statistics

        TRACEONLY EXPLAIN option

        use of

        working operations

    later plan retrieve

    optimizer

    predicates

    problem solving

        missing/suboptimal index

        stale statistics

        TABLE ACCESS FULL operation

        wrap-up

    selectivities

    SET SERVEROUTPUT OFF

    statistics

    using, for problem solving

    viewing recently generated SQL

EXTRACT() function

Fast full scan

For loop

FORMAT parameter

FROM clause

Full scan

Full scan access methods

    full scan operations

        COUNT aggregate function

        query

        random vs. sequentially stored row values

        TABLE ACCESS FULL operation

        test table

    high-water mark

        blocks

        example

        normal operations

    multiblock reads

    throwaway

Global indexes

GROUP BY and ORDER BY clauses

GROUP BY clause See also CUBE extension

    advanced functionality

    basic usage

    column requirements

    convoluted SQL

    COUNT() function

    execution plan

    GROUPING() function

        DECODE() function

        HAVING clause

        NULL values

    GROUPING_ID()

    HAVING clause

    ORDER BY clause

    restrictions

    ROLLUP() extension

    SETS() extension

GROUPING() function

    DECODE() function

    HAVING clause

    NULL values

GROUPING_ID()

GROUPING SETS() extension

Hard parse

Hash joins

Hash partitioning scheme

    Algorithm

    creation

    dbms_rowid.rowid_object

    RAC

    right-hand growth index

HAVING clause

High-water mark

    blocks

    example

    normal operations

Hint-based mechanisms

    Outlines/Stored Outlines

        CREATE_OUTLINE Procedure

        DBMS_OUTLN.CREATE_OUTLINE

        OL sqlid planhash

        USE_STORED_OUTLINES

    SQL Patches

    SQL Plan Baselines

        12c, plan evolution

        create_baseline_awr.sql script

        create_baseline.sql script

        dba_sql_plan_baselines

        evolvation

        fixed set

    SQL Profiles

        DBMS_SQLTUNE.IMPORT_SQL_PROFILE

        FORCE_MATCHING

        sql_profiles.sql script

        SQLTUNE_CATEGORY

        SQL Tuning Advisor (STA)

        STA

        USE_STORED_OUTLINE parameter

        Wrap-up

    SQL Profiles creation

        create_sql_profile_awr.sql Script

        create_sql_profile.sql

        create_sql_profile.sql script

        DBMS_XPLAN.DISPLAY_CURSOR

        FORCE_MATCHING

        move_sql_profile.sql script

        Wrap-up

Identical statement

    bind variables

    different executions

    latches

    serialization devices

    spinning

IF statement

IGNORE NAV clause

Indexes See also Application characteristics

    access path

    Bitmap Indexes

        BITMAP AND operation

        DML

    B-tree indexes

        structure

        tree traversal algorithms

    buffer cache

    columns

    global indexes

    hash partitioning scheme

        Algorithm

        creation

        dbms_rowid.rowid_object

        RAC

        right-hand growth index

    index-organized tables (IOTs)

        primary key

        properties

        secondary indexes

        unique indexes

    Local partitioned indexes

        partition elimination

        table partition

    management problems

        Bitmap join indexes

        invisible indexes

        virtual indexes

    NULL handling

    parallelism

    range partitioning scheme

    uses

Index-organized tables (IOTs)

    primary key

    properties

    secondary indexes

    unique indexes

Index scan access methods

    block accesses

    B-tree index

    column value and rowid

    fast full scan

    full scan

    index structure

        blevel

        B-tree indexes

        leaf blocks

        logical view

        RBA

        root block

    index unique scan

    range scan

    rowid

    scan types

        clustering factor

        computing clustering_factor

        different table blocks

        random vs. sequentially loaded row values

        TABLE_CACHE_BLOCKS parameter

    single-block reads

    skip scan

Index unique scan

Inner join

INSERT APPEND technique

INSERT statement See also DML error logging

    APPEND hint

    compared with MERGE

    conditional processing

    direct path inserts

    multitable insert

    single-table insert

Instance

Instrumentation Library for Oracle (ILO)

    advantages

    AWR or StatsPack data

    consistent labeling

    CPU

    database aplication instalation

    data entry

    elapsed time

    ILO_MODULE and ILO_ACTION labels

    in single session

    repeating/simultaneous executions

    requirements

    resource usage

    SQL tracing

    task sequences

    test harness and measurement tool

    troubleshooting problems

        AWR data

        sequential listing processes

        timeout errors

    unbounded data ranges

    ZIP file

INTERSECT

Isolating transactions

Isolation levels

Iteration

    PRESENTV function

        vs. PRESENTNNV function

        syntax

    syntax

Join elimination

    limitations

    outer join guarantees

    primary key–foreign key constraint

Join methods

    cartesian join

    diagram

    driven-to table

    driving table

    equi-joins

    FROM and WHERE clause

    hash joins

    inner table

    nested loops joins

    order

    outer joins

        ANSI join syntax

        full outer joins

        oracle–equivalent syntax

        sample query

    PRIMARY KEY constraint

    sort-merge joins

    UNIQUE constraint

KEEP NAV clause

Lag function

LEAD() function

Least Recently Used (LRU) algorithm

Library cache

LIKE operator

listagg function

LNNVL function

Materialized view

MERGE statement

    compared with INSERT and CTAS

    performance comparison

    syntax and usage

MINUS

Model clause See also Iteration

    aggregate functions

    execution plans

        ACYCLIC algorithm

        ACYCLIC FAST algorithm

        CYCLIC algorithm

        sequential order

    for loop

    Indexes

    inter-row reference

        array elements

        denormalized fact table

        inventory formula calculation

        rules and formula

        SQL statement

    lookup tables

        definition

        main model section

        reference clause

        using Iso_code rule

    material views

    NULLs

        IGNORE NAV clause

        KEEP NAV clause

    parallelism

    partition pruning

    positional notation

    predicate pushing

    RETURN UPDATED ROWS clause

    row evaluation order

        cell level

        sequential order

        using DESC keyword

    rule evaluation order

        automatic order

        sequential order

    spreadsheets

    subquery factor

        in SQL Access

        WITH clause

    symbolic notation

Multiblock reads

Multi-table inserts

Multi-version read consistency model

Nested loop antijoin

Nested loop semijoin

Nested loops joins

Nesting analytic functions

NO_MERGE hint

NO_QUERY_TRANSFORMATION

NOT NULL constraint

nth_value function

    definition

    syntax

NTILE function

Null(s)

    aggregate functions

    comparisons and expressions

    GROUP BY and ORDER BY clauses

    set operations

    sets and

    two-value logic

    unintuitive results

    value

NULL handling

NULLIF function

NVL() function

Object-level statistics

Online analytic processing (OLAP) queries

Oracle architecture

Oracle Call Interface (OCI)

Oracle Concepts Guide

Oracle cost-based optimizer (CBO)

Oracle database

ORDER BY clause

ORDER BY elimination

Order Entry (OE) schema

    confirmation queries

    inventory

    order transaction

    sales manager

    schema changes

    tables

Outer joins

    ANSI join syntax

    full outer joins

    Oracle–equivalent syntax

    sample query

Parallelism

Partition-by-clause

percentile_cont function

    calculation

    median value

    syntax

percentile_disc function

percent_rank function

PGA_AGGREGATE_LIMIT (PGAL)

PGA_AGGREGATE_TARGET (PGAT)

PIVOT function

    clauses

    execution plan

    GROUP BY clause

    vs. Old pivot query formulation

    pivot_in_clause

    syntax

    using aliases

    using XML

    WHERE clause

PIVOT operator

Plan control

    Hints, optimizer

        Explicit named blocks

        Query Block Naming

        Query Transformation Turned Off

        Trace File Excerpts

        valid_hints.sql.Script

    literals

    Modified Query Structure

    problem solving

    without code access

        Add or Remove access paths

        changing statistics

        Hint-based mechanisms (see Hint-based mechanisms)

        modifying databases

Plan instability end

Plan stability and control

    identification

        aggregating statistics

        diagnostic scripts queries

        history of a statement’s performance

        statistical variance

        variations around a point in time

    plan instability

        adaptive cursor sharing

        bind variable peeking

        CBO inputs

        environmental values change

        no_invalidate parameter

        object-level statistics

        OLTP-type environment

        rolling invalidation

        SQL statement

    statistics feedback

        execution plan

        SQL plan directive

Positional notation

Predicate pushing

Procedural vs. set-based approach

    EMP and DEPT sets

    logical reads

    process flow diagram vs. nested set diagram

    self-join

    thinking approach

Program (process) global area (PGA)

Quality assurance. See Testing and quality assurance

Query block

Query transformation See also SQL execution

    join elimination

        limitations

        outer join guarantees

        primary key–foreign key constraint

    materialized view

    NO_MERGE hint

    NO_PUSH_PRED hint

    ORDER BY elimination

    predicate pushing

    query blocks

    REWRITE hint

    rownum pseudocolumn

    SELECT keyword

    statement

    subquery unnesting

        correlated subquery

        FILTER operation

        HASH JOIN join

        NO_UNNEST hint

        subquery caching

        uncorrelated subquery

        views and subqueries

    view merging

Questions

    building logical expressions

        AND condition

        bind variables

        CASE statements

        CONCATENATION

        conditional logic

        conditional WHERE clause

        UNION ALL

        WHERE clause

    categorizations

        common types

        conversation

        differences

    data

    intellectual habit and habits

    purpose

    question

Range scan

Rank function

    calculation

    definition

    syntax

Ratio-report function

Read committed isolation level

Read uncommitted isolation level

Real Application Clusters(RAC)

Recursive subquery factoring (RSF)

    BREADTH FIRST searching

    CONNECT BY clause (see also Connect_by_iscycle Pseudocolumn and NOCYCLE parameter; Connect_by_isleaf pseudocolumn)

        CONNECT_BY_ROOT operator

        PRIOR operator

        pseudocolumn level creation

        START WITH clause

        SYS_CONNECT_BY_PATH Function

        types

    employee table

    restrictions

    UNION ALL operator

Regression testing

Relational database management systems (RDBMS)

Relative Block Address (RBA)

Repeatable read isolation level

Rolling invalidation

ROLLUP() extension

ROLLUP operation

row_number function

Savepoints

    data returning

    verification

Scalar subqueries

Searched CASE statement

SELECT. See Data Manipulation Language (DML)

SELECT statement

    CBO

    female customers

    FROM clause

    GROUP BY clause

    HAVING clause

    ORDER BY clause

    query processing

    SELECT list

    WHERE clause

Semijoins

    ANY keyword

    correlated vs. noncorrelated subquery

    definition

    EXISTS syntax

    hints

        FILTER operation

        NO_SEMIJOIN hint

    IF statement

    inner join

    inner join with DISTINCT

    instance level

        nested loops semijoin

        restriction

        valid values parameter

    nested loop semijoin

    requirements

    ugly INTERSECT

    using EXISTS query

    using IN query

    venn diagram

Serializable isolation level

Serializable transactions

Set(s) See also Set operations

    nulls

        aggregate functions

        comparisons and expressions

        GROUP BY and ORDER BY clauses

        set operations

        two-value logic

        unintuitive results

        value

    procedural vs. set-based approach

        EMP and DEPT sets

        logical reads

        process flow diagram vs. nested set diagram

        self-join

        thinking approach

    thinking in sets

SET clause

SET game

Set operations

    INTERSECT operator

    MINUS operator

    queries

    SELECT statements

    UNION ALL operator

    UNION and UNION ALL

        examples

        HASH UNIQUE operation

        ORDER BY clause

        venn diagram

    UNION operator

Set transaction command

Simple CASE statement

Single child operations

Single-table inserts

Skip scan

Sliding windowing-clause

Soft parse

Sort–merge joins

SQL

    database interface

    DELETE statement

    INSERT statement

        multitable insert

        single-table insert

    language

    MERGE statement

    preprocessor

    SELECT statement

        CBO

        female customers

        FROM clause

        GROUP BY clause

        HAVING clause

        ORDER BY clause

        query processing

        SELECT list

        WHERE clause

    SQL*Plus

        command execution

        configuration

        database connection

        introduction

        login.sql file

        set command

        slash (/)

    statements

    UPDATE statement

SQL Constructs See also Conditional logic constructs

    PIVOT function

        clauses

        execution plan

        GROUP BY clause

        vs. Old pivot query formulation

        pivot_in_clause

        syntax

        using aliases

        using XML

        WHERE clause

    test data generation

        MODEL clause

        using big table

        using CONNECT BY clause

        WITH clause

    UNPIVOT function

        clauses

        execution plan

        syntax

        table creation

        unpivot_for_clause

        unpivot_in_clause

        using aliases

        VIEW operation

SQL execution

    buffer cache

        blocks

        latches

        logical and physical read

        soft and hard parse

        touch count

    executing and fetching rows

        arraysize

        FETCH calls

        SELECT statements

    execution plan

        cost of

        definition of

        optimizer

        predicates

        selectivities

        statistics

    identical statement

        bind variables

        different executions

        hard parsing

        latches

        serialization devices

        spinning

    library cache

    Oracle architure

    overview

    shared pool and

SQL monitor reports

    dbms_sqltune.report_sql_monitor

    display

    HTML

    MONITOR and NO_MONITOR

SQL optimization

    cost calculation

    FROM clause

    modified income search

        INLINE hint

        MATERIALIZING

    using WITH clause

    WITH PL/SQL function

        advantages

        and INLINE hint

        MATERIALIZING

SQL Plan Management (SPM)

SQL TUNING ADVISOR

Stale statistics

Statistical Variance

Statistics feedback

    execution plan

    SQL plan directive

stddev function

Subquery factoring See also SQL optimization

    RSF (see Recursive subquery factoring (RSF))

    with cross table

    without cross table

    WITH PL/SQL function

        customer report generation

        EXTRACT() function

        features

        temporary tables

Subquery unnesting

    correlated subquery

    FILTER operation

    HASH JOIN join

    NO_UNNEST hint

    subquery caching

    uncorrelated subquery

    views and subqueries

Symbolic notation

System change number (SCN)

System global area (SGA)

    buffer cache

        blocks

        latches

        logical and physical read

        soft and hard parse

        touch count

    shared pool

Task sequences

Test-driven development (TDD)

Testing and quality assurance

    data model

    execution plan

    ILO (see Instrumentation Library for Oracle (ILO))

    regression testing

    schema changes

        and new product data

        historical data

        object recompilation

    TDD

    test cases

    unit tests

        and scripts

        invalid objects

        lists

        verification task

Throwaway

Transaction processing

    ACID compliance

    ACID properties

    active transactions

        atomicity

        SCN number

    atomicity

    autonomous

        calling transaction

        order logging transaction

        use of

    constistency

    control statements

        commit

        rollback

        savepoint

        set constraints

        set transaction

    definition

    dirty read

    durability

    grouping operations into

    isolation

    isolation levels

    multi-version read consistency

    non-repeatable read

    Order Entrey (OE) schema

        confirmation queries

        inventory

        order transaction

        sales manager

        schema changes

        tables

    phantom read

    redo logs

    savepoints

        data returning

        verification

    serializing

    undo blocks

TRUNCATE command

Unbounded data ranges

Unintuitive results

UNION ALL operator

UNION and UNION ALL

    examples

    HASH UNIQUE operation

    ORDER BY clause

    venn diagram

Unit tests

    invalid objects

    lists

    and scripts

    verification task

UNKNOWN value

UNPIVOT function

    clauses

    execution plan

    syntax

    table creation

    unpivot_for_clause

    unpivot_in_clause

    using aliases

    VIEW operation

UPDATE statement

    Create Table As Select

    INSERT APPEND technique

Venn diagram

VIEW keyword

View merging

WHERE clause

Windowing functions

WITH clause

WITH PL/SQL function