Description:
query result changed after UPDATE HISTOGRAM, caused by query plan changed to "skip scan",like this:
mysql> explain analyze SELECT t2.c0 AS ref0, t2.c1 AS ref1, t2.c2 AS ref2, t2.c3 AS ref3, t2.vc5 AS ref4, t2.vc7 AS ref5 FROM t2 WHERE (! ((t2.c3) >= ('2013-08-06')));
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Filter: (t2.c3 < '2013-08-06') (cost=165 rows=544) (actual time=0.0311..2.06 rows=1024 loops=1)
-> Covering index scan on t2 using i0 (cost=165 rows=1632) (actual time=0.0278..1.62 rows=1632 loops=1)
|
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set, 9 warnings (0.01 sec)
mysql> ANALYZE TABLE t2 UPDATE HISTOGRAM ON c0, c1, c2, c3, vc5, vc7;
+----------------+-----------+----------+------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+----------------+-----------+----------+------------------------------------------------+
| 0304test156.t2 | histogram | status | Histogram statistics created for column 'c0'. |
| 0304test156.t2 | histogram | status | Histogram statistics created for column 'c1'. |
| 0304test156.t2 | histogram | status | Histogram statistics created for column 'c2'. |
| 0304test156.t2 | histogram | status | Histogram statistics created for column 'c3'. |
| 0304test156.t2 | histogram | status | Histogram statistics created for column 'vc5'. |
| 0304test156.t2 | histogram | status | Histogram statistics created for column 'vc7'. |
+----------------+-----------+----------+------------------------------------------------+
6 rows in set (0.05 sec)
mysql> explain analyze SELECT t2.c0 AS ref0, t2.c1 AS ref1, t2.c2 AS ref2, t2.c3 AS ref3, t2.vc5 AS ref4, t2.vc7 AS ref5 FROM t2 WHERE (! ((t2.c3) >= ('2013-08-06')));
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| -> Filter: (t2.c3 < '2013-08-06') (cost=108 rows=112) (actual time=0.143..0.715 rows=112 loops=1)
-> Covering index skip scan on t2 using i0 over NULL < c3 <= '00:20:13' (cost=108 rows=112) (actual time=0.138..0.672 rows=112 loops=1)
|
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
How to repeat:
CREATE TABLE `t2` (
`c0` datetime /*!50606 STORAGE DISK */ /*!50606 COLUMN_FORMAT FIXED */ DEFAULT NULL COMMENT 'asdf',
`c1` smallint DEFAULT NULL COMMENT 'asdf',
`c2` year /*!50606 STORAGE MEMORY */ /*!50606 COLUMN_FORMAT DYNAMIC */ DEFAULT NULL COMMENT 'asdf',
`c3` time /*!50606 STORAGE DISK */ DEFAULT NULL,
`vc5` decimal(10,0) DEFAULT NULL /*!80023 INVISIBLE */,
`vc7` time DEFAULT NULL,
UNIQUE KEY `i0` (`c0` DESC,`c1`,`c2`,`c3`,`vc5`,`vc7`),
UNIQUE KEY `i3` (`c0`,`c1`,`c2`,`c3`,`vc5`,`vc7` DESC),
UNIQUE KEY `i4` (`c0`,`c1`,`c2`,`c3` DESC,`vc5`,`vc7`),
KEY `i43` (`c0`,`c1`,`c2`,`c3`,`vc5`,`vc7`),
KEY `i1` (`c0`,`c1`,`c2` DESC,`c3` DESC,`vc5`,`vc7`) /*!80000 INVISIBLE */,
KEY `i2` (`c0`,`c1` DESC,`c2`,`c3`,`vc5`,`vc7` DESC),
KEY `i5` (`c0`,`c1` DESC,`c2`,`c3` DESC,`vc5` DESC,`vc7`),
KEY `i6` (`c0`,`c1`,`c2`,`c3`,`vc5`,`vc7`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO `t2` (`c0`, `c1`, `c2`, `c3`, `vc5`, `vc7`) VALUES ('2525-01-01 00:00:00',-32768,NULL,'05:47:37',NULL,NULL),('2036-06-04 14:42:39',NULL,2145,NULL,NULL,NULL),('2034-10-25 05:36:39',-32768,2051,'06:48:40',NULL,NULL),('2032-01-15 19:25:01',15429,NULL,'11:52:20',NULL,NULL),('2030-11-15 00:01:28',-32768,NULL,'11:09:50',NULL,NULL),('2030-04-16 09:27:12',17423,2023,NULL,NULL,NULL),('2029-08-19 07:17:07',NULL,NULL,NULL,NULL,NULL),('2029-06-15 07:17:03',32767,NULL,NULL,NULL,NULL),('2028-07-29 16:03:13',27,NULL,'14:51:00',NULL,NULL),('2027-11-24 15:28:04',32767,2024,'22:28:04',NULL,NULL),('2027-10-02 23:56:09',32767,NULL,NULL,NULL,NULL),('2027-02-04 04:46:23',-32768,NULL,NULL,NULL,NULL),('2026-12-22 02:11:14',NULL,NULL,NULL,NULL,NULL),('2024-02-29 00:00:00',12061,2155,NULL,NULL,NULL),('2021-05-31 03:57:57',-32768,NULL,'00:00:00',NULL,NULL),('2020-08-05 05:11:24',NULL,2058,'03:15:09',NULL,NULL),('2018-07-25 13:54:23',32767,NULL,NULL,NULL,NULL),('2016-11-15 10:04:20',32767,0000,NULL,NULL,NULL),('2013-01-10 13:20:00',NULL,NULL,'16:47:57',NULL,NULL),('2011-07-22 00:20:01',-91,0000,NULL,NULL,NULL),('2007-02-23 12:27:00',-32768,0000,'09:22:11',NULL,NULL),('2005-10-21 05:55:22',-66,NULL,'12:57:22',NULL,NULL),('2005-06-01 08:42:25',NULL,NULL,NULL,NULL,NULL),('2004-09-27 09:47:00',32767,0000,'21:11:13',NULL,NULL),('2003-08-09 09:55:24',-32768,NULL,NULL,NULL,NULL),('2000-01-01 00:00:00',NULL,1963,'18:35:43',NULL,NULL),('1995-09-20 21:24:09',-20243,NULL,NULL,NULL,NULL),('1994-04-03 06:35:00',NULL,1970,'03:54:13',NULL,NULL),('1994-03-12 07:12:02',106,2140,'12:49:13',NULL,NULL),('1991-01-11 19:58:33',7,2111,'20:43:31',NULL,NULL),('1989-12-12 06:47:05',NULL,2038,NULL,NULL,NULL),('1986-06-26 07:16:19',-32768,NULL,NULL,NULL,NULL),('1983-02-07 15:30:00',NULL,2068,NULL,NULL,NULL),('1980-11-30 11:54:58',NULL,NULL,NULL,NULL,NULL),('1980-11-25 11:18:01',-124,0000,'02:45:26',NULL,NULL),('1978-08-09 18:14:02',19046,2014,'09:18:36',NULL,NULL),('1977-12-06 18:51:08',NULL,1945,'12:37:05',NULL,NULL),('1976-09-29 02:16:33',3890,2058,'00:26:00',NULL,NULL),('1972-09-21 15:25:29',NULL,0000,NULL,NULL,NULL),('1971-01-05 02:52:30',-24617,0000,'18:05:41',NULL,NULL),('1969-12-31 23:59:59',NULL,NULL,'08:21:26',NULL,NULL),('0000-00-00 00:00:00',NULL,NULL,NULL,NULL,NULL),('0000-00-00 00:00:00',NULL,1983,NULL,NULL,NULL),('0000-00-00 00:00:00',NULL,1983,'-25:30:45',NULL,NULL),('0000-00-00 00:00:00',NULL,1983,'00:00:00',NULL,NULL),('0000-00-00 00:00:00',NULL,1983,'00:00:01',NULL,NULL),('0000-00-00 00:00:00',NULL,1983,'00:48:27',NULL,NULL),('0000-00-00 00:00:00',NULL,1983,'01:51:14',NULL,NULL),('0000-00-00 00:00:00',NULL,1983,'06:43:48',NULL,NULL),('0000-00-00 00:00:00',NULL,1983,'09:27:41',NULL,NULL),('0000-00-00 00:00:00',NULL,1983,'10:45:32',NULL,NULL),('0000-00-00 00:00:00',NULL,1983,'11:03:14',NULL,NULL),('0000-00-00 00:00:00',NULL,1983,'11:09:28',NULL,NULL),('0000-00-00 00:00:00',NULL,1983,'11:23:42',NULL,NULL),('0000-00-00 00:00:00',NULL,1983,'11:50:42',NULL,NULL),('0000-00-00 00:00:00',NULL,1983,'12:03:57',NULL,NULL),('0000-00-00 00:00:00',NULL,1983,'12:14:51',NULL,NULL),('0000-00-00 00:00:00',NULL,1983,'12:16:52',NULL,NULL),('0000-00-00 00:00:00',NULL,1983,'12:34:57',NULL,NULL),('0000-00-00 00:00:00',NULL,1983,'13:37:41',NULL,NULL),('0000-00-00 00:00:00',NULL,1983,'13:41:06',NULL,NULL),('0000-00-00 00:00:00',NULL,1983,'16:42:53',NULL,NULL),('0000-00-00 00:00:00',NULL,1983,'20:03:16',NULL,NULL),('0000-00-00 00:00:00',-32768,0000,NULL,NULL,NULL),('0000-00-00 00:00:00',-32768,1983,'15:14:21',NULL,NULL),('0000-00-00 00:00:00',-32768,1983,'24:00:00',NULL,NULL),('0000-00-00 00:00:00',-30800,1983,NULL,NULL,NULL),('0000-00-00 00:00:00',7,NULL,'-25:30:45',NULL,NULL),('0000-00-00 00:00:00',35,1983,NULL,NULL,NULL),('0000-00-00 00:00:00',49,1983,'13:04:53',NULL,NULL),('0000-00-00 00:00:00',74,1983,NULL,NULL,NULL),('0000-00-00 00:00:00',89,1983,NULL,NULL,NULL),('0000-00-00 00:00:00',18520,1983,NULL,NULL,NULL),('0000-00-00 00:00:00',32767,NULL,NULL,NULL,NULL),('0000-00-00 00:00:00',32767,NULL,'10:18:07',NULL,NULL),('0000-00-00 00:00:00',32767,NULL,'20:07:46',NULL,NULL),('0000-00-00 00:00:00',32767,1974,'05:57:39',NULL,NULL),('0000-00-00 00:00:00',32767,1983,'15:02:49',NULL,NULL),('0000-00-00 00:00:00',32767,1983,'18:21:26',NULL,NULL),('0000-00-00 00:00:00',32767,2101,NULL,NULL,NULL),(NULL,NULL,NULL,'11:50:41',NULL,NULL),(NULL,NULL,NULL,'15:24:20',NULL,NULL),(NULL,NULL,NULL,'16:10:48',NULL,NULL),(NULL,NULL,NULL,'16:30:43',NULL,NULL),(NULL,NULL,NULL,'18:36:25',NULL,NULL),(NULL,NULL,0000,'05:16:01',NULL,NULL),(NULL,NULL,2062,'20:29:39',NULL,NULL),(NULL,NULL,2122,'00:49:46',NULL,NULL),(NULL,NULL,2130,'06:13:22',NULL,NULL),(NULL,NULL,2152,'12:41:30',NULL,NULL),(NULL,-32768,NULL,'-838:59:59',NULL,NULL),(NULL,-32768,NULL,'-25:30:45',NULL,NULL),(NULL,-32768,NULL,'14:57:10',NULL,NULL),(NULL,-32768,2117,'01:12:44',NULL,NULL),(NULL,5,NULL,'12:00:00',NULL,NULL),(NULL,78,NULL,NULL,NULL,NULL),(NULL,19627,NULL,NULL,NULL,NULL),(NULL,22796,NULL,NULL,NULL,NULL),(NULL,32767,NULL,NULL,NULL,NULL),(NULL,32767,NULL,'19:57:33',NULL,NULL),(NULL,32767,1978,'21:13:50',NULL,NULL),(NULL,32767,2038,'22:20:50',NULL,NULL);
insert into t2(c0,c1,c2,c3) select c0,c1,c2,c3 from t2;
insert into t2(c0,c1,c2,c3) select c0,c1,c2,c3 from t2;
insert into t2(c0,c1,c2,c3) select c0,c1,c2,c3 from t2;
insert into t2(c0,c1,c2,c3) select c0,c1,c2,c3 from t2;
SELECT t2.c0 AS ref0, t2.c1 AS ref1, t2.c2 AS ref2, t2.c3 AS ref3, t2.vc5 AS ref4, t2.vc7 AS ref5 FROM t2 WHERE (! ((t2.c3) >= ('2013-08-06')));
ANALYZE TABLE t2 UPDATE HISTOGRAM ON c0, c1, c2, c3, vc5, vc7;
SELECT t2.c0 AS ref0, t2.c1 AS ref1, t2.c2 AS ref2, t2.c3 AS ref3, t2.vc5 AS ref4, t2.vc7 AS ref5 FROM t2 WHERE (! ((t2.c3) >= ('2013-08-06')));
Suggested fix:
the root cause is:
1. after UPDATE HISTOGRAM, optimizer choose "skip scan" to execute this query;
2. using index skip scan, the query is changed;
Description: query result changed after UPDATE HISTOGRAM, caused by query plan changed to "skip scan",like this: mysql> explain analyze SELECT t2.c0 AS ref0, t2.c1 AS ref1, t2.c2 AS ref2, t2.c3 AS ref3, t2.vc5 AS ref4, t2.vc7 AS ref5 FROM t2 WHERE (! ((t2.c3) >= ('2013-08-06'))); +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Filter: (t2.c3 < '2013-08-06') (cost=165 rows=544) (actual time=0.0311..2.06 rows=1024 loops=1) -> Covering index scan on t2 using i0 (cost=165 rows=1632) (actual time=0.0278..1.62 rows=1632 loops=1) | +-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set, 9 warnings (0.01 sec) mysql> ANALYZE TABLE t2 UPDATE HISTOGRAM ON c0, c1, c2, c3, vc5, vc7; +----------------+-----------+----------+------------------------------------------------+ | Table | Op | Msg_type | Msg_text | +----------------+-----------+----------+------------------------------------------------+ | 0304test156.t2 | histogram | status | Histogram statistics created for column 'c0'. | | 0304test156.t2 | histogram | status | Histogram statistics created for column 'c1'. | | 0304test156.t2 | histogram | status | Histogram statistics created for column 'c2'. | | 0304test156.t2 | histogram | status | Histogram statistics created for column 'c3'. | | 0304test156.t2 | histogram | status | Histogram statistics created for column 'vc5'. | | 0304test156.t2 | histogram | status | Histogram statistics created for column 'vc7'. | +----------------+-----------+----------+------------------------------------------------+ 6 rows in set (0.05 sec) mysql> explain analyze SELECT t2.c0 AS ref0, t2.c1 AS ref1, t2.c2 AS ref2, t2.c3 AS ref3, t2.vc5 AS ref4, t2.vc7 AS ref5 FROM t2 WHERE (! ((t2.c3) >= ('2013-08-06'))); +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | EXPLAIN | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | -> Filter: (t2.c3 < '2013-08-06') (cost=108 rows=112) (actual time=0.143..0.715 rows=112 loops=1) -> Covering index skip scan on t2 using i0 over NULL < c3 <= '00:20:13' (cost=108 rows=112) (actual time=0.138..0.672 rows=112 loops=1) | +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ How to repeat: CREATE TABLE `t2` ( `c0` datetime /*!50606 STORAGE DISK */ /*!50606 COLUMN_FORMAT FIXED */ DEFAULT NULL COMMENT 'asdf', `c1` smallint DEFAULT NULL COMMENT 'asdf', `c2` year /*!50606 STORAGE MEMORY */ /*!50606 COLUMN_FORMAT DYNAMIC */ DEFAULT NULL COMMENT 'asdf', `c3` time /*!50606 STORAGE DISK */ DEFAULT NULL, `vc5` decimal(10,0) DEFAULT NULL /*!80023 INVISIBLE */, `vc7` time DEFAULT NULL, UNIQUE KEY `i0` (`c0` DESC,`c1`,`c2`,`c3`,`vc5`,`vc7`), UNIQUE KEY `i3` (`c0`,`c1`,`c2`,`c3`,`vc5`,`vc7` DESC), UNIQUE KEY `i4` (`c0`,`c1`,`c2`,`c3` DESC,`vc5`,`vc7`), KEY `i43` (`c0`,`c1`,`c2`,`c3`,`vc5`,`vc7`), KEY `i1` (`c0`,`c1`,`c2` DESC,`c3` DESC,`vc5`,`vc7`) /*!80000 INVISIBLE */, KEY `i2` (`c0`,`c1` DESC,`c2`,`c3`,`vc5`,`vc7` DESC), KEY `i5` (`c0`,`c1` DESC,`c2`,`c3` DESC,`vc5` DESC,`vc7`), KEY `i6` (`c0`,`c1`,`c2`,`c3`,`vc5`,`vc7`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; INSERT INTO `t2` (`c0`, `c1`, `c2`, `c3`, `vc5`, `vc7`) VALUES ('2525-01-01 00:00:00',-32768,NULL,'05:47:37',NULL,NULL),('2036-06-04 14:42:39',NULL,2145,NULL,NULL,NULL),('2034-10-25 05:36:39',-32768,2051,'06:48:40',NULL,NULL),('2032-01-15 19:25:01',15429,NULL,'11:52:20',NULL,NULL),('2030-11-15 00:01:28',-32768,NULL,'11:09:50',NULL,NULL),('2030-04-16 09:27:12',17423,2023,NULL,NULL,NULL),('2029-08-19 07:17:07',NULL,NULL,NULL,NULL,NULL),('2029-06-15 07:17:03',32767,NULL,NULL,NULL,NULL),('2028-07-29 16:03:13',27,NULL,'14:51:00',NULL,NULL),('2027-11-24 15:28:04',32767,2024,'22:28:04',NULL,NULL),('2027-10-02 23:56:09',32767,NULL,NULL,NULL,NULL),('2027-02-04 04:46:23',-32768,NULL,NULL,NULL,NULL),('2026-12-22 02:11:14',NULL,NULL,NULL,NULL,NULL),('2024-02-29 00:00:00',12061,2155,NULL,NULL,NULL),('2021-05-31 03:57:57',-32768,NULL,'00:00:00',NULL,NULL),('2020-08-05 05:11:24',NULL,2058,'03:15:09',NULL,NULL),('2018-07-25 13:54:23',32767,NULL,NULL,NULL,NULL),('2016-11-15 10:04:20',32767,0000,NULL,NULL,NULL),('2013-01-10 13:20:00',NULL,NULL,'16:47:57',NULL,NULL),('2011-07-22 00:20:01',-91,0000,NULL,NULL,NULL),('2007-02-23 12:27:00',-32768,0000,'09:22:11',NULL,NULL),('2005-10-21 05:55:22',-66,NULL,'12:57:22',NULL,NULL),('2005-06-01 08:42:25',NULL,NULL,NULL,NULL,NULL),('2004-09-27 09:47:00',32767,0000,'21:11:13',NULL,NULL),('2003-08-09 09:55:24',-32768,NULL,NULL,NULL,NULL),('2000-01-01 00:00:00',NULL,1963,'18:35:43',NULL,NULL),('1995-09-20 21:24:09',-20243,NULL,NULL,NULL,NULL),('1994-04-03 06:35:00',NULL,1970,'03:54:13',NULL,NULL),('1994-03-12 07:12:02',106,2140,'12:49:13',NULL,NULL),('1991-01-11 19:58:33',7,2111,'20:43:31',NULL,NULL),('1989-12-12 06:47:05',NULL,2038,NULL,NULL,NULL),('1986-06-26 07:16:19',-32768,NULL,NULL,NULL,NULL),('1983-02-07 15:30:00',NULL,2068,NULL,NULL,NULL),('1980-11-30 11:54:58',NULL,NULL,NULL,NULL,NULL),('1980-11-25 11:18:01',-124,0000,'02:45:26',NULL,NULL),('1978-08-09 18:14:02',19046,2014,'09:18:36',NULL,NULL),('1977-12-06 18:51:08',NULL,1945,'12:37:05',NULL,NULL),('1976-09-29 02:16:33',3890,2058,'00:26:00',NULL,NULL),('1972-09-21 15:25:29',NULL,0000,NULL,NULL,NULL),('1971-01-05 02:52:30',-24617,0000,'18:05:41',NULL,NULL),('1969-12-31 23:59:59',NULL,NULL,'08:21:26',NULL,NULL),('0000-00-00 00:00:00',NULL,NULL,NULL,NULL,NULL),('0000-00-00 00:00:00',NULL,1983,NULL,NULL,NULL),('0000-00-00 00:00:00',NULL,1983,'-25:30:45',NULL,NULL),('0000-00-00 00:00:00',NULL,1983,'00:00:00',NULL,NULL),('0000-00-00 00:00:00',NULL,1983,'00:00:01',NULL,NULL),('0000-00-00 00:00:00',NULL,1983,'00:48:27',NULL,NULL),('0000-00-00 00:00:00',NULL,1983,'01:51:14',NULL,NULL),('0000-00-00 00:00:00',NULL,1983,'06:43:48',NULL,NULL),('0000-00-00 00:00:00',NULL,1983,'09:27:41',NULL,NULL),('0000-00-00 00:00:00',NULL,1983,'10:45:32',NULL,NULL),('0000-00-00 00:00:00',NULL,1983,'11:03:14',NULL,NULL),('0000-00-00 00:00:00',NULL,1983,'11:09:28',NULL,NULL),('0000-00-00 00:00:00',NULL,1983,'11:23:42',NULL,NULL),('0000-00-00 00:00:00',NULL,1983,'11:50:42',NULL,NULL),('0000-00-00 00:00:00',NULL,1983,'12:03:57',NULL,NULL),('0000-00-00 00:00:00',NULL,1983,'12:14:51',NULL,NULL),('0000-00-00 00:00:00',NULL,1983,'12:16:52',NULL,NULL),('0000-00-00 00:00:00',NULL,1983,'12:34:57',NULL,NULL),('0000-00-00 00:00:00',NULL,1983,'13:37:41',NULL,NULL),('0000-00-00 00:00:00',NULL,1983,'13:41:06',NULL,NULL),('0000-00-00 00:00:00',NULL,1983,'16:42:53',NULL,NULL),('0000-00-00 00:00:00',NULL,1983,'20:03:16',NULL,NULL),('0000-00-00 00:00:00',-32768,0000,NULL,NULL,NULL),('0000-00-00 00:00:00',-32768,1983,'15:14:21',NULL,NULL),('0000-00-00 00:00:00',-32768,1983,'24:00:00',NULL,NULL),('0000-00-00 00:00:00',-30800,1983,NULL,NULL,NULL),('0000-00-00 00:00:00',7,NULL,'-25:30:45',NULL,NULL),('0000-00-00 00:00:00',35,1983,NULL,NULL,NULL),('0000-00-00 00:00:00',49,1983,'13:04:53',NULL,NULL),('0000-00-00 00:00:00',74,1983,NULL,NULL,NULL),('0000-00-00 00:00:00',89,1983,NULL,NULL,NULL),('0000-00-00 00:00:00',18520,1983,NULL,NULL,NULL),('0000-00-00 00:00:00',32767,NULL,NULL,NULL,NULL),('0000-00-00 00:00:00',32767,NULL,'10:18:07',NULL,NULL),('0000-00-00 00:00:00',32767,NULL,'20:07:46',NULL,NULL),('0000-00-00 00:00:00',32767,1974,'05:57:39',NULL,NULL),('0000-00-00 00:00:00',32767,1983,'15:02:49',NULL,NULL),('0000-00-00 00:00:00',32767,1983,'18:21:26',NULL,NULL),('0000-00-00 00:00:00',32767,2101,NULL,NULL,NULL),(NULL,NULL,NULL,'11:50:41',NULL,NULL),(NULL,NULL,NULL,'15:24:20',NULL,NULL),(NULL,NULL,NULL,'16:10:48',NULL,NULL),(NULL,NULL,NULL,'16:30:43',NULL,NULL),(NULL,NULL,NULL,'18:36:25',NULL,NULL),(NULL,NULL,0000,'05:16:01',NULL,NULL),(NULL,NULL,2062,'20:29:39',NULL,NULL),(NULL,NULL,2122,'00:49:46',NULL,NULL),(NULL,NULL,2130,'06:13:22',NULL,NULL),(NULL,NULL,2152,'12:41:30',NULL,NULL),(NULL,-32768,NULL,'-838:59:59',NULL,NULL),(NULL,-32768,NULL,'-25:30:45',NULL,NULL),(NULL,-32768,NULL,'14:57:10',NULL,NULL),(NULL,-32768,2117,'01:12:44',NULL,NULL),(NULL,5,NULL,'12:00:00',NULL,NULL),(NULL,78,NULL,NULL,NULL,NULL),(NULL,19627,NULL,NULL,NULL,NULL),(NULL,22796,NULL,NULL,NULL,NULL),(NULL,32767,NULL,NULL,NULL,NULL),(NULL,32767,NULL,'19:57:33',NULL,NULL),(NULL,32767,1978,'21:13:50',NULL,NULL),(NULL,32767,2038,'22:20:50',NULL,NULL); insert into t2(c0,c1,c2,c3) select c0,c1,c2,c3 from t2; insert into t2(c0,c1,c2,c3) select c0,c1,c2,c3 from t2; insert into t2(c0,c1,c2,c3) select c0,c1,c2,c3 from t2; insert into t2(c0,c1,c2,c3) select c0,c1,c2,c3 from t2; SELECT t2.c0 AS ref0, t2.c1 AS ref1, t2.c2 AS ref2, t2.c3 AS ref3, t2.vc5 AS ref4, t2.vc7 AS ref5 FROM t2 WHERE (! ((t2.c3) >= ('2013-08-06'))); ANALYZE TABLE t2 UPDATE HISTOGRAM ON c0, c1, c2, c3, vc5, vc7; SELECT t2.c0 AS ref0, t2.c1 AS ref1, t2.c2 AS ref2, t2.c3 AS ref3, t2.vc5 AS ref4, t2.vc7 AS ref5 FROM t2 WHERE (! ((t2.c3) >= ('2013-08-06'))); Suggested fix: the root cause is: 1. after UPDATE HISTOGRAM, optimizer choose "skip scan" to execute this query; 2. using index skip scan, the query is changed;