bad plan in case of joining a big table with empty temporary table

Sun, 23 Jul 2006 21:33:23 -0700 

Hi MaxDB team,


7.6.00.32 has the same problem with empty tables as 7.6.00.27.


CREATE TABLE TEMP.user_attrs
   (
     counter      INT,
     resid      VARCHAR(10),
     resnum      INT,

     attr       VARCHAR(50)
   )
//
explain
select distinct a.counter, a.resid, a.resnum, b.attr, b.attr_name, b.attr_type, b.attr_group, c.CONST_VALUE, c.const_name 
       from
         TEMP.user_attrs a,
         GIS.invest d,
         LIS.er_dictionary_attributes b,
         LIS.er_attribute_default_values c
       where
a.counter = d.counter and a.attr = b.attr AND b.DEFINED = 'const' AND a.attr = c.attr and c.rule is null 
for reuse


  C  UNIQUE_RULE     RANGE CONDITION FOR INDEX                        7
     RULE                 (USED INDEX COLUMN)
  B  ATTR            JOIN VIA KEY COLUMN                              1
                     TABLE HASHED
  D  INVEST_CARTNUM  INDEX SCAN                                   69584
                     ONLY INDEX ACCESSED
  A                  JOIN VIA RANGE OF MULTIPLE KEY COLUMNS           0
                     TABLE TEMPORARY SORTED
     COUNTER              (USED SORT COLUMN)
     ATTR                 (USED SORT COLUMN)
                          RESULT IS COPIED   , COSTVALUE IS     > 2 E10

After execution

General error;-915 POS(1) No more memory
2006-07-24 12:25:31 26645 ERR 54008 MEMORY Calculated chunk size smaller than reque 
2006-07-24 12:25:31 26645 ERR 54008 MEMORY   sted size 7639E808



Best regards,
Alexey Gaidukov


-----Original Message-----
From: Alexey Gaidukov [mailto:[EMAIL PROTECTED] Sent: Mittwoch, 31. Mai 2006 11:07 
To: Becker, Holger
Subject: Re: bad plan in case of joining a big table with empty temporary table 

Right now I have other problem.

When the table 'TEMP.user_attrs' is empty


General error;-915 POS(1) No more memory
select distinct a.counter, d.resid, b.attr, b.attr_name, b.attr_type, b.attr_group, c.CONST_VALUE, c.const_name 
        from
          TEMP.user_attrs a,
          GIS.invest d,
          LIS.er_dictionary_attributes b,
          LIS.er_attribute_default_values c
        where
a.counter = d.counter and a.attr = b.attr AND b.DEFINED = 'const' AND a.attr = c.attr and c.rule is null 


insert TEMP.user_attrs  (counter, attr) values (1, 'a')


C  UNIQUE_RULE  RANGE CONDITION FOR INDEX                        7
     RULE              (USED INDEX COLUMN)
  A               JOIN VIA KEY RANGE                               0
                  TABLE TEMPORARY SORTED
     ATTR              (USED SORT COLUMN)
  D  COUNTER      JOIN VIA KEY COLUMN                          57704
  B  ATTR         JOIN VIA KEY COLUMN                              1
                  TABLE HASHED
                       RESULT IS COPIED   , COSTVALUE IS           6


Alexey Gaidukov пишет:
> C UNIQUE_RULE RANGE CONDITION FOR INDEX 7 > RULE (USED INDEX COLUMN) B > ATTR JOIN VIA KEY COLUMN 1 > TABLE HASHED D > INVEST_CARTNUM INDEX SCAN 69584 > ONLY INDEX ACCESSED > A JOIN VIA RANGE OF MULTIPLE KEY COLUMNS 0 > TABLE TEMPORARY SORTED > COUNTER (USED SORT COLUMN) > ATTR (USED SORT COLUMN) > RESULT IS COPIED , COSTVALUE IS > 2 E10 
>
> Becker, Holger пишет:
>> Hi,
>> >> let's start with the explain plan. >> >> Thanks 
>> Holger
>>
>> ________________________________
>>
>> From: Alexey Gaidukov [mailto:[EMAIL PROTECTED] Sent: >> Mittwoch, 31. Mai 2006 09:49 
>>     To: Becker, Holger
>> Subject: Re: bad plan in case of joining a big table with empty >> temporary table >> >> >> Yes, I have examples. Do you need a plan of the statement or >> source of the statement? >> >> Becker, Holger пишет: 
>>         Hi,
>> >> have you an example where you run in problems? >> >> Kind regards >> Holger >> >> -----Original Message----- >> From: Alexey Gaidukov [mailto:[EMAIL PROTECTED] >> Sent: Mittwoch, 31. Mai 2006 03:23 
>>             To: Becker, Holger; maxdb@lists.mysql.com
>> Subject: Re: bad plan in case of joining a big table with >> empty temporary table >> >> >> Becker, Holger пишет: >> >> Hi, >> >> it's definitive a bug and based on some type mixing >> within the join 
>>                 optimizer.
>> >> I'll fix it with one of the next release (for >> detailed >> version info see >> >> http://www.sapdb.org/webpts?wptsdetail=yes&ErrorType=0&ErrorID 
>>             =1141684)
>> >> As far as I can see this bug influences only the >> explain >> output not the >> >> decision of the optimizer so it >> should not be critical for your application. >> >> Thank you for reporting it. >> >> Kind regards 
>>                 Holger
>> >> >> It the simple case this bug influences only the explain >> output but in complex joins have really big problems. I >> had to add checks for empty temporary tables. >> >> -- MaxDB Discussion Mailing List 
>>             For list archives: http://lists.mysql.com/maxdb
>> To unsubscribe: >> http://lists.mysql.com/[EMAIL PROTECTED] >> >> >> >> >> >> 
>>
>> > 
>







--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]

Reply via email to