Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Create table as select.... with partitions

733226Nov 20 2009 — edited Nov 20 2009
Hi,
i wanted to ask if it's possible to create a table as select from, specyfing also a partition schema, or if it's possible to add to this table the partitions later. This table is very very big, so i can't use the INSERT statement(it take 16 hours when create table as takes 30 minutes)
Any suggestion will be appreciated!
Thanks in advance

Comments

Pierre Forstmann
You can specify a PARTITION clause in SELECT statement but this works only for a single partition:
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for 32-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

SQL>
SQL> drop table x purge;

Table dropped.

SQL> drop table y purge;

Table dropped.

SQL>
SQL> create table x
  2  (
  3  c1  number(5),
  4  c2   varchar2(30),
  5  c3   date
  6  )
  7  partition by list (c1)
  8  (
  9   partition c1p1 values(1),
 10   partition c1p2 values(2),
 11   partition c1p3 values(3)
 12  );

Table created.

SQL>
SQL>
SQL> alter table x add c4 number not null;

Table altered.

SQL> insert into x values(1,'C1P1',sysdate, 1);

1 row created.

SQL> insert into x values(1,'C1P1',sysdate, 1);

1 row created.

SQL> insert into x values(1,'C1P1',sysdate, 1);

1 row created.

SQL> insert into x values(2,'C1P2',sysdate, 2);

1 row created.

SQL> insert into x values(2,'C1P2',sysdate, 2);

1 row created.

SQL> insert into x values(2,'C1P2',sysdate, 2);

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL> select * from x;

        C1 C2                             C3                C4
---------- ------------------------------ --------- ----------
         1 C1P1                           20-NOV-09          1
         1 C1P1                           20-NOV-09          1
         1 C1P1                           20-NOV-09          1
         2 C1P2                           20-NOV-09          2
         2 C1P2                           20-NOV-09          2
         2 C1P2                           20-NOV-09          2

6 rows selected.

SQL> create table y as select * from x partition (c1p1);

Table created.

SQL> select * from y;

        C1 C2                             C3                C4
---------- ------------------------------ --------- ----------
         1 C1P1                           20-NOV-09          1
         1 C1P1                           20-NOV-09          1
         1 C1P1                           20-NOV-09          1

SQL>
It could be easier to create the new table empty and to use partitions exchanging to copy partitions to the new table:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/partiti.htm#sthref2762



Edited by: P. Forstmann on 20 nov. 2009 09:48
Anurag Tibrewal
Hi,

I do not think so you can create partition table with CTAS.

You can try
1) Inserting with APPEND hint
2) Creating individual tables for each partition and then doing exchange partition.
3) expdp/impdb to the empty paritition table. (Original table should be renamed or should not exist in destination schema)

Regards
Anurag
Pierre Forstmann
Here is an example with partitions exchanging that requires new table is created empty with same partitioning structure:
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for 32-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

SQL>
SQL> drop table x purge;

Table dropped.

SQL> drop table y purge;

Table dropped.

SQL> drop table tmp purge;

Table dropped.

SQL>
SQL> whenever sqlerror exit failure;
SQL>
SQL> create table x
  2  (
  3  c1  number(5),
  4  c2   varchar2(30),
  5  c3   date,
  6  c4   number
  7  )
  8  partition by list (c1)
  9  (
 10   partition c1p1 values(1),
 11   partition c1p2 values(2),
 12   partition c1p3 values(3)
 13  );

Table created.

SQL>
SQL>
SQL> insert into x values(1,'C1P1',sysdate, 1);

1 row created.

SQL> insert into x values(1,'C1P1',sysdate, 1);

1 row created.

SQL> insert into x values(1,'C1P1',sysdate, 1);

1 row created.

SQL> insert into x values(2,'C1P2',sysdate, 2);

1 row created.

SQL> insert into x values(2,'C1P2',sysdate, 2);

1 row created.

SQL> insert into x values(2,'C1P2',sysdate, 2);

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL> select * from x;

        C1 C2                             C3                C4
---------- ------------------------------ --------- ----------
         1 C1P1                           20-NOV-09          1
         1 C1P1                           20-NOV-09          1
         1 C1P1                           20-NOV-09          1
         2 C1P2                           20-NOV-09          2
         2 C1P2                           20-NOV-09          2
         2 C1P2                           20-NOV-09          2

6 rows selected.

SQL>
SQL> create table y
  2  (
  3  c1  number(5),
  4  c2  varchar2(30),
  5  c3  date,
  6  c4  number
  7  )
  8  partition by list (c1)
  9  (
 10   partition c1p1 values(1),
 11   partition c1p2 values(2),
 12   partition c1p3 values(3)
 13  );

Table created.

SQL>
SQL> create table tmp as select * from y where 1=0;

Table created.

SQL>
SQL> alter table x exchange partition c1p1 with table tmp;

Table altered.

SQL> alter table y exchange partition c1p1 with table tmp;

Table altered.

SQL> alter table x exchange partition c1p2 with table tmp;

Table altered.

SQL> alter table y exchange partition c1p2 with table tmp;

Table altered.

SQL>
SQL> select * from x;

no rows selected

SQL> select * from y;

        C1 C2                             C3                C4
---------- ------------------------------ --------- ----------
         1 C1P1                           20-NOV-09          1
         1 C1P1                           20-NOV-09          1
         1 C1P1                           20-NOV-09          1
         2 C1P2                           20-NOV-09          2
         2 C1P2                           20-NOV-09          2
         2 C1P2                           20-NOV-09          2

6 rows selected.

SQL>
733226
Now i'm trying so...i have created the table without partitions with ctas and i'm inserting the data into an empty partitioned table..but it's taking very much time...
733226
But one my two tables is without partitons beacuse has been created with the "CREATE TABLE AS SELECT" statement...so i can't use this solution :(
Rob van Wijk
user12195888 wrote:
This table is very very big, so i can't use the INSERT statement(it take 16 hours when create table as takes 30 minutes)
This is a strange statement. Here are my findings:
SQL> set timing on
SQL> create table very_very_big (id,name)
  2  as
  3   select level
  4        , 'Name' || to_char(level)
  5     from dual
  6  connect by level <= 10000000
  7  /

Tabel is aangemaakt.

Verstreken: 00:00:29.05
SQL> drop table very_very_big purge
  2  /

Tabel is verwijderd.

Verstreken: 00:00:00.06
SQL> create table very_very_big
  2  ( id   number(8)
  3  , name varchar2(12)
  4  )
  5  partition by range (id)
  6  ( partition p1  values less than (1000000)
  7  , partition p2  values less than (2000000)
  8  , partition p3  values less than (3000000)
  9  , partition p4  values less than (4000000)
 10  , partition p5  values less than (5000000)
 11  , partition p6  values less than (6000000)
 12  , partition p7  values less than (7000000)
 13  , partition p8  values less than (8000000)
 14  , partition p9  values less than (9000000)
 15  , partition p10 values less than (maxvalue)
 16  )
 17  /

Tabel is aangemaakt.

Verstreken: 00:00:00.00
SQL> insert /*+ append */ into very_very_big
  2   select level
  3        , 'Name' || to_char(level)
  4     from dual
  5  connect by level <= 10000000
  6  /

10000000 rijen zijn aangemaakt.

Verstreken: 00:00:36.03
Not that much slower ...
Did you use the APPEND hint?

Regards,
Rob.
Hoek
Hi,

Maybe this will give you ideas:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5532451667350#tom5544884963551
1 - 7
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Dec 18 2009
Added on Nov 20 2009
7 comments
14,326 views