What is a partition table? Why use partition table? How to create a partition table?

If the data in a table in your database meets the following conditions, you should consider creating a partitioned table.
1. There are a lot of data in a table in the database. What are many concepts? Ten thousand? 20000? One hundred thousand or one million? This, I think, is a matter of different opinions. Of course, there are so many data in the data table that you obviously feel that the data is very slow when querying. Then, you can consider using the sub table. If I have to say a value, I think it is 1 million.

2. However, too much data is not the only condition for creating a partitioned table. Even if you have 10 million records, but these 10 million records are common records, you'd better not use a partitioned table, which may outweigh the gains. Only if your data is segmented data, you should consider whether you need to use partitioned tables.

3. What is data segmentation? Although this statement is not professional, it is easy to understand. For example, your data is separated by years. For this year's data, you often add, modify, delete and query. For the data of previous years, you hardly need operations, or your operations are often limited to query. Congratulations, you can use the partition table. In other words, if your operations on data often involve only some data rather than all data, then you can consider what partition table.

So, what is a partition table?
To put it simply, a partitioned table is to divide a large table into several small tables. Suppose that you have a sales record table that records the sales of each mall, then you can divide the sales record table into several small tables according to time, for example, five small tables. One table is used for records before 2009, one table for records in 2010, one table for records in 2011, one table for records in 2012 and one table for records after 2012. Then, if you want to query the records of which year, you can query in the corresponding table. Since the number of records in each table is less, the query time will naturally be reduced.
However, the processing method of dividing a large table into several small tables will increase the difficulty of programming for programmers. Take adding records as an example. The above five tables are five independent tables. When adding records at different times, programmers should use different SQL statements. For example, when adding records in 2011, programmers should add records to the table in 2011; When adding records in 2012, the programmer should add the records to the table in 2012. In this way, the workload of programmers will increase and the possibility of errors will increase.

Using partition table can solve the above problems. Partition table can physically divide a large table into several small tables, but logically, it is still a large table.
Then, in the above example, the partition table can divide a sales record table into five physical small tables, but for the programmer, he is still facing a large table. Whether he adds records in 2010 or 2012, it does not need to be considered by the programmer. He just needs to insert the records into the sales record table - the large table in the logic. SQL Server will automatically put it in the small physical table where it should stay.
Similarly, for the query, the programmer only needs to set the query conditions. If OK, SQL Server will automatically query in the corresponding table without too much care.

