Informix vs Oracle vs DB2. SQL Query optimization.

Warning: This is going to be a bit esoteric and may bore some of you to death. Only read/respond at your own risk.... Ok, having said that. Here's the question. You have a table A, that has millions of millions of records. (Its huge, its really huge) The two columns in question are column 1, an index, and column 3, a spatial object that also has a spatial index. Your application generates a list of objects that have column 1 and column 2. Column 1 is an identity index, and column 2 is a spatial object that is also indexed. TABLE A (col_id, foo) TABLE B (obj_id, foo) Now the problem. I want to find all of the obj_ids for the objects foo in table B that overlap(touch) the objects in table A where the col_id > X. (Some number which is much, much, much less than the rows in the table.) So the query would look like something like this: SELECT B.obj_id FROM A, B WHERE A.col_id > 83000 AND SDO.Geometry Filter (A.foo, B.foo, 'query_type = window') = True; Ok this is a bit mangled SQL. In Oracle you use the second table as a filter for the rows in the first table. The syntax isn't important. The explain plan in Oracle is that you get a subset of table A, or a collection based on col_id > 83000, and you then apply the spatial filter on table A to get the object ids. The problem is that because you're dealing with a collection, you no longer have a spatial index and you end up doing a table scan on B and a table scan on the collection from A. This is slow. A quick fix, we flip B.foo and A.foo in the filter. This forces Oracle to use the spatial index on B.foo. The problem is that foo is the data from the application and its stored in a global temp table. (indexed). This is a major problem in that the collection from A contains 720,000 rows and the temp table, for testing, contained (160, 600, 22K, and 66K) rows for each test. The time the query took was pretty consistent regardless of the number of rows in the temp table. (The key factor was that theh 720K rows were still being sequentially scanned.) SO it took ~40 minutes each time we ran the test. To make things even faster... We took the 720K rows and inserted them in to a global temp table, also with a spatial index. This time, the index used was on the 720K rows and then the time varied due to the number of rows in the second temp table. (That table was sequentially scanned.) Using Informix, we wouldn't have to create global temp tables. When wen create the temp table off the collection, we do it as part of the select and it dynamically creates the table. We then put an index on the temp table. Also assuming that IDS has a similar filter in use like Oracle. So this query(s) would be faster in IDS. My question... Is there a way that you can get a collection (a subset of the records from a table) to use the index of that table? Note that we're talking about a spatial index which is an add on to either database. (DB2, Oracle, and IDS.) This will alleviate the need for using temp tables and having to re- index the results. Will an index hint work on a collection or subset of the entire data? Sorry if this is a bit esoteric but I've got to be careful with what I say. If you are an IDS engine optimization guru and/or a spatial guru, you know the e-mail address. -G

Ian Michael Gumby wrote: > Warning: > > This is going to be a bit esoteric and may bore some of you to death. > Only read/respond at your own risk.... > > > > Ok, having said that. Here's the question. > > You have a table A, that has millions of millions of records. (Its > huge, its really huge) > The two columns in question are column 1, an index, and column 3, a > spatial object that also has a spatial index. > > Your application generates a list of objects that have column 1 and > column 2. Column 1 is an identity index, and column 2 is a spatial > object that is also indexed. > > TABLE A (col_id, foo) > TABLE B (obj_id, foo) > > Now the problem. > > I want to find all of the obj_ids for the objects foo in table B that > overlap(touch) the objects in table A where the col_id > X. (Some > number which is much, much, much less than the rows in the table.) > > So the query would look like something like this: > > SELECT B.obj_id > FROM A, B > WHERE A.col_id > 83000 > AND SDO.Geometry Filter (A.foo, B.foo, 'query_type = window') = True;> > Ok this is a bit mangled SQL. In Oracle you use the second table as a > filter for the rows in the first table. The syntax isn't important. > > The explain plan in Oracle is that you get a subset of table A, or a > collection based on col_id > 83000, and you then apply the spatial > filter on table A to get the object ids. > > The problem is that because you're dealing with a collection, you no > longer have a spatial index and you end up doing a table scan on B and > a table scan on the collection from A. This is slow. > > A quick fix, we flip B.foo and A.foo in the filter. This forces Oracle > to use the spatial index on B.foo. > > The problem is that foo is the data from the application and its > stored in a global temp table. (indexed). > This is a major problem in that the collection from A contains 720,000 > rows and the temp table, for testing, contained (160, 600, 22K, and > 66K) rows for each test. > > The time the query took was pretty consistent regardless of the number > of rows in the temp table. (The key factor was that theh 720K rows > were still being sequentially scanned.) SO it took ~40 minutes each > time we ran the test. > > To make things even faster... We took the 720K rows and inserted them > in to a global temp table, also with a spatial index. This time, the > index used was on the 720K rows and then the time varied due to the > number of rows in the second temp table. (That table was sequentially > scanned.) > > Using Informix, we wouldn't have to create global temp tables. > When wen create the temp table off the collection, we do it as part of > the select and it dynamically creates the table. We then put an index > on the temp table. Also assuming that IDS has a similar filter in use > like Oracle. > > So this query(s) would be faster in IDS. > > My question... > > Is there a way that you can get a collection (a subset of the records > from a table) to use the index of that table? > Note that we're talking about a spatial index which is an add on to > either database. (DB2, Oracle, and IDS.) > This will alleviate the need for using temp tables and having to re- > index the results. > > Will an index hint work on a collection or subset of the entire data? > > Sorry if this is a bit esoteric but I've got to be careful with what I > say. > > If you are an IDS engine optimization guru and/or a spatial guru, you > know the e-mail address. > > -G The solution to the problem in Oracle would not use a global temporary table and an Oracle professional would post an explain plan output generated using DBMS_XPLAN so that people might see what was actually going on. Your problem with Oracle remains that you don't know how to use the product. Contact Hans Forbrich. http://apex.oracle.com/pls/otn/f?p=19297:4:7552326429234015::NO:4:P4_ID:57 He teaches classes on Spatial and tuning. -- Daniel A. Morgan University of Washington damorgan@x.washington.edu (replace x with u to respond)