Hello

I have the following tables:

iwadisobject (bold_id integer, bold_type integer, folder integer)
word (bold_id integer, attribute varchar, data varchar, phoneticdata varchar, indexedobject integer)
folder (bold_id integer, bold_type integer)
document (bold_id integer, bold_type integer)

And the following indexes:
iwadisobject (bold_id)
iwadisobject (bold_type)
iwadisobject (folder)
folder (bold_id)
folder (bold_type)
document (bold_id)
document (bold_type)
word (bold_id)
word (bold_type)
Word (attribute, data)
Word (attribute, phoneticdata)
Word (data, indexedObject)
Word (phoneticdata, indexedObject)
Word (indexedObject)

Number of rows for each table:
iwadisobject: 105697
folder: 3
document: 52492
word: 13227942

If I executed the select

SELECT IwadisObje_1.BOLD_ID, IwadisObje_1.BOLD_TYPE
FROM IwadisObject IwadisObje_1, Word Word_1 JOIN IwadisObject IwadisObje_2 ON (IwadisObje_2.BOLD_ID = Word_1.indexedObject), Folder Folder_1, Document Document_1
WHERE IwadisObje_2.BOLD_ID = Folder_1.BOLD_ID
AND IwadisObje_1.folder = Folder_1.BOLD_ID
AND IwadisObje_1.BOLD_ID = Document_1.BOLD_ID
AND ((Word_1.attribute = 'REFERENCE') and (Word_1.data = '99'))
AND (IwadisObje_2.BOLD_TYPE IN (127))
AND (IwadisObje_1.BOLD_TYPE IN (126))
AND EXISTS (SELECT Word_2.BOLD_ID
FROM Word Word_2 JOIN Document Document_2 ON (Word_2.indexedObject = Document_2.BOLD_ID)
WHERE IwadisObje_1.BOLD_ID = Document_2.BOLD_ID
AND ((Word_2.attribute = 'SCANDATA.CIP_MANDANT') and (Word_2.data='STANDARDXX'))
)

I get a execution time of 3 seconds and a plan as shown:

PLAN JOIN (DOCUMENT_2 INDEX (IX_DOCUMENT_BOLD_ID), WORD_2 INDEX (IDXWORD3))
PLAN JOIN (FOLDER_1 NATURAL, IWADISOBJE_2 INDEX (IX_IWADISOBJECT_BOLD_ID), WORD_1 INDEX (IDXWORD3), IWADISOBJE_1 INDEX (IX_IWADISOBJECT_FOLDER, IDXIWADISOBJECT1), DOCUMENT_1 INDEX (IX_DOCUMENT_BOLD_ID))


If I replace the = with a like and execute the query:

SELECT IwadisObje_1.BOLD_ID, IwadisObje_1.BOLD_TYPE
FROM IwadisObject IwadisObje_1, Word Word_1 JOIN IwadisObject IwadisObje_2 ON (IwadisObje_2.BOLD_ID = Word_1.indexedObject), Folder Folder_1, Document Document_1
WHERE IwadisObje_2.BOLD_ID = Folder_1.BOLD_ID
AND IwadisObje_1.folder = Folder_1.BOLD_ID
AND IwadisObje_1.BOLD_ID = Document_1.BOLD_ID
AND ((Word_1.attribute = 'REFERENCE') and (Word_1.data = '99'))
AND (IwadisObje_2.BOLD_TYPE IN (127))
AND (IwadisObje_1.BOLD_TYPE IN (126))
AND EXISTS (SELECT Word_2.BOLD_ID
FROM Word Word_2 JOIN Document Document_2 ON (Word_2.indexedObject = Document_2.BOLD_ID)
WHERE IwadisObje_1.BOLD_ID = Document_2.BOLD_ID
AND ((Word_2.attribute = 'SCANDATA.CIP_MANDANT') and (Word_2.data LIKE 'STANDARDXX%'))
)

I get an execution time of 360 seconds and a plan like:

PLAN JOIN (DOCUMENT_2 INDEX (IX_DOCUMENT_BOLD_ID), WORD_2 INDEX (IDXWORD5, IDXWORD1))
PLAN JOIN (FOLDER_1 NATURAL, IWADISOBJE_2 INDEX (IX_IWADISOBJECT_BOLD_ID), WORD_1 INDEX (IDXWORD3), IWADISOBJE_1 INDEX (IX_IWADISOBJECT_FOLDER, IDXIWADISOBJECT1), DOCUMENT_1 INDEX (IX_DOCUMENT_BOLD_ID))



Why does the like compares so bad to the equal?

Thanks

Stefan