SQL 如何从 SELECT AS 在 Oracle 11g R2 中创建表并按范围列表对表进行分区？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13070723/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How would I create a table in Oracle 11g R2 from a SELECT AS and partition the table by range-list?
提问by Benjamin C. Huskisson-Snider
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.
我正在尝试从名为 Titles 的现有表创建一个名为 Titles2 的新表。我必须使用 SELECT AS 语句从 Titles 创建 Titles2 中的列。我还必须按 RANGE 然后按 LIST 对 Titles2 进行分区。
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:
以下代码是我迄今为止从 Titles 创建 Titles2 表的代码：
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!
但是，创建 Titles2 的代码不会执行。有人能帮我执行 Titles2 代码吗？谢谢！
回答by Nick Krasnov
Create table Titles2using just create tablestatement(not CTAS) with appropriate partitions just like you did with Titles. And then use insertstatement to populate Titles2with data from Titles
创建表Titles2只用create table用，就像你做了适当的分区声明（不CTAS） Titles。然后使用insert语句填充Titles2来自Titles
insert into Titles2
select <<columns>>
from Titles
Or you can rewrite your last create tablestatement as follows:
或者您可以create table按如下方式重写您的最后一条语句：
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
