How would I create a table in Oracle 11g R2 from a SELECT AS and partition the table by range-list?

oracle, oracle11gr2, sql

Solution

Create table `Titles2` using just `create table` statement(not CTAS) with appropriate partitions just like you did with `Titles`. And then use `insert` statement to populate `Titles2` with data from `Titles`

insert into Titles2 
  select <<columns>>
    from Titles

Or you can rewrite your last `create table` statement as follows:

CREATE TABLE Titles2
  PARTITION BY RANGE (Revenue)(
    SUBPARTITION BY LIST (Genre)
    SUBPARTITION TEMPLATE (
    SUBPARTITION G1 VALUES ('history', 'biography'),
    SUBPARTITION G2 VALUES ('computer','children'),
    SUBPARTITION G3 VALUES (DEFAULT)) (
    PARTITION P1 VALUES LESS THAN (100000),
    PARTITION P2 VALUES LESS THAN (500000),
    PARTITION P3 VALUES LESS THAN (1000000),
    PARTITION P4 VALUES LESS THAN (MAXVALUE))
   ) AS
SELECT Title_id AS TID
     , Title
     , Genre
     , Sales
     , (Sales * Price) AS Revenue
     , Pub_id AS P#
FROM Titles

Problem

I am trying to create a new table called Titles2 from an existing table called Titles. I have to use a SELECT AS statement to create the columns in Titles2 from Titles. I also have to partition Titles2 by RANGE and then by LIST. The code used to create Titles: ``` SQL> CREATE TABLE Titles 2 ( 3 Title_id char(3) , 4 Title varchar2(40), 5 Genre varchar2(10), 6 Pages number , 7 Price number(5,2) , 8 Sales number , 9 Pub_id char(3) , 10 Pubdate date , 11 Advance number(9,2) , 12 Royalty_rate number(5,2) , 13 CONSTRAINT Titles_pk PRIMARY KEY (title_id), 14 CONSTRAINT Titles_Publishers_fk FOREIGN KEY (Pub_id) 15 REFERENCES Publishers (pub_id) 16 ) 17 PARTITION BY RANGE (Pubdate) ( 18 PARTITION P1 VALUES LESS THAN (TO_DATE('01-JAN-1995', 'DD-MON-YYYY')) TABLESPACE TSLab8ben1, 19 PARTITION P2 VALUES LESS THAN (TO_DATE('01-JAN-2000', 'DD-MON-YYYY')) TABLESPACE TSLab8ben2, 20 PARTITION P3 VALUES LESS THAN (MAXVALUE) TABLESPACE TSLab8ben3 21 ); Table created. ``` The following code is what I have so far for creating a Titles2 table from Titles: ``` CREATE TABLE Titles2 AS SELECT Title_id AS TID, Title, Genre, Sales, (Sales * Price) AS Revenue, Pub_id AS P# FROM Titles PARTITION BY RANGE (Revenue) SUBPARTITION BY LIST (Genre) SUBPARTITION TEMPLATE ( SUBPARTITION G1 VALUES ('history', 'biography'), SUBPARTITION G2 VALUES ('computer','children'), SUBPARTITION G3 VALUES (DEFAULT)) ( PARTITION P1 VALUES LESS THAN (100000), PARTITION P2 VALUES LESS THAN (500000), PARTITION P3 VALUES LESS THAN (1000000), PARTITION P4 VALUES LESS THAN (MAXVALUE)); ``` However, the code for creating Titles2 doesn't execute. Would anybody be able to help me get the Titles2 code to execute? Thanks!

Original source