MySQL的in很长，超过200条不走index dive,要用cardinality,可能不准，大表的甚至可能走全表，然后逐条判断Filter,造成性能慢，

可以改为values row行构造为临时表，转为semi join,转为的是semi join,按理说剔重后转为join更好

SELECT *

FROM (VALUES

ROW(1,'Apple'),

ROW(2,'Banana'),

ROW(3,'Cherry')

) AS t(id, fruit);

### in很长，超过200条不走index dive,要用cardinality,可能不准，大表的甚至可能走全表，然后逐条判断Filter,造成性能慢

select * from t11 where a in (

1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157,158,159,160,161,162,163,164,165,166,167,168,169,170,171,172,173,174,175,176,177,178,179,180,181,182,183,184,185,186,187,188,189,190,191,192,193,194,195,196,197,198,199,200,201,202,203,204,205,206,207,208,209,210,211,212,213,214,215,216,217,218,219,220,221,222,223,224,225,226,227,228,229,230,231,232,233,234,235,236,237,238,239,240,241,242,243,244,245,246,247,248,249,250,251,252,253,254,255,256,257,258,259,260,261,262,263,264,265,266,267,268,269,270,271,272,273,274,275,276,277,278,279,280,281,282,283,284,285,286,287,288,289,290,291,292,293,294,295,296,297,298,299,300,301,302,303,304,305,306,307,308,309,310,311,312,313,314,315,316,317,318,319,320,321,322,323,324,325,326,327,328,329,330,331,332,333,334,335,336,337,338,339,340,341,342,343,344,345,346,347,348,349,350,351,352,353,354,355,356,357,358,359,360,361,362,363,364,365,366,367,368,369,370,371,372,373,374,375,376,377,378,379,380,381,382,383,384,385,386,387,388,389,390,391,392,393,394,395,396,397,398,399,400,401,402,403,404,405,406,407,408,409,410,411,412,413,414,415,416,417,418,419,420,421,422,423,424,425,426,427,428,429,430,431,432,433,434,435,436,437,438,439,440,441,442,443,444,445,446,447,448,449,450,451,452,453,454,455,456,457,458,459,460,461,462,463,464,465,466,467,468,469,470,471,472,473,474,475,476,477,478,479,480,481,482,483,484,485,486,487,488,489,490,491,492,493,494,495,496,497,498,499,500,501,502,503,504,505,506,507,508,509,510,511,512,513,514,515,516,517,518,519,520,521,522,523,524,525,526,527,528,529,530,531,532,533,534,535,536,537,538,539,540,541,542,543,544,545,546,547,548,549,550,551,552,553,554,555,556,557,558,559,560,561,562,563,564,565,566,567,568,569,570,571,572,573,574,575,576,577,578,579,580,581,582,583,584,585,586,587,588,589,590,591,592,593,594,595,596,597,598,599,600,601,602,603,604,605,606,607,608,609,610,611,612,613,614,615,616,617,618,619,620,621,622,623,624,625,626,627,628,629,630,631,632,633,634,635,636,637,638,639,640,641,642,643,644,645,646,647,648,649,650,651,652,653,654,655,656,657,658,659,660,661,662,663,664,665,666,667,668,669,670,671,672,673,674,675,676,677,678,679,680,681,682,683,684,685,686,687,688,689,690,691,692,693,694,695,696,697,698,699,700,701,702,703,704,705,706,707,708,709,710,711,712,713,714,715,716,717,718,719,720,721,722,723,724,725,726,727,728,729,730,731,732,733,734,735,736,737,738,739,740,741,742,743,744,745,746,747,748,749,750,751,752,753,754,755,756,757,758,759,760,761,762,763,764,765,766,767,768,769,770,771,772,773,774,775,776,777,778,779,780,781,782,783,784,785,786,787,788,789,790,791,792,793,794,795,796,797,798,799,800,801,802,803,804,805,806,807,808,809,810,811,812,813,814,815,816,817,818,819,820,821,822,823,824,825,826,827,828,829,830,831,832,833,834,835,836,837,838,839,840,841,842,843,844,845,846,847,848,849,850,851,852,853,854,855,856,857,858,859,860,861,862,863,864,865,866,867,868,869,870,871,872,873,874,875,876,877,878,879,880,881,882,883,884,885,886,887,888,889,890,891,892,893,894,895,896,897,898,899,900,901,902,903,904,905,906,907,908,909,910,911,912,913,914,915,916,917,918,919,920,921,922,923,924,925,926,927,928,929,930,931,932,933,934,935,936,937,938,939,940,941,942,943,944,945,946,947,948,949,950,951,952,953,954,955,956,957,958,959,960,961,962,963,964,965,966,967,968,969,970,971,972,973,974,975,976,977,978,979,980,981,982,983,984,985,986,987,988,989,990,991,992,993,994,995,996,997,998,999,1000,1001,1002,1003,1004,1005,1006,1007,1008,1009,1010,1011,1012,1013,1014,1015,1016,1017,1018,1019,1020,1021,1022,1023,1024,1025,1026,1027,1028,1029,1030,1031,1032,1033,1034,1035,1036,1037,1038,1039,1040,1041);

###解决方法，使用临时表，或用MySQL8.2的row constructor构造，对应(values row(...)) as 别名(列名,....

将big in list转为join方式提高效率

对于子查询使用values row构造，然后要select from 构造的结果集，则可以转为普通临时表，可以走materilize scan,lookup,这里临时表行数少，走scan,自动剔重

如果数据量大，还可以走hash join提高效率，避免全表扫描逐行判断IN LIST

