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?
Question 2: What are the columns O, D, T and M?
Thanks,
S�bastien Koller
----------------------------------------------------------------------------
--------
S�bastien Koller
Assistant laboratoire de bases de donn�es
ESNIG (Ecole Sup�rieure Neuch�teloise d'Informatique de Gestion)
Maladi�re 84
2007 Neuch�tel
--------
www.esnig.ch
E-mail: [EMAIL PROTECTED]
--------
tel : +4132 717 40 80
fax: +4132 717 40 89
_______________________________________________
sapdb.general mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general
