Oracle Create a table using a query

Here is a neat little SQL trick. First a little bit of background since its informative (scroll down if you dont want it)

Joins in LARGE tables are always expensive. Whenever possible they should be catered for in your ETL.

Here's what happened. Ericsson OSS has a table with time and another table with cell stats (many other tables in fact :) ). First I decided to copy the data as it is. However later on I wanted daily averages of counters. The plan was to use a simple view over these two tables for the stats. And then another view which averages this over every day.

The query performance = 54 secs

This is pretty fast considering its all cells for all hours of all days.

But then I read about Oracle function based indices (FBI)! Amazing stuff. I could add the day as an index to the table (using trunc(time,'DD')) . Cool. That should really boast the performance.
However VIEWs don't support indices. (Only materialized views do ... but a materialized view would consume too much space and be an overkill .. just to add indices). So I needed to convert the two tables into a single table.

Using function based index which truncates date I get a query performance = 21 secs.



SQL

So I decided to move the data from those two tables to the new table.

create table yourtable as select * from whatever

This will create the table from the results of your query. Quick and neat way to move data. Love SQL.... Love Oracle!