Keep Oracle tables rows/blocks sorted!

In Oracle, a table is an unordered set of rows, meaning there is no inherent ordering unless explicitly enforced. If you want a table to always remain sorted, consider the following options:

Alireza Kamrani
06/March/2025

How to Keep an Oracle Table Sorted?
Oracle tables do not maintain an inherent order, but you can enforce sorting efficiently using different techniques:

1. Use a BTREE INDEX to Retrieve Sorted Data Efficiently

A B-tree index stores values in sorted order, allowing Oracle to return sorted results efficiently without additional sorting.

CREATE INDEX sorted_idx ON my_table(created_at); --Asc or Desc.

Queries that use this index will read the data in sorted order:

SELECT * FROM my_table ORDER BY created_at; -- Uses the index for sorting

Pros:
🔻 No additional storage (beyond the index).
🔻 Fast sorted retrieval (Oracle reads directly from the index).
🔻 Avoids sorting overhead in execution plans.

Cons:
🔻 Does not physically sort table rows (but for queries with ORDER BY, this doesn’t matter).
🔻 Only effective if the query follows the index order (e.g., ORDER BY created_at ASC).


2. Use an INDEX ORGANIZED TABLE (IOT)

IOTs store data physically sorted by the primary key inside a B-tree structure.

CREATE TABLE sorted_table ( id NUMBER PRIMARY KEY, name VARCHAR2(100) ) ORGANIZATION INDEX;

Pros:
🔻 Data is physically stored in sorted order.
🔻 Eliminates the need for an extra index.

Cons:
🔻 Slower INSERT/UPDATE/DELETE compared to heap tables.
🔻 No support for LOBs (CLOB/BLOB).

3. Use a VIEW with an ORDER BY

A view ensures that queries always return sorted results.

CREATE VIEW sorted_view AS SELECT * FROM my_table ORDER BY created_at DESC;

Pros:
🔻 Simple to implement, no schema changes.
🔻 Ensures consistent sorting across queries.

Cons:
🔻 Sorting still happens at query time, not at storage level.
🔻 Performance depends on indexing.


4. Use a MATERIALIZED VIEW with REFRESH ON COMMIT

If sorted storage is required, a materialized view maintains persistent sorted data.

CREATE MATERIALIZED VIEW sorted_mv BUILD IMMEDIATE REFRESH ON COMMIT AS SELECT * FROM my_table ORDER BY created_at DESC;

Pros:
🔻 Data is physically stored sorted.
🔻 Improves performance for frequent queries.

Cons:
🔻 Extra storage required.
🔻 Overhead on INSERT/UPDATE/DELETE operations.

5. Use a TRIGGER to Maintain Order (Not Recommended for Large Tables)

A trigger can enforce sorting by modifying insert behavior.

CREATE OR REPLACE TRIGGER enforce_order BEFORE INSERT ON my_table FOR EACH ROW BEGIN SELECT COALESCE(MAX(id), 0) + 1 INTO :NEW.id FROM my_table; END; /
Pros:
🔻 Ensures sequential IDs.

Cons:
🔻 Performance overhead on inserts (causes row-by-row processing).
🔻 Not efficient for high-volume transactions.


6. Use CLUSTERING (Partitioning with ORDERED Data)

Oracle table clusters or partitions can optimize storage and retrieval for sorted data.

CREATE CLUSTER sorted_cluster (id NUMBER) SIZE 1024;
CREATE TABLE sorted_table ( id NUMBER PRIMARY KEY, name VARCHAR2(100) ) CLUSTER sorted_cluster (id);
Pros:
🔻 Faster range queries on sorted keys.

Cons:
🔻 Adds complexity to schema design.
🔻 Not always necessary if an index is sufficient.
How to Keep Rows of an Oracle Table Physically Contiguous (Avoid Fragmentation)?

Oracle heap tables do not guarantee physical row order, leading to fragmentation over time due to row migrations, splits, and updates. Here’s how to optimize storage and maintain physical row order effectively:

1. Use PCTFREE and PCTUSED to Control Free Space

Adjusting these settings reduces fragmentation by managing how blocks are filled.
ALTER TABLE my_table PCTFREE 5 PCTUSED 80;

• PCTFREE 5 → Leaves only 5% free space per block, reducing row migration.--adjust base on table characteristics(not frequently update)and your workload.

• PCTUSED 80 → Ensures blocks are reused efficiently.

🔻 Pros: Reduces row migration & wasted space.
🔻 Cons: Does not physically reorder existing rows.

2. Use ALTER TABLE ... MOVE to Defragment and Compact Data

Moving a table reorganizes data physically into contiguous blocks.

ALTER TABLE my_table MOVE;

• Must rebuild indexes after moving: ALTER INDEX my_index REBUILD;

🔻 Pros: Defragments and compacts rows.
🔻 Cons: Requires downtime (table is locked during move).

3. Use DBMS_REDEFINITION for Online Table Reorganization

The DBMS_REDEFINITION package allows online table reorganization without downtime.

BEGIN DBMS_REDEFINITION.START_REDEF_TABLE('SCHEMA_NAME', 'my_table', 'temp_table');
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('SCHEMA_NAME', 'my_table', 'temp_table', 1);
DBMS_REDEFINITION.FINISH_REDEF_TABLE('SCHEMA_NAME', 'my_table', 'temp_table'); END;
/

🔻 Pros: Online operation (no downtime), compacts table.
🔻 Cons: More complex to implement than ALTER TABLE MOVE.

4. Use EXPORT & IMPORT to Physically Reorder Data

Exporting and re-importing the table forces physical row reordering and defragmentation.

expdp user/pass tables=my_table directory=DATA_PUMP_DIR dumpfile=my_table.dmp

Then, truncate the table and re-import:

impdp user/pass tables=my_table directory=DATA_PUMP_DIR dumpfile=my_table.dmp

🔻 Pros: Ensures full defragmentation and optimized storage.
🔻 Cons: Requires downtime, may need additional space for dump files.

5. Use TABLE PARTITIONING to Keep Data Organized

Partitioning ensures rows are stored together logically, reducing fragmentation.

CREATE TABLE my_table ( id NUMBER PRIMARY KEY, data VARCHAR2(100), created_at DATE )
PARTITION BY RANGE (created_at) ( PARTITION p1 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD')),
PARTITION p2 VALUES LESS THAN (TO_DATE('2025-01-01', 'YYYY-MM-DD')) );

🔻 Pros: Keeps data logically and physically ordered.
🔻 Cons: Requires schema changes and partition maintenance.

6. Use an INDEX ORGANIZED TABLE (IOT) to Store Rows in Order

IOTs physically store rows in index order, reducing fragmentation.
CREATE TABLE my_table ( id NUMBER PRIMARY KEY, data VARCHAR2(100) ) ORGANIZATION INDEX;

🔻 Pros: Always stores rows in sorted order.
🔻 Cons: No support for LOBs (CLOB/BLOB), and UPDATE can be expensive.

7. Use HEAP TABLE COMPRESSION to Minimize Fragmentation

Table compression packs rows together and reduces fragmentation.

ALTER TABLE my_table COMPRESS FOR OLTP;

🔻 Pros: Saves space, improves cache efficiency.
🔻 Cons: Increases CPU usage for inserts and updates.


8. Use ASSM (Automatic Segment Space Management)

ASSM helps Oracle automatically manage space, reducing row migration.

ALTER DATABASE DATAFILE 'your_datafile.dbf' AUTOEXTEND ON;

🔻 Pros: Automatically handles free space allocation.
🔻 Cons: Less control over exact data placement