Running TRUNCATE PARTITION
works fine
DROP DATABASE IF EXISTS partest;
CREATE DATABASE partest;
USE partest
CREATE TABLE rolando (
id int not null auto_increment,
LogDate DATE,
PRIMARY KEY (id,LogDate)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
PARTITION BY RANGE (DAYOFYEAR(`LogDate`))
(PARTITION p1 VALUES LESS THAN (8) ENGINE = InnoDB,
PARTITION p2 VALUES LESS THAN (15) ENGINE = InnoDB,
PARTITION p3 VALUES LESS THAN (22) ENGINE = InnoDB,
PARTITION p4 VALUES LESS THAN (29) ENGINE = InnoDB,
PARTITION p5 VALUES LESS THAN (36) ENGINE = InnoDB,
PARTITION p6 VALUES LESS THAN (43) ENGINE = InnoDB,
PARTITION p7 VALUES LESS THAN (50) ENGINE = InnoDB,
PARTITION p8 VALUES LESS THAN (57) ENGINE = InnoDB,
PARTITION p9 VALUES LESS THAN (64) ENGINE = InnoDB,
PARTITION p51 VALUES LESS THAN (358) ENGINE = InnoDB,
PARTITION p52 VALUES LESS THAN MAXVALUE ENGINE = InnoDB);
ALTER TABLE rolando TRUNCATE PARTITION p4;
Here is the output
mysql> DROP DATABASE IF EXISTS partest;
Query OK, 1 row affected (1.55 sec)
mysql> CREATE DATABASE partest;
Query OK, 1 row affected (0.00 sec)
mysql> USE partest
Database changed
mysql> CREATE TABLE rolando (
-> id int not null auto_increment,
-> LogDate DATE,
-> PRIMARY KEY (id,LogDate)
-> ) ENGINE=InnoDB DEFAULT CHARSET=utf8
-> PARTITION BY RANGE (DAYOFYEAR(`LogDate`))
-> (PARTITION p1 VALUES LESS THAN (8) ENGINE = InnoDB,
-> PARTITION p2 VALUES LESS THAN (15) ENGINE = InnoDB,
-> PARTITION p3 VALUES LESS THAN (22) ENGINE = InnoDB,
-> PARTITION p4 VALUES LESS THAN (29) ENGINE = InnoDB,
-> PARTITION p5 VALUES LESS THAN (36) ENGINE = InnoDB,
-> PARTITION p6 VALUES LESS THAN (43) ENGINE = InnoDB,
-> PARTITION p7 VALUES LESS THAN (50) ENGINE = InnoDB,
-> PARTITION p8 VALUES LESS THAN (57) ENGINE = InnoDB,
-> PARTITION p9 VALUES LESS THAN (64) ENGINE = InnoDB,
-> PARTITION p51 VALUES LESS THAN (358) ENGINE = InnoDB,
-> PARTITION p52 VALUES LESS THAN MAXVALUE ENGINE = InnoDB);
Query OK, 0 rows affected (2.60 sec)
mysql> ALTER TABLE rolando TRUNCATE PARTITION p4;
Query OK, 0 rows affected (0.25 sec)
mysql>
OK, fine. It works. However, doing this is logically unsafe. Why ? The partitioned table is based on DAYOFYEAR(datetime_col))
. Since each partition contains a week for multiple years, running alter table t1 truncate partition p4;
would wipe out week 4 from multiple years.
OK, let's try dropping and adding the partition
mysql> ALTER TABLE rolando DROP PARTITION p4;
Query OK, 0 rows affected (0.41 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE rolando ADD PARTITION
-> (PARTITION p4 VALUES LESS THAN (29) ENGINE = InnoDB);
ERROR 1481 (HY000): MAXVALUE can only be used in last partition definition
mysql>
This makes sense since MAXVALUE
already exists in the definition. This prevents you from adding (appending) a partition if MAXVALUE
is present.
While you could play games with ALTER TABLE ... REORGANIZE PARTITION
, the complexity isn't worth it and you won't achieve the result of wedging in a partition.
Rather that explanining further partition machinations, just do the following
- Make sure an index exists on the date field
- Perform the
DELETE
SQL already mentioned
An alternative would be to create a table with a weekly partition then drop the exact week.
I would consider using a date range as the partitioning scheme, and splitting by week which you can do using a function against the timestamp field.
Using days would create too many partitions, and months would not help your queries much especially when the range spans two months.
Using the range partition, you can create mutliple partitions covering your active time period, and out into the future for a year, then nearer the end of that range you can add partitions to the schema as required.
If necessary, you can also drop the old partitions when they are no longer needed.
I can't prepare a sample partition schema for you, as I'm running short of time at the moment, but the MySQL docs have pretty good coverage for this type of partitioning.
Hope that helps,
Dave
Best Answer
AskTom (answered by Connor McDonald) : ORA - 08103:Object No Longer Exists and an even more detailed explanation by Connor McDonald: A cool thing with EXCHANGE PARTITION (recommended by AskTom here).
TRUNCATE TABLE is a DDL statement, not a DML statement like delete. But what happens is a phenomena that is similar to a phenomena that can be caused by delete or update statements.
If delete or update statements are involved then we have
There is also an AskTom article for ORA-01555
What happens:
Assume in your transaction you start a SELECT statement on a table. During this select statement another transaction manipulates the data:
In this case your session that executes the select statement cannot find the data on its original place because the table has already changed. But it is necessary to retrieve the table data in a consistent way, this means not need the data from the table from the point in time when the query started. YOur session it may be able to find the data in a rollback segment (in case of a DML statement) or in the table whose segment you have exchanged with the partition (in case of exchange partition) or it is a segment that is not assigned to a table anymore but is still available and unaltered on disk (DDL statement). If Oracle finds all the data it can continue with your select statement. If it does not find the data, either because the rollback segment where already overwritten (DML case) or the segment was already modified or deleted (DDL case) then Oracle will throw an error. in the DML case Oracle will throw an ORA-01555, in the DDL case it will throw a ORA-08103.
Connor McDonald writes in the first article referenced:
Both articles are very instructive and I recommend you to read both (the second one is a video)
So how can you handle this problem?