Pit, nickname pit building Lord the king, not write the code is filling holes everywhere,,,,,,,
Currently have a project to take over to tackle the problem of a few what said to me before the SQL look not to come out, ok, I want to a nothing important also, estimation is adjusted under the SQL statements, not to increase the index, after yao when I saw the real data, I silly force,,,
Business table, there are mainly two, an order the main table, a commodity list and customer need many query statements, at present, the two tables I need data, but the current order data in the table has reached 350 million, and goods list, at least, is 1:2, the relationship between the at least 700 million, collection table the estimate is almost 700 million, and the main table daily to 40 w increasing data, increasing data table is 100 w,,,

CodePudding user response:

The current query data of a day, barely, 2-3 days to check a data will first go to smoke a cigarette, to play a circle, to check the data of one week, ha ha!

CodePudding user response:

Partitions, so big table incredibly don't partition

CodePudding user response:

Order is outdated and rectified may have a look at, can do data archiving,, otherwise don't so large amount of data partition, all together, is there any indexes have what meaning,

CodePudding user response:

Check the data of one day, or a week, is what's look like?
Specific issues specific analysis, we can describe

CodePudding user response:

In accordance with the time partition as one of the following Monday partition example directly copy others work,
The create table liusheng (orderid number (10), name varchar2 (10), ls_date date);
Insert into liusheng values (1, 'ls1, to_date (' 1981-01-02', '- dd yyyy - mm));
Insert into liusheng values (1, 'ls2, to_date (' 1998-01-03', '- dd yyyy - mm));
Insert into liusheng values (1, 'you', to_date (' 1999-01-04 ', '- dd yyyy - mm));
Insert into liusheng values (1, 'ls4 to_date (' 2000-01-05', '- dd yyyy - mm));
Insert into liusheng values (1, 'ls5 to_date (' 2000-01-06', '- dd yyyy - mm));
Insert into liusheng values (1, 'ls6, to_date (' 2001-01-07', '- dd yyyy - mm));
Insert into liusheng values (1, 'ls7, to_date (' 2001-01-08', '- dd yyyy - mm));
Insert into liusheng values (1, 'ls8 to_date (' 2002-01-09', '- dd yyyy - mm));
Insert into liusheng values (1, 'ls9, to_date (' 2002-01-10', '- dd yyyy - mm));
Insert into liusheng values (1, 'ls10 to_date (' 2011-01-11', '- dd yyyy - mm));
Create a range partition table
The create table liusheng_part
Partition by range (ls_date)
(
Partition liusheng_part_1999_1 values less than (to_date (' 1999-01-01 ', '- dd yyyy - mm)),
Partition liusheng_part_2000_1 values less than (to_date (' 2000-01-01 ', '- dd yyyy - mm)),
Partition liusheng_part_2001_1 values less than (to_date (' 2001-01-01 ', '- dd yyyy - mm)),
Partition liusheng_part_2002_1 values less than (to_date (' 2002-01-01 ', '- dd yyyy - mm)),
Partition liusheng_part_2003_1 values less than (to_date (' 2003-01-01 ', '- dd yyyy - mm)),
Partition other values less than (maxvalue)
)
As select * from liusheng;
Check the partition
Select * from liusheng_part partition (liusheng_part_1999_1);
Select * from liusheng_part partition (liusheng_part_2000_1);
Select * from liusheng_part partition (liusheng_part_2001_1);
Select * from liusheng_part partition (liusheng_part_2002_1);
Select * from liusheng_part partition (liusheng_part_2003_1);
Select * from liusheng_part partition (other);

CodePudding user response:

Worship the great god of the pupil, don't know the partition

CodePudding user response:

Can also according to the time node for a business data archive
To the data before the date of a cut out