Oracle Big Data volume. Table Partitioning hints Query efficiency

Now the business has a Usertrack log record table. 300,000 data per day is generated. Large amount of data query efficiency will be very slow

So I'm thinking of using table partitioning to suggest that efficiency is logically a table. But partitioning will divide the data into different physical files according to the partitioning criteria

Advantages:

1) Improve query performance: Queries on partitioned objects can search only the partitions they care about and improve the retrieval speed.

2) Enhanced usability: If one partition of the table fails, the data for the table on the other partitions is still available;

3) Easy maintenance: If a partition of the table fails, the data needs to be repaired, only the partition can be repaired;

4) Balanced I/O: Different partitions can be mapped to disk to balance I/O and improve overall system performance.

Disadvantages:

Partitioned table-related, existing tables have no methods that can be converted directly into partitioned tables.

Table partitioning is mainly divided into the range partition list partition hash partition combination range hash partition compound range hash partition (this big blog says very detailed: http://www.cnblogs.com/leiOOlei/archive/2012/06/ 08/2541306.html)

My business scenarios choose to use range partitioning

Because partitioned tables cannot be created on existing tables, you want to back up the data to delete the original table and recreate the table as a partitioned table

--Backing up data to a temporary table
create TABLE Usertracktemp

As SELECT * FROM  usertrack

　

-- Check that the data is consistent
Select Count (1 from usertracktemp
SelectCount(1from Usertrack

-- Delete a table
truncate Table usertrack;  
 Drop Table Usertrack

--Create a table and create a partition
Create TableUsertrack PARTITION byRANGE (createtime) (PARTITION prot20151101ValuesLess THAN (To_date ('2015-11-01 00:00:00','YYYY-MM-DD Hh24:mi:ss')), PARTITION prot20151201ValuesLess THAN (To_date ('2015-12-01 00:00:00','YYYY-MM-DD Hh24:mi:ss')), PARTITION prot20160201ValuesLess THAN (To_date ('2016-02-01 00:00:00','YYYY-MM-DD Hh24:mi:ss')), PARTITION prot20160301ValuesLess THAN (To_date ('2016-03-01 00:00:00','YYYY-MM-DD Hh24:mi:ss')), PARTITION prot20160401ValuesLess THAN (To_date ('2016-04-01 00:00:00','YYYY-MM-DD Hh24:mi:ss')), PARTITION prot20160501ValuesLess THAN (To_date ('2016-05-01 00:00:00','YYYY-MM-DD Hh24:mi:ss')), PARTITION ProtmaxtValuesLess THAN (maxvalue)) as  
Select  *
   fromUsertracktemp

I can clear the data in this range. Partitioning by month so it was written straight to death. Create a partitioned table first if you cannot specify it. Write a stored procedure time grouping. Then create a new partition on the partition table with the pointer traversal

A few common commands

-- querying data
for a specified partition Select  from Usertrack PARTITION (PROTMAXT)

-- query all partitions
below the specified table Select *  from where segment_type='TABLE PARTITION' and Segment_name = ' Usertrack ';

Segment_name the name of the table remember the uniform capitalization

 --  create partition syntax when MaxValue partition condition  alter  table  usertracktest SPLIT PARTITION protmaxt at (to_date ( '   

-- When there are no maxvalue partitioning conditions
ALTER TABLE ADD VALUES Less THAN (to_date ('2003-06-01','yyyy-mm-dd'

At this point, I'm going to create a new job. Once a month is created. And then the data generated by the current month is inserted into the partition of the heart.

Create a new stored procedure that executes once a month

Create or Replace procedureCreatePartition as
--declaring VariablesPartitionNamevarchar( -); Createsqltextvarchar( -);
Partitiondate date; beginpartitiondate:=Add_months (Sysdate,1); PartitionName:=' Part'||To_char (Partitiondate,'YYYYMMDD');
Createsqltext:='ALTER TABLE usertracktest SPLIT PARTITION protmaxt at (to_date (" "||To_char (Partitiondate,'YYYY-MM-DD Hh24:mi:ss')||" ","'YYYY-MM-DD Hh24:mi:ss"') into (PARTITION'||PartitionName||', PARTITION Protmaxt) UPDATE GLOBAL INDEXES';
 Dbms_output.put_line (Createsqltext); Executeimmediate createsqltext;End;

Create a new job

　

--Query work
SELECT * FROM Sys.user_jobs

Common time interval directives

1: Execute Every Minute

Interval = TRUNC (sysdate, ' mi ') + 1/(24*60)

Or

Interval = sysdate+1/1440

2: Regular execution every day

Example: Daily 1 o'clock in the morning execution

Interval = TRUNC (sysdate) + 1 +1/(24)

3: Regular Weekly execution

For example: Every Monday 1 o'clock in the morning execution

Interval = TRUNC (Next_day (sysdate, ' Monday ')) +1/24

4: Regular Monthly execution

For example: 1st 1 o'clock in the morning every month to execute

Interval =>trunc (Last_day (sysdate)) +1+1/24

5: Quarterly Scheduled execution

For example, the first day of each quarter is 1 o'clock in the morning execution

Interval = TRUNC (Add_months (sysdate,3), ' Q ') + 1/24

6: Every half-yearly scheduled execution

For example: Every July 1 and January 1 1 o'clock in the morning

Interval = Add_months (trunc (sysdate, ' yyyy '), 6) +1/24

7: Scheduled execution every year

For example: January 1 1 o'clock in the morning every year to execute

Interval =>add_months (trunc (sysdate, ' yyyy '), 12) +1/24

Performance testing

Do not add any query criteria

Filtering by using a partition condition

It can be seen that each metric partition is better than a table without partitions.

Oracle Big Data volume. Table Partitioning hints Query efficiency