Uri C. wrote: > 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?
How actual are your optimizer statistics? You should try an updtat stat command on both tables. Kind regards, Holger SAP Labs Berlin _______________________________________________ sapdb.general mailing list [EMAIL PROTECTED] http://listserv.sap.com/mailman/listinfo/sapdb.general
