Edson Richter wrote : >Hi! I've created the following query (and without explain it's so fast!!!):
>EXPLAIN >SELECT P.ID, P.NAME, P.TYPE, P.STATUS, P.OLD_ID, > OI.SITE, OI.EMAIL, OI.BIRTHDAY, OI.FUNDS, OI.NUMOFPEOPLE, > OI.SIZE, PP.SIZE_DETAILS > FROM TABLE_A P > LEFT OUTER JOIN TABLE_B OI > ON P.ID = OI.ID > LEFT OUTER JOIN TABLE_C PP > ON OI.SIZE = PP.SIZE > WHERE P.ID < 1100 >And I get the follwoing explain: > P RANGE CONDITION FOR KEY COLUMN 1 > ID (USED KEY COLUMN) > OI ID JOIN VIA KEY COLUMN 549 > PP SIZE JOIN VIA KEY COLUMN 245 >MGR RESULT IS COPIED , COSTVALUE IS 9 >Ok, it�s what I expected. But now I do >CREATE VIEW VI_TESTE AS >SELECT P.ID, P.NAME, P.TYPE, P.STATUS, P.OLD_ID, > OI.SITE, OI.EMAIL, OI.BIRTHDAY, OI.FUNDS, OI.NUMOFPEOPLE, > OI.SIZE, PP.SIZE_DETAILS > FROM TABLE_A P > LEFT OUTER JOIN TABLE_B OI > ON P.ID = OI.ID > LEFT OUTER JOIN TABLE_C PP > ON OI.SIZE = PP.SIZE >and then I issue >EXPLAIN >SELECT * > FROM VI_TESTE > WHERE ID < 1100 This view a so called 'complex view', because it contains an outer join. Views of this kind normally are materialized, before the search condition is applied. The are only some cases, where a dedicated optimizer is able to avoid the materialization. Unfortunately this is not the case in your example. >and the result is (and without explain, it's so slow!!!) > P TABLE SCAN > 1 > OI ID JOIN VIA KEY COLUMN 549 > PP SIZE JOIN VIA KEY COLUMN 245 >INTERNAL TEMPORARY RESULT TABLE SCAN >500 >MGR RESULT IS COPIED , COSTVALUE IS >1559 >why isn't index being used for P, contraring info found in docs: >http://www.sapdb.org/7.4/htmhelp/6d/117c44d14811d2a97400a0c9449261/content.htm >at paragraph >'The view table is always identical to the table that would be obtained as the result >of the >QUERY expression.' This means that the result is always identical to the table that would be obtained as the result of the QUERY expression. It does not mean that the ways to obtain the results are always the same. >Can someone help me point if this is a bug, or I'm doing something really wrong? I don't think it's a bug, its just caused by the implementation. We are working on improvements of the complex view optimizer, but today I can't guarantee, that the example above will profit by it. Best Regards, Thomas -- Thomas Anhaus SAP DB, SAP Labs Berlin [EMAIL PROTECTED] http://www.sapdb.org/ _______________________________________________ sapdb.general mailing list [EMAIL PROTECTED] http://listserv.sap.com/mailman/listinfo/sapdb.general -- MaxDB Discussion Mailing List For list archives: http://lists.mysql.com/maxdb To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
