Re: Optimizer executes inperformant subquery first
Posted by: Martin Kirchner
Date: December 20, 2013 02:24AM
Date: December 20, 2013 02:24AM
Well, I increased the memory size. The MISSING_BYTES_BEYOND_MAX_MEM_SIZE is now 0.
However, the trace is still truncated. Probably it has something to do with the warning displayed:
The second issue that makes the analysis more difficult is that the LIVE system's optimizer now seems to evaluate the more restrictive subquery first (I'll check the replication slave if the problems occurs there):
The new optimizer trace (still truncated):
However, the trace is still truncated. Probably it has something to do with the warning displayed:
kirchner@localhost [abc]>SELECT * INTO DUMPFILE '/tmp/optimizer-trace-live_20131220.json' FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE; Query OK, 1 row affected, 1 warning (0,07 sec) kirchner@localhost [abc]>show warnings; +---------+------+--------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+--------------------------------------------------------------------------+ | Warning | 1366 | Incorrect string value: '\xD9[\x943j\x99...' for column 'TRACE' at row 1 | +---------+------+--------------------------------------------------------------------------+ 1 row in set (0,00 sec)
The second issue that makes the analysis more difficult is that the LIVE system's optimizer now seems to evaluate the more restrictive subquery first (I'll check the replication slave if the problems occurs there):
kirchner@localhost [abc]>EXPLAIN SELECT ADDRESS.KUNDENWERT, ADDRESS.COMPNAME, ADDRESS.NAME, ADDRESS.CHRISTIANNAME, ADDRESS.CATEGORY_ASSURANCE, ADDRESS.ZIP1, ADDRESS.TOWN1, ADDRESS.AGENT_ID, ADDRESS.CORPORATE_ASSOCIATION_ID, ADDRESS.ASSURANCE_CUSTOMERID, ADDRESS.CORP_CUST_PREMIUM_CAR, ADDRESS.PREMIUM_NONK, ADDRESS.GGUID, ADDRESS.ACTIVITY_TYPE_ALLOWED, ADDRESS.DEACTIVATED FROM address WHERE (((ADDRESS.DEACTIVATED = 0) AND (ADDRESS.GGUID IN (SELECT selectionvaluesrelation.TableGUID FROM selectionvaluesrelation WHERE selectionvaluesrelation.ValueGUID IN (0xD95B94336A9946A39CF5B58CFE772D8C)))) AND (ADDRESS.GGUID IN (SELECT TABLEGUID FROM ADDRESSORel WHERE EIMRight >= 64 AND (OID IN (3900, -364, -92, -397, -270, -97, -428, -99, -153, -8, -320, -423, -424, -355, -98, -279, -96, -427, -319, -101, -88, -305, -91, -94, -90, -326, -50, -93, -95, 0) OR (OID IN (-364, -394, -375, -328, -327, -326, -325, -316, -154, -153, 3880, 3881, 4222, 3879, 4191, 3915, 4470, 3902, 3907, 4377, 3058, 3611, 3061, 3918, 3919, 3882, 3916, 3917, 4318, 4118, 3923, 3922, 3921, 3920, 3927, 3926, 3925, 3924, 3884, 3885, 3886, 3887, 3901, 3903, 4287, 3897, 4286, 3896, 3899, 3898, 3953, 3893, 3892, 3895, 3894, 3889, 3888, 3891, 3890, 3914, 3912, 3913, 3906, 4107, 3904, 3905, 3910, 3911, 3908, 4381, 3909, 4112, 4316, 4437, 3808, 4312, 4292, 4188, 4190, 3059, 3060, 3062, -270, -8, -279, -320, -88, -99, -90, -96, -424, -50, -94, -305, -101, -423, -92, -91, -98, -93, -97, -319, -355, -95, -427, -397, -396, -428, -426, -89, -317, -357, -407) AND ADDRESS.ForeignEditPermission = 65535))))) AND (ADDRESS.ISDELETED = 0 AND ADDRESS.ISTEMPLATE = 0) ORDER BY ADDRESS.AGENT_ID ASC LIMIT 0, 100; +----+-------------+-------------------------+------+-----------------------------------------------------------------------------------------------------------------------+---------------------------------------+---------+---------------------------------------+-------+-------------------------------------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------------------------+------+-----------------------------------------------------------------------------------------------------------------------+---------------------------------------+---------+---------------------------------------+-------+-------------------------------------------------------------------------+ | 1 | SIMPLE | selectionvaluesrelation | ref | IDX_SelectionValuesRelation_ValueGUID | IDX_SelectionValuesRelation_ValueGUID | 16 | const | 82482 | Using index condition; Using temporary; Using filesort; Start temporary | | 1 | SIMPLE | address | ref | IDX_ADDRESS0_GGUID,IDX_GGUID_UNIQUE | IDX_ADDRESS0_GGUID | 17 | abc.selectionvaluesrelation.TableGUID | 1 | Using where; End temporary | | 1 | SIMPLE | ADDRESSORel | ref | IDX_ADDRESSORel_OID_EIMRole,IDX_OID10,IDX_TableGUID11,IX_MIXOREL_V511,IDX_ADDRESSORel_Role,IDX_ADDRESSORel_Permission | IDX_TableGUID11 | 16 | abc.selectionvaluesrelation.TableGUID | 1 | Using where; FirstMatch(address) | +----+-------------+-------------------------+------+-----------------------------------------------------------------------------------------------------------------------+---------------------------------------+---------+---------------------------------------+-------+-------------------------------------------------------------------------+ 3 rows in set (0,01 sec)
The new optimizer trace (still truncated):
SELECT ADDRESS.KUNDENWERT, ADDRESS.COMPNAME, ADDRESS.NAME, ADDRESS.CHRISTIANNAME, ADDRESS.CATEGORY_ASSURANCE, ADDRESS.ZIP1, ADDRESS.TOWN1, ADDRESS.AGENT_ID, ADDRESS.CORPORATE_ASSOCIATION_ID, ADDRESS.ASSURANCE_CUSTOMERID, ADDRESS.CORP_CUST_PREMIUM_CAR, ADDRESS.PREMIUM_NONK, ADDRESS.GGUID, ADDRESS.ACTIVITY_TYPE_ALLOWED, ADDRESS.DEACTIVATED FROM ADDRESS0 AS address WHERE (((ADDRESS.DEACTIVATED = 0) AND (ADDRESS.GGUID IN (SELECT selectionvaluesrelation.TableGUID FROM selectionvaluesrelation WHERE selectionvaluesrelation.ValueGUID IN (0xD95B94336A9946A39CF5B58CFE772D8C)))) AND (ADDRESS.GGUID IN (SELECT TABLEGUID FROM ADDRESSORel WHERE EIMRight >= 64 AND (OID IN (3900, -364, -92, -397, -270, -97, -428, -99, -153, -8, -320, -423, -424, -355, -98, -279, -96, -427, -319, -101, -88, -305, -91, -94, -90, -326, -50, -93, -95, 0) OR (OID IN (-364, -394, -375, -328, -327, -326, -325, -316, -154, -153, 3880, 3881, 4222, 3879, 4191, 3915, 4470, 3902, 3907, 4377, 3058, 3611, 3061, 3918, 3919, 3882, 3916, 3917, 4318, 4118, 3923, 3922, 3921, 3920, 3927, 3926, 3925, 3924, 3884, 3885, 3886, 3887, 3901, 3903, 4287, 3897, 4286, 3896, 3899, 3898, 3953, 3893, 3892, 3895, 3894, 3889, 3888, 3891, 3890, 3914, 3912, 3913, 3906, 4107, 3904, 3905, 3910, 3911, 3908, 4381, 3909, 4112, 4316, 4437, 3808, 4312, 4292, 4188, 4190, 3059, 3060, 3062, -270, -8, -279, -320, -88, -99, -90, -96, -424, -50, -94, -305, -101, -423, -92, -91, -98, -93, -97, -319, -355, -95, -427, -397, -396, -428, -426, -89, -317, -357, -407) AND ADDRESS.ForeignEditPermission = 65535))))) AND (ADDRESS.ISDELETED = 0 AND ADDRESS.ISTEMPLATE = 0) ORDER BY ADDRESS.AGENT_ID ASC LIMIT 0, 100{
"steps": [
{
"join_preparation": {
"select#": 1,
"steps": [
{
"join_preparation": {
"select#": 2,
"steps": [
{
"expanded_query": "/* select#2 */ select `selectionvaluesrelation`.`TableGUID` from `selectionvaluesrelation` where (`selectionvaluesrelation`.`ValueGUID` = 0x9cf5b58cfe772d8c)"
},
{
"transformation": {
"select#": 2,
"from": "IN (SELECT)",
"to": "semijoin",
"chosen": true
}
}
]
}
},
{
"join_preparation": {
"select#": 3,
"steps": [
{
"expanded_query": "/* select#3 */ select `addressorel`.`TableGUID` from `addressorel` where ((`addressorel`.`EIMRight` >= 64) and ((`addressorel`.`OID` in (3900,-(364),-(92),-(397),-(270),-(97),-(428),-(99),-(153),-(8),-(320),-(423),-(424),-(355),-(98),-(279),-(96),-(427),-(319),-(101),-(88),-(305),-(91),-(94),-(90),-(326),-(50),-(93),-(95),0)) or ((`addressorel`.`OID` in (-(364),-(394),-(375),-(328),-(327),-(326),-(325),-(316),-(154),-(153),3880,3881,4222,3879,4191,3915,4470,3902,3907,4377,3058,3611,3061,3918,3919,3882,3916,3917,4318,4118,3923,3922,3921,3920,3927,3926,3925,3924,3884,3885,3886,3887,3901,3903,4287,3897,4286,3896,3899,3898,3953,3893,3892,3895,3894,3889,3888,3891,3890,3914,3912,3913,3906,4107,3904,3905,3910,3911,3908,4381,3909,4112,4316,4437,3808,4312,4292,4188,4190,3059,3060,3062,-(270),-(8),-(279),-(320),-(88),-(99),-(90),-(96),-(424),-(50),-(94),-(305),-(101),-(423),-(92),-(91),-(98),-(93),-(97),-(319),-(355),-(95),-(427),-(397),-(396),-(428),-(426),-(89),-(317),-(357),-(407))) and (`address`.`FOREIGNEDITPERMISSION` = 65535))))"
},
{
"transformation": {
"select#": 3,
"from": "IN (SELECT)",
"to": "semijoin",
"chosen": true
}
}
]
}
},
{
"expanded_query": "/* select#1 */ select `address`.`KundenWert` AS `KUNDENWERT`,`address`.`COMPNAME` AS `COMPNAME`,`address`.`NAME` AS `NAME`,`address`.`ChristianName` AS `CHRISTIANNAME`,`address`.`CATEGORY_ASSURANCE` AS `CATEGORY_ASSURANCE`,`address`.`ZIP1` AS `ZIP1`,`address`.`TOWN1` AS `TOWN1`,`address`.`AGENT_ID` AS `AGENT_ID`,`address`.`CORPORATE_ASSOCIATION_ID` AS `CORPORATE_ASSOCIATION_ID`,`address`.`ASSURANCE_CUSTOMERID` AS `ASSURANCE_CUSTOMERID`,`address`.`CORP_CUST_PREMIUM_CAR` AS `CORP_CUST_PREMIUM_CAR`,`address`.`PREMIUM_NONK` AS `PREMIUM_NONK`,`address`.`GGUID` AS `GGUID`,`address`.`ACTIVITY_TYPE_ALLOWED` AS `ACTIVITY_TYPE_ALLOWED`,`address`.`DEACTIVATED` AS `DEACTIVATED` from `address0` `address` where ((`address`.`DEACTIVATED` = 0) and `address`.`GGUID` in (/* select#2 */ select `selectionvaluesrelation`.`TableGUID` from `selectionvaluesrelation` where (`selectionvaluesrelation`.`ValueGUID` = 0x9cf5b58cfe772d8c)) and `address`.`GGUID` in (/* select#3 */ select `addressorel`.`TableGUID` from `addressorel` where ((`addressorel`.`EIMRight` >= 64) and ((`addressorel`.`OID` in (3900,-(364),-(92),-(397),-(270),-(97),-(428),-(99),-(153),-(8),-(320),-(423),-(424),-(355),-(98),-(279),-(96),-(427),-(319),-(101),-(88),-(305),-(91),-(94),-(90),-(326),-(50),-(93),-(95),0)) or ((`addressorel`.`OID` in (-(364),-(394),-(375),-(328),-(327),-(326),-(325),-(316),-(154),-(153),3880,3881,4222,3879,4191,3915,4470,3902,3907,4377,3058,3611,3061,3918,3919,3882,3916,3917,4318,4118,3923,3922,3921,3920,3927,3926,3925,3924,3884,3885,3886,3887,3901,3903,4287,3897,4286,3896,3899,3898,3953,3893,3892,3895,3894,3889,3888,3891,3890,3914,3912,3913,3906,4107,3904,3905,3910,3911,3908,4381,3909,4112,4316,4437,3808,4312,4292,4188,4190,3059,3060,3062,-(270),-(8),-(279),-(320),-(88),-(99),-(90),-(96),-(424),-(50),-(94),-(305),-(101),-(423),-(92),-(91),-(98),-(93),-(97),-(319),-(355),-(95),-(427),-(397),-(396),-(428),-(426),-(89),-(317),-(357),-(407))) and (`address`.`FOREIGNEDITPERMISSION` = 65535))))) and (`address`.`IsDeleted` = 0) and (`address`.`IsTemplate` = 0)) order by `address`.`AGENT_ID` limit 0,100"
}
]
}
},
{
"join_optimization": {
"select#": 1,
"steps": [
{
"transformation": {
"select#": 3,
"from": "IN (SELECT)",
"to": "semijoin",
"chosen": true,
"evaluating_constant_semijoin_conditions": [
]
}
},
{
"transformation": {
"select#": 2,
"from": "IN (SELECT)",
"to": "semijoin",
"chosen": true,
"evaluating_constant_semijoin_conditions": [
]
}
},
{
"transformations_to_nested_joins": {
"transformations": [
"semijoin"
],
"expanded_query": "/* select#1 */ select `address`.`KundenWert` AS `KUNDENWERT`,`address`.`COMPNAME` AS `COMPNAME`,`address`.`NAME` AS `NAME`,`address`.`ChristianName` AS `CHRISTIANNAME`,`address`.`CATEGORY_ASSURANCE` AS `CATEGORY_ASSURANCE`,`address`.`ZIP1` AS `ZIP1`,`address`.`TOWN1` AS `TOWN1`,`address`.`AGENT_ID` AS `AGENT_ID`,`address`.`CORPORATE_ASSOCIATION_ID` AS `CORPORATE_ASSOCIATION_ID`,`address`.`ASSURANCE_CUSTOMERID` AS `ASSURANCE_CUSTOMERID`,`address`.`CORP_CUST_PREMIUM_CAR` AS `CORP_CUST_PREMIUM_CAR`,`address`.`PREMIUM_NONK` AS `PREMIUM_NONK`,`address`.`GGUID` AS `GGUID`,`address`.`ACTIVITY_TYPE_ALLOWED` AS `ACTIVITY_TYPE_ALLOWED`,`address`.`DEACTIVATED` AS `DEACTIVATED` from `address0` `address` semi join (`addressorel`) semi join (`selectionvaluesrelation`) where ((`address`.`DEACTIVATED` = 0) and 1 and 1 and (`address`.`IsDeleted` = 0) and (`address`.`IsTemplate` = 0) and (`addressorel`.`EIMRight` >= 64) and ((`addressorel`.`OID` in (3900,-(364),-(92),-(397),-(270),-(97),-(428),-(99),-(153),-(8),-(320),-(423),-(424),-(355),-(98),-(279),-(96),-(427),-(319),-(101),-(88),-(305),-(91),-(94),-(90),-(326),-(50),-(93),-(95),0)) or ((`addressorel`.`OID` in (-(364),-(394),-(375),-(328),-(327),-(326),-(325),-(316),-(154),-(153),3880,3881,4222,3879,4191,3915,4470,3902,3907,4377,3058,3611,3061,3918,3919,3882,3916,3917,4318,4118,3923,3922,3921,3920,3927,3926,3925,3924,3884,3885,3886,3887,3901,3903,4287,3897,4286,3896,3899,3898,3953,3893,3892,3895,3894,3889,3888,3891,3890,3914,3912,3913,3906,4107,3904,3905,3910,3911,3908,4381,3909,4112,4316,4437,3808,4312,4292,4188,4190,3059,3060,3062,-(270),-(8),-(279),-(320),-(88),-(99),-(90),-(96),-(424),-(50),-(94),-(305),-(101),-(423),-(92),-(91),-(98),-(93),-(97),-(319),-(355),-(95),-(427),-(397),-(396),-(428),-(426),-(89),-(317),-(357),-(407))) and (`address`.`FOREIGNEDITPERMISSION` = 65535))) and (`address`.`GGUID` = `addressorel`.`TableGUID`) and (`selectionvaluesrelation`.`ValueGUID` = 0x9cf5b58cfe772d8c) and (`address`.`GGUID` = `selectionvaluesrelation`.`TableGUID`)) order by `address`.`AGENT_ID` limit 0,100"
}
},
{
"condition_processing": {
"condition": "WHERE",
"original_condition": "((`address`.`DEACTIVATED` = 0) and 1 and 1 and (`address`.`IsDeleted` = 0) and (`address`.`IsTemplate` = 0) and (`addressorel`.`EIMRight` >= 64) and ((`addressorel`.`OID` in (3900,-(364),-(92),-(397),-(270),-(97),-(428),-(99),-(153),-(8),-(320),-(423),-(424),-(355),-(98),-(279),-(96),-(427),-(319),-(101),-(88),-(305),-(91),-(94),-(90),-(326),-(50),-(93),-(95),0)) or ((`addressorel`.`OID` in (-(364),-(394),-(375),-(328),-(327),-(326),-(325),-(316),-(154),-(153),3880,3881,4222,3879,4191,3915,4470,3902,3907,4377,3058,3611,3061,3918,3919,3882,3916,3917,4318,4118,3923,3922,3921,3920,3927,3926,3925,3924,3884,3885,3886,3887,3901,3903,4287,3897,4286,3896,3899,3898,3953,3893,3892,3895,3894,3889,3888,3891,3890,3914,3912,3913,3906,4107,3904,3905,3910,3911,3908,4381,3909,4112,4316,4437,3808,4312,4292,4188,4190,3059,3060,3062,-(270),-(8),-(279),-(320),-(88),-(99),-(90),-(96),-(424),-(50),-(94),-(305),-(101),-(423),-(92),-(91),-(98),-(93),-(97),-(319),-(355),-(95),-(427),-(397),-(396),-(428),-(426),-(89),-(317),-(357),-(407))) and (`address`.`FOREIGNEDITPERMISSION` = 65535))) and (`address`.`GGUID` = `addressorel`.`TableGUID`) and (`selectionvaluesrelation`.`ValueGUID` = 0x9cf5b58cfe772d8c) and (`address`.`GGUID` = `selectionvaluesrelation`.`TableGUID`))",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "(1 and 1 and (`addressorel`.`EIMRight` >= 64) and ((`addressorel`.`OID` in (3900,-(364),-(92),-(397),-(270),-(97),-(428),-(99),-(153),-(8),-(320),-(423),-(424),-(355),-(98),-(279),-(96),-(427),-(319),-(101),-(88),-(305),-(91),-(94),-(90),-(326),-(50),-(93),-(95),0)) or ((`addressorel`.`OID` in (-(364),-(394),-(375),-(328),-(327),-(326),-(325),-(316),-(154),-(153),3880,3881,4222,3879,4191,3915,4470,3902,3907,4377,3058,3611,3061,3918,3919,3882,3916,3917,4318,4118,3923,3922,3921,3920,3927,3926,3925,3924,3884,3885,3886,3887,3901,3903,4287,3897,4286,3896,3899,3898,3953,3893,3892,3895,3894,3889,3888,3891,3890,3914,3912,3913,3906,4107,3904,3905,3910,3911,3908,4381,3909,4112,4316,4437,3808,4312,4292,4188,4190,3059,3060,3062,-(270),-(8),-(279),-(320),-(88),-(99),-(90),-(96),-(424),-(50),-(94),-(305),-(101),-(423),-(92),-(91),-(98),-(93),-(97),-(319),-(355),-(95),-(427),-(397),-(396),-(428),-(426),-(89),-(317),-(357),-(407))) and multiple equal(65535, `address`.`FOREIGNEDITPERMISSION`))) and multiple equal(0, `address`.`DEACTIVATED`) and multiple equal(0, `address`.`IsDeleted`) and multiple equal(0, `address`.`IsTemplate`) and multiple equal(`address`.`GGUID`, `addressorel`.`TableGUID`, `selectionvaluesrelation`.`TableGUID`) and multiple equal(0x9cf5b58cfe772d8c, `selectionvaluesrelation`.`ValueGUID`))"
},
{
"transformation": "constant_propagation",
"resulting_condition": "(1 and 1 and (`addressorel`.`EIMRight` >= 64) and ((`addressorel`.`OID` in (3900,-(364),-(92),-(397),-(270),-(97),-(428),-(99),-(153),-(8),-(320),-(423),-(424),-(355),-(98),-(279),-(96),-(427),-(319),-(101),-(88),-(305),-(91),-(94),-(90),-(326),-(50),-(93),-(95),0)) or ((`addressorel`.`OID` in (-(364),-(394),-(375),-(328),-(327),-(326),-(325),-(316),-(154),-(153),3880,3881,4222,3879,4191,3915,4470,3902,3907,4377,3058,3611,3061,3918,3919,3882,3916,3917,4318,4118,3923,3922,3921,3920,3927,3926,3925,3924,3884,3885,3886,3887,3901,3903,4287,3897,4286,3896,3899,3898,3953,3893,3892,3895,3894,3889,3888,3891,3890,3914,3912,3913,3906,4107,3904,3905,3910,3911,3908,4381,3909,4112,4316,4437,3808,4312,4292,4188,4190,3059,3060,3062,-(270),-(8),-(279),-(320),-(88),-(99),-(90),-(96),-(424),-(50),-(94),-(305),-(101),-(423),-(92),-(91),-(98),-(93),-(97),-(319),-(355),-(95),-(427),-(397),-(396),-(428),-(426),-(89),-(317),-(357),-(407))) and multiple equal(65535, `address`.`FOREIGNEDITPERMISSION`))) and multiple equal(0, `address`.`DEACTIVATED`) and multiple equal(0, `address`.`IsDeleted`) and multiple equal(0, `address`.`IsTemplate`) and multiple equal(`address`.`GGUID`, `addressorel`.`TableGUID`, `selectionvaluesrelation`.`TableGUID`) and multiple equal(0x9cf5b58cfe772d8c, `selectionvaluesrelation`.`ValueGUID`))"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "((`addressorel`.`EIMRight` >= 64) and ((`addressorel`.`OID` in (3900,-(364),-(92),-(397),-(270),-(97),-(428),-(99),-(153),-(8),-(320),-(423),-(424),-(355),-(98),-(279),-(96),-(427),-(319),-(101),-(88),-(305),-(91),-(94),-(90),-(326),-(50),-(93),-(95),0)) or ((`addressorel`.`OID` in (-(364),-(394),-(375),-(328),-(327),-(326),-(325),-(316),-(154),-(153),3880,3881,4222,3879,4191,3915,4470,3902,3907,4377,3058,3611,3061,3918,3919,3882,3916,3917,4318,4118,3923,3922,3921,3920,3927,3926,3925,3924,3884,3885,3886,3887,3901,3903,4287,3897,4286,3896,3899,3898,3953,3893,3892,3895,3894,3889,3888,3891,3890,3914,3912,3913,3906,4107,3904,3905,3910,3911,3908,4381,3909,4112,4316,4437,3808,4312,4292,4188,4190,3059,3060,3062,-(270),-(8),-(279),-(320),-(88),-(99),-(90),-(96),-(424),-(50),-(94),-(305),-(101),-(423),-(92),-(91),-(98),-(93),-(97),-(319),-(355),-(95),-(427),-(397),-(396),-(428),-(426),-(89),-(317),-(357),-(407))) and multiple equal(65535, `address`.`FOREIGNEDITPERMISSION`))) and multiple equal(0, `address`.`DEACTIVATED`) and multiple equal(0, `address`.`IsDeleted`) and multiple equal(0, `address`.`IsTemplate`) and multiple equal(`address`.`GGUID`, `addressorel`.`TableGUID`, `selectionvaluesrelation`.`TableGUID`) and multiple equal(0x9cf5b58cfe772d8c, `selectionvaluesrelation`.`ValueGUID`))"
}
]
}
},
{
"table_dependencies": [
{
"table": "`address0` `address`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
]
},
{
"table": "`addressorel`",
"row_may_be_null": false,
"map_bit": 1,
"depends_on_map_bits": [
]
},
{
"table": "`selectionvaluesrelation`",
"row_may_be_null": false,
"map_bit": 2,
"depends_on_map_bits": [
]
}
]
},
{
"ref_optimizer_key_uses": [
{
"table": "`address0` `address`",
"field": "GGUID",
"equals": "`addressorel`.`TableGUID`",
"null_rejecting": false
},
{
"table": "`address0` `address`",
"field": "GGUID",
"equals": "`selectionvaluesrelation`.`TableGUID`",
"null_rejecting": false
},
{
"table": "`address0` `address`",
"field": "GGUID",
"equals": "`addressorel`.`TableGUID`",
"null_rejecting": false
},
{
"table": "`address0` `address`",
"field": "GGUID",
"equals": "`selectionvaluesrelation`.`TableGUID`",
"null_rejecting": false
},
{
"table": "`addressorel`",
"field": "TableGUID",
"equals": "`selectionvaluesrelation`.`TableGUID`",
"null_rejecting": false
},
{
"table": "`addressorel`",
"field": "TableGUID",
"equals": "`address`.`GGUID`",
"null_rejecting": true
},
{
"table": "`addressorel`",
"field": "TableGUID",
"equals": "`address`.`GGUID`",
"null_rejecting": true
},
{
"table": "`addressorel`",
"field": "TableGUID",
"equals": "`selectionvaluesrelation`.`TableGUID`",
"null_rejecting": false
},
{
"table": "`selectionvaluesrelation`",
"field": "ValueGUID",
"equals": "0x9cf5b58cfe772d8c",
"null_rejecting": false
}
]
},
{
"pulled_out_semijoin_tables": [
]
},
{
"rows_estimation": [
{
"table": "`address0` `address`",
"table_scan": {
"rows": 287436,
"cost": 16640
}
},
{
"table": "`addressorel`",
"range_analysis": {
"table_scan": {
"rows": 664051,
"cost": 137333
},
"potential_range_indices": [
{
"index": "PRIMARY",
"usable": false,
"cause": "not_applicable"
},
{
"index": "IDX_ADDRESSORel_OID_EIMRole",
"usable": true,
"key_parts": [
"OID",
"EIMRole",
"GGUID"
]
},
{
"index": "IDX_OID10",
"usable": true,
"key_parts": [
"OID",
"GGUID"
]
},
{
"index": "IDX_TableGUID11",
"usable": false,
"cause": "not_applicable"
},
{
"index": "IX_MIXOREL_V511",
"usable": false,
"cause": "not_applicable"
},
{
"index": "IDX_ADDRESSORel_Role",
"usable": true,
"key_parts": [
"OID",
"EIMRole",
"TableGUID",
"GGUID"
]
},
{
"index": "IDX_ADDRESSORel_Permission",
"usable": true,
"key_parts": [
"EIMRight",
"OID",
"TableGUID",
"GGUID"
]
}
],
"best_covering_index_scan": {
"index": "IDX_ADDRESSORel_Permission",
"cost": 136050,
"chosen": true
},
"setup_range_conditions": [
],
"group_index_range": {
"chosen": false,
"cause": "not_single_table"
},
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "IDX_ADDRESSORel_OID_EIMRole",
"ranges": [
"-428 <= OID <= -428",
"-427 <= OID <= -427",
"-426 <= OID <= -426",
"-424 <= OID <= -424",
"-423 <= OID <= -423",
"-407 <= OID <= -407",
"-397 <= OID <= -397",
"-396 <= OID <= -396",
"-394 <= OID <= -394",
"-375 <= OID <= -375",
"-364 <= OID <= -364",
"-357 <= OID <= -357",
"-355 <= OID <= -355",
"-328 <= OID <= -328",
"-327 <= OID <= -327",
"-326 <= OID <= -326",
"-325 <= OID <= -325",
"-320 <= OID <= -320",
"-319 <= OID <= -319",
"-317 <= OID <= -317",
"-316 <= OID <= -316",
"-305 <= OID <= -305",
"-279 <= OID <= -279",
"-270 <= OID <= -270",
"-154 <= OID <= -154",
"-153 <= OID <= -153",
"-101 <= OID <= -101",
"-99 <= OID <= -99",
"-98 <= OID <= -98",
"-97 <= OID <= -97",
"-96 <= OID <= -96",
"-95 <= OID <= -95",
"-94 <= OID <= -94",
"-93 <= OID <= -93",
"-92 <= OID <= -92",
"-91 <= OID <= -91",
"-90 <= OID <= -90",
"-89 <= OID <= -89",
"-88 <= OID <= -88",
"-50 <= OID <= -50",
"-8 <= OID <= -8",
"0 <= OID <= 0",
"3058 <= OID <= 3058",
"3059 <= OID <= 3059",
"3060 <= OID <= 3060",
"3061 <= OID <= 3061",
"3062 <= OID <= 3062",
"3611 <= OID <= 3611",
"3808 <= OID <= 3808",
"3879 <= OID <= 3879",
"3880 <= OID <= 3880",
"3881 <= OID <= 3881",
"3882 <= OID <= 3882",
"3884 <= OID <= 3884",
"3885 <= OID <= 3885",
"3886 <= OID <= 3886",
"3887 <= OID <= 3887",
"3888 <= OID <= 3888",
"3889 <= OID <= 3889",
"3890 <= OID <= 3890",
"3891 <= OID <= 3891",
"3892 <= OID <= 3892",
"3893 <= OID <= 3893",
"3894 <= OID <= 3894",
"3895 <= OID <= 3895",
"3896 <= OID <= 3896",
"3897 <= OID <= 3897",
"3898 <= OID <= 3898",
"3899 <= OID <= 3899",
"3900 <= OID <= 3900",
"3901 <= OID <= 3901",
"3902 <= OID <= 3902",
"3903 <= OID <= 3903",
"3904 <= OID <= 3904",
"3905 <= OID <= 3905",
"3906 <= OID <= 3906",
"3907 <= OID <= 3907",
"3908 <= OID <= 3908",
"3909 <= OID <= 3909",
"3910 <= OID <= 3910",
"3911 <= OID <= 3911",
"3912 <= OID <= 3912",
"3913 <= OID <= 3913",
"3914 <= OID <= 3914",
"3915 <= OID <= 3915",
"3916 <= OID <= 3916",
"3917 <= OID <= 3917",
"3918 <= OID <= 3918",
"3919 <= OID <= 3919",
"3920 <= OID <= 3920",
"3921 <= OID <= 3921",
"3922 <= OID <= 3922",
"3923 <= OID <= 3923",
"3924 <= OID <= 3924",
"3925 <= OID <= 3925",
"3926 <= OID <= 3926",
"3927 <= OID <= 3927",
"3953 <= OID <= 3953",
"4107 <= OID <= 4107",
"4112 <= OID <= 4112",
"4118 <= OID <= 4118",
"4188 <= OID <= 4188",
"4190 <= OID <= 4190",
"4191 <= OID <= 4191",
"4222 <= OID <= 4222",
"4286 <= OID <= 4286",
"4287 <= OID <= 4287",
"4292 <= OID <= 4292",
"4312 <= OID <= 4312",
"4316 <= OID <= 4316",
"4318 <= OID <= 4318",
"4377 <= OID <= 4377",
"4381 <= OID <= 4381",
"4437 <= OID <= 4437",
"4470 <= OID <= 4470"
],
"index_dives_for_eq_ranges": false,
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
"rows": 67390,
"cost": 80983,
"chosen": true
},
{
"index": "IDX_OID10",
"ranges": [
"-428 <= OID <= -428",
"-427 <= OID <= -427",
"-426 <= OID <= -426",
"-424 <= OID <= -424",
"-423 <= OID <= -423",
"-407 <= OID <= -407",
"-397 <= OID <= -397",
"-396 <= OID <= -396",
"-394 <= OID <= -394",
"-375 <= OID <= -375",
"-364 <= OID <= -364",
"-357 <= OID <= -357",
"-355 <= OID <= -355",
"-328 <= OID <= -328",
"-327 <= OID <= -327",
"-326 <= OID <= -326",
"-325 <= OID <= -325",
"-320 <= OID <= -320",
"-319 <= OID <= -319",
"-317 <= OID <= -317",
"-316 <= OID <= -316",
"-305 <= OID <= -305",
"-279 <= OID <= -279",
"-270 <= OID <= -270",
"-154 <= OID <= -154",
"-153 <= OID <= -153",
"-101 <= OID <= -101",
"-99 <= OID <= -99",
"-98 <= OID <= -98",
"-97 <= OID <= -97",
"-96 <= OID <= -96",
"-95 <= OID <= -95",
"-94 <= OID <= -94",
"-93 <= OID <= -93",
"-92 <= OID <= -92",
"-91 <= OID <= -91",
"-90 <= OID <= -90",
"-89 <= OID <= -89",
"-88 <= OID <= -88",
"-50 <= OID <= -50",
"-8 <= OID <= -8",
"0 <= OID <= 0",
"3058 <= OID <= 3058",
"3059 <= OID <= 3059",
"3060 <= OID <= 3060",
"3061 <= OID <= 3061",
"3062 <= OID <= 3062",
"3611 <= OID <= 3611",
"3808 <= OID <= 3808",
"3879 <= OID <= 3879",
"3880 <= OID <= 3880",
"3881 <= OID <= 3881",
"3882 <= OID <= 3882",
"3884 <= OID <= 3884",
"3885 <= OID <= 3885",
"3886 <= OID <= 3886",
"3887 <= OID <= 3887",
"3888 <= OID <= 3888",
"3889 <= OID <= 3889",
"3890 <= OID <= 3890",
"3891 <= OID <= 3891",
"3892 <= OID <= 3892",
"3893 <= OID <= 3893",
"3894 <= OID <= 3894",
"3895 <= OID <= 3895",
"3896 <= OID <= 3896",
"3897 <= OID <= 3897",
"3898 <= OID <= 3898",
"3899 <= OID <= 3899",
"3900 <= OID <= 3900",
"3901 <= OID <= 3901",
"3902 <= OID <= 3902",
"3903 <= OID <= 3903",
"3904 <= OID <= 3904",
"3905 <= OID <= 3905",
"3906 <= OID <= 3906",
"3907 <= OID <= 3907",
"3908 <= OID <= 3908",
"3909 <= OID <= 3909",
"3910 <= OID <= 3910",
"3911 <= OID <= 3911",
"3912 <= OID <= 3912",
"3913 <= OID <= 3913",
"3914 <= OID <= 3914",
"3915 <= OID <= 3915",
"3916 <= OID <= 3916",
"3917 <= OID <= 3917",
"3918 <= OID <= 3918",
"3919 <= OID <= 3919",
"3920 <= OID <= 3920",
"3921 <= OID <= 3921",
"3922 <= OID <= 3922",
"3923 <= OID <= 3923",
"3924 <= OID <= 3924",
"3925 <= OID <= 3925",
"3926 <= OID <= 3926",
"3927 <= OID <= 3927",
"3953 <= OID <= 3953",
"4107 <= OID <= 4107",
"4112 <= OID <= 4112",
"4118 <= OID <= 4118",
"4188 <= OID <= 4188",
"4190 <= OID <= 4190",
"4191 <= OID <= 4191",
"4222 <= OID <= 4222",
"4286 <= OID <= 4286",
"4287 <= OID <= 4287",
"4292 <= OID <= 4292",
"4312 <= OID <= 4312",
"4316 <= OID <= 4316",
"4318 <= OID <= 4318",
"4377 <= OID <= 4377",
"4381 <= OID <= 4381",
"4437 <= OID <= 4437",
"4470 <= OID <= 4470"
],
"index_dives_for_eq_ranges": false,
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
"rows": 64975,
"cost": 78085,
"chosen": true
},
{
"index": "IDX_ADDRESSORel_Role",
"ranges": [
"-428 <= OID <= -428",
"-427 <= OID <= -427",
"-426 <= OID <= -426",
"-424 <= OID <= -424",
"-423 <= OID <= -423",
"-407 <= OID <= -407",
"-397 <= OID <= -397",
"-396 <= OID <= -396",
"-394 <= OID <= -394",
"-375 <= OID <= -375",
"-364 <= OID <= -364",
"-357 <= OID <= -357",
"-355 <= OID <= -355",
"-328 <= OID <= -328",
"-327 <= OID <= -327",
"-326 <= OID <= -326",
"-325 <= OID <= -325",
"-320 <= OID <= -320",
"-319 <= OID <= -319",
"-317 <= OID <= -317",
"-316 <= OID <= -316",
"-305 <= OID <= -305",
"-279 <= OID <= -279",
"-270 <= OID <= -270",
"-154 <= OID <= -154",
"-153 <= OID <= -153",
"-101 <= OID <= -101",
"-99 <= OID <= -99",
"-98 <= OID <= -98",
"-97 <= OID <= -97",
"-96 <= OID <= -96",
"-95 <= OID <= -95",
"-94 <= OID <= -94",
"-93 <= OID <= -93",
"-92 <= OID <= -92",
"-91 <= OID <= -91",
"-90 <= OID <= -90",
"-89 <= OID <= -89",
"-88 <= OID <= -88",
"-50 <= OID <= -50",
"-8 <= OID <= -8",
"0 <= OID <= 0",
"3058 <= OID <= 3058",
"3059 <= OID <= 3059",
"3060 <= OID <= 3060",
"3061 <= OID <= 3061",
"3062 <= OID <= 3062",
"3611 <= OID <= 3611",
"3808 <= OID <= 3808",
"3879 <= OID <= 3879",
"3880 <= OID <= 3880",
"3881 <= OID <= 3881",
"3882 <= OID <= 3882",
"3884 <= OID <= 3884",
"3885 <= OID <= 3885",
"3886 <= OID <= 3886",
"3887 <= OID <= 3887",
"3888 <= OID <= 3888",
"3889 <= OID <= 3889",
"3890 <= OID <= 3890",
"3891 <= OID <= 3891",
"3892 <= OID <= 3892",
"3893 <= OID <= 3893",
"3894 <= OID <= 3894",
"3895 <= OID <= 3895",
"3896 <= OID <= 3896",
"3897 <= OID <= 3897",
"3898 <= OID <= 3898",
"3899 <= OID <= 3899",
"3900 <= OID <= 3900",
"3901 <= OID <= 3901",
"3902 <= OID <= 3902",
"3903 <= OID <= 3903",
"3904 <= OID <= 3904",
"3905 <= OID <= 3905",
"3906 <= OID <= 3906",
"3907 <= OID <= 3907",
"3908 <= OID <= 3908",
"3909 <= OID <= 3909",
"3910 <= OID <= 3910",
"3911 <= OID <= 3911",
"3912 <= OID <= 3912",
"3913 <= OID <= 3913",
"3914 <= OID <= 3914",
"3915 <= OID <= 3915",
"3916 <= OID <= 3916",
"3917 <= OID <= 3917",
"3918 <= OID <= 3918",
"3919 <= OID <= 3919",
"3920 <= OID <= 3920",
"3921 <= OID <= 3921",
"3922 <= OID <= 3922",
"3923 <= OID <= 3923",
"3924 <= OID <= 3924",
"3925 <= OID <= 3925",
"3926 <= OID <= 3926",
"3927 <= OID <= 3927",
"3953 <= OID <= 3953",
"4107 <= OID <= 4107",
"4112 <= OID <= 4112",
"4118 <= OID <= 4118",
"4188 <= OID <= 4188",
"4190 <= OID <= 4190",
"4191 <= OID <= 4191",
"4222 <= OID <= 4222",
"4286 <= OID <= 4286",
"4287 <= OID <= 4287",
"4292 <= OID <= 4292",
"4312 <= OID <= 4312",
"4316 <= OID <= 4316",
"4318 <= OID <= 4318",
"4377 <= OID <= 4377",
"4381 <= OID <= 4381",
"4437 <= OID <= 4437",
"4470 <= OID <= 4470"
],
"index_dives_for_eq_ranges": false,
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
"rows": 60605,
"cost": 72841,
"chosen": true
},
{
"index": "IDX_ADDRESSORel_Permission",
"ranges": [
"64 <= EIMRight"
],
"index_dives_for_eq_ranges": true,
"rowid_ordered": false,
"using_mrr": false,
"index_only": true,
"rows": 332025,
"cost": 68026,
"chosen": true
}
],
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
}
},
"chosen_range_access_summary": {
"range_access_plan": {
"type": "range_scan",
"index": "IDX_ADDRESSORel_Permission",
"rows": 332025,
"ranges": [
"64 <= EIMRight"
]
},
"rows_for_plan": 332025,
"cost_for_plan": 68026,
"chosen": true
}
}
},
{
"table": "`selectionvaluesrelation`",
"range_analysis": {
"table_scan": {
"rows": 2261879,
"cost": 472538
},
"potential_range_indices": [
{
"index": "PRIMARY",
"usable": false,
"cause": "not_applicable"
},
{
"index": "IDX_SelectionValuesRelation_TableGUID_ValueGUID",
"usable": false,
"cause": "not_applicable"
},
{
"index": "IDX_SelectionValuesRelation_ValueGUID",
"usable": true,
"key_parts": [
"ValueGUID",
"GGUID"
]
}
],
"best_covering_index_scan": {
"index": "IDX_SelectionValuesRelation_TableGUID_ValueGUID",
"cost": 498538,
"chosen": false,
"cause": "cost"
},
"setup_range_conditions": [
],
"group_index_range": {
"chosen": false,
"cause": "not_single_table"
},
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "IDX_SelectionValuesRelation_ValueGUID",
"ranges": [
"00
Subject
Views
Written By
Posted
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.