I have a view that joins 2 tables. The view definition is something like: CREATE VIEW MY_VIEW AS SELECT TABLE1.*, TABLE2.T2_COLUMN1, TABLE2.T2_COLUMN2 FROM TABLE1, TABLE2 WHERE TABLE1.ID_T2 = TABLE2.ID
ID_T2 is a foreign key referencing TABLE2.ID (which is the primary key of TABLE2). The problem is that the time it takes to perform queries on the view seems to grow exponentially as the number of rows in TABLE1 grows. I guess this happens because of the way that SAPDB performs the query (i.e. join first to create the view, then perform query), so a huge intermediate table has to be created. Just to demonstrate, with 30,000 rows in TABLE1 and 30 rows in TABLE2 (and effectively 30,000 rows in the view), the following query produced these results: Query: SELECT * FROM MY_VIEW ORDER BY ID, T1_COLUMN1. Average Time: about 30 seconds. A similar query on TABLE1 alone (SELECT * FROM TABLE1 ORDER BY ID, T1_COLUMN1), took 57 ms. The columns TABLE1.ID, TABLE2.ID, TABLE1.T1_COLUMN1 are of type INT. Is there anyway to optimize this behavior? if not, how can create an efficient mechanism without duplicating data from TABLE2 in TABLE1? Any help would be greatly appreciated, Uri _______________________________________________ sapdb.general mailing list [EMAIL PROTECTED] http://listserv.sap.com/mailman/listinfo/sapdb.general