select t11.* from t11 where a in (select t2.a from

(values row(1),row(2),row(3),row(4),row(5),row(6),row(7),row(8),row(9),row(10),row(11),row(12),row(13),row(14),row(15),row(16),row(17),row(18),row(19),row(20),row(21),row(22),row(23),row(24),row(25),row(26),row(27),row(28),row(29),row(30),row(31),row(32),row(33),row(34),row(35),row(36),row(37),row(38),row(39),row(40),row(41),row(42),row(43),row(44),row(45),row(46),row(47),row(48),row(49),row(50),row(51),row(52),row(53),row(54),row(55),row(56),row(57),row(58),row(59),row(60),row(61),row(62),row(63),row(64),row(65),row(66),row(67),row(68),row(69),row(70),row(71),row(72),row(73),row(74),row(75),row(76),row(77),row(78),row(79),row(80),row(81),row(82),row(83),row(84),row(85),row(86),row(87),row(88),row(89),row(90),row(91),row(92),row(93),row(94),row(95),row(96),row(97),row(98),row(99),row(100),row(101),row(102),row(103),row(104),row(105),row(106),row(107),row(108),row(109),row(110),row(111),row(112),row(113),row(114),row(115),row(116),row(117),row(118),row(119),row(120),row(121),row(122),row(123),row(124),row(125),row(126),row(127),row(128),row(129),row(130),row(131),row(132),row(133),row(134),row(135),row(136),row(137),row(138),row(139),row(140),row(141),row(142),row(143),row(144),row(145),row(146),row(147),row(148),row(149),row(150),row(151),row(152),row(153),row(154),row(155),row(156),row(157),row(158),row(159),row(160),row(161),row(162),row(163),row(164),row(165),row(166),row(167),row(168),row(169),row(170),row(171),row(172),row(173),row(174),row(175),row(176),row(177),row(178),row(179),row(180),row(181),row(182),row(183),row(184),row(185),row(186),row(187),row(188),row(189),row(190),row(191),row(192),row(193),row(194),row(195),row(196),row(197),row(198),row(199),row(200),row(201),row(202),row(203),row(204),row(205),row(206),row(207),row(208),row(209),row(210),row(211),row(212),row(213),row(214),row(215),row(216),row(217),row(218),row(219),row(220),row(221),row(222),row(223),row(224),row(225),row(226),row(227),row(228),row(229),row(230),row(231),row(232),row(233),row(234),row(235),row(236),row(237),row(238),row(239),row(240),row(241),row(242),row(243),row(244),row(245),row(246),row(247),row(248),row(249),row(250),row(251),row(252),row(253),row(254),row(255),row(256),row(257),row(258),row(259),row(260),row(261),row(262),row(263),row(264),row(265),row(266),row(267),row(268),row(269),row(270),row(271),row(272),row(273),row(274),row(275),row(276),row(277),row(278),row(279),row(280),row(281),row(282),row(283),row(284),row(285),row(286),row(287),row(288),row(289),row(290),row(291),row(292),row(293),row(294),row(295),row(296),row(297),row(298),row(299),row(300),row(301),row(302),row(303),row(304),row(305),row(306),row(307),row(308),row(309),row(310),row(311),row(312),row(313),row(314),row(315),row(316),row(317),row(318),row(319),row(320),row(321),row(322),row(323),row(324),row(325),row(326),row(327),row(328),row(329),row(330),row(331),row(332),row(333),row(334),row(335),row(336),row(337),row(338),row(339),row(340),row(341),row(342),row(343),row(344),row(345),row(346),row(347),row(348),row(349),row(350),row(351),row(352),row(353),row(354),row(355),row(356),row(357),row(358),row(359),row(360),row(361),row(362),row(363),row(364),row(365),row(366),row(367),row(368),row(369),row(370),row(371),row(372),row(373),row(374),row(375),row(376),row(377),row(378),row(379),row(380),row(381),row(382),row(383),row(384),row(385),row(386),row(387),row(388),row(389),row(390),row(391),row(392),row(393),row(394),row(395),row(396),row(397),row(398),row(399),row(400),row(401),row(402),row(403),row(404),row(405),row(406),row(407),row(408),row(409),row(410),row(411),row(412),row(413),row(414),row(415),row(416),row(417),row(418),row(419),row(420),row(421),row(422),row(423),row(424),row(425),row(426),row(427),row(428),row(429),row(430),row(431),row(432),row(433),row(434),row(435),row(436),row(437),row(438),row(439),row(440),row(441),row(442),row(443),row(444),row(445),row(446),row(447),row(448),row(449),row(450),row(451),row(452),row(453),row(454),row(455),row(456),row(457),row(458),row(459),row(460),row(461),row(462),row(463),row(464),row(465),row(466),row(467),row(468),row(469),row(470),row(471),row(472),row(473),row(474),row(475),row(476),row(477),row(478),row(479),row(480),row(481),row(482),row(483),row(484),row(485),row(486),row(487),row(488),row(489),row(490),row(491),row(492),row(493),row(494),row(495),row(496),row(497),row(498),row(499),row(500),row(501),row(502),row(503),row(504),row(505),row(506),row(507),row(508),row(509),row(510),row(511),row(512),row(513),row(514),row(515),row(516),row(517),row(518),row(519),row(520),row(521),row(522),row(523),row(524),row(525),row(526),row(527),row(528),row(529),row(530),row(531),row(532),row(533),row(534),row(535),row(536),row(537),row(538),row(539),row(540),row(541),row(542),row(543),row(544),row(545),row(546),row(547),row(548),row(549),row(550),row(551),row(552),row(553),row(554),row(555),row(556),row(557),row(558),row(559),row(560),row(561),row(562),row(563),row(564),row(565),row(566),row(567),row(568),row(569),row(570),row(571),row(572),row(573),row(574),row(575),row(576),row(577),row(578),row(579),row(580),row(581),row(582),row(583),row(584),row(585),row(586),row(587),row(588),row(589),row(590),row(591),row(592),row(593),row(594),row(595),row(596),row(597),row(598),row(599),row(600),row(601),row(602),row(603),row(604),row(605),row(606),row(607),row(608),row(609),row(610),row(611),row(612),row(613),row(614),row(615),row(616),row(617),row(618),row(619),row(620),row(621),row(622),row(623),row(624),row(625),row(626),row(627),row(628),row(629),row(630),row(631),row(632),row(633),row(634),row(635),row(636),row(637),row(638),row(639),row(640),row(641),row(642),row(643),row(644),row(645),row(646),row(647),row(648),row(649),row(650),row(651),row(652),row(653),row(654),row(655),row(656),row(657),row(658),row(659),row(660),row(661),row(662),row(663),row(664),row(665),row(666),row(667),row(668),row(669),row(670),row(671),row(672),row(673),row(674),row(675),row(676),row(677),row(678),row(679),row(680),row(681),row(682),row(683),row(684),row(685),row(686),row(687),row(688),row(689),row(690),row(691),row(692),row(693),row(694),row(695),row(696),row(697),row(698),row(699),row(700),row(701),row(702),row(703),row(704),row(705),row(706),row(707),row(708),row(709),row(710),row(711),row(712),row(713),row(714),row(715),row(716),row(717),row(718),row(719),row(720),row(721),row(722),row(723),row(724),row(725),row(726),row(727),row(728),row(729),row(730),row(731),row(732),row(733),row(734),row(735),row(736),row(737),row(738),row(739),row(740),row(741),row(742),row(743),row(744),row(745),row(746),row(747),row(748),row(749),row(750),row(751),row(752),row(753),row(754),row(755),row(756),row(757),row(758),row(759),row(760),row(761),row(762),row(763),row(764),row(765),row(766),row(767),row(768),row(769),row(770),row(771),row(772),row(773),row(774),row(775),row(776),row(777),row(778),row(779),row(780),row(781),row(782),row(783),row(784),row(785),row(786),row(787),row(788),row(789),row(790),row(791),row(792),row(793),row(794),row(795),row(796),row(797),row(798),row(799),row(800),row(801),row(802),row(803),row(804),row(805),row(806),row(807),row(808),row(809),row(810),row(811),row(812),row(813),row(814),row(815),row(816),row(817),row(818),row(819),row(820),row(821),row(822),row(823),row(824),row(825),row(826),row(827),row(828),row(829),row(830),row(831),row(832),row(833),row(834),row(835),row(836),row(837),row(838),row(839),row(840),row(841),row(842),row(843),row(844),row(845),row(846),row(847),row(848),row(849),row(850),row(851),row(852),row(853),row(854),row(855),row(856),row(857),row(858),row(859),row(860),row(861),row(862),row(863),row(864),row(865),row(866),row(867),row(868),row(869),row(870),row(871),row(872),row(873),row(874),row(875),row(876),row(877),row(878),row(879),row(880),row(881),row(882),row(883),row(884),row(885),row(886),row(887),row(888),row(889),row(890),row(891),row(892),row(893),row(894),row(895),row(896),row(897),row(898),row(899),row(900),row(901),row(902),row(903),row(904),row(905),row(906),row(907),row(908),row(909),row(910),row(911),row(912),row(913),row(914),row(915),row(916),row(917),row(918),row(919),row(920),row(921),row(922),row(923),row(924),row(925),row(926),row(927),row(928),row(929),row(930),row(931),row(932),row(933),row(934),row(935),row(936),row(937),row(938),row(939),row(940),row(941),row(942),row(943),row(944),row(945),row(946),row(947),row(948),row(949),row(950),row(951),row(952),row(953),row(954),row(955),row(956),row(957),row(958),row(959),row(960),row(961),row(962),row(963),row(964),row(965),row(966),row(967),row(968),row(969),row(970),row(971),row(972),row(973),row(974),row(975),row(976),row(977),row(978),row(979),row(980),row(981),row(982),row(983),row(984),row(985),row(986),row(987),row(988),row(989),row(990),row(991),row(992),row(993),row(994),row(995),row(996),row(997),row(998),row(999),

row(1),row(2),row(3),row(4),row(5),row(6),row(7),row(8),row(9),row(10),row(11),row(12),row(13),row(14),row(15),row(16),row(17),row(18),row(19),row(20),row(21),row(22),row(23),row(24),row(25),row(26),row(27),row(28),row(29),row(30),row(31),row(32),row(33),row(34),row(35),row(36),row(37),row(38),row(39),row(40),row(41),row(42),row(43),row(44),row(45),row(46),row(47),row(48),row(49),row(50),row(51),row(52),row(53),row(54),row(55),row(56),row(57),row(58),row(59),row(60),row(61),row(62),row(63),row(64),row(65),row(66),row(67),row(68),row(69),row(70),row(71),row(72),row(73),row(74),row(75),row(76),row(77),row(78),row(79),row(80),row(81),row(82),row(83),row(84),row(85),row(86),row(87),row(88),row(89),row(90),row(91),row(92),row(93),row(94),row(95),row(96),row(97),row(98),row(99),row(100),row(101),row(102),row(103),row(104),row(105),row(106),row(107),row(108),row(109),row(110),row(111),row(112),row(113),row(114),row(115),row(116),row(117),row(118),row(119),row(120),row(121),row(122),row(123),row(124),row(125),row(126),row(127),row(128),row(129),row(130),row(131),row(132),row(133),row(134),row(135),row(136),row(137),row(138),row(139),row(140),row(141),row(142),row(143),row(144),row(145),row(146),row(147),row(148),row(149),row(150),row(151),row(152),row(153),row(154),row(155),row(156),row(157),row(158),row(159),row(160),row(161),row(162),row(163),row(164),row(165),row(166),row(167),row(168),row(169),row(170),row(171),row(172),row(173),row(174),row(175),row(176),row(177),row(178),row(179),row(180),row(181),row(182),row(183),row(184),row(185),row(186),row(187),row(188),row(189),row(190),row(191),row(192),row(193),row(194),row(195),row(196),row(197),row(198),row(199),row(200),row(201),row(202),row(203),row(204),row(205),row(206),row(207),row(208),row(209),row(210),row(211),row(212),row(213),row(214),row(215),row(216),row(217),row(218),row(219),row(220),row(221),row(222),row(223),row(224),row(225),row(226),row(227),row(228),row(229),row(230),row(231),row(232),row(233),row(234),row(235),row(236),row(237),row(238),row(239),row(240),row(241),row(242),row(243),row(244),row(245),row(246),row(247),row(248),row(249),row(250),row(251),row(252),row(253),row(254),row(255),row(256),row(257),row(258),row(259),row(260),row(261),row(262),row(263),row(264),row(265),row(266),row(267),row(268),row(269),row(270),row(271),row(272),row(273),row(274),row(275),row(276),row(277),row(278),row(279),row(280),row(281),row(282),row(283),row(284),row(285),row(286),row(287),row(288),row(289),row(290),row(291),row(292),row(293),row(294),row(295),row(296),row(297),row(298),row(299),row(300),row(301),row(302),row(303),row(304),row(305),row(306),row(307),row(308),row(309),row(310),row(311),row(312),row(313),row(314),row(315),row(316),row(317),row(318),row(319),row(320),row(321),row(322),row(323),row(324),row(325),row(326),row(327),row(328),row(329),row(330),row(331),row(332),row(333),row(334),row(335),row(336),row(337),row(338),row(339),row(340),row(341),row(342),row(343),row(344),row(345),row(346),row(347),row(348),row(349),row(350),row(351),row(352),row(353),row(354),row(355),row(356),row(357),row(358),row(359),row(360),row(361),row(362),row(363),row(364),row(365),row(366),row(367),row(368),row(369),row(370),row(371),row(372),row(373),row(374),row(375),row(376),row(377),row(378),row(379),row(380),row(381),row(382),row(383),row(384),row(385),row(386),row(387),row(388),row(389),row(390),row(391),row(392),row(393),row(394),row(395),row(396),row(397),row(398),row(399),row(400),row(401),row(402),row(403),row(404),row(405),row(406),row(407),row(408),row(409),row(410),row(411),row(412),row(413),row(414),row(415),row(416),row(417),row(418),row(419),row(420),row(421),row(422),row(423),row(424),row(425),row(426),row(427),row(428),row(429),row(430),row(431),row(432),row(433),row(434),row(435),row(436),row(437),row(438),row(439),row(440),row(441),row(442),row(443),row(444),row(445),row(446),row(447),row(448),row(449),row(450),row(451),row(452),row(453),row(454),row(455),row(456),row(457),row(458),row(459),row(460),row(461),row(462),row(463),row(464),row(465),row(466),row(467),row(468),row(469),row(470),row(471),row(472),row(473),row(474),row(475),row(476),row(477),row(478),row(479),row(480),row(481),row(482),row(483),row(484),row(485),row(486),row(487),row(488),row(489),row(490),row(491),row(492),row(493),row(494),row(495),row(496),row(497),row(498),row(499),row(500),row(501),row(502),row(503),row(504),row(505),row(506),row(507),row(508),row(509),row(510),row(511),row(512),row(513),row(514),row(515),row(516),row(517),row(518),row(519),row(520),row(521),row(522),row(523),row(524),row(525),row(526),row(527),row(528),row(529),row(530),row(531),row(532),row(533),row(534),row(535),row(536),row(537),row(538),row(539),row(540),row(541),row(542),row(543),row(544),row(545),row(546),row(547),row(548),row(549),row(550),row(551),row(552),row(553),row(554),row(555),row(556),row(557),row(558),row(559),row(560),row(561),row(562),row(563),row(564),row(565),row(566),row(567),row(568),row(569),row(570),row(571),row(572),row(573),row(574),row(575),row(576),row(577),row(578),row(579),row(580),row(581),row(582),row(583),row(584),row(585),row(586),row(587),row(588),row(589),row(590),row(591),row(592),row(593),row(594),row(595),row(596),row(597),row(598),row(599),row(600),row(601),row(602),row(603),row(604),row(605),row(606),row(607),row(608),row(609),row(610),row(611),row(612),row(613),row(614),row(615),row(616),row(617),row(618),row(619),row(620),row(621),row(622),row(623),row(624),row(625),row(626),row(627),row(628),row(629),row(630),row(631),row(632),row(633),row(634),row(635),row(636),row(637),row(638),row(639),row(640),row(641),row(642),row(643),row(644),row(645),row(646),row(647),row(648),row(649),row(650),row(651),row(652),row(653),row(654),row(655),row(656),row(657),row(658),row(659),row(660),row(661),row(662),row(663),row(664),row(665),row(666),row(667),row(668),row(669),row(670),row(671),row(672),row(673),row(674),row(675),row(676),row(677),row(678),row(679),row(680),row(681),row(682),row(683),row(684),row(685),row(686),row(687),row(688),row(689),row(690),row(691),row(692),row(693),row(694),row(695),row(696),row(697),row(698),row(699),row(700),row(701),row(702),row(703),row(704),row(705),row(706),row(707),row(708),row(709),row(710),row(711),row(712),row(713),row(714),row(715),row(716),row(717),row(718),row(719),row(720),row(721),row(722),row(723),row(724),row(725),row(726),row(727),row(728),row(729),row(730),row(731),row(732),row(733),row(734),row(735),row(736),row(737),row(738),row(739),row(740),row(741),row(742),row(743),row(744),row(745),row(746),row(747),row(748),row(749),row(750),row(751),row(752),row(753),row(754),row(755),row(756),row(757),row(758),row(759),row(760),row(761),row(762),row(763),row(764),row(765),row(766),row(767),row(768),row(769),row(770),row(771),row(772),row(773),row(774),row(775),row(776),row(777),row(778),row(779),row(780),row(781),row(782),row(783),row(784),row(785),row(786),row(787),row(788),row(789),row(790),row(791),row(792),row(793),row(794),row(795),row(796),row(797),row(798),row(799),row(800),row(801),row(802),row(803),row(804),row(805),row(806),row(807),row(808),row(809),row(810),row(811),row(812),row(813),row(814),row(815),row(816),row(817),row(818),row(819),row(820),row(821),row(822),row(823),row(824),row(825),row(826),row(827),row(828),row(829),row(830),row(831),row(832),row(833),row(834),row(835),row(836),row(837),row(838),row(839),row(840),row(841),row(842),row(843),row(844),row(845),row(846),row(847),row(848),row(849),row(850),row(851),row(852),row(853),row(854),row(855),row(856),row(857),row(858),row(859),row(860),row(861),row(862),row(863),row(864),row(865),row(866),row(867),row(868),row(869),row(870),row(871),row(872),row(873),row(874),row(875),row(876),row(877),row(878),row(879),row(880),row(881),row(882),row(883),row(884),row(885),row(886),row(887),row(888),row(889),row(890),row(891),row(892),row(893),row(894),row(895),row(896),row(897),row(898),row(899),row(900),row(901),row(902),row(903),row(904),row(905),row(906),row(907),row(908),row(909),row(910),row(911),row(912),row(913),row(914),row(915),row(916),row(917),row(918),row(919),row(920),row(921),row(922),row(923),row(924),row(925),row(926),row(927),row(928),row(929),row(930),row(931),row(932),row(933),row(934),row(935),row(936),row(937),row(938),row(939),row(940),row(941),row(942),row(943),row(944),row(945),row(946),row(947),row(948),row(949),row(950),row(951),row(952),row(953),row(954),row(955),row(956),row(957),row(958),row(959),row(960),row(961),row(962),row(963),row(964),row(965),row(966),row(967),row(968),row(969),row(970),row(971),row(972),row(973),row(974),row(975),row(976),row(977),row(978),row(979),row(980),row(981),row(982),row(983),row(984),row(985),row(986),row(987),row(988),row(989),row(990),row(991),row(992),row(993),row(994),row(995),row(996),row(997),row(998),row(999)

) as t2(a)

);

