How to Partition a Non-partitioned Table [ID 1070693.6]

How to Partition a Non-partitioned Table [ID 1070693.6]


Modified03-MAR-2010TypeBULLETINStatusPUBLISHED

PURPOSE

You have a table that is not partitioned that you would like to make into a partitioned table.This article describes four possible methods for partitioning a non-partitioned table.

These steps can also be used to change other partitioning characteristics such as adding subpartitioning to a partitioned table.

SCOPE & APPLICATION

Users needing to partition a non-partitioned table.

RELATED DOCUMENTS

Note:72332.1DIAGNOSING ORA-14097 ON ALTER TABLE EXCHANGE PARTITION

Note:105317.1Section "VII.7 Partition exchanges

Note:472449.1How To Partition Existing Table Using DBMS_Redefinition

You can partition a non-partitioned table in one of four ways:

A)Export/import method

B)Insert with a subquery method

C)Partition exchange method

D)DBMS_REDEFINITION

Either of these four methods will create a partitioned table from an existing non-partitioned table.

A. Export/import method

--------------------

1)Export your table:

exp usr/pswd tables=numbers file=exp.dmp

2)Drop the table:

drop table numbers;

3)Recreate the table with partitions:

create table numbers (qty number(3), name varchar2(15))

partition by range (qty)

(partition p1 values less than (501),

partition p2 values less than (maxvalue));

4)Import the table with ignore=y:

imp usr/pswd file=exp.dmp ignore=y

The ignore=y causes the import to skip the table creation and

continues to load all rows.

B. Insert with a subquery method

-----------------------------

1)Create a partitioned table:

create table partbl (qty number(3), name varchar2(15))

partition by range (qty)

(partition p1 values less than (501),

partition p2 values less than (maxvalue));

2)Insert into the partitioned table with a subquery from the

non-partitioned table:

insert into partbl (qty, name)

select * from origtbl;

3)If you want the partitioned table to have the same name as the

original table, then drop the original table and rename the

new table:

drop table origtbl;

alter table partbl rename to origtbl;

C. Partition Exchange method

-------------------------

ALTER TABLE EXCHANGE PARTITION can be used to convert a partition (or

subpartition) into a non-partitioned table and a non-partitioned table into a

partition (or subpartition) of a partitioned table by exchanging their data

and index segments.

1) Create table dummy_t as select with the required partitions

2) Alter table EXCHANGE partition partition_name

with table non-partition_table;

Example

-------

SQL> CREATE TABLE p_emp

2(sal NUMBER(7,2))

3PARTITION BY RANGE(sal)

4(partition emp_p1 VALUES LESS THAN (2000),

5partition emp_p2 VALUES LESS THAN (4000));

Table created.

SQL> SELECT * FROM emp;

EMPNO ENAMEJOBMGR HIREDATESAL

--------- ---------- --------- --------- --------- ---------

7369 SMITHCLERK7902 17-DEC-80800

7499 ALLENSALESMAN7698 20-FEB-811600

7521 WARDSALESMAN7698 22-FEB-811250

7566 JONESMANAGER7839 02-APR-812975

7654 MARTINSALESMAN7698 28-SEP-811250

7698 BLAKEMANAGER7839 01-MAY-812850

7782 CLARKMANAGER7839 09-JUN-812450

7788 SCOTTANALYST7566 19-APR-873000

7839 KINGPRESIDENT17-NOV-815000

7844 TURNERSALESMAN7698 08-SEP-811500

7876 ADAMSCLERK7788 23-MAY-871100

7900 JAMESCLERK7698 03-DEC-81950

7902 FORDANALYST7566 03-DEC-813000

7934 MILLERCLERK7782 23-JAN-821300

14 rows selected.

SQL> CREATE TABLE dummy_y as SELECT sal

FROM emp WHEREsal<2000;

Table created.

SQL> CREATE TABLE dummy_z as SELECT sal FROM emp WHERE sal

BETWEEN 2000 AND 3999;

Table created.

SQL> alter table p_emp exchange partition emp_p1

with table dummy_y;

Table altered.

SQL> alter table p_emp exchange partition emp_p2

with table dummy_z;

Table altered.

D. DBMS_REDEFINITION

-----------------

See Note 472449.1 "How To Partition Existing Table Using DBMS_Redefinition" for detailed instructions.

在线重定义参考:

http://blog.csdn.net/xujinyang/article/details/6831289

------------------------------------------------------------------------------

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
To optimize queries in Hive, you can follow these best practices: 1. Use partitioning: Partitioning is a technique of dividing a large table into smaller, more manageable parts based on specific criteria such as date, region, or category. It can significantly improve query performance by reducing the amount of data that needs to be scanned. 2. Use bucketing: Bucketing is another technique of dividing a large table into smaller, more manageable parts based on the hash value of a column. It can improve query performance by reducing the number of files that need to be read. 3. Use appropriate file formats: Choose the appropriate file format based on the type of data and the query patterns. For example, ORC and Parquet formats are optimized for analytical queries, while Text and SequenceFile formats are suitable for batch processing. 4. Optimize data storage: Optimize the way data is stored on HDFS to improve query performance. For example, use compression to reduce the amount of data that needs to be transferred across the network. To create a partition table with Hive, you can follow these steps: 1. Create a database (if it doesn't exist) using the CREATE DATABASE statement. 2. Create a table using the CREATE TABLE statement, specifying the partition columns using the PARTITIONED BY clause. 3. Load data into the table using the LOAD DATA statement, specifying the partition values using the PARTITION clause. Here's an example: ``` CREATE DATABASE my_db; USE my_db; CREATE TABLE my_table ( id INT, name STRING ) PARTITIONED BY (date STRING); LOAD DATA LOCAL INPATH '/path/to/data' OVERWRITE INTO TABLE my_table PARTITION (date='2022-01-01'); ``` This creates a table called `my_table` with two columns `id` and `name`, and one partition column `date`. The data is loaded into the table with the partition value `2022-01-01`.

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值