Oracle SQL Extensions - Glossary

Access method

The path Oracle uses to retrieve data for a query. Examples include full table scans, index range scans, index unique scans, and other optimizer-chosen strategies. Choosing an efficient access method is a key part of SQL tuning.

Array

In Oracle, this typically refers to a VARRAY collection type: an ordered, bounded set of elements of a single datatype (for example, NUMBER or a user-defined object type). VARRAYs are stored as a single logical unit and have a predefined maximum size.

AI Vector Search

A feature introduced with Oracle Database 23ai that lets you store and index high-dimensional vector embeddings in database tables, and query them using similarity search (for example, cosine distance) directly in SQL. AI Vector Search is designed for generative AI, semantic search, and recommendation workloads, and integrates with traditional relational data in the same database.

Block

The smallest unit of I/O and storage allocation in an Oracle database. Each tablespace is subdivided into blocks (for example, 8 KB), and Oracle reads and writes data at the block level. Tables, indexes, and other segments are ultimately stored as one or more blocks.

Check constraint

A rule that enforces a Boolean condition on each row in a table. The condition must evaluate to TRUE (or UNKNOWN when using NULLs) for every row; otherwise, the INSERT or UPDATE is rejected.

Column constraint

A constraint defined at the column level in a CREATE TABLE or ALTER TABLE statement that applies to a single column (for example, NOT NULL, a column-level CHECK, or a single-column UNIQUE or REFERENCES clause).

Constraint

A database rule defined in the data dictionary that Oracle enforces automatically to maintain data integrity. Common constraint types include NOT NULL, CHECK, PRIMARY KEY, UNIQUE, and FOREIGN KEY.

Correlated sub-query

A subquery that references columns from its outer query. The subquery is re-evaluated for each row of the outer query, making it dependent (correlated) on the current row.

Data dictionary views

Oracle-provided views (for example, USER_, ALL_, and DBA_ views) that expose metadata such as tables, indexes, constraints, privileges, and users. These views describe the structure and configuration of the database itself.

Data Manipulation Language (DML)

SQL commands that modify data stored in tables: INSERT, UPDATE, DELETE, and MERGE. These statements change the contents of rows, as opposed to Data Definition Language (DDL) statements which change schema objects.

Datafile

A physical file on disk (or ASM storage) used by Oracle to store database data. Each tablespace is backed by one or more datafiles, and each datafile belongs to exactly one tablespace.

Dynamic list

A list of values derived from a subquery. Because the list is based on a query against live data, it automatically reflects current values stored in the database.

Foreign key

A column or set of columns in one table that references the primary key (or a unique key) of another table. A foreign key enforces referential integrity between parent and child rows.

Glossary

A reference section that defines key Oracle SQL and database terms used throughout this course, helping you interpret examples and official documentation.

Group function

A predefined function that operates on groups of rows and returns a single result, such as SUM, AVG, MIN, MAX, or COUNT. In Oracle, these functions can be used as aggregate functions and, in many cases, as analytic functions.

Hierarchical query

A query that returns parent–child relationships, typically using CONNECT BY and START WITH, or a recursive subquery factoring clause (WITH ... CONNECT BY/recursive CTE) to navigate hierarchical data such as org charts or bill-of-materials trees.

Inner join

A join that returns only those rows where the join condition between two (or more) tables evaluates to TRUE in both tables. Rows without a matching partner in the other table(s) are excluded from the result.

Instance

An Oracle database instance is the combination of memory structures (such as the System Global Area) and background processes that manage access to the physical database files. An instance can mount and open one database; in multitenant environments it may serve multiple pluggable databases.

JDBC

Java Database Connectivity. A standard Java API that defines how Java applications connect to and interact with relational databases using JDBC drivers, including the Oracle JDBC driver for Oracle Database.

JSON Relational Duality

A capability introduced in Oracle Database 23c that lets the same data be accessed and managed both as relational tables and as JSON documents, without duplication. JSON Relational Duality Views present relational data as JSON while preserving ACID properties, indexing, and SQL-based access.

Left outer join

An outer join where all rows from the left table are returned, and matching rows from the right table are included when available. Rows from the left table that have no match in the right table appear with NULLs for the right-table columns.

Meta-data

Data about data structures in the database. Examples include table names, column datatypes, index definitions, constraints, and tablespace assignments. In Oracle, metadata is primarily exposed through the data dictionary views.

Nested table

A collection type in Oracle that stores an unbounded set of elements of a single datatype (for example, NUMBER or an object type). Unlike VARRAYs, nested tables can conceptually grow without a fixed upper limit and are stored as separate internal tables.

Object table

A table whose rows are instances of a user-defined object type. Each row represents a single object, with attributes defined by the object type. This contrasts with a purely relational table, where each row is defined as a set of scalar columns.

Object type

A user-defined datatype that encapsulates both attributes (data) and methods (behavior). Object types support object-relational modeling within Oracle Database.

Object-oriented design

A design approach that models data as objects with associated attributes and methods. In Oracle, object-relational features such as object types, object tables, and references support object-oriented design patterns at the database level.

ODBC

Open Database Connectivity. A standard API that enables applications to access relational databases using a common interface and ODBC drivers, regardless of the underlying vendor.

Outer join

A join that returns all matching rows plus non-matching rows from one or both tables, filling in NULLs for missing columns. Common variants are LEFT OUTER JOIN, RIGHT OUTER JOIN, and FULL OUTER JOIN.

Primary key

One or more columns that uniquely identify each row in a table and cannot contain NULL values. A table can have only one primary key constraint, although it may consist of multiple columns.

Pseudocolumn

A virtual column provided by Oracle that behaves like a real column but is not stored in a table. Examples include ROWID, ROWNUM, SYSDATE, and USER. Pseudocolumns are often used for pagination, auditing, and system information.

Reference

In the object-relational model, a REF value that points to an instance of an object type in an object table. It plays a similar role to a foreign key, but within the object-relational framework.

Right outer join

An outer join where all rows from the right table are returned, and matching rows from the left table are included when available. Rows from the right table that have no match in the left table appear with NULLs for the left-table columns.

Schema

The collection of database objects (tables, views, indexes, procedures, and so on) owned by a single database user. By convention, the schema name is the same as the owning user name (for example, the SCOTT schema).

Table constraint

A constraint defined at the table level that can involve one or more columns, such as a multi-column PRIMARY KEY, UNIQUE, or CHECK constraint.

Tablespace

A logical storage container within an Oracle database that groups related segments (tables, indexes, and other objects). Each tablespace maps to one or more physical datafiles. A non-partitioned table resides in a single tablespace; partitioned tables may span multiple tablespaces.

Unique key

A constraint that enforces uniqueness of a column or combination of columns, allowing at most one row with a given set of values. Unlike a primary key, a unique key can generally contain NULL values, and a table may have multiple unique key constraints.

Wildcard

A symbol used for pattern matching with the LIKE operator. In Oracle, % matches any sequence of characters (including an empty sequence), and _ matches exactly one character.