*************************** 1. row ***************************

EXPLAIN: -> Nested loop inner join (cost=1153.85 rows=1998) (actual time=1.649..4.808 rows=490 loops=1)

-> Table scan on <subquery2> (cost=0.01..27.48 rows=1998) (actual time=0.000..0.060 rows=999 loops=1)

-> Materialize with deduplication (cost=427.09..454.55 rows=1998) (actual time=1.064..1.186 rows=999 loops=1)

-> Table scan on t2 (cost=0.01..27.48 rows=1998) (actual time=0.001..0.099 rows=1998 loops=1)

-> Materialize (cost=199.81..227.28 rows=1998) (actual time=0.368..0.586 rows=1998 loops=1)

-> Rows fetched before execution (cost=0.00 rows=1998) (actual time=0.000..0.170 rows=1998 loops=1)

-> Index lookup on t11 using idx_t11 (a="<subquery2>".a), with index condition: (t11.a = "<subquery2>".a) (cost=499.60 rows=1) (actual time=0.003..0.003 rows=0 loops=999)

1 row in set (0.01 sec)

##也可以改为join,但是in list没有剔重,MySQL子查询使用行构造转为的是semi join,按理说剔重后转为join更好

select t11.* from t11,(values row(1),row(2),row(3),row(4),row(5),row(6),row(7),row(8),row(9),row(10),row(11),row(12),row(13),row(14),row(15),row(16),row(17),row(18),row(19),row(20),row(21),row(22),row(23),row(24),row(25),row(26),row(27),row(28),row(29),row(30),row(31),row(32),row(33),row(34),row(35),row(36),row(37),row(38),row(39),row(40),row(41),row(42),row(43),row(44),row(45),row(46),row(47),row(48),row(49),row(50),row(51),row(52),row(53),row(54),row(55),row(56),row(57),row(58),row(59),row(60),row(61),row(62),row(63),row(64),row(65),row(66),row(67),row(68),row(69),row(70),row(71),row(72),row(73),row(74),row(75),row(76),row(77),row(78),row(79),row(80),row(81),row(82),row(83),row(84),row(85),row(86),row(87),row(88),row(89),row(90),row(91),row(92),row(93),row(94),row(95),row(96),row(97),row(98),row(99),row(100),row(101),row(102),row(103),row(104),row(105),row(106),row(107),row(108),row(109),row(110),row(111),row(112),row(113),row(114),row(115),row(116),row(117),row(118),row(119),row(120),row(121),row(122),row(123),row(124),row(125),row(126),row(127),row(128),row(129),row(130),row(131),row(132),row(133),row(134),row(135),row(136),row(137),row(138),row(139),row(140),row(141),row(142),row(143),row(144),row(145),row(146),row(147),row(148),row(149),row(150),row(151),row(152),row(153),row(154),row(155),row(156),row(157),row(158),row(159),row(160),row(161),row(162),row(163),row(164),row(165),row(166),row(167),row(168),row(169),row(170),row(171),row(172),row(173),row(174),row(175),row(176),row(177),row(178),row(179),row(180),row(181),row(182),row(183),row(184),row(185),row(186),row(187),row(188),row(189),row(190),row(191),row(192),row(193),row(194),row(195),row(196),row(197),row(198),row(199),row(200),row(201),row(202),row(203),row(204),row(205),row(206),row(207),row(208),row(209),row(210),row(211),row(212),row(213),row(214),row(215),row(216),row(217),row(218),row(219),row(220),row(221),row(222),row(223),row(224),row(225),row(226),row(227),row(228),row(229),row(230),row(231),row(232),row(233),row(234),row(235),row(236),row(237),row(238),row(239),row(240),row(241),row(242),row(243),row(244),row(245),row(246),row(247),row(248),row(249),row(250),row(251),row(252),row(253),row(254),row(255),row(256),row(257),row(258),row(259),row(260),row(261),row(262),row(263),row(264),row(265),row(266),row(267),row(268),row(269),row(270),row(271),row(272),row(273),row(274),row(275),row(276),row(277),row(278),row(279),row(280),row(281),row(282),row(283),row(284),row(285),row(286),row(287),row(288),row(289),row(290),row(291),row(292),row(293),row(294),row(295),row(296),row(297),row(298),row(299),row(300),row(301),row(302),row(303),row(304),row(305),row(306),row(307),row(308),row(309),row(310),row(311),row(312),row(313),row(314),row(315),row(316),row(317),row(318),row(319),row(320),row(321),row(322),row(323),row(324),row(325),row(326),row(327),row(328),row(329),row(330),row(331),row(332),row(333),row(334),row(335),row(336),row(337),row(338),row(339),row(340),row(341),row(342),row(343),row(344),row(345),row(346),row(347),row(348),row(349),row(350),row(351),row(352),row(353),row(354),row(355),row(356),row(357),row(358),row(359),row(360),row(361),row(362),row(363),row(364),row(365),row(366),row(367),row(368),row(369),row(370),row(371),row(372),row(373),row(374),row(375),row(376),row(377),row(378),row(379),row(380),row(381),row(382),row(383),row(384),row(385),row(386),row(387),row(388),row(389),row(390),row(391),row(392),row(393),row(394),row(395),row(396),row(397),row(398),row(399),row(400),row(401),row(402),row(403),row(404),row(405),row(406),row(407),row(408),row(409),row(410),row(411),row(412),row(413),row(414),row(415),row(416),row(417),row(418),row(419),row(420),row(421),row(422),row(423),row(424),row(425),row(426),row(427),row(428),row(429),row(430),row(431),row(432),row(433),row(434),row(435),row(436),row(437),row(438),row(439),row(440),row(441),row(442),row(443),row(444),row(445),row(446),row(447),row(448),row(449),row(450),row(451),row(452),row(453),row(454),row(455),row(456),row(457),row(458),row(459),row(460),row(461),row(462),row(463),row(464),row(465),row(466),row(467),row(468),row(469),row(470),row(471),row(472),row(473),row(474),row(475),row(476),row(477),row(478),row(479),row(480),row(481),row(482),row(483),row(484),row(485),row(486),row(487),row(488),row(489),row(490),row(491),row(492),row(493),row(494),row(495),row(496),row(497),row(498),row(499),row(500),row(501),row(502),row(503),row(504),row(505),row(506),row(507),row(508),row(509),row(510),row(511),row(512),row(513),row(514),row(515),row(516),row(517),row(518),row(519),row(520),row(521),row(522),row(523),row(524),row(525),row(526),row(527),row(528),row(529),row(530),row(531),row(532),row(533),row(534),row(535),row(536),row(537),row(538),row(539),row(540),row(541),row(542),row(543),row(544),row(545),row(546),row(547),row(548),row(549),row(550),row(551),row(552),row(553),row(554),row(555),row(556),row(557),row(558),row(559),row(560),row(561),row(562),row(563),row(564),row(565),row(566),row(567),row(568),row(569),row(570),row(571),row(572),row(573),row(574),row(575),row(576),row(577),row(578),row(579),row(580),row(581),row(582),row(583),row(584),row(585),row(586),row(587),row(588),row(589),row(590),row(591),row(592),row(593),row(594),row(595),row(596),row(597),row(598),row(599),row(600),row(601),row(602),row(603),row(604),row(605),row(606),row(607),row(608),row(609),row(610),row(611),row(612),row(613),row(614),row(615),row(616),row(617),row(618),row(619),row(620),row(621),row(622),row(623),row(624),row(625),row(626),row(627),row(628),row(629),row(630),row(631),row(632),row(633),row(634),row(635),row(636),row(637),row(638),row(639),row(640),row(641),row(642),row(643),row(644),row(645),row(646),row(647),row(648),row(649),row(650),row(651),row(652),row(653),row(654),row(655),row(656),row(657),row(658),row(659),row(660),row(661),row(662),row(663),row(664),row(665),row(666),row(667),row(668),row(669),row(670),row(671),row(672),row(673),row(674),row(675),row(676),row(677),row(678),row(679),row(680),row(681),row(682),row(683),row(684),row(685),row(686),row(687),row(688),row(689),row(690),row(691),row(692),row(693),row(694),row(695),row(696),row(697),row(698),row(699),row(700),row(701),row(702),row(703),row(704),row(705),row(706),row(707),row(708),row(709),row(710),row(711),row(712),row(713),row(714),row(715),row(716),row(717),row(718),row(719),row(720),row(721),row(722),row(723),row(724),row(725),row(726),row(727),row(728),row(729),row(730),row(731),row(732),row(733),row(734),row(735),row(736),row(737),row(738),row(739),row(740),row(741),row(742),row(743),row(744),row(745),row(746),row(747),row(748),row(749),row(750),row(751),row(752),row(753),row(754),row(755),row(756),row(757),row(758),row(759),row(760),row(761),row(762),row(763),row(764),row(765),row(766),row(767),row(768),row(769),row(770),row(771),row(772),row(773),row(774),row(775),row(776),row(777),row(778),row(779),row(780),row(781),row(782),row(783),row(784),row(785),row(786),row(787),row(788),row(789),row(790),row(791),row(792),row(793),row(794),row(795),row(796),row(797),row(798),row(799),row(800),row(801),row(802),row(803),row(804),row(805),row(806),row(807),row(808),row(809),row(810),row(811),row(812),row(813),row(814),row(815),row(816),row(817),row(818),row(819),row(820),row(821),row(822),row(823),row(824),row(825),row(826),row(827),row(828),row(829),row(830),row(831),row(832),row(833),row(834),row(835),row(836),row(837),row(838),row(839),row(840),row(841),row(842),row(843),row(844),row(845),row(846),row(847),row(848),row(849),row(850),row(851),row(852),row(853),row(854),row(855),row(856),row(857),row(858),row(859),row(860),row(861),row(862),row(863),row(864),row(865),row(866),row(867),row(868),row(869),row(870),row(871),row(872),row(873),row(874),row(875),row(876),row(877),row(878),row(879),row(880),row(881),row(882),row(883),row(884),row(885),row(886),row(887),row(888),row(889),row(890),row(891),row(892),row(893),row(894),row(895),row(896),row(897),row(898),row(899),row(900),row(901),row(902),row(903),row(904),row(905),row(906),row(907),row(908),row(909),row(910),row(911),row(912),row(913),row(914),row(915),row(916),row(917),row(918),row(919),row(920),row(921),row(922),row(923),row(924),row(925),row(926),row(927),row(928),row(929),row(930),row(931),row(932),row(933),row(934),row(935),row(936),row(937),row(938),row(939),row(940),row(941),row(942),row(943),row(944),row(945),row(946),row(947),row(948),row(949),row(950),row(951),row(952),row(953),row(954),row(955),row(956),row(957),row(958),row(959),row(960),row(961),row(962),row(963),row(964),row(965),row(966),row(967),row(968),row(969),row(970),row(971),row(972),row(973),row(974),row(975),row(976),row(977),row(978),row(979),row(980),row(981),row(982),row(983),row(984),row(985),row(986),row(987),row(988),row(989),row(990),row(991),row(992),row(993),row(994),row(995),row(996),row(997),row(998),row(999),

row(1),row(2),row(3),row(4),row(5),row(6),row(7),row(8),row(9),row(10),row(11),row(12),row(13),row(14),row(15),row(16),row(17),row(18),row(19),row(20),row(21),row(22),row(23),row(24),row(25),row(26),row(27),row(28),row(29),row(30),row(31),row(32),row(33),row(34),row(35),row(36),row(37),row(38),row(39),row(40),row(41),row(42),row(43),row(44),row(45),row(46),row(47),row(48),row(49),row(50),row(51),row(52),row(53),row(54),row(55),row(56),row(57),row(58),row(59),row(60),row(61),row(62),row(63),row(64),row(65),row(66),row(67),row(68),row(69),row(70),row(71),row(72),row(73),row(74),row(75),row(76),row(77),row(78),row(79),row(80),row(81),row(82),row(83),row(84),row(85),row(86),row(87),row(88),row(89),row(90),row(91),row(92),row(93),row(94),row(95),row(96),row(97),row(98),row(99),row(100),row(101),row(102),row(103),row(104),row(105),row(106),row(107),row(108),row(109),row(110),row(111),row(112),row(113),row(114),row(115),row(116),row(117),row(118),row(119),row(120),row(121),row(122),row(123),row(124),row(125),row(126),row(127),row(128),row(129),row(130),row(131),row(132),row(133),row(134),row(135),row(136),row(137),row(138),row(139),row(140),row(141),row(142),row(143),row(144),row(145),row(146),row(147),row(148),row(149),row(150),row(151),row(152),row(153),row(154),row(155),row(156),row(157),row(158),row(159),row(160),row(161),row(162),row(163),row(164),row(165),row(166),row(167),row(168),row(169),row(170),row(171),row(172),row(173),row(174),row(175),row(176),row(177),row(178),row(179),row(180),row(181),row(182),row(183),row(184),row(185),row(186),row(187),row(188),row(189),row(190),row(191),row(192),row(193),row(194),row(195),row(196),row(197),row(198),row(199),row(200),row(201),row(202),row(203),row(204),row(205),row(206),row(207),row(208),row(209),row(210),row(211),row(212),row(213),row(214),row(215),row(216),row(217),row(218),row(219),row(220),row(221),row(222),row(223),row(224),row(225),row(226),row(227),row(228),row(229),row(230),row(231),row(232),row(233),row(234),row(235),row(236),row(237),row(238),row(239),row(240),row(241),row(242),row(243),row(244),row(245),row(246),row(247),row(248),row(249),row(250),row(251),row(252),row(253),row(254),row(255),row(256),row(257),row(258),row(259),row(260),row(261),row(262),row(263),row(264),row(265),row(266),row(267),row(268),row(269),row(270),row(271),row(272),row(273),row(274),row(275),row(276),row(277),row(278),row(279),row(280),row(281),row(282),row(283),row(284),row(285),row(286),row(287),row(288),row(289),row(290),row(291),row(292),row(293),row(294),row(295),row(296),row(297),row(298),row(299),row(300),row(301),row(302),row(303),row(304),row(305),row(306),row(307),row(308),row(309),row(310),row(311),row(312),row(313),row(314),row(315),row(316),row(317),row(318),row(319),row(320),row(321),row(322),row(323),row(324),row(325),row(326),row(327),row(328),row(329),row(330),row(331),row(332),row(333),row(334),row(335),row(336),row(337),row(338),row(339),row(340),row(341),row(342),row(343),row(344),row(345),row(346),row(347),row(348),row(349),row(350),row(351),row(352),row(353),row(354),row(355),row(356),row(357),row(358),row(359),row(360),row(361),row(362),row(363),row(364),row(365),row(366),row(367),row(368),row(369),row(370),row(371),row(372),row(373),row(374),row(375),row(376),row(377),row(378),row(379),row(380),row(381),row(382),row(383),row(384),row(385),row(386),row(387),row(388),row(389),row(390),row(391),row(392),row(393),row(394),row(395),row(396),row(397),row(398),row(399),row(400),row(401),row(402),row(403),row(404),row(405),row(406),row(407),row(408),row(409),row(410),row(411),row(412),row(413),row(414),row(415),row(416),row(417),row(418),row(419),row(420),row(421),row(422),row(423),row(424),row(425),row(426),row(427),row(428),row(429),row(430),row(431),row(432),row(433),row(434),row(435),row(436),row(437),row(438),row(439),row(440),row(441),row(442),row(443),row(444),row(445),row(446),row(447),row(448),row(449),row(450),row(451),row(452),row(453),row(454),row(455),row(456),row(457),row(458),row(459),row(460),row(461),row(462),row(463),row(464),row(465),row(466),row(467),row(468),row(469),row(470),row(471),row(472),row(473),row(474),row(475),row(476),row(477),row(478),row(479),row(480),row(481),row(482),row(483),row(484),row(485),row(486),row(487),row(488),row(489),row(490),row(491),row(492),row(493),row(494),row(495),row(496),row(497),row(498),row(499),row(500),row(501),row(502),row(503),row(504),row(505),row(506),row(507),row(508),row(509),row(510),row(511),row(512),row(513),row(514),row(515),row(516),row(517),row(518),row(519),row(520),row(521),row(522),row(523),row(524),row(525),row(526),row(527),row(528),row(529),row(530),row(531),row(532),row(533),row(534),row(535),row(536),row(537),row(538),row(539),row(540),row(541),row(542),row(543),row(544),row(545),row(546),row(547),row(548),row(549),row(550),row(551),row(552),row(553),row(554),row(555),row(556),row(557),row(558),row(559),row(560),row(561),row(562),row(563),row(564),row(565),row(566),row(567),row(568),row(569),row(570),row(571),row(572),row(573),row(574),row(575),row(576),row(577),row(578),row(579),row(580),row(581),row(582),row(583),row(584),row(585),row(586),row(587),row(588),row(589),row(590),row(591),row(592),row(593),row(594),row(595),row(596),row(597),row(598),row(599),row(600),row(601),row(602),row(603),row(604),row(605),row(606),row(607),row(608),row(609),row(610),row(611),row(612),row(613),row(614),row(615),row(616),row(617),row(618),row(619),row(620),row(621),row(622),row(623),row(624),row(625),row(626),row(627),row(628),row(629),row(630),row(631),row(632),row(633),row(634),row(635),row(636),row(637),row(638),row(639),row(640),row(641),row(642),row(643),row(644),row(645),row(646),row(647),row(648),row(649),row(650),row(651),row(652),row(653),row(654),row(655),row(656),row(657),row(658),row(659),row(660),row(661),row(662),row(663),row(664),row(665),row(666),row(667),row(668),row(669),row(670),row(671),row(672),row(673),row(674),row(675),row(676),row(677),row(678),row(679),row(680),row(681),row(682),row(683),row(684),row(685),row(686),row(687),row(688),row(689),row(690),row(691),row(692),row(693),row(694),row(695),row(696),row(697),row(698),row(699),row(700),row(701),row(702),row(703),row(704),row(705),row(706),row(707),row(708),row(709),row(710),row(711),row(712),row(713),row(714),row(715),row(716),row(717),row(718),row(719),row(720),row(721),row(722),row(723),row(724),row(725),row(726),row(727),row(728),row(729),row(730),row(731),row(732),row(733),row(734),row(735),row(736),row(737),row(738),row(739),row(740),row(741),row(742),row(743),row(744),row(745),row(746),row(747),row(748),row(749),row(750),row(751),row(752),row(753),row(754),row(755),row(756),row(757),row(758),row(759),row(760),row(761),row(762),row(763),row(764),row(765),row(766),row(767),row(768),row(769),row(770),row(771),row(772),row(773),row(774),row(775),row(776),row(777),row(778),row(779),row(780),row(781),row(782),row(783),row(784),row(785),row(786),row(787),row(788),row(789),row(790),row(791),row(792),row(793),row(794),row(795),row(796),row(797),row(798),row(799),row(800),row(801),row(802),row(803),row(804),row(805),row(806),row(807),row(808),row(809),row(810),row(811),row(812),row(813),row(814),row(815),row(816),row(817),row(818),row(819),row(820),row(821),row(822),row(823),row(824),row(825),row(826),row(827),row(828),row(829),row(830),row(831),row(832),row(833),row(834),row(835),row(836),row(837),row(838),row(839),row(840),row(841),row(842),row(843),row(844),row(845),row(846),row(847),row(848),row(849),row(850),row(851),row(852),row(853),row(854),row(855),row(856),row(857),row(858),row(859),row(860),row(861),row(862),row(863),row(864),row(865),row(866),row(867),row(868),row(869),row(870),row(871),row(872),row(873),row(874),row(875),row(876),row(877),row(878),row(879),row(880),row(881),row(882),row(883),row(884),row(885),row(886),row(887),row(888),row(889),row(890),row(891),row(892),row(893),row(894),row(895),row(896),row(897),row(898),row(899),row(900),row(901),row(902),row(903),row(904),row(905),row(906),row(907),row(908),row(909),row(910),row(911),row(912),row(913),row(914),row(915),row(916),row(917),row(918),row(919),row(920),row(921),row(922),row(923),row(924),row(925),row(926),row(927),row(928),row(929),row(930),row(931),row(932),row(933),row(934),row(935),row(936),row(937),row(938),row(939),row(940),row(941),row(942),row(943),row(944),row(945),row(946),row(947),row(948),row(949),row(950),row(951),row(952),row(953),row(954),row(955),row(956),row(957),row(958),row(959),row(960),row(961),row(962),row(963),row(964),row(965),row(966),row(967),row(968),row(969),row(970),row(971),row(972),row(973),row(974),row(975),row(976),row(977),row(978),row(979),row(980),row(981),row(982),row(983),row(984),row(985),row(986),row(987),row(988),row(989),row(990),row(991),row(992),row(993),row(994),row(995),row(996),row(997),row(998),row(999)

)

