I have a view 'invest_arm'. It is a complex by definition. It contains an outer join. The following simple query performs very fast

explain
select i.counter,i.resid,i.res_num
from gis.invest_arm i
where cabid like 'cab'
for reuse


 I  INVEST_CABID  EQUAL CONDITION FOR INDEX                     48962
 A  INDEX001      JOIN VIA RANGE OF MULTIPLE INDEXED COL.           1
OS_USER (USED INDEX COLUMN) S SMU_ID JOIN VIA INDEXED COLUMN 8 NO TEMPORARY RESULTS CREATED RESULT IS COPIED , COSTVALUE IS 11
                  QUERY REWRITTEN ON SQLSTATEMENT LEVEL             0


but a little bit more complex query builds a temporary table and performs about 10 minutes.

explain
select i.counter,i.resid,i.res_num
from gis.invest_arm i
where cabid like 'cab' and DID_DOC_FORMA(i.counter,i.resid,i.res_num) is null
for reuse


I TABLE SCAN 48962 A INDEX001 JOIN VIA RANGE OF MULTIPLE INDEXED COL. 1 OS_USER (USED INDEX COLUMN) S SMU_ID JOIN VIA INDEXED COLUMN 8 NO TEMPORARY RESULTS CREATED INTERNAL TEMPORARY RESULT TABLE SCAN 500 RESULT IS COPIED , COSTVALUE IS 14263347



Is there a way to make the plan of the second query simular the plan of the first query?

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

Reply via email to