【實驗】【PARTITION】RANGE分割槽表截斷表分割槽(Truncate Partition)
truncate表的效率有目共睹,那如何truncate分割槽表呢?
請看一下簡單的演示:
1.語法
alter table Partition_Table_Name truncate partition/subpartition Partition_Name update indexes;
2.建立分割槽表並插入資料
sec@ora10g> create table t_partition_range (id number,name varchar2(50))
2 partition by range(id)(
3 partition t_range_p1 values less than (10) tablespace tbs_part01,
4 partition t_range_p2 values less than (20) tablespace tbs_part02,
5 partition t_range_p3 values less than (30) tablespace tbs_part03,
6 partition t_range_pmax values less than (maxvalue) tablespace tbs_part04);
Table created.
sec@ora10g> col TABLE_NAME for a20
sec@ora10g> col partition_name for a20
sec@ora10g> col HIGH_VALUE for a10
sec@ora10g> col TABLESPACE_NAME for a15
sec@ora10g> select table_name,partition_name,high_value,tablespace_name from user_tab_partitions where table_name='T_PARTITION_RANGE' order by partition_position;
TABLE_NAME PARTITION_NAME HIGH_VALUE TABLESPACE_NAME
-------------------- -------------------- ---------- ---------------
T_PARTITION_RANGE T_RANGE_P1 10 TBS_PART01
T_PARTITION_RANGE T_RANGE_P2 20 TBS_PART02
T_PARTITION_RANGE T_RANGE_P3 30 TBS_PART03
T_PARTITION_RANGE T_RANGE_PMAX MAXVALUE TBS_PART04
sec@ora10g> insert into T_PARTITION_RANGE values (2,'secooler02');
1 row created.
sec@ora10g> insert into T_PARTITION_RANGE values (3,'secooler03');
1 row created.
sec@ora10g> insert into T_PARTITION_RANGE values (4,'secooler04');
1 row created.
sec@ora10g> commit;
Commit complete.
sec@ora10g> select *from t_partition_range partition(t_range_p1);
ID NAME
---------- --------------------------------------------------
2 secooler02
3 secooler03
4 secooler04
3.就是這樣的truncate分割槽
sec@ora10g> alter table t_partition_range truncate partition t_range_p1 update indexes;
Table truncated.
sec@ora10g> select * from t_partition_range partition(t_range_p1);
no rows selected
-- The End --
請看一下簡單的演示:
1.語法
alter table Partition_Table_Name truncate partition/subpartition Partition_Name update indexes;
2.建立分割槽表並插入資料
sec@ora10g> create table t_partition_range (id number,name varchar2(50))
2 partition by range(id)(
3 partition t_range_p1 values less than (10) tablespace tbs_part01,
4 partition t_range_p2 values less than (20) tablespace tbs_part02,
5 partition t_range_p3 values less than (30) tablespace tbs_part03,
6 partition t_range_pmax values less than (maxvalue) tablespace tbs_part04);
Table created.
sec@ora10g> col TABLE_NAME for a20
sec@ora10g> col partition_name for a20
sec@ora10g> col HIGH_VALUE for a10
sec@ora10g> col TABLESPACE_NAME for a15
sec@ora10g> select table_name,partition_name,high_value,tablespace_name from user_tab_partitions where table_name='T_PARTITION_RANGE' order by partition_position;
TABLE_NAME PARTITION_NAME HIGH_VALUE TABLESPACE_NAME
-------------------- -------------------- ---------- ---------------
T_PARTITION_RANGE T_RANGE_P1 10 TBS_PART01
T_PARTITION_RANGE T_RANGE_P2 20 TBS_PART02
T_PARTITION_RANGE T_RANGE_P3 30 TBS_PART03
T_PARTITION_RANGE T_RANGE_PMAX MAXVALUE TBS_PART04
sec@ora10g> insert into T_PARTITION_RANGE values (2,'secooler02');
1 row created.
sec@ora10g> insert into T_PARTITION_RANGE values (3,'secooler03');
1 row created.
sec@ora10g> insert into T_PARTITION_RANGE values (4,'secooler04');
1 row created.
sec@ora10g> commit;
Commit complete.
sec@ora10g> select *from t_partition_range partition(t_range_p1);
ID NAME
---------- --------------------------------------------------
2 secooler02
3 secooler03
4 secooler04
3.就是這樣的truncate分割槽
sec@ora10g> alter table t_partition_range truncate partition t_range_p1 update indexes;
Table truncated.
sec@ora10g> select * from t_partition_range partition(t_range_p1);
no rows selected
-- The End --
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/29119536/viewspace-1141877/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 分割槽Partition
- 分割槽partition知識點
- Oracle查詢Interval partition分割槽表內資料Oracle
- Spark學習——分割槽Partition數Spark
- 分割槽表truncate慢處理
- Oracle Partition 分割槽詳細總結Oracle
- 分割槽函式Partition By的基本用法函式
- Kafka分割槽分配策略(Partition Assignment Strategy)Kafka
- Oracle分割槽表基礎運維-07增加分割槽(6RANGE_RANGE)Oracle運維
- oracle分割槽表和分割槽表exchangeOracle
- oracle分割槽表和非分割槽表exchangeOracle
- 分割槽函式partition by的基本用法【轉載】函式
- 分割槽表-實戰
- PostgreSQL/LightDB 分割槽表之分割槽裁剪SQL
- Oracle分割槽表基礎運維-07增加分割槽(4 RANGE_HASH)Oracle運維
- Oracle分割槽表基礎運維-07增加分割槽(5RANGE_LIST)Oracle運維
- PG的非分割槽表線上轉分割槽表
- MySQL全面瓦解29:使用Partition功能實現水平分割槽MySql
- 【MYSQL】 分割槽表MySql
- Mysql表分割槽實現MySql
- Mysql表分割槽實操MySql
- 非分割槽錶轉換成分割槽表
- [oracle] expdp 匯出分割槽表的分割槽Oracle
- Oracle分割槽表基礎運維-07增加分割槽(3列表分割槽)Oracle運維
- oracle 分割槽表move和包含分割槽表的lob moveOracle
- 移動分割槽表和分割槽索引的表空間索引
- 使用parted建立大分割槽時 mkpart Warning: The resulting partition is not properly
- Oracle分割槽表基礎運維-04列表分割槽Oracle運維
- HGDB的分割槽表實現SQL Server的分割槽檢視SQLServer
- Oracle分割槽表基礎運維-07增加分割槽(2 HASH分割槽)Oracle運維
- MySQL 分割槽表探索MySql
- Oracle分割槽表基礎運維-06分割槽表索引Oracle運維索引
- 【Linux】MBR磁碟分割槽表只能有四個分割槽?Linux
- oracle 線上重新定義,普通表改變分割槽表,分割槽表可以更改型別、分割槽欄位等Oracle型別
- Oracle分割槽表基礎運維-07增加分割槽(1範圍分割槽)Oracle運維
- Oracle分割槽表基礎運維-01分割槽表分類Oracle運維
- Oracle分割槽表基礎運維-09刪除分割槽Oracle運維
- Oracle分割槽表基礎運維-05組合分割槽Oracle運維
- Oracle分割槽表基礎運維-02範圍分割槽Oracle運維