as t2(a)

where t11.a=t2.a;

*************************** 1. row ***************************

EXPLAIN: -> Nested loop inner join (cost=926.58 rows=1998) (actual time=1.615..11.976 rows=980 loops=1)

-> Table scan on t2 (cost=0.01..27.48 rows=1998) (actual time=0.002..0.210 rows=1998 loops=1)

-> Materialize (cost=199.81..227.28 rows=1998) (actual time=0.670..1.049 rows=1998 loops=1)

-> Rows fetched before execution (cost=0.00 rows=1998) (actual time=0.000..0.288 rows=1998 loops=1)

-> Index lookup on t11 using idx_t11 (a=t2.a), with index condition: (t11.a = t2.a) (cost=0.25 rows=1) (actual time=0.005..0.005 rows=0 loops=1998)

1 row in set (0.02 sec)

### 不过MySQL这种方式是走不了软解析的，本来就没有共享执行计划，像在oracle里可以用table函数构造in list,转为join,当然table函数的基数默认是8868，可以

使用cardinality hints,dynamic_sampling或use_nl等hints固定执行计划

--将字符串转换为convert_value_list,convert_value_list类似于值的列表，比如将'a,b,c'转为'a','b','c'

SELECT ….. FROM ….. WHERE column in (convert_value_list);

