Re: Informix vs Oracle vs DB2. SQL Query optimization.

On Oct 8, 1:33 am, DA Morgan <damor...@psoug.org> wrote: > 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_... > He teaches classes on Spatial and tuning. > -- > Daniel A. Morgan > University of Washington > damor...@x.washington.edu (replace x with u to respond) Sigh. Thats funny Daniel. I did. And I benchmarked it. The use of the indexed row reduces a multi-million row table down to 270K rows or so in a collection. (But you can't index the collection.) So when you apply a spatial filter, you end up doing a table scan on the rows in the temp table against the collection. This takes a very long, long time. So if you flip the filter so that you're using the index on the temp table, you get the query to come back after about 40 minutes. What its doing is a index vs a table scan. Regardless of the data size in the temp table, since the 270K rows in the collection were the same, 40 minutes. Note: When I tested this without forcing the use of an index, I had to stop the query after 2.6 hours. By taking the 270K rows in to a temp table that has an index, I was able to use that index over the temp data I stored from the application. This meant that if I had 160 rows in my temp from the app, it came back in seconds. 1000 a little longer. 60K, about 14 minutes. Which isn't bad. So to answer your point Daniel, yes, I know Oracle better than I will admit, and to tell the truth, I'm not impressed. Getting back to the question on hand... Regardless of the database, if you are doing a complex query and you have collections that are > 100,000 rows, is there a way to force it to use the underlying index of the table? Or are you better off storing the results in an indexed temp table? With IDS, its very easy to create a temp table dynamically, and then put an index on it. The only drawback? What happens to your transaction? Does the create index cause you to autocommit your transaction? (IMHO this isn't an issue in our query but it could be an issue for a larger system.)

Ian Michael Gumby wrote: > On Oct 8, 1:33 am, DA Morgan <damor...@psoug.org> wrote: >> 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_... >> He teaches classes on Spatial and tuning. >> -- >> Daniel A. Morgan >> University of Washington >> damor...@x.washington.edu (replace x with u to respond) > Sigh. > > Thats funny Daniel. > > I did. > And I benchmarked it. > > The use of the indexed row reduces a multi-million row table down to > 270K rows or so in a collection. > (But you can't index the collection.) > > So when you apply a spatial filter, you end up doing a table scan on > the rows in the temp table against the collection. This takes a very > long, long time. > > So if you flip the filter so that you're using the index on the temp > table, you get the query to come back after about 40 minutes. What its > doing is a index vs a table scan. Regardless of the data size in the > temp table, since the 270K rows in the collection were the same, 40 > minutes. Note: When I tested this without forcing the use of an index, > I had to stop the query after 2.6 hours. > > By taking the 270K rows in to a temp table that has an index, I was > able to use that index over the temp data I stored from the > application. > This meant that if I had 160 rows in my temp from the app, it came > back in seconds. 1000 a little longer. 60K, about 14 minutes. > > Which isn't bad. > > So to answer your point Daniel, yes, I know Oracle better than I will > admit, and to tell the truth, I'm not impressed. > > Getting back to the question on hand... > > Regardless of the database, if you are doing a complex query and you > have collections that are > 100,000 rows, is there a way to force it > to use the underlying index of the table? Or are you better off > storing the results in an indexed temp table? > > With IDS, its very easy to create a temp table dynamically, and then > put an index on it. The only drawback? What happens to your > transaction? > Does the create index cause you to autocommit your transaction? (IMHO > this isn't an issue in our query but it could be an issue for a larger > system.) You did "something" but what you did is not the best way to handle the issue. Consider taking a class and learn how to use the product properly before complaining about it. -- Daniel A. Morgan University of Washington damorgan@x.washington.edu (replace x with u to respond)

Hello Ian, Ideally your problem would be solved with a star schema. TABLE A (the fact table) should be (col_id, obj_id, foo_id) defined as narrowly as possible (smallint, integer, integer8) to reduce disk storage (and therefore SQL I/O) and run faster on the CPU. Computers process whole numbers faster than decimal, char, etc. because they do it on the CPU itself (remember the math co-processor?). Then join to your matching dimension tables. To further reduce disk I/O put an index (or materialized view) on just the columns from the fact table that you need so that you will scan the index and not the fact table. But you are probably stuck with the ERD you have. In that case you might be able to cheat by using Unix commands wrapped around your SQL to eliminate one or more of your filters. You might even be able to eliminate one of your tables! Anyway, here is an informix example to move the "> 83000" filter to unix: echo "$SQL_statement" | dbaccess database_name | egrep "83[0-9][0-9][0-9]$|8[4-9][0-9][0-9][0-9]|9[0-9][0-9][0-9]$|[0-9][0-9] [0-9][0-9][0-9]" | grep -v 83000 Many (but not all) unix commands run surprisingly fast. Luckily "egrep" runs fast. -L.S.

On Oct 8, 1:14 pm, DA Morgan <damor...@psoug.org> wrote: > You did "something" but what you did is not the best way to handle > the issue. Consider taking a class and learn how to use the product > properly before complaining about it. > -- Daniel, You're too funny for words. Take a class? LOL... Dude! I think you need to take a refresher.