Learning Databases Beyond SQL: My First Day with Oracle Tables

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 is me documenting 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:

You create it → insert rows → query it.

Oracle kind of blew that up right in front of me.

Apparently, how a table stores data physically matters a lot, and Oracle gives you several options.

Heap Tables

The Default one everyone's familiar with. Heap tables are what I’m already used to. This basically means:

• Rows go wherever there’s free space
• No guaranteed order
• General-purpose, most common type

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

Honestly, this feels like the “normal” table I’ve always used, just with a formal name now.

Index-Organized Tables

Another familiar one but now having a formal name to identify by. Where the heap table store rows randomly, the index-organized tables on the other hand have rows where everything is physically sorted by the primary key.

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

This is useful when:

• You always search by primary key
• You want fast lookups without jumping between table + index

External Tables

This one surprised me as they're tables That Aren’t Really tables. External tables let you query files as if they were tables like CSVs (Comma Separated Values).

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

Important thing I learned:

• The file lives on the database server
• You’re not importing data
• You’re reading it in place

Temporary Tables

For Session-Only Data. There are two types:

• Global Temporary Tables Everyone can see the table but only you can see the rows you insert.

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

What confused me at first for this type was that the table definition is permanent but the data is session-specific. So in the environment, if you query the list of user tables, it is displayed but it you terminate the session, the data goes with the session.

• Private Temporary Tables

This one feels almost secretive because even the table is private. Only your session can see the table while other sessions don’t even know it exists. Oracle doesn’t store it in the data dictionary

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

Partitioned Tables

Partitioning was the most “enterprise” concept today. The idea is simple:

Split one big table into smaller ones based on a key.

Oracle supports:

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

Example:

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

What clicked here was that queries can scan only the relevant partition and this matters a lot at scale.

Table Clusters

Storing Related Tables Together. This one felt advanced but fascinating. You can physically store rows from different tables together if they share a key.

create cluster toy_cluster (
  toy_name varchar2(100)
);

Then:

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

What I Took Away From Today's Session

Today wasn’t about writing complex queries. It was about learning and realizing core concepts.

Databases care deeply about how data is stored and tables are a design decision, not just syntax and SQL is only part of the story.

Some of this felt familiar and Some of it felt intimidating but All of it felt useful.

What’s Next

Continue the Oracle database for developers modules. Keep grounding concepts in why they exist. Slowly connect this to PostgreSQL and data engineering concepts.

I’m still very much a beginner, but days like this make the database world feel bigger and more interesting.

If you’re also learning databases and everything feels like “too much” sometimes, you’re not alone. I’m figuring it out one module at a time.