--创建嵌套表

CREATE OR REPLACE TYPE varchar2_tt AS TABLE

OF VARCHAR2 (1000);

/

CREATE OR REPLACE

FUNCTION f_str2list( in_str IN VARCHAR2 ,in_delimiter IN VARCHAR2 DEFAULT ',' )

RETURN varchar2_tt PIPELINED

AS

v_str VARCHAR2(32767) DEFAULT in_str || in_delimiter;

i NUMBER;

BEGIN

LOOP

EXIT WHEN v_str IS NULL;

i := INSTR( v_str, in_delimiter );

PIPE ROW (TRIM(SUBSTR(v_str, 1, i - 1)));

v_str := SUBSTR(v_str, i + 1);

END LOOP;

RETURN;

END;

/

测试结果完全一样：

dingjun123@ORADB> SELECT count(*)

2 FROM t

3 WHERE object_name IN

4 (SELECT column_value

5 FROM TABLE( CAST(f_str2list(:str) AS varchar2_tt )

6 )

7 );

COUNT(*)

----------

2

Binging in list性能问题

Binging in list问题要特别注意性能问题，一般选择的字段都建有索引，希望通过nested loop方式连接查询，而不希望通过hash join或sort merge join方式连接查询，因为实际中传入的一般都不是很长的字符串。本部分主要探讨使用集合函数解决binging in list问题中注意的相关问题，对于正则表达式和INSTR+SUBSTR也可以通过hint来固定计划，比较简单，所以只探讨集合函数的性能问题，请看：

