Data Types, Domains & Indexes

Lecture 5 · the built-in temporal types, how to define your own types and domains, how large objects are stored, and how CREATE INDEX speeds a query up.

Built-in date & time types

Type	Meaning	Example
`date`	Dates: a (4-digit) year, month and date	`date '2005-7-27'`
`time`	Time of day in hours, minutes and seconds	`time '09:00:30' · time '09:00:30.75'`
`timestamp`	Date plus time of day	`timestamp '2005-7-27 09:00:30.75'`
`interval`	A period of time	`interval '1' day`

Interval arithmetic. Subtracting a date/time/timestamp value from another gives an interval value; interval values can be added to date/time/timestamp values. That is how you compute “days of leave” or “hours worked”.

User-defined types

The create type construct creates a user-defined type.

create type

create type Dollars as numeric(12,2) final;

create table department (
    dept_name varchar(20) not null,
    building  varchar(15) not null,
    budget    Dollars          -- ← user-defined data type
);

Domains

The create domain construct (SQL-92) creates user-defined domain types. Types and domains are similar, but domains can carry constraints such as not null or a check.

create domain

-- Example 1
create domain person_name char(20) not null;

-- Example 2
create domain degree_level varchar(10)
    constraint degree_level_test
    check (value in ('Bachelors','Masters','Doctorate'));

Type vs domain in one line: a type defines the shape of the value; a domain defines the shape plus the rules it must satisfy. Declaring the rule once in a domain means every column using it inherits the same check.

Large-object types

Large objects — photos, videos, CAD files — are stored as a large object:

blob (binary large object) — a large collection of uninterpreted binary data, whose interpretation is left to an application outside the database system.
clob (character large object) — a large collection of character data.

When a query returns a large object, a pointer is returned rather than the large object itself — otherwise every SELECT * would drag megabytes through the network.

Index creation

An index is a data structure that speeds up SELECT queries by letting the database find rows faster. It works like the index of a book: instead of scanning every row (page), the database uses a separate, ordered structure to jump directly to the needed rows.

What an index stores

the indexed column value(s) (e.g. name);
a pointer to the row (row id / page+offset / primary-key reference).

So the DB finds matching pointers quickly, then fetches only those rows.

create index

create index studentID_index on Student(student_id);

select *
from student
where ID = 102;   -- executed via the index, without looking at all records

Student

student_id	name	dept_name	cgpa
101	Rahim	CSE	3.75
102	Karim	EEE	3.40
103	Joya	BBA	3.90
3003	Green	Finance	null

studentID_index

studentID_index
101
102
103
3003

① search the small ordered index → ② jump straight to the row.

When to create indexes

columns frequently used in WHERE;
columns used in JOIN conditions (foreign keys);
columns often used in ORDER BY / GROUP BY.

Avoid indexing

very small tables — a full scan is cheaper than the index lookup;
columns with very low selectivity (e.g. a boolean), unless combined with another column or used in a filtered index.

Indexing has a whole chapter of its own — metrics, ten index types, B-trees and B+ trees: Indexing & Hashing →