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

On Oct 8, 2:51 pm, LIGHT SCANS <light_sc...@xxxxxxxxx> wrote:
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.

Huh?

No offense, but I think you're confused because you don't know Oracle.

First, the query chooses to limit TAB_A by the col_id. This reduces
the number of rows from millions (Lets say 24 million rows) to
270,000 rows.
The second part of the query will then perform the Oracle spatial
filter. (Its in the query and has nothing to do with Unix or anything
outside of the engine.
The syntax of the filter is SDO_FILTER(geo1, geo2, params) = 'TRUE'

The first geometry is the data set, the second geometry is the filter
to be applied. In our case params = 'query_type=window' since we're
just looking for overlapping objects.

So in our query, initially we have the collection as geo1. and then
geo2 is the temp table that contains the object ids and their geo
spatial data.
The problem is that the query then does two table scans of the geo1
and geo2 elements, ignoring both of the spatial indexes. So in the
case of 270,000 rows in the collection, and 60,000 rows in the temp
table, you have a major performance issue. Oracle is performing a
Cartesian product and it takes way to long

By flipping the order where geo1 is our indexed temp table and geo2 is
our collection of 270,000 rows, we at least can use the index on the
temp table, but still do a table scan on the collection. What this
does is get the query time down to 40 minutes or so, depending on the
server load.

Taking this a step further, we take the 270,000 rows of the collection
and place them in to an Oracle global temp table which has a spatial
index.
Then going back to geo1 = to the indexed temp table of 270k rows and
geo2 = my data from the app (60K,27K, 1K, 160 ) rows, we get much
better performance.

Even if we include the time to break the initial statement down in to
two statements, one to load the temp table, and then the second is the
spatial query, we get an over all performance boost.

So the question I had is this...

Oracle doesn't appear to be able to apply a spatial index to a
collection (defining a collection as a subset of records from an
earlier part of the query process). Is this also true of DB2 and IDS?

If true, then all things considered, IDS will outperform Oracle and
DB2 because of the ability to create temp tables on the fly along with
the ability to add the index after the temp table was created.

My concern is why one can't apply an index to the collection. Is this
because the spatial object is a derived data type?

So now does this question make sense?

Oh BTW, a note to Daniel. Yes Daniel I read the Oracel spatial 10g
guide along with talking with several people who took Oracle's spatial
class and also used TOAD's sql explain tool. So I could see the query
plan and that's how I knew of the gaps in Oracle.

-G

.

Relevant Pages

  • Re: How to Create Local Temporary Table
    ... hold a result set from one query to be used in another query. ... With the improvements in Oracle 9i and 10g and what Oracle has added to its SQL feature set, one can most often get by with just a single SQL statement, which is more efficient than writing data to a table and then reading it back again. ... I can see a developer storing data into a temp table on the first pass and then going through the result set for a second pass. ... Amongst other things PL/SQL provides BULK COLLECT INTO and FOR ALL to speed up processing of VARRAY. ...
    (comp.databases.oracle.server)
  • Re: How to Create Local Temporary Table
    ... Actually I have many query that contain subquery that is used ... I think that this thread has shown that there are other things aside from temp tables that you will probably want to employ in Oracle. ... do you have a specific query that one could look at and give advice on. ...
    (comp.databases.oracle.server)
  • Re: Informix vs Oracle vs DB2. SQL Query optimization.
    ... filter on table A to get the object ids. ... stored in a global temp table. ... like Oracle. ... I had to stop the query after 2.6 hours. ...
    (comp.databases.informix)
  • Re: How to pass date constant to Oracle via openquery
    ... You try using executing this a temp table. ... Simple select * from temp table to feed into Oracle table. ... >I am trying to pass a query through SQL2k to Oracle as follows: ... > SQL server doesn't like the single quotes around the dates, ...
    (microsoft.public.sqlserver)
  • Re: Views - performance
    ... when u use stored object views oracle has to read the data dictionary ... this includes lots of steps for execution ... semantics of the query and fires it ... you'll possibly have any idea is to use explain plan to provide a base ...
    (comp.databases.oracle.server)