Subject Re: [firebird-support] create table as select
Author Richard Wesley
On Jul 17, 2006, at 02:09, Dimitry Sibiryakov wrote:

> It is handy but it is against relation theory. Normalization! Why
> you can want to have two tables in a database with the same structure
> and the same data?..

In Tableau, we do this for performance in engines that support it.
Not the same data admittedly, but the same structure:

select into [#temp] * from [table]
where ...

(T-SQL syntax IIRC)

If you have a huge data warehouse and the user wants to look at only
a small subset of it (e.g. one salesman for one month) it is often
much faster to extract the data into a (temporary) table in this
manner and run queries against it instead of querying the entire
warehouse with complex filters and aggregations each time the user
makes a gesture in our UI.

For Firebird, we have to implement this by querying for the filtered
data and shovelling it into a temporary database file. This allows
us to index it as well, but I believe the big engines do all that
sort of processing automagically when you use their SELECT INTO
functionality. We can do it manually because we have a lot of
metadata lying around but I can see that applications which are
simpler or which have a different focus could find the functionality
useful. Heck, we even use the functionality if it is available (i.e.
SQL Server, MySQL Oracle).
Richard Wesley Software Engineer
Tableau Software hawkfish
tableausoftware com