Re: Question about index performance for a "...where x > 'y
Art,
Thanking you again for your help.
I guess this may be a development environment issue and so you may not have
enough information for an answer.
BUT, when I run SET EXPLAIN ON I get error -534, -1.
We are using Solaris, and I am using dbaccess to test out certain queries
on the database before entring them into ESQL in the application. I have
tried moving to my home directory and creating a directory with full
permissions and launching dbaccess from their, I have even tried moving to
/usr/informix, a directory owned by the 'informic' user, who it is I
presume (as the server) that is trying to write the sqexplain.out file.
I have tried SET EXPLAIN FILE TO '...............' WITH APPEND, but I get a
syntax error, may be I don't have the right informix version. HOWEVER,
that may not do me any good with the ownership issue any way.
Andrew.
|---------+---------------------------->
| | "ART KAGEL, |
| | BLOOMBERG/ 65E |
| | 55TH" |
| | <KAGEL@bloomberg.|
| | net> |
| | |
| | 25/11/2003 15:38 |
| | |
|---------+---------------------------->
>--------------------------------------------------------------------------------------------------------------------------------------------------|
| |
| To: Andrew.Hardy@marconi.com |
| cc: |
| Subject: Re: Question about index performance for a "...where x > 'y |
>--------------------------------------------------------------------------------------------------------------------------------------------------|
----- Original Message -----
From: Andrew Hardy <Andrew.Hardy@marconi.com>
At: 11/25 10:31
>
> Thank you for your help.
>
> The oid field is actually an opaque type which uses '....' for its input
> function.
Ahh.
> My example is misleading and only designed to demonstrate the order I
> employed in my test.
Got it.
> I will try using OPTIMIZATION FIRST_ROWS.
>
> In light of the oid not being an integer, and asuming the functions are
OK
> (the index creates successfully), do you have any thing to add ?
Only to run under SET EXPLAIN ON; so you can see what the optimizer is
actually
doing. Remember that the optimizer can choose to ignore the USE directive.
Art
> Many thanks again.
>
> Andrew.
>
>
>
> |---------+---------------------------->
> | | "Art S. Kagel" |
> | | <KAGEL@bloomberg.|
|
> | | |
> | | 25/11/2003 13:58 |
> | | Please respond to|
> | | kagel |
> | | |
> |---------+---------------------------->
>
>
>-------------------------------------------------------------------------------
> -------------------------------------------------------------------|
> |
> |
> | To: "Andrew Hardy" <Andrew.Hardy@marconi.com>
> |
> | cc:
> |
> | Subject: Re: Question about index performance for a "...where
x >
'y'
> order by |
>
>
>-------------------------------------------------------------------------------
> -------------------------------------------------------------------|
>
>
>
>
> [This is an email copy of a Usenet post to "comp.databases.informix"]
>
> On Tue, 25 Nov 2003 03:58:10 -0500, Andrew Hardy wrote:
>
> A 10000 row table may be considered small by the engine if there are many
> rows
> on a single page, if so, the engine is likely performing a table scan to
> identify the rows and sorting. Have you tried running under SET EXPLAIN
> ON; to
> see the query plan and verify the perceived behavior? You could try SET
> OPTIMIZATION FIRST_ROWS; this will force the engine to optimize the query
> for
> returning the initial rows as quickly as possible, this is more likely to
> use an
> index that matches the ORDER BY clause. But wait, there's more:
>
> > SELECT {+INDEX(user_file_read_table, oid_index)} first 1 oid, reader,
> > fileName, readStatus FROM user_file_read_table WHERE oid >
> > '\\15tesdir1/testdir2/4998\\06hardya' ORDER BY oid
>
> Ummmmm. The oid column is an integer? According to below it contains
> small
> numbers. So, why is the query above comparing it to a string that is
> apparently
> a file path? This will cause the engine to convert the values of oid to
> strings
> for comparison. Since ALL of the strings "1", "2", etc. are
> lexicographically
> greater than any string that begins with "\\15" (character Octal(15)), the
> filter
> is selecting all rows and so the sequential scan followed by a physical
> sort is
> the BEST query path, and that is the behavior you are experiencing.
>
> Art S. Kagel
>
> > I have the above sql statement. An ascending index has been
successfully
> > created on the opaque type oid field.
> >
> > I have a 10,000 row table for testing, which in order of appearance has
> the
> > first oid starting in the middle of the table and ascending to the end
of
> the
> > table, the next in oid order is then the first record in order of
> appearance
> > continuing to the middle.
> >
> > A miniture example would be as follows
> >
> > Rows in order of appearance
> > 1. oid = 5, filename = "xxx", ...
> > 2. oid = 6, filename = "xxx", ...
> > 3. oid = 7, filename = "xxx", ...
> > 4. oid = 8, filename = "xxx", ...
> > 5. oid = 1, filename = "xxx", ...
> > 6. oid = 2, filename = "xxx", ...
> > 7. oid = 3, filename = "xxx", ...
> > 8. oid = 4, filename = "xxx", ...
> >
> > This index appeared to create ok.
> >
> > CREATE UNIQUE INDEX oid_index ON %s (oid ASC); ALTER TABLE %s ADD> CONSTRAINT
> > PRIMARY KEY (oid);
> >
> > And had all the functions it needed.
> >
> > Now, if I execute the above statement to get the first row (in oid
order
> )
> > after a specified oid, I get the right answer, what ever oid I specify
or
> what
> > ever I put into the database.
> >
> > BUT the oids I specify nearer the top end of the oid range, the longer
> the
> > quer