Partition table explains how to select the type of partition, how to select the partition column. Before you do that, you need to be clear about your goals — manageability versus performance — and which one you’re more focused on. Partitioned tables affect performance, manageability, and data cleansing. Here are the specific effects of partitioning tables on each item, performance: This is generally the main purpose of partitioning. Partitioning turns large tables into small tables, avoiding a full table scan when the conditions after WHERE reflect the specific values of the partitioned fields. Ease of management: For large tables with large amounts of data, the ease of management brought by partitioning is obvious. When you suggest an index based on a non-partitioned table, the only option is to create the entire index. If the table is partitioned, you can create indexes for the table in parallel according to the partition, for example: ALTER INDEX PAR_IND_01 reuild partition YY05; In addition, you can also do many things at the same time, such as changing the tablespace of the table, exporting the table, deleting the table data, and so on. Data cleansing: It is often necessary to delete some historical data of a table. The common practice is delete, but this can result in rapid growth of undo and redo information and affect overall database performance. Alter TABLE TAB_A DROP partition YY01; alter table tab_A drop partition YY01; When a table partition is deleted, the corresponding local index is also deleted. If the global index still exists, it becomes unusable. To prevent this from happening, you can run the following command: ALTER TABLE TAB_A DROP PARTITION YY01 UPDATE global indexes; The above is the content of Internet users for you, there is more about the design of ORACLE partition table in Aliyun _oracle content, welcome to continue to use the search button in the upper right corner to search ORACLE, partition table, so that you can get more relevant information. A tablespace is a collection of one or more data files. All data objects are stored in a specified tablespace, but most of the data objects are stored in tables. Partitioned tables: Partitioning addresses the key problem of supporting very large tables and indexes. It takes their approach of breaking them down into smaller and manageable pieces called partitions. Once a partition is defined, SQL statements can access operations on a partition rather than the entire table, thus improving the efficiency of management. Partitioning is very effective for data warehouse applications because they often store and analyze huge amounts of historical data. As the amount of data in a table increases, the speed of querying the data slows down and the performance of your application deteriorates, so you should consider partitioning the table. After a table is partitioned, the table is still logically a complete table, but the data in the table is physically stored in multiple tablespaces (physical files), so that the whole table is not scanned each time when querying data. How table partitioning works: Oracle’s partitioned table functionality delivers significant benefits to a wide range of applications by improving manageability, performance, and availability. In general, partitioning can greatly improve the performance of certain query and maintenance operations. In addition, partitioning can greatly simplify common administrative tasks, and partitioning is a key tool for building gigabyte data systems or ultra-high availability systems. Partitioning provides the ability to further subdivide a table, index, or index organization table into segments, called partitions, of these database objects. Each partition has its own name and can choose its own storage features. From a database administrator’s perspective, a partitioned object has multiple segments that can be managed collectively or individually, giving the database administrator considerable flexibility in managing partitioned objects. However, from an application perspective, a partitioned table is exactly the same as a non-partitioned table, and no modifications are required when a partitioned table is accessed using SQL DML commands. When do you use partitioned tables? 1. Table size exceeds 2GB. 2, the table contains historical data, new data is added to the new partition. Advantages and disadvantages of table partitioning:
1, improve query performance: the query of partitioned objects can only search the partition they care about, improve the retrieval speed. 2. Enhanced availability: If one partition of a table fails, data in other partitions of the table is still available; 3. Easy maintenance: If a partition of the table fails and data needs to be repaired, only the partition can be repaired; 4. I/O balancing: You can map different partitions to disks to balance I/O and improve the overall system performance. Disadvantages: 1, partitioned table related: there is no way to directly convert existing tables into partitioned tables. Oracle does, however, provide the ability to redefine tables online. 2. Partitioning addresses the key problem of supporting very large tables and indexes. It takes their approach of breaking them down into smaller and manageable pieces called partitionsCopy the codeStep 1: Check the Oracle database version and operating system
select * from v$version
Copy the codeOracle Database 11G Enterprise Edition Release 11.2.0.3.0-64bit Production 2 PL/SQL Release 11.2.0.3.0 – Production 3 “CORE 11.2.0.3.0 Production” 4 TNS for HPUX: Version 11.2.0.3.0 – Production 5 NLSRTL Version 11.2.0.3.0 – Production
d.tablespace_name "Name", d.contents "Type", d.extent_management "Extent Management", 'to_char(NVL (a.ytes / 1024/1024, 0), '99999999.999') "Total Size (M)", to_char(NVL (a.ytes - NVL (f.ytes, 0), To_char (NVL (f.bytes, 0)/f.bytes, 0)/f.bytes, 0)/f.bytes, '99999999.999') (M) "Free", to_char (NVL ((a. ytes - NVL (f.b ytes, 0))/a. ytes * 100, 0), '990.00') "% 2"Copy the codeFROM sys.dba_tablespaces d,
(SELECT tablespace_name, SUM(bytes) bytes
FROM dba_data_files
GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM(bytes) bytes
FROM dba_free_space
GROUP BY tablespace_name) fCopy the codeWHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = f.tablespace_name(+)
AND NOT
(d.extent_management LIKE 'LOCAL' AND d.contents LIKE 'TEMPORARY')Copy the codeUNION ALL
SELECT d.status “Status”,
d.tablespace_name "Name", d.contents "Type", d.extent_management "Extent Management", Size (M)", to_char(NVL (t.ytes, 0) / 1024/1024, '99999990.900') '99999999.999') "Used (M)", to_char((NVL (a.bytes / 1024/1024, 0)) - (NVL (t.bytes, 0) / 1024/1024), '99999999.999') "Free (M)", to_char(NVL (t.ytes/a.ytes * 100, 0), '990.00') "Used %"Copy the codeFROM sys.dba_tablespaces d,
(SELECT tablespace_name, SUM(bytes) bytes
FROM dba_temp_files
GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM(bytes_cached) bytes
FROM v$temp_extent_pool
GROUP BY tablespace_name) tCopy the codeWHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = t.tablespace_name(+) AND d.extent_management LIKE ‘LOCAL’ AND D.C. montents LIKE ‘TEMPORARY’ ORDER BY “Used %” DESC DESC finds that the tablespace usage of the sjck_bz user has reached 97%. Example Collect statistics about the data amount of user sjck_bz. SELECT T.OWNER,T.TABLE_NAME,T.NUM_ROWS,T.LAST_ANALYZED FROM DBA_TABLES T WHERE T.LAST_ANALYZED IS NOT NULL ORDER BY T. num_rows DESC Statistics the storage usage of sjck_bz users. SELECT t.segment_name,SUM(T.BYTES/1024/1024/1024) FROM DBA_SEGMENTS T group by t.segment_name order by SUM(T.BYTES/1024/1024/1024) desc
A maximum of 10 tables that contain data and occupy storage occupy about 3 TERabytes of tablespace FP_ZZSFP_HWMX FP_ZZSPTFP FP_ZZSZYFP_HWMX FP_ZZSFP FP_ZZSPTFP_HWXX FP_ZZSZYFP SB_ZZS_2013_FB2 CW_ZCFZB_XQYKJZZ CW_ZCFZB_QYKJZZ SB_ZZS_2013_FB1
For example, see which table occupies the most space using user_segments, SELECT t.segment_name, SUM(t. ytes / 1024/1024) FROM user_segments t where t.segment_name=’FP_ZZSFP_HWMX’ SELECT t.segment_name FROM user_segments t where t.segment_name=’FP_ZZSFP_HWMX’
View the records for each subpartition
SELECT a.table_name,a.partition_name FROM user_tab_partitions a WHERE a.table_name =’FP_ZZSFP_HWMX’;
Check whether the index of the partitioned table is LOCAL – first check the index of the table
SELECT * FROM User_Indexes a WHERE a.table_name=’FP_ZZSFP_HWMX’;
Select * from local; If it does not exist; Is the global index
select table_name,index_name,LOCALITY from user_part_indexes where table_name=’FP_ZZSFP_HWMX’ ;
If the LOCAL index is used, delete the partition data directly. If the global index is used, you need to rebuild the partition data after the deletion
Local indexes are dropped when you drop a subpartitioned table; It doesn’t matter.
–1. Check the table size
Select count(*) from FP_ZZSFP_HWMX; select count(*) from FP_ZZSPTFP; Select segment_name,sum(bytes)/1024/1024 from user_segments WHERE segment_name in (‘FP_ZZSFP_HWMX’,
'FP_ZZSPTFP') Copy the codegroup by segment_name; Select OWNER, TRIGGER_NAME, TABLE_NAME, STATUS from dba_triggers WHERE TABLE_NAME in (‘FP_ZZSFP_HWMX’,
'FP_ZZSPTFP'); --3. View the table structure -- View the table informationCopy the code/ * by temporary partitioned iot_type three fields, table tennis table type is global temporary tables, whether the partition table, whether an index table must have been through degree field is set to the parallelism of the attribute (value greater than 1) Observe the value of the last_analyzed field to see if there is a normal collection, to see if there is a value, or to see if the time is long ago. */ select t.table_name,
t.num_rows,
t.blocks,
t.degree,
t.last_analyzed,
t.temporary,
t.partitioned,
t.iot_type,
t.pct_free,
t.tablespace_nameCopy the codefrom user_tables t
where table_name in (‘FP_ZZSFP_HWMX’,
'FP_ZZSPTFP') ;
Copy the codeUser_part_tables records information about partitioned tables, and user_tab_partitions records information about partitioned tables.
Select table_name from table_name select table_name from table_name
t.partitioning_type,
t.partition_count Copy the codefrom user_part_tables t
where table_name in (‘FP_ZZSFP_HWMX’,
'FP_ZZSPTFP') ; Know what columns are used as partitions for these tablesCopy the codeselect name,object_type, column_name
from user_part_key_columns
where name in (‘FP_ZZSFP_HWMX’,
'FP_ZZSPTFP') ; Copy the codeSELECT table_name,partition_name, high_value, tablespace_name FROM user_tab_partitions t where table_name in (‘FP_ZZSFP_HWMX’,
'FP_ZZSPTFP') Copy the codeorder by table_name,t.partition_position;
ALTER TABLE FP_ZZSFP_HWMX DROP PARTITION P201305; ALTER TABLE FP_ZZSFP_HWMX DROP PARTITION P201306; ALTER TABLE FP_ZZSFP_HWMX DROP PARTITION P201307; ALTER TABLE FP_ZZSFP_HWMX DROP PARTITION P201308; ALTER TABLE FP_ZZSFP_HWMX DROP PARTITION P201309; ALTER TABLE FP_ZZSFP_HWMX DROP PARTITION P201310; Two approaches are provided
Alter table tableName DROP PARTITION partionName; Alter table tableName TRUNCATE PARTITION partionName;
Note:
Partitions deleted from Oracle will not be Recyclebin
In An Oracle database, a single deleted partition is not placed in the recycle bin. A full deleted partition can be placed in the recycle bin with the full table. This is because after a single partition is deleted, it cannot be added to the original partition table through a simple flashback. Since consistency cannot be guaranteed, the partition will not go to the recycle bin
Step 4: Shrink the tablespace to reduce the high water level
1. Understand Segment shrinkage 2. Automatically execute Segment Advisor 3. contraction
- Understanding segment contraction
Application scenario: If a table is frequently inserted, updated, or deleted, a large number of fragments may occur over a long period of time. In this scenario, Oracle introduces the segment shrink function to reduce fragments. Oracle segment shrinkage performs two different tasks :(1) compressing data rows, and (2) moving high water mark (HWM). Because the relocation of HWM may block DML operations of the user, the shrink operation of the storage does not have this effect. All indexes on the table are maintained during the operation.
- Automatically run the Segment Advisor server -> Oracle Scheduler -> Automatic Maintenance Tasks -> Configure to enable or disable the Segment Guidance task
2.2 Using SQL Commands
— Disable Segment Advisor BEGIN DBMS_auto_task_admin. disable(client_name => ‘Auto Space Advisor ‘, operation => NULL, window_name => NULL); END; /
Start dbMS_auto_task_admin. enable(client_name => ‘Auto Space Advisor ‘, operation => NULL, window_name => NULL); END;
Set the Segment Advisor to run the variable task_id number; declare name varchar2(100); descr varchar2(500); obj_id number; begin name := ”; –unique name descr := ‘Check FP_ZZSFP_HWMX table’; dbms_advisor.create_task(‘Segment Advisor’, :task_id, name, descr, null); dbms_advisor.create_object(name, ‘TABLE’, ‘SJCK_BZ’, ‘FP_ZZSFP_HWMX’, null, null, obj_id); dbms_advisor.set_task_parameter(name, ‘RECOMMEND_ALL’, ‘TRUE’); dbms_advisor.execute_task(name); end; print task_id
TASK_ID
1358Copy the codeQuery DBA_ADVISOR_FINDINGS based on the TASK_ID above
select owner, task_id, task_name, type, message, more_info from dba_advisor_findings where task_id=FP_ZZSFP_HWMX;
SQL > alter table tablespace tablespace shrink 100G
alter table FP_ZZSFP_HWMX enable row movement;
alter table FP_ZZSFP_HWMX shrink space;
select segment_name, BYTES/1024/1024 “MB” from user_segments where segment_name = ‘FP_ZZSFP_HWMX’; The storage space used by this table is reduced by about 100 GB
Note that the shrink command has two limitations:
Oracle 10g cannot work with LOB segments based on Oracle 10g.
It is not allowed to operate on tables that contain any functional index rules, whether using 10G or 11G.
Mysql > delete global index and primary key index. Mysql > delete global index and primary key index. Mysql > delete global index and primary key index.
Alter table tableName DROP PARTITION partionName; Alter table tableName TRUNCATE PARTITION partionName;
To view index invalidation commands: Log in to the sjck_bz user to see select T.index_name, T.table_name, T.status, t.ast_analyzed from user_indexes T where t.table_name=’FP_ZZSFP’; Select * from user_ind_partitions
Rebuild primary key index Delete Delete primary key first
Nologging and PARALLEL cannot be used when creating a primary key index. As a result, creating a unique primary key index takes about 5 hours. So consider a way to create a primary key unique index (nologging and parallel), then create a primary key, and then turn off parallelism (this method can be used in 25 minutes). For tables that already have a large amount of data and cannot create primary keys in parallel, create unique indexes in parallel and then add primary keys. Primary keys cannot be empty, and unique indexes can be empty. 1: alter table FP_ZZSFP drop constraint PK_FP_ZZSFP; 2: drop index PK_FP_ZZSFP; 3: Create unique index PK_FP_ZZSFP on FP_ZZSFP (XXX, XXX, XXX) nologging PARALLEL 8 online; When creating an index with create Index INX_1 on t(COL1) (when the table has a large amount of data), OralCE and LOKC will complete the table. In this case, a major accident may occur in a production system, and all DML operations need to wait. In order not to affect the DML execution of the production system, we can sometimes use the online keyword when creating the index, so that the DML operation 4 will not be blocked when the index is created and DML execution is performed. alter table FP_ZZSFP add constraint PK_FP_ZZSFP primary key (FP_ID, FPHM, KPYF_ID); 5: alter index PK_FP_ZZSFP NOPARALLEL; 6: Select T. egree from DBA_indexes T where T.index_name =’PK_FP_ZZSFP’;
Step 6: Begin DBMS_stats. gather_TABLE_stats (OWNNAME=>’SJCK_BZ’, — user name TABNAME=>’FP_ZZSFP’,– table name ESTIMATE_PERCENT=>100, METHOD_OPT=>’for all indexed columns size repeat’, DEGREE=>8, GRANULARITY=>’ALL’, CASCADE=> true, NO_INVALIDATE=>false ); end; /
It takes about 40 minutes to collect 500 gigabytes of data.
Step 7: Count the daily data growth of the tablespace
The dba_HIST_seg_stat view is introduced at the beginning of Oracle 10G. This view records historical segment-level statistics within the snapshot time: DBA_HIST_SEG_STAT displays historical information about segment-level statistics. This view captures the top segments based on a set of criteria and captures information from V
SNAP_ID NAME TABLESPACE_SIZE_MB TABLESPACE_USED_MAXSIZE RTIME
How to estimate the historical growth of Oracle database and database objects
—– Database growth in the last seven days, this is just an estimate.
select sum(space_used_total)/1024/1024/1024 “last 7 days db increase – G”
from
dba_hist_seg_stat s,
dba_hist_seg_stat_obj o,
dba_hist_snapshot sn
where
s.obj# = o.obj#
and
sn.snap_id = s.snap_id
and begin_interval_time > sysdate-8
order by
begin_interval_time
/
Here are two more similar scripts on the web:
Scripts: displays the historical growth of the database
The historical growth of a database is calculated by calculating the historical growth of all tablespaces in a database. — Excluding undo and temp
with tmp as
(select rtime,
sum(tablespace_usedsize_kb) tablespace_usedsize_kb,
sum(tablespace_size_kb) tablespace_size_kb
from (select rtime,
e.tablespace_id,
(e.tablespace_usedsize) * (f.block_size) / 1024 tablespace_usedsize_kb,
(e.tablespace_size) * (f.block_size) / 1024 tablespace_size_kb
from dba_hist_tbspc_space_usage e,
dba_tablespaces f,
v$tablespace g
where e.tablespace_id = g.TS#
and f.tablespace_name = g.NAME
and f.contents not in (‘TEMPORARY’,’UNDO’))
group by rtime)
select tmp.rtime,
tablespace_usedsize_kb,
tablespace_size_kb,
(tablespace_usedsize_kb –
LAG(tablespace_usedsize_kb, 1, NULL) OVER(ORDER BY tmp.rtime)) AS DIFF_KB
from tmp,
(select max(rtime) rtime
from tmp
group by substr(rtime, 1, 10)) FP_ZZSPTFP
where FP_ZZSPTFP.rtime = tmp.rtime;
— Contains undo and temp
with tmp as
(select min(rtime) rtime,
sum(tablespace_usedsize_kb) tablespace_usedsize_kb,
sum(tablespace_size_kb) tablespace_size_kb
from (select rtime,
e.tablespace_id,
(e.tablespace_usedsize) * (f.block_size) / 1024 tablespace_usedsize_kb,
(e.tablespace_size) * (f.block_size) / 1024 tablespace_size_kb
from dba_hist_tbspc_space_usage e,
dba_tablespaces f,
v$tablespace g
where e.tablespace_id = g.TS#
and f.tablespace_name = g.NAME)
group by rtime)
select tmp.rtime,
tablespace_usedsize_kb,
tablespace_size_kb,
(tablespace_usedsize_kb –
LAG(tablespace_usedsize_kb, 1, NULL) OVER(ORDER BY tmp.rtime)) AS DIFF_KB
from tmp,
(select min(rtime) rtime
from tmp
group by substr(rtime, 1, 10)) FP_ZZSPTFP where FP_ZZSPTFP.rtime = tmp.rtime
SQL script: lists the historical change information about the space used by the segment object during the snapshot time:
column owner format a16
column object_name format a36
column start_day format a11
column block_increase format 9999999999
select obj.owner, obj.object_name,
to_char(sn.BEGIN_INTERVAL_TIME,’RRRR-MON-DD’) start_day,
sum(a.db_block_changes_delta) block_increase
from dba_hist_seg_stat a,
dba_hist_snapshot sn,
dba_objects obj
where sn.snap_id = a.snap_id
and obj.object_id = a.obj#
and obj.owner not in (‘SYS’,’SYSTEM’)
and end_interval_time between to_timestamp(’17-FEB-2014′,’DD-MON-RRRR’)
and to_timestamp(’25-FEB-2014′,’DD-MON-RRRR’)
group by obj.owner, obj.object_name,
to_char(sn.BEGIN_INTERVAL_TIME,’RRRR-MON-DD’)
order by obj.owner, obj.object_name ;