DINGJUN123>SELECT COUNT(*) FROM t;

COUNT(*)

----------

14006

已选择 1 行。

DINGJUN123>SET AUTOTRACE TRACEONLY

DINGJUN123>VAR str VARCHAR2(100);

DINGJUN123>EXEC :str :='XY,YZ';

PL/SQL 过程已成功完成。

DINGJUN123> SELECT *

2 FROM t

3 WHERE object_name IN

4 (SELECT column_value

5 FROM TABLE( CAST( f_str2list(:str) AS varchar2_tt ))

6 );

已选择2行。

执行计划

----------------------------------------------------------

Plan hash value: 3487633200

-------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 88 | 84 (3)| 00:00:02 |

|* 1 | HASH JOIN RIGHT SEMI | | 1 | 88 | 84 (3)| 00:00:02 |

| 3 | TABLE ACCESS FULL | T | 14006 | 1176K| 54 (2)| 00:00:01 |

-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - access("OBJECT_NAME"=VALUE(KOKBF$))

统计信息

----------------------------------------------------------

927 recursive calls

0 db block gets

486 consistent gets

233 physical reads

0 redo size

1257 bytes sent via SQL*Net to client

384 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

8 sorts (memory)

0 sorts (disk)

2 rows processed

第1次硬解析，为了比较再次执行，统计信息为：

