Hello AskTom team,
Typically we code CTAS as
CREATE TABLE <TABLE_NAME> AS SELECT <COL_LIST> FROM <TABLE_NAME> WHERE ..
I have a requirement where i have to create a TMP table with a particular structure which is later used for exchange partition. This table would have about 25 columns and potentially several million rows of data.
We tried 2 options
1st option (this option was faster than doing INSERT APPEND directly on the table):
1) CREATE TMP table with required structure
2) INSERT /*+APPEND*/ INTO <TMP_TABLE>...SELECT
3) exchange partition
2nd option:
1) CREATE TABLE AS SELECT CAST(COL1 AS VARCHAR2(255)) COL1, CAST(COL2 AS DATE) COL2,....FROM
2) exchange partition
After several trials the 2nd option seemed to be faster.
But I think running CAST over all the columns over millions of rows is still not optimal. Is there any other way of doing or optimizing this?
Thanks!
In either case, you are potentially looking at a datatype conversion, so if the 2nd one works better for you, then by all means use it.
In (1), even if TMP has the correct datatype, then the insert-select will invoke a data conversion as data is loaded into TMP, eg insert into (date_col) select string_col from ..., does a conversion even if you havent explicitly coded the TO_DATE function.
The same of course applies in (2), just the the data conversion is now explicit in the SELECT, but the net effect will be similar.
If there is lots of duplicated date, then you might get some benefit with scalar query caching, eg
SQL> create table t1 ( x date );
Table created.
SQL> create table t2 ( s varchar2(20));
Table created.
SQL>
SQL> insert /*+ APPEND */ into t2
2 select '2016-01-01' from
3 ( select 1 from dual connect by level <= 1000 ),
4 ( select 1 from dual connect by level <= 10000 );
10000000 rows created.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> set timing on
SQL> insert /*+ APPEND */ into t1 select to_date(s,'yyyy-mm-dd') from t2;
10000000 rows created.
Elapsed: 00:00:19.18
SQL> commit;
Commit complete.
SQL> drop table t1 purge;
Table dropped.
SQL> create table t1 ( x date );
Table created.
SQL> set timing on
SQL> insert /*+ APPEND */ into t1 select
2 ( select to_date(s,'yyyy-mm-dd') from dual )
3 from t2;
10000000 rows created.
Elapsed: 00:00:09.28
SQL> commit;
Commit complete.
SQL>
SQL>