Koller S�bastien wrote: > Hello, > > We have the following schema: > > - one table SITE > - one table PROXY_0101 > - one table PROXY_0201 > - one view V_PROXY > CREATE VIEW v_proxy AS > SELECT * FROM proxy_0101 > UNION ALL > SELECT * FROM proxy_0201) > > - one index on PROXY_0101 --> CREATE INDEX i_proxy_site ON PROXY_0101 > (site_num) > - one index on PROXY_0201 --> CREATE INDEX i_proxy_site ON PROXY_0201 > (site_num) > > Table PROXY_0101 as about 3'900'000 rows > Table PROXY_0201 as 0 row > > The first query (EXPLAIN): > > EXPLAIN (sql_stat) SELECT Count(*) > FROM v_proxy, site > WHERE site_num = site.numero > AND site = 'www.hen.ch' > > OWNER TABLENAME COLUMN_OR_INDEX STRATEGY > PAGECOUNT O > D T M > PROXY PROXY_0101 TABLE SCAN 44087 > > PROXY PROXY_0201 TABLE SCAN 1 > > PROXY SITE SITE EQUAL CONDITION FOR INDEXED COLUMN > 820 * > > INTERNAL TEMPORARY RESULT TABLE SCAN 500 > > INTERNAL TEMPORARY RESULT TABLE SCAN 500 > > PROXY RESULT IS COPIED , COSTVALUE IS 1650 > > > And the second one: > > EXPLAIN (sql_stat) SELECT Count(*) > FROM proxy_0101, site > WHERE proxy_0101.site_num = site.numero > AND site = 'www.hen.ch' > // > SELECT * FROM sql_stat > // > OWNER TABLENAME COLUMN_OR_INDEX STRATEGY > PAGECOUNT O > D T M > PROXY SITE SITE EQUAL CONDITION FOR INDEXED COLUMN > 820 * > > PROXY PROXY_0101 SITE_NUM JOIN VIA INDEXED COLUMN 44087 > > INTERNAL TEMPORARY RESULT TABLE SCAN 500 > > PROXY RESULT IS COPIED , COSTVALUE IS 1006 > > > PROXY RESULT IS COPIED , COSTVALUE IS 1005 > > > Question 1: When we use the first query(using a view), the > indexes are not > used (there is a TABLE SCAN), does it mean that the optimizer > doesn't care > about indexes through a view? Does this problem will be > solved in the futur?
If it is a complex view (see http://www.sapdb.org/htmhelp/c7/12ffd406ab11d3a97d00a0c9449261/frameset.htm) the result of the view is prepared first, then the select using this view-result is done. --> the qualification on view-columns (no matter if with literals, parameters or as part of a join-condition) will not be handled during the view-result-preparation (usually). Even if the qualification uses columns which are indexed on the primary tables, those indexes cannot be used, because the select using the corresponding qualifications will be done on the resulting view-result, not on the primary tables. Only for some combinations of selects on complex views it is possible to build one select (using the primary tables directly) out of this complex-view-select and user-select and then the qualifications can use the indexes of the primary tables. Elke SAP Labs Berlin BTW: the subject line is very helpful for us to decide which mail to read and answer... _______________________________________________ sapdb.general mailing list [EMAIL PROTECTED] http://listserv.sap.com/mailman/listinfo/sapdb.general