According to YEAR: INTERVAL(NUMTOYMINTERVAL(1 'YEAR'))
According to MONTH: INTERVAL(NUMTOYMINTERVAL(1 'MONTH'))
According to DAY: interval (nummodsinterval (1 'DAY'))
According to the HOUR, MINUTE and SECOND: nummodsinterval (n, {DAY '|' HOUR '|' MINUTE '|' SECOND '})

The data is for reference only

create table nwom.LTE_PARAM_CHEK_5G(
start_time date,
time_stamp date,          --time stamp
MinimumRxLevel varchar2(128),   --Minimum receiving level
NonIntraFreqMeassrpThld varchar2(128),  --Different frequency measurement RSRP Trigger threshold
ServFreqLowPriRsrpReselhd varchar2(128), --Service frequency low priority RSRP Reselection threshold
EutranFreqLowPriReselThld varchar2(128), --E-UTRAN Frequency point low priority reselection threshold
NMinimumRxLevel varchar2(128),     --NMinimumRxLevel Minimum receiving level
EUTRAN_HO_SWITCH varchar2(128), --Switching mode switch-switch
EUTRAN_REDIRECT_SWITCH varchar2(128), --Switching mode switch-redirect
InterRatHoA1RsrpThld varchar2(128),  --Different system switching A1 RSRP threshold
InterRatHoA2RsrpThld varchar2(128),  --Different system switching A2 RSRP threshold
CovHoToEutranBlndA2Thld varchar2(128), --Override based switch to E-UTRAN blind A2 RSRP threshold
CovHoToEutranBRsrpThld1 varchar2(128),--Override based switch to E-UTRAN B2 RSRP Threshold 1
CovBasedHoB1RsrpThld varchar2(128),  --Overlay based switching B1 RSRP threshold
NrHoSwitch varchar2(128), --NR Change-over switch
NrRedirectSwtch varchar2(128),  --NR Redirection switch
Sib24Switch varchar2(128),  --SIB24 switch
ServBaseNrHoSwitch varchar2(128), --Business based E-UTRAN switch/Redirect to NR switch
CoverageScenario varchar2(128),--8 Antenna coverage scene
NrDuCellDrxAlgSwitch varchar2(128), --CDRX switch
PsclA2RsrpThld varchar2(128),--delete SCGA2
B1ThresholdDCNR varchar2(128) --Add threshold

) tablespace cm
partition by range (START_TIME)
(
  partition P_1D_20210302 values less than (TO_DATE(' 2021-03-18 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    tablespace CM)

create unique index nwom.LTE_PARAM_CHECK_5G_UIDX on nwom.LTE_PARAM_CHEK_5G(START_TIME,dn_cu,dn_du) tablespace cm local


COMMENT ON COLUMN nwom.LTE_PARAM_CHECK_5G.MinimumRxLevel  IS             'Minimum receiving level'                  
COMMENT ON COLUMN nwom.LTE_PARAM_CHECK_5G.NonIntraFreqMeasRsrpThld  IS   'Different frequency measurement RSRP Trigger threshold'        
COMMENT ON COLUMN nwom.LTE_PARAM_CHECK_5G.ServFreqLowPriRsrpReselThd  IS 'Service frequency low priority RSRP Reselection threshold'        
COMMENT ON COLUMN nwom.LTE_PARAM_CHECK_5G.EutranFreqLowPriReselThld  IS  'E-UTRAN Frequency point low priority reselection threshold'     
COMMENT ON COLUMN nwom.LTE_PARAM_CHECK_5G.NMinimumRxLevel  IS             'N Minimum receiving level'                  
COMMENT ON COLUMN nwom.LTE_PARAM_CHECK_5G.EUTRAN_HO_SWITCH  IS           'Switching mode switch-switch'               
COMMENT ON COLUMN nwom.LTE_PARAM_CHECK_5G.EUTRAN_REDIRECT_SWITCH  IS     'Switching mode switch-redirect'              
COMMENT ON COLUMN nwom.LTE_PARAM_CHECK_5G.InterRatHoA1RsrpThld  IS       'Different system switching A1 RSRP threshold'          
COMMENT ON COLUMN nwom.LTE_PARAM_CHECK_5G.InterRatHoA2RsrpThld  IS       'Different system switching A2 RSRP threshold'          
COMMENT ON COLUMN nwom.LTE_PARAM_CHECK_5G.CovHoToEutranBlindA2Thld  IS   'Override based switch to E-UTRAN blind A2 RSRP door'
COMMENT ON COLUMN nwom.LTE_PARAM_CHECK_5G.CovHoToEutranB2RsrpThld1  IS   'Override based switch to E-UTRAN B2 RSRP door'
COMMENT ON COLUMN nwom.LTE_PARAM_CHECK_5G.CovBasedHoB1RsrpThld  IS       'Overlay based switching B1 RSRP threshold'     
COMMENT ON COLUMN nwom.LTE_PARAM_CHECK_5G.NrHoSwitch   IS                 'NR Change-over switch'                  
COMMENT ON COLUMN nwom.LTE_PARAM_CHECK_5G.NrRedirectSwitch   IS           'NR Redirection switch'                 
COMMENT ON COLUMN nwom.LTE_PARAM_CHECK_5G.Sib24Switch   IS                'SIB24 switch'                 
COMMENT ON COLUMN nwom.LTE_PARAM_CHECK_5G.ServBasedNrHoSwitch  IS        'Business based E-UTRAN switch/Redirect to NR switch' 
COMMENT ON COLUMN nwom.LTE_PARAM_CHECK_5G.CoverageScenario    IS           '8 Antenna coverage scene'                 
COMMENT ON COLUMN nwom.LTE_PARAM_CHECK_5G.NrDuCellDrxAlgoSwitch  IS      'CDRX switch'                  
COMMENT ON COLUMN nwom.LTE_PARAM_CHECK_5G.PscellA2RsrpThld   IS           'delete SCGA2'                 
COMMENT ON COLUMN nwom.LTE_PARAM_CHECK_5G.B1ThresholdENDCNR   IS          'Add threshold'

(1)OPERATION_TIME is the partition key. A partition will be automatically created every month. The partition key cannot be null.

(2)alter table table_name enable row movement; It means that the partition key of the partition table is updatable. When a row is updated, if the updated column does not belong to the original partition, if this option is enabled, the row will be deleted from the partition and added to the partition to which it belongs. At this time, the rowid will be changed. It is equivalent to an implicit delete+insert, but the insert/delete trigger will not be triggered. If this option is not enabled, an error will be reported in the update.