gpt4 book ai didi

sql - 如何从 SELECT AS 在 Oracle 11g R2 中创建表并按范围列表对表进行分区?

转载 作者:行者123 更新时间:2023-12-04 22:10:12 25 4
gpt4 key购买 nike

我正在尝试从名为 Titles 的现有表创建一个名为 Titles2 的新表。我必须使用 SELECT AS 语句从 Titles 创建 Titles2 中的列。我还必须按 RANGE 然后按 LIST 对 Titles2 进行分区。

用于创建标题的代码:

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.

以下代码是我迄今为止从 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));

但是,创建 Titles2 的代码不会执行。有人能帮我执行 Titles2 代码吗?谢谢!

最佳答案

就像使用 Titles2 一样,仅使用 create table 语句(不是 CTAS)和适当的分区创建表 Titles 。然后使用 insert 语句用来自 Titles2 的数据填充 Titles

insert into Titles2 
select <<columns>>
from Titles

或者您可以按如下方式重写最后的 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

关于sql - 如何从 SELECT AS 在 Oracle 11g R2 中创建表并按范围列表对表进行分区?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/13070723/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com