NOTE: Create table is a form of data-definition language (DDL) statement. These change the objects in your database. Oracle Database runs a commit before and after DDL. So if the create works, it's saved to your database.

You can also create a table based on a select statement. This makes a table with the same columns and rows as the source query. This operation is known as create-table-as-select (CTAS).

This is a handy way to copy one table to another. For example, the following creates toys_clone from toys:

create table toys_clone as
select * from toys;

Easy, right?

Yes. But, as always, there's more to it than this. You'll want to add some constraints to your table. And there are many types of table available in Oracle Database, including:

Heaps are good general purpose tables. They are the most common type you'll see in Oracle Database installations.

With these, the database is free to store new rows wherever there is space. So if you read ten rows, they could be anywhere on disk.

If you're lucky, they're all in the same place. So the query can get them all in one trip to disk.

But it's not guaranteed. Each row could be in a different location. Meaning you need ten I/O operations to read them all.

This is bad news if you want the query to be as fast as possible. The more disk reads your SQL does, the slower it will be. Even if the rows are cached in memory, accessing ten memory addresses is slower than hitting one.

Luckily you can force the database to store rows with similar values in the same place. This can reduce work your query does to get them. Making your SQL faster!

To force this physical order, you need to change your table's properties. The first we'll look at is an index-organized table.

Index-Organized Tables (IOTs)

Indexes are ordered data structures. So an IOT stores rows physically sorted according to its primary key.

NOTE: A primary key (PK) is a constraint. Each set of values in its columns can only appear once. So you can't have duplicates. It also has a not null constraint. And creates a unique index in the background.

To create one, add the organization index clause to the table definition:

This stores the values sorted by customer_id, then address_id. So all the addresses for customer 1 are next to each other on disk.

So if you search for all the addresses for this customer, like so:

select *
from customers
where customer_id = 1;

You know all the rows will be in the same few locations. Making your SQL that tiny bit faster.

Whereas with a heap table, the database could store them anywhere.

You can extend this principle to any table with a composite PK. And you (almost always) search for rows where the first column of the PK equals some value.

This is common for tables in a master-detail relationship. For example:

Orders and order items

Invoices and invoice lines

Flight itineraries and their flights

Here you usually get the rows from the detail table matching a row in the parent. Such as all products in an order.

Remember, to use an IOT the table must have a primary key. So the values you want to sort by must be unique. If they're not, you can get around this by creating a fake primary key. But this is a niche technique. Only use if you're sure what you're doing!

You can also use partitioning or table clusters to impose order on your data. But let's cover off the organization clause first.

External Tables

The final option for the organization clause is external. You use this to read text files stored on the database's file system. This enables you to read CSV or other formatted files into your database using SQL.

To create one you must have a directory object in place. This points to the folder where the file is:

create or replace directory ext_files as '/path/to/files';

To read the file toys.csv in /path/to/files, use this directory and define the file like so:

NOTE: the cryptic ora$ptt_ prefix for the table name. This must match whatever your database's private_temp_table_prefix parameter is set to. Otherwise it won't work! Also, unlike all other forms of DDL, create private temporary table does NOT commit!

By default, these only last for the duration of a transaction. As soon as you commit (or rollback) the table is gone.

If you need it to persist for the length of your connection, set the on commit clause to preserve definition:

So far we've dealt with making queries against one table faster. But what if you want to get rows from two tables at the same time?

Consider:

Table Clusters

Joining tables is one of the most common operations in a database. To do this, first the database reads rows from one table. Then finds rows matching the join criteria in the other.

So you need at least two lots of reads get the data. One for each table. In practice joins can do many more.

Table clusters avoid this "join penalty" by storing rows from different tables in the same place. Rows with the same value for the cluster key from each table go in the same location. This means you get "two-for-the-price-of-one" access. Instead of an I/O operation per table you query, you can get all the rows in one shot.

Say you have a lookup table for colours. And you often join the toy table to this on the colour. Clustering these tables by colour ensures the database stores rows with the same colour in the same place.

To use table clusters, first you need to create the cluster. This can be a hash or index cluster. The following creates a hash cluster:

create cluster colour_clus (
colour varchar2(10)
) hashkeys 1024;

The cluster's columns are its key. Place the tables in this by adding the cluster clause to your create table. Here you state the cluster columns. These must have the same data type as in the cluster.

The following adds colours_clustered and toys_clustered to the colour_clus:

Now, if you insert a row with the colour red in both colours_clustered and toys_clustered, the database will stick them in the same place.

Table clusters are an advanced feature. While they can make joins faster, they come with several caveats. Read up on them before diving in!

So Which Type Should I Create?!

Phew!

That was a lot of options! And there are few other, more specialized types available too. For a full list of options, read up on tables and table clusters in the Concepts Guide. Or view the full create table syntax.

So now you may be wondering: which type should I use?

In most cases, a heap table is the way to go. These are the most versatile.

But think about how you'll access the rows in the table. Will you have one or two queries that need to be fast as possible? If so, an index-organized or partitioned table may be the way to go.

Or will it need to support many queries on different columns? In which case a default heap table is the better option.

And bear in mind you can combine some options. For example, you can have a partitioned IOT. Knowing which to use will come from experience. But more importantly testing!

So get familiar with the table types available. Play around with them to see how they work. When building new functionality, try different table settings. And test to see how they perform.

But making new tables is a small part of database development. Often you'll need to change existing tables. It's time to find out how to:

How to Alter Tables

So you've created a shiny new table. But the chances are most of your development will be against existing tables. Tables you'll need to extend to store new information. So you need to add extra columns.

How to Add Columns

To add columns to your table, you need an alter table statement. This lists the table you're changing, with the names and data types of the columns you want to add. So to add a price column to toys, run:

alter table toys add ( price number );

If you want to add more than one column, you can list them out in the add clause:

So that's easy enough. But what about columns you no longer need? How do you get rid of them?

How to Drop Columns

Every now and then you may want to remove a column from a table. Maybe the business has realised each toy has many colours. So there's no point storing this on the toys table. Thus you want remove this column.

You can do so with an alter table drop command:

alter table toys drop ( weight );

But beware!

This is an expensive operation. On tables storing millions of rows it will take a long time to run. And it blocks other changes to the table. So your application may be unusable while this runs!

A better option is to set the column unused:

alter table toys set unused column weight;

This is an instant operation. No matter how many rows are in the table, it will take the same amount of time.

This is because it doesn't physically remove the columns from the database. It marks them as unavailable. The data still exists. You just can't get to it!

If you're hoping to reclaim the space these columns used, you need to wipe them from the table. Do this with the following command:

alter table toys drop ununsed columns;

As you're now doing the work of deleting the data, this can take a long time. The key difference here is dropping unused columns is non-blocking. Your application can continue to run as normal.

Whichever method you use, take care when removing columns. Dropping columns or setting them unused are both one-way operations. There is no "undrop column" command. If you made a mistake, you'll have to recover the table from a backup!

So that helps if you're removing some of the columns. But what if you want to scrap the whole table?

It's time to drop it!

How to Drop Tables

Sometimes you may want to delete a whole table. To do this, drop the table like so:

drop table toys;

This removes the whole table and its data. Like dropping columns, this is a one-way operation.