统计信息

----------------------------------------------------------

0 recursive calls

0 db block gets

184 consistent gets

0 physical reads

0 redo size

1257 bytes sent via SQL*Net to client

384 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

2 rows processed

从上面结果看到，上面SQL采用的是Hash join的连接方式，全表访问表t，第1次执行逻辑读很大，为486，第2次再执行，逻辑读为184，平均每行逻辑读为92(184/2)，这种计划是很差的。那为什么Oracle会采用这种计划呢?如下分析：

DINGJUN123>SELECT/*+first_rows*/ *

2 FROM TABLE( CAST( f_str2list(:str) AS varchar2_tt ));

已选择2行。

执行计划

----------------------------------------------------------

Plan hash value: 2025561284

------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 8168 | 16336 | 29 (0)| 00:00:01 |

------------------------------------------------------------------------------------------------

从上面结果看出，TABLE函数的默认行数是8168行(TABLE函数创建的伪表是没有统计信息的)，这个值不小了，一般比实际应用中的行数要多的多，经常导致执行计划走hash join，而不是nested loop。怎么改变这种情况呢?当然是加hint提示来改变执行计划了,对binging in list，常常使用的hint有：first_rows，index，cardinality等。这里特别介绍下cardinality(table|alias,n)，这个hint很有用，它可以让CBO优化器认为表的行数是n，这样就可以改变执行计划了。现在改写上面的查询：

