Learning Databases Beyond SQL: My First Day with Oracle Tables

Source: Dev.to

Well today didn’t go the way I expected. I planned to continue my usual PostgreSQL + SQL practice, but I ended up starting a free Oracle course for developers instead. I only completed the first module, but it introduced me to something I hadn’t really thought about before:

Tables are not just tables. Same word. Very different behaviors.

This post documents what I learned about table types in Oracle, what made sense immediately, and what feels advanced but interesting.

Tables Aren’t Always Just “A Place to Store Rows”

Before today, my mental model of a table was simple:

1. Create it →
2. Insert rows →
3. Query it.

Oracle showed me that how a table stores data physically matters a lot, and it gives you several options.

Heap Tables

The default, most familiar type. Rows go wherever there’s free space, with no guaranteed order.

create table toys_heap (
  toy_name varchar2(100)
) organization heap;

Index‑Organized Tables (IOT)

Rows are physically sorted by the primary key, providing fast lookups without jumping between a table and a separate index.

create table toys_iot (
  toy_id   integer primary key,
  toy_name varchar2(100)
) organization index;

Useful when you always search by primary key.

External Tables

Treat files (e.g., CSVs) as tables; you query them in place without importing data.

create table toys_ext (
  toy_name varchar2(100)
) organization external (
  default directory tmp
  location ('toys.csv')
);

• The file lives on the database server.
• You’re reading it directly, not loading it.

Temporary Tables

Global Temporary Tables

The table definition is permanent, but rows are session‑specific.

create global temporary table toys_gtt (
  toy_name varchar2(100)
);

All sessions see the table, but each session sees only its own rows. Data disappears when the session ends.

Private Temporary Tables

Both the table and its rows are private to the creating session and are not stored in the data dictionary.

create private temporary table ora$ptt_toys (
  toy_name varchar2(100)
);

Partitioned Tables

Split a large table into smaller pieces based on a key, improving query performance at scale.

Supported methods:

• Range (e.g., dates)
• List (specific values)
• Hash (even distribution)

Example (hash partitioning):

create table books_hash (
  toy_name varchar2(100)
) partition by hash (toy_name) partitions 4;

Queries can scan only the relevant partition.

Table Clusters

Store rows from different tables together physically when they share a key.

create cluster toy_cluster (
  toy_name varchar2(100)
);

create table toys_cluster_tab (
  toy_name varchar2(100)
) cluster toy_cluster (toy_name);

What I Took Away From Today’s Session

The focus wasn’t on writing complex queries but on understanding core storage concepts. Databases care deeply about how data is stored; table design is a crucial decision, not just syntax. Some ideas felt familiar, others intimidating, but all were useful.

What’s Next

I’ll continue the Oracle for developers modules, grounding each concept in its purpose and eventually connecting them to PostgreSQL and broader data‑engineering topics. I’m still a beginner, but days like this make the database world feel bigger and more interesting. If you’re also learning databases and sometimes feel it’s “too much,” you’re not alone—one module at a time.