Consider the following statement on a RANGE partitioned table:
ALTER TABLE orders DROP PARTITION p1, p3;
What is the outcome of executing the above statement?
A.
Only the first partition (p1) will be dropped as only one can be dropped at any time.
B.
All data in p1 and p3 partitions are removed, but the table definition remains unchanged.
C.
A syntax error will result as you cannot specify more than one partition in the same statement.
D.
All data in pi and p3 partitions are removed and the table definition is changed.
Explanation:
Reference: http://docs.oracle.com/cd/F49540_01/DOC/server.815/a67772/partiti.htm
D.
B
Reference: http://docs.oracle.com/cd/F49540_01/DOC/server.815/a67772/partiti.htm
D. -> http://mysqlresources.com/documentation/db-table-schema/alter-table-partition-altering-clauses
Tested.
D
if you drop partition, the data will lose and all partition that you drop will deleted.
Reference :http://dev.mysql.com/doc/refman/5.6/en/alter-table-partition-operations.html
Definitely D – have tested
Data lost and Defnition changed
D
B.
Table definition does NOT change – if you execute desc ; the result will be the same after dropping these partitions.
Definition definitely *DOES* change:
MySQL [test]> CREATE TABLE t1 (
-> id INT,
-> year_col INT
-> )
-> PARTITION BY RANGE (year_col) (
-> PARTITION p0 VALUES LESS THAN (1991),
-> PARTITION p1 VALUES LESS THAN (1995),
-> PARTITION p2 VALUES LESS THAN (1999),
-> PARTITION p3 VALUES LESS THAN (2003),
-> PARTITION p4 VALUES LESS THAN (2007)
-> );
Query OK, 0 rows affected (0.45 sec)
MySQL [test]> altert table t1 drop partition p0, p1;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘altert table t1 drop partition p0, p1’ at line 1
MySQL [test]> alter table t1 drop partition p0, p1;
Query OK, 0 rows affected (0.30 sec)
Records: 0 Duplicates: 0 Warnings: 0
MySQL [test]> show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` int(11) DEFAULT NULL,
`year_col` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
/*!50100 PARTITION BY RANGE (year_col)
(PARTITION p2 VALUES LESS THAN (1999) ENGINE = InnoDB,
PARTITION p3 VALUES LESS THAN (2003) ENGINE = InnoDB,
PARTITION p4 VALUES LESS THAN (2007) ENGINE = InnoDB) */
1 row in set (0.21 sec)
“if you execute desc ; the result will be the same”
“Desc” does not show you the full definition of the table – it will not show you partition information. As demonstrated, the definition does change and the correct answer is D