DINGJUN123>SELECT/*+cardinality(tab,5)*/ column_value

2 FROM TABLE( CAST( f_str2list(:str) AS varchar2_tt )) tab;

已选择2行。

执行计划

----------------------------------------------------------

Plan hash value: 2025561284

------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 5 | 10 | 29 (0)| 00:00:01 |

------------------------------------------------------------------------------------------------

加了cardinality(tab,5)自动走CBO优化器了，优化器把表的基数看成5，前面的binging in list查询基数默认为8168的时候走的是hash join，现在有了cardinality，测试如下：

DINGJUN123> SELECT *

2 FROM t

3 WHERE object_name IN

4 (SELECT /*+cardinality(tab,5)*/ column_value

5 FROM TABLE( CAST( f_str2list(:str) AS varchar2_tt )) tab

6 );

已选择2行。

执行计划

----------------------------------------------------------

Plan hash value: 4129437246

------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 6 | 528 | 36 (3)| 00:00:0

| 1 | TABLE ACCESS BY INDEX ROWID | T | 1 | 86 | 2 (0)| 00:00:0

| 2 | NESTED LOOPS | | 6 | 528 | 36 (3)| 00:00:0

| 3 | SORT UNIQUE | | | | |

|* 5 | INDEX RANGE SCAN | IDX_T | 1 | | 1 (0)| 00:00:0

------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

5 - access("OBJECT_NAME"=VALUE(KOKBF$))

统计信息

----------------------------------------------------------

590 recursive calls

0 db block gets

149 consistent gets

14 physical reads

0 redo size

1257 bytes sent via SQL*Net to client

384 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

6 sorts (memory)

0 sorts (disk)

2 rows processed

看下第2次软解析的统计信息：

统计信息

----------------------------------------------------------

0 recursive calls

0 db block gets

7 consistent gets

0 physical reads

0 redo size

1257 bytes sent via SQL*Net to client

384 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

1 sorts (memory)

0 sorts (disk)

2 rows processed

VAR str VARCHAR2(100);

EXEC :str :='XY,YZ';

SELECT count(*)

FROM t

WHERE object_name IN

(SELECT column_value

FROM TABLE( CAST(f_str2list(:str) AS varchar2_tt )

)

);

lan hash value: 2165785974

---------------------------------------------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.02 | 475 | | | |

| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.02 | 475 | | | |

|* 2 | HASH JOIN RIGHT SEMI | | 1 | 1 | 0 |00:00:00.02 | 475 | 2278K| 2278K| 818K (0)|

| 3 | COLLECTION ITERATOR PICKLER FETCH| F_STR2LIST | 1 | 8168 | 2 |00:00:00.01 | 0 | | | |

| 4 | INDEX FAST FULL SCAN | IDX_T | 1 | 73750 | 73750 |00:00:00.01 | 475 | | | |

---------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - access("OBJECT_NAME"=VALUE(KOKBF$))

23 rows selected.

## 动态采样准确，走nested loops,逻辑读变为6

SELECT/*+dynamic_sampling(3)*/ count(*)

FROM t

WHERE object_name IN

(SELECT column_value

FROM TABLE( CAST(f_str2list(:str) AS varchar2_tt )

)

);

PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

SQL_ID 2pty799rhf7my, child number 0

-------------------------------------

SELECT/*+dynamic_sampling(3)*/ count(*) FROM t WHERE

object_name IN (SELECT column_value FROM TABLE(

CAST(f_str2list(:str) AS varchar2_tt ) ) )

Plan hash value: 2224312052

----------------------------------------------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 6 | | | |

| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 6 | | | |

| 2 | NESTED LOOPS | | 1 | 1 | 0 |00:00:00.01 | 6 | | | |

| 3 | SORT UNIQUE | | 1 | 2 | 2 |00:00:00.01 | 0 | 2048 | 2048 | 2048 (0)|

| 4 | COLLECTION ITERATOR PICKLER FETCH| F_STR2LIST | 1 | 2 | 2 |00:00:00.01 | 0 | | | |

|* 5 | INDEX RANGE SCAN | IDX_T | 2 | 1 | 0 |00:00:00.01 | 6 | | | |

----------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

5 - access("OBJECT_NAME"=VALUE(KOKBF$))

Note

-----

- dynamic statistics used: dynamic sampling (level=3)

###PG

select t11.* from t11 where a in (1,2,3,4,5);

QUERY PLAN

----------------------------------------------------------------------

Index Scan using idxa_t11 on t11 (cost=0.29..25.51 rows=5 width=50)

Index Cond: (a = ANY ('{1,2,3,4,5}'::integer[]))

(2 rows)

###转换成临时表，用values(xxx,mysql使用values row(....

select t11.* from t11 where a in (

select t2.a from

(values (1),(2),(3),(4),(5)) as t2(a)

);

QUERY PLAN

---------------------------------------------------------------------------

Nested Loop (cost=0.36..41.69 rows=5 width=50)

-> HashAggregate (cost=0.07..0.12 rows=5 width=4)

Group Key: "*VALUES*".column1

-> Values Scan on "*VALUES*" (cost=0.00..0.06 rows=5 width=4)

-> Index Scan using idxa_t11 on t11 (cost=0.29..8.30 rows=1 width=50)

Index Cond: (a = "*VALUES*".column1)

(6 rows)

ITPub博客

MySQL in list使用row constructor或临时表优化

最新文章