| Bug #5882 | Left Outer Join Malfunction | ||
|---|---|---|---|
| Submitted: | 4 Oct 2004 15:18 | Modified: | 17 Jan 2005 19:07 |
| Reporter: | Chris Wall | Email Updates: | |
| Status: | No Feedback | Impact on me: | |
| Category: | MaxDB | Severity: | S2 (Serious) |
| Version: | 7.5.00.23 | OS: | Linux (Linux) |
| Assigned to: | Assigned Account | CPU Architecture: | Any |
[4 Oct 2004 18:54]
Chris Wall
The complete statement causing the problem, I may have oversimplified it previously. This statement execute much quicker in version 16 and on Microsoft SQL. I realize that by rewriting the statement to the one included at the bottom of this message that I can get the same information, but unfortunately I don't have access to the code.
Statement causing the problem:
select movement.* from movement left outer join movement_order on
movement.id = movement_order.movement_id
and movement_order.sequence = 1 and movement_order.company_id =
'TMS' ,stop origin_stop ,stop dest_stop
,equipment_group where movement.company_id = 'TMS' and
equipment_group.id in ( select distinct equipment_group_id
from equipment_item where ( ( (equipment_item.equipment_id = '3276')
and equipment_item.type_sequence
= 0 and equipment_item.equipment_type_id = 'T' ) ) ) and
movement.origin_stop_id = origin_stop.id and
origin_stop.company_id = 'TMS' and movement.dest_stop_id =
dest_stop.id and dest_stop.company_id = 'TMS'
and movement.equipment_group_id = equipment_group.id and
equipment_group.company_id = 'TMS'
Rewritten statement that works:
select movement.* from movement left outer join movement_order on
movement.id = movement_order.movement_id and movement_order.sequence = 1 and
movement_order.company_id = 'TMS', stop, equipment_group where
movement.company_id = 'TMS' and equipment_group.id in ( select distinct
equipment_group_id from equipment_item where ( (
(equipment_item.equipment_id = '3276') and equipment_item.type_sequence = 0
and equipment_item.equipment_type_id = 'T' ) ) ) and
movement.origin_stop_id = stop.id and stop.company_id = 'TMS' and
movement.equipment_group_id = equipment_group.id and
equipment_group.company_id = 'TMS'
[26 Oct 2004 15:16]
Ulf Wendel
Hello Mr. Wall, can you send me the definition of the involved tables. I know that our developers will reject my questions as long as I cannot provide them with the table definitions and very likely with at least parts of the data. Have you checked the output of the EXPLAIN [VIEW] command for hints? Generally speaking it is very hard to give performance related hints without beeing able to log into the running database. But there is a general tipp which improves the performance in many cases. The MaxDB optimizer makes heavy use of the system table OPTIMIZERSTATISTICS to determin the best strategy for executing a SQL statement. You should update the optimizer statistics from time to time using UPDATE STATISTICS, especially after massive changes (loads, update, delete, insert...). The statistics are based on samples. Per default 20.000 rows per table are examined to "guess" good values for the optimizer statistics. If one of your table has becomes very large over the time (more than 1.000.00 rows) a sample of 20.000 rows is quite often simply not enough to guess good values. You can corret the sample size with CREATE/AKTER TABLE using absolute and relative (percentage) values and give UPDATE STATISTICS hints on the sample size. Best regards, Ulf Wendel
[26 Oct 2004 15:44]
Ulf Wendel
Thank you for this bug report. To properly diagnose the problem, we need a backtrace to see what is happening behind the scenes. To find out how to generate a backtrace, please read http://www.mysql.com/doc/en/Making_trace_files.html Once you have generated a backtrace, please submit it to this bug report and change the status back to 'Open'. Thank you for helping us make our products better.
[27 Oct 2004 10:01]
Ulf Wendel
Hello Chris, I have recieved your private mail to my mysql.com postbox and I think it is quite Ok to send certain informations to MySQL that way. But I have done some more research on your problem and found a note that might be of public interest. MaxDB has done some major changes to the join optimizer in 7.5. In some cases we recommend to deactivate the join optimization for 7.5 versions with build level less or equal than 16 (7.5.00 - 7.5.00.16). The deactivation can be done by setting the database parameter JOIN_OPERATOR_IMPLEMENTATION to "no". From build 17 on, you should turn on the optimization because it gives you performance benefits. However, you are using build 18. Please check that you have set JOIN_OPERATOR_IMPLEMENTATION to "yes". This is more interesting for you: build 18 uses a new algorithm for OUTER JOINS. You can disable the new algorithm by setting OPTIMIZE_JOIN_OUTER to "no". Can you check if that gives you a performance benefit? If it does do the trick for you, we have at least a workaround for you. Nevertheless we are intrested in getting out the reason of the slowdown. I will answer you to your email later on, so that we can find a way to check it out in more detail. Best regards, Ulf Wendel
[27 Oct 2004 12:26]
Ulf Wendel
Hi Chris, one of the developer provided me with more questions and suggestions: - check what happens if you add a hint for the optimizer: select /* +ordered */ movement.* ... ? - substitute the subselect with the output of the subselect and send us the EXPLAIN output of the resulting SQL command: ... equipment_group.id in (1, 2, 101, ...) .... - how many result sets does the subselect create? One more note about my previous comment earlier this day. Remember that parameter changes require a database restart. Thanks! Ulf
[27 Oct 2004 14:22]
Chris Wall
Changing the value of the JOIN_OPERATOR_IMPLEMENTATION from YES to NO did not fix the problem. I decided to try playing with some other values.
I went into the parameters and modified the JOIN_SEARCH_LEVEL which was set to 0. Using values 1..8 the query results returned quickly. It was only when this value was set to 9 that the results table overflowed. Can you give me more detail about this parameter and a recommend values besides 0 or 9.
I now have a work around to the problem.
Setting JOIN_SEARCH_LEVEL to 9 I ran the following test.
> - check what happens if you add a hint for the optimizer:
When I tried the following "select /* +ordered */ movement.* ..." it did not help the problem.
> - substitute the subselect with the output of the subselect and send us the
> EXPLAIN output of the resulting SQL command:
> ... equipment_group.id in (1, 2, 101, ...) ....
I substituted in ... equipment_group.id in ('WIN20010938020000408', 'WIN20010907659112710') ....
The results table still overflowed and result of the explain statement is
DEST_STOP TABLE SCAN 636
ORIGIN_STOP TABLE SCAN 636
LME MOVEMENT X_MOV_MOVEORIGIN JOIN VIA MULTIPLE INDEXED COLUMNS 310
ORIGIN_STOP_ID (USED INDEX COLUMN)
COMPANY_ID (USED INDEX COLUMN)
LME EQUIPMENT_GROUP JOIN VIA MULTIPLE KEY COLUMNS 30
ID (USED KEY COLUMN)
COMPANY_ID (USED KEY COLUMN)
LME MOVEMENT_ORDER X_MOO_MOVE JOIN VIA MULTIPLE INDEXED COLUMNS 52
MOVEMENT_ID (USED INDEX COLUMN)
SEQUENCE (USED INDEX COLUMN)
COMPANY_ID (USED INDEX COLUMN)
RESULT IS COPIED , COSTVALUE IS > 2 E10
> - how many result sets does the subselect create?
Between 0 and 100. With current data and the statement at the bottom the result returns 74 records
Chris
[29 Oct 2004 9:49]
Ulf Wendel
Okay Chris, next round :(. Our gurus are still wondering why it is so slow. One of them does not trust the optimizer statistics, especially the sample size. If possible, set the sample size to 100%, update the optimizer statistics and send us the explain output of the statement on a 7.5.00.16 installation and the output of it on your 7.5.00.18 installation. Please add the full SQL statement to you answer. As you did before, I would like you to replace the subselect with the result of the subselect. If you do not replace the subselect we cannot see the "real" execution plan. Can you also provide me with the table definitions? I think you offered it before and asked for the format. You can use the PAGES format. There is not much more to say about the parameter JOIN_SEARCH_LEVEL . You probably know the manual page: http://dev.mysql.com/doc/maxdb/en/f1/8e3d4014fa6f13e10000000a1550b0/frameset.htm Basic Informations - Concepts of ... - Performance - SQL Optimizer - How the optimizer works - Search Strategies - Joins - JOIN_SERCH_LEVEL ... You do not need to modify the default settings. Your query contains 4 tables after the substitution of the subselect. That means that the optimizer will calculate all possible join sequences and choose the best for you (default: JOIN_MAXTAB_LEVEL9 = 5). Best regards, Ulf
[29 Oct 2004 16:34]
Chris Wall
explain select movement.* from movement left outer join movement_order on movement.id = movement_order.movement_id
and movement_order.sequence = 1 and movement_order.company_id = 'TMS' ,stop origin_stop ,stop dest_stop
,equipment_group where movement.company_id = 'TMS' and equipment_group.id in ('WIN20010960313814061096032045017', 'WIN20010942206753321094222113664') and movement.origin_stop_id = origin_stop.id and
origin_stop.company_id = 'TMS' and movement.dest_stop_id = dest_stop.id and dest_stop.company_id = 'TMS'
and movement.equipment_group_id = equipment_group.id and equipment_group.company_id = 'TMS'
I updated the statistics using 100 percent. Since I don't have access to a .16 version database lets see if we can solve the problem without me having to set one up.
With the JOIN_SEARCH_LEVEL set to 8 the following is the result.
LME MOVEMENT X_MOV_MOVEEQGRP RANGE CONDITION FOR INDEX 330
EQUIPMENT_GROUP_ID (USED INDEX COLUMN)
COMPANY_ID (USED INDEX COLUMN)
ORIGIN_STOP JOIN VIA MULTIPLE KEY COLUMNS 679
ID (USED KEY COLUMN)
COMPANY_ID (USED KEY COLUMN)
DEST_STOP JOIN VIA MULTIPLE KEY COLUMNS 679
ID (USED KEY COLUMN)
COMPANY_ID (USED KEY COLUMN)
LME EQUIPMENT_GROUP JOIN VIA MULTIPLE KEY COLUMNS 32
TABLE HASHED
ID (USED KEY COLUMN)
COMPANY_ID (USED KEY COLUMN)
LME MOVEMENT_ORDER X_MOO_MOVE JOIN VIA MULTIPLE INDEXED COLUMNS 55
MOVEMENT_ID (USED INDEX COLUMN)
SEQUENCE (USED INDEX COLUMN)
COMPANY_ID (USED INDEX COLUMN)
NO TEMPORARY RESULTS CREATED
RESULT IS COPIED , COSTVALUE IS 1928
With the JOIN_SEARCH_LEVEL=0 the result is
DEST_STOP TABLE SCAN 679
ORIGIN_STOP COMPANY_ID_IDX JOIN VIA INDEXED COLUMN 679
LME MOVEMENT X_MOV_MOVEORIGIN JOIN VIA MULTIPLE INDEXED COLUMNS 330
ORIGIN_STOP_ID (USED INDEX COLUMN)
COMPANY_ID (USED INDEX COLUMN)
LME EQUIPMENT_GROUP JOIN VIA MULTIPLE KEY COLUMNS 32
TABLE HASHED
ID (USED KEY COLUMN)
COMPANY_ID (USED KEY COLUMN)
LME MOVEMENT_ORDER X_MOO_MOVE JOIN VIA MULTIPLE INDEXED COLUMNS 55
MOVEMENT_ID (USED INDEX COLUMN)
SEQUENCE (USED INDEX COLUMN)
COMPANY_ID (USED INDEX COLUMN)
NO TEMPORARY RESULTS CREATED
RESULT IS COPIED , COSTVALUE IS 36887474
CREATE TABLE "LME"."MOVEMENT"
(
"COMPANY_ID" Char (4) ASCII NOT NULL,
"AUTHORIZED" Char (1) ASCII,
"BROKERAGE" Char (1) ASCII,
"CARRIER_TRACTOR" Char (12) ASCII,
"CARRIER_TRAILER" Char (12) ASCII,
"DEST_STOP_ID" Char (32) ASCII,
"DISPATCHER_USER_ID" Char (10) ASCII,
"EMPTY2NEXT_ORDER" Char (1) ASCII,
"EQUIPMENT_GROUP_ID" Char (32) ASCII,
"FUEL_DISTANCE" Fixed (12,1),
"FUEL_DISTANCE_UM" Char (2) ASCII,
"FUEL_TAX_EXEMPT" Char (1) ASCII,
"ID" Char (32) ASCII NOT NULL,
"LOADED" Char (1) ASCII,
"LTL" Char (1) ASCII,
"MANIFEST_ID" Char (8) ASCII,
"MOVE_DISTANCE" Fixed (12,1),
"MOVE_DISTANCE_UM" Char (2) ASCII,
"ORIGIN_STOP_ID" Char (32) ASCII,
"OVERRIDE_CRNY_TYPE" Char (3) ASCII,
"OVERRIDE_DRIVER_NM" Varchar (32) ASCII,
"OVERRIDE_PAY_AMT" Fixed (16,2),
"OVERRIDE_PAY_AMT_C" Char (3) ASCII,
"OVERRIDE_PAY_AMT_D" Date,
"OVERRIDE_PAY_AMT_N" Fixed (16,2),
"OVERRIDE_PAY_AMT_R" Fixed (8,4),
"OVERRIDE_PAY_RATE" Fixed (12,4),
"OVERRIDE_PAYE_RATE" Fixed (12,4),
"OVERRIDE_PAYEE_ID" Char (8) ASCII,
"OVERRIDE_TYPE" Char (1) ASCII,
"OVERRIDE_UNIT_DESC" Char (10) ASCII,
"OVERRIDE_UNITS" Fixed (12,4),
"PAY_DISTANCE" Fixed (12,1),
"PAY_DISTANCE_UM" Char (2) ASCII,
"PREASSIGN_SEQUENCE" Integer,
"SEG_ALLOC_CODE" Char (8) ASCII,
"STATUS" Char (1) ASCII,
"TRAILER_RENT_PCT" Fixed (6,2),
"TRIP_LEASE" Char (12) ASCII,
"XFER2SETTLE_DATE" Timestamp,
"XFERRED2FUELTAX" Char (1) ASCII,
"XFERRED2FUELTAXDT" Timestamp,
"XMITTED2DRIVER" Timestamp,
"AGENT_PAYEE_ID" Char (8) ASCII,
"CONTAINER_RETURNED" Char (1) ASCII,
"EMPTY_CONTAINER" Char (14) ASCII,
"SS_LOCATION_ID" Char (8) ASCII,
"CONTAINER" Char (14) ASCII,
"IS_CONFIRMED" Char (1) ASCII,
"CHASSIS" Char (8) ASCII,
"RDY_PICKUP_DATE" Date,
PRIMARY KEY ("ID", "COMPANY_ID")
CREATE TABLE "LME"."EQUIPMENT_GROUP"
(
"COMPANY_ID" Char (4) ASCII NOT NULL,
"CURRENTMOVEMENT_ID" Char (32) ASCII,
"ID" Char (32) ASCII NOT NULL,
PRIMARY KEY ("ID", "COMPANY_ID")
)
CREATE TABLE "LME"."MOVEMENT_ORDER"
(
"COMPANY_ID" Char (4) ASCII NOT NULL,
"ID" Char (32) ASCII NOT NULL,
"MOVEMENT_ID" Char (32) ASCII,
"ON_AT_MOVE_DEST" Char (1) ASCII,
"ON_AT_MOVE_ORIGIN" Char (1) ASCII,
"ORDER_ID" Char (8) ASCII,
"SEQUENCE" Integer,
PRIMARY KEY ("ID", "COMPANY_ID")
)
Let me know what else you need.
Thanks for the help.
Chris
[29 Oct 2004 16:39]
Chris Wall
>Can you also provide me with the table definitions? I think you offered it >before and asked for the format. You can use the PAGES format. I am not entirely clear what you mean here. I inlcuded the create table statments. If you need more information about the table definitioins, please tell me how and where to get that information. Thanks.
[29 Oct 2004 20:39]
Ulf Wendel
Hey, that's a lot of input :-) ! I doubt that I will be able to solve the problem on my own, so you'll have to wait until monday for an answer. When I mentioned PAGES I was referring to a syntax detail of the loader command DBEXTRACT: DBEXTRACT CATALOG OUTSTREAM FILE '/tmp/catalog' PAGES . You can unload data and catalog informations in various formats, some of them are binary safe. PAGES is binary safe, it is human readable (at least for catalogs) and I would call it kind of the "natural" output format for MaxDB. The way you submitted the catalog informations is very ok. Have a nice weekend! Ulf Wendel
[1 Nov 2004 15:48]
Ulf Wendel
Hello Chris, the fact that JOIN_SEARCH_LEVEL = 0 and JOIN_SEARCH_LEVEL = 8 give you different results is confusing. As I said before you join hits only four tables and the optimizer should use level 9 automatically for so few tables (as long as you do not change the default settings). Our developers are asking for the output of an undocumented command that has been implemented for debugging purpose only: EXPLAIN SEQUENCE select_with_join The command will show a result table with only one column "TEXT". Please do not ask me how to read the output. There is no documentation about it. Our developers are free to change the informations given in whatever version they want. So, please send us the output of the EXPLAIN SEQUENCE select_join command as you did with the EXPLAIN select_join command before. Best regards, Ulf
[5 Nov 2004 12:09]
Donatas Ciuksys
Hello, I just want to say, that I also encounter the same problem. My SELECT statement heavily use LEFT JOIN's and RIGHT JOIN's (to produce reports), and on 7.5.00.16 I get answer in 4-6 seconds, though on 7.5.00.18 either data volume overflows or I get answer in more than 10 minutes. So this is really a bug, and not one customer issue. Regards, Donatas
[5 Nov 2004 12:29]
Ulf Wendel
It is sad to hear, that we really have a problem with joins but it is very good to know that it is in fact reproduceable. However, please understand that I must provide our developers with a reproducable test case. If you have the time Donatas, please try to cook a test case for us. Thanks for your input! Ulf
[17 Jan 2005 18:15]
Chris Wall
I upgraded to version 7.5.00.23 still have the same problem.
With
Join_operator_implementation=Yes
Join_Search_Level=0
Hashed_resultset=YES
The cost value is a huge number. The issue can still be resolved by setting Join_operator_implementation=NO and Join_serach_level=8.
explain SEQUENCE select movement.* from movement left outer join movement_order on
movement.id = movement_order.movement_id
and movement_order.sequence = 1 and movement_order.company_id = 'TMS'
,stop origin_stop ,stop dest_stop
,equipment_group where movement.company_id = 'TMS' and equipment_group.id
in ('WIN20010960313814061096032045017', 'WIN20010942206753321094222113664') and
movement.origin_stop_id = origin_stop.id and
origin_stop.company_id = 'TMS' and movement.dest_stop_id = dest_stop.id
and dest_stop.company_id = 'TMS'
and movement.equipment_group_id = equipment_group.id and
equipment_group.company_id = 'TMS'
TABLE No. 1
MOVEMENT
INV IN
strat 0.288E-03
pages searc 0.118E+00
all_pages 410
cost 3
recs_per_p 16
TABLE No. 2
MOVEMENT_ORDER
KEY RANGE FETCH
strat 0.100E+01
pages searc 0.690E+02
all_pages 69
cost 130
recs_per_p 100
TABLE No. 3
ORIGIN_STOP
KEY RANGE FETCH
strat 0.100E+01
pages searc 0.842E+03
all_pages 842
cost 1370
recs_per_p 17
TABLE No. 4
DEST_STOP
KEY RANGE FETCH
strat 0.100E+01
pages searc 0.842E+03
all_pages 842
[17 Jan 2005 19:07]
Chris Wall
Little bit more detail. I think the end of the last explain sequence got cutoff. Below is with level set to 0 implementation=Yes The cost with just explain select is a very large number. TABLE No. 1 MOVEMENT INV IN strat 0.288E-03 pages searc 0.118E+00 all_pages 410 cost 3 recs_per_p 16 TABLE No. 2 MOVEMENT_ORDER KEY RANGE FETCH strat 0.100E+01 pages searc 0.690E+02 all_pages 69 cost 130 recs_per_p 100 TABLE No. 3 ORIGIN_STOP KEY RANGE FETCH strat 0.100E+01 pages searc 0.842E+03 all_pages 842 cost 1370 recs_per_p 17 TABLE No. 4 DEST_STOP KEY RANGE FETCH strat 0.100E+01 pages searc 0.842E+03 all_pages 842 cost 1370 recs_per_p 17 TABLE No. 5 EQUIPMENT_GROUP KEY IN strat 0.363E-03 pages searc 0.145E-01 all_pages 40 cost 2 recs_per_p 137 | FROM 1 2 3 4 5 TO 1 -- MJ MJ MJ MJ 2 MJ -- = = = 3 MJ MJ -- MJ MJ 4 MJ MJ MJ -- MJ 5 MJ = = = -- mt_cnt : 12 TO via multiplier T F J I Used tables 2 INDEXCOLUMNS 0.100E+01 1 3 0 1 [ 1 ] 2 INDEXCOLUMNS 0.100E+01 1 1 0 4 [ 1 ] 1 KEY 0.100E+01 1 2 0 0 [ 2 ] 1 INDEXCOLUMNS 0.693E+04 0 1 3 8 [ ] 3 KEY 0.100E+01 1 2 4 0 [ 1 ] 1 INDEXCOLUMNS 0.100E+01 1 2 4 5 [ 3 ] 3 INDEXCOLUMNS 0.151E+05 0 1 5 4 [ ] 4 KEY 0.100E+01 1 2 6 0 [ 1 ] 1 INDEXCOLUMNS 0.100E+01 1 2 6 2 [ 4 ] 4 INDEXCOLUMNS 0.151E+05 0 1 7 4 [ ] 5 KEY 0.100E+01 1 2 8 0 [ 1 ] 1 INDEXCOLUMNS 0.200E+01 1 2 8 3 [ 5 ] TO via multiplier costs ji mi 1 KEY 0.100E+01 0.497476E-01 2 3 multiplejoin 2 INDEX-FIELDS 0.100E+01 0.115263E+03 1 1 multiplejoin 3 KEY 0.100E+01 0.114629E+03 1 5 multiplejoin 4 KEY 0.100E+01 0.114629E+03 1 8 multiplejoin 5 KEY 0.100E+01 0.417214E-01 1 11 multiplejoin JOIN SEQUENCE SEARCH = AUTOMATIC - LEVEL 9 BUFFER SIZE PER TASK : 131072 BYTES TABLE 4 : 60031 BYTES (recs est. 14314) TABLE 3 : 70926 BYTES (recs est. 15100) TABLE 1 : 48 BYTES (recs est. 1) TABLE 5 : 63 bytes (recs est. 1) TABLE 2 : 4 BYTES (recs est. 1) Below is with Level=8 and implementation=NO TABLE No. 1 MOVEMENT INV IN strat 0.288E-03 pages searc 0.118E+00 all_pages 410 cost 5 recs_per_p 16 TABLE No. 2 MOVEMENT_ORDER KEY RANGE strat 0.100E+01 pages searc 0.690E+02 all_pages 69 cost 310 recs_per_p 100 TABLE No. 3 ORIGIN_STOP KEY RANGE strat 0.100E+01 pages searc 0.842E+03 all_pages 842 cost 1646 recs_per_p 17 TABLE No. 4 DEST_STOP KEY RANGE strat 0.100E+01 pages searc 0.842E+03 all_pages 842 cost 1646 recs_per_p 17 TABLE No. 5 EQUIPMENT_GROUP KEY IN strat 0.363E-03 pages searc 0.145E-01 all_pages 40 cost 4 recs_per_p 137 | FROM 1 2 3 4 5 TO 1 -- MJ MJ MJ MJ 2 MJ -- = = = 3 MJ MJ -- MJ MJ 4 MJ MJ MJ -- MJ 5 MJ = = = -- mt_cnt : 12 TO via multiplier T F J I Used tables 2 INDEXCOLUMNS 0.100E+01 1 3 0 1 [ 1 ] 2 INDEXCOLUMNS 0.100E+01 1 1 0 4 [ 1 ] 1 KEY 0.100E+01 1 2 0 0 [ 2 ] 1 INDEXCOLUMNS 0.693E+04 0 1 3 8 [ ] 3 KEY 0.100E+01 1 2 4 0 [ 1 ] 1 INDEXCOLUMNS 0.100E+01 1 2 4 5 [ 3 ] 3 INDEXCOLUMNS 0.151E+05 0 1 5 4 [ ] 4 KEY 0.100E+01 1 2 6 0 [ 1 ] 1 INDEXCOLUMNS 0.100E+01 1 2 6 2 [ 4 ] 4 INDEXCOLUMNS 0.151E+05 0 1 7 4 [ ] 5 KEY 0.100E+01 1 2 8 0 [ 1 ] 1 INDEXCOLUMNS 0.200E+01 1 2 8 3 [ 5 ] TO via multiplier costs ji mi 1 KEY 0.100E+01 0.497476E-01 2 3 multiplejoin 2 INDEX-FIELDS 0.100E+01 0.115263E+03 1 1 multiplejoin 3 KEY 0.100E+01 0.114629E+03 1 5 multiplejoin 4 KEY 0.100E+01 0.114629E+03 1 8 multiplejoin 5 KEY 0.100E+01 0.417214E-01 1 11 multiplejoin JOIN SEQUENCE SEARCH = LEVEL 4 < COSTVALUE : 0.690583E+01 [ 1 3 4 5 ] > COSTVALUE : 0.509068E+07 [ 3 4 5 1 ] > COSTVALUE : 0.304042E+04 [ 3 1 4 5 ] > COSTVALUE : 0.509068E+07 [ 4 3 5 1 ] > COSTVALUE : 0.304042E+04 [ 4 1 3 5 ] > COSTVALUE : 0.713117E+03 [ 5 3 1 4 ] > COSTVALUE : 0.700218E+01 [ 5 1 3 4 ]
Description: The following statement overflows the data volume, it executed very quickly in version 16. I also found a case where a similar problem arose but it was able to eventually return the correct answer albeit very slowly. select movement.* from movement left outer join movement_order on movement.id = movement_order.movement_id ,stop origin_stop ,equipment_group where equipment_group.id='WIN20010938020004081093805211564' How to repeat: The following statement overflows the data volume and it worked in version 16 select movement.* from movement left outer join movement_order on movement.id = movement_order.movement_id ,stop origin_stop ,equipment_group where equipment_group.id='WIN20010938020004081093805211564' The following statement without ,stop origin_stop executes almost instantly. select movement.* from movement left outer join movement_order on movement.id = movement_order.movement_id ,equipment_group where equipment_group.id='WIN20010938020004081093805211564'