Jose Dillet wrote: > Hello, > > we are having some performance problems with some kind of > queries. This is a > simplified example of what is happening in our production environment. > > We have an application with jboss and maxdb 7.5.00.23 (but > same I've tested > 7.5.00.31, and the behaviour is the same) > > I've created a database with default parameters. Let's say we > have a table, > with a field (HEADER_LINE_ID) is a foreign key of the same table, and > indexed. > > CREATE TABLE LINE > ( > ID Fixed (19,0) NOT NULL, > TYPE Integer, > CODE Char (20) ASCII, > HEADER_LINE_ID Fixed (19,0), > CREATION_DATE Date, > PRIMARY KEY (ID) > ) > > ALTER TABLE LINE ADD CONSTRAINT FK_HEADER_LINE FOREIGN KEY > (HEADER_LINE_ID) > REFERENCES LINE (ID) > ) > // > CREATE INDEX IDX_LINE_HEADER_LINE ON LINE (HEADER_LINE_ID) > // > > I've done this test inserting 100.000 registers. > Under some circumstances (when jboss knows IDs for registers > it wants to > load), it creates a query like this > > SELECT ID, TYPE, CODE, HEADER_LINE_ID, CREATION_DATE > FROM LINE > WHERE (ID=2294752131926180503) > OR (ID=2294763105441405458) > . > . (n times) > . > OR (ID=2302419071075259925) > > When the query has 50 or terms or less, the explain we get is > like this: > > LINE DIFFERENT STRATEGIES FOR OR-TERMS 1010 > EQUAL CONDITION FOR KEY > ID (USED KEY COLUMN) > EQUAL CONDITION FOR KEY > . > . ( n times) > . > ID (USED KEY COLUMN) > EQUAL CONDITION FOR KEY > ID (USED KEY COLUMN) > RESULT IS COPIED , COSTVALUE IS 50 > > > but, for 51 or terms of more, we get a table scan: > > LINE TABLE SCAN 1010 > RESULT IS NOT COPIED , COSTVALUE IS 1010 > > > this seems strange, because for 50 or terms, cost is 50, and > for 1 term > more, cost goes to 1010 (in production, cost changes from 50 > to 18340). > > Moreover, for field HEADER_LINE_ID, something similar happens. > > For example, we get a query like this: > > SELECT ID, TYPE, CODE, HEADER_LINE_ID, CREATION_DATE > FROM LINE > WHERE (HEADER_LINE_ID=2294752131926180503) > OR (HEADER_LINE_ID=2294763105441405458) > . > . (n times) > . > > OR (HEADER_LINE_ID=2294478368382782110) > > For 40 or terms or less, query is made with strategy > "DIFFERENT STRATEGIES > FOR OR-TERMS" using index defined over HEADER_LINE_ID > > LINE DIFFERENT STRATEGIES FOR OR-TERMS 298 > IDX_LINE_HEADER_LINE EQUAL CONDITION FOR INDEX > . > . (n times) > . > IDX_LINE_HEADER_LINE EQUAL CONDITION FOR INDEX > RESULT IS COPIED , COSTVALUE IS 160 > > > But for 41 terms, we get also a table scan > > LINE TABLE SCAN 1010 > RESULT IS NOT COPIED , COSTVALUE IS 1010 > > > So, for more than 50 terms if table is accesed by primary > key, or for more > than 40 terms if table is accesed by and indexed column, > strategy "DIFFERENT > STRATEGIES FOR OR-TERMS" seems to be discarted. So, I'm changing > configuration of jboss to get queries with a limited number of > or terms, but I'd like to know if quantity of or-terms to > discard or-terms > strategy is fixed, or is related to some database parameters, > or maybe > depends of size of table accesed.
Hi, this is a restriction within the MaxDB Optimizer, only 50 or terms could be handled. We are currently transforming the optimizer code from Pascal to C++ and hope that this will give us the ability to get rid of such restrictions but don't expect it in the near future. As a workaround you should try to transform the OR terms into an IN like Robert already stated. Kind regards Holger -- MaxDB Discussion Mailing List For list archives: http://lists.mysql.com/maxdb To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
