Re: Advice on picking a database (Oracle, Sybase, Informix, SQL Server

From: Mark D. Stock <marks_at_west.co.za>
Date: 1997/02/27
Message-ID: <33158261.B05@west.co.za>#1/1

Ken Denny wrote:
>
> On a recent project we were evaluating Oracle vs Informix. Although
> Informix had a lot of good things going for it, what made us settle on
> Oracle was that there was one place in the application where we had a
> table which used two columns (SITE and NAME) as its primaty key. We
> needed to start with a particular site and name and select all rows from
> there to the end of the table. The where clause in our select statement
> was:
> WHERE SITE > START_SITE OR (SITE = START_SITE AND NAME >= START_NAME)
> Because there was an "OR" in the where clause Informix couldn't figure
> out that it could use the index and so did a full table scan. Oracle,
> on the other hand, was able to use the index. There are other things
> though that I like about Informix better than Oracle (Descending Indexes
> come to mind).

That sounds like a load of BS to me. :-) But then I may be wrong. If I create a table like so:

	CREATE TABLE site
	(
		site CHAR(20),
		name CHAR(20)
	);
	CREATE INDEX site1 ON site(site,name);

Then run:

	SELECT	*
	FROM	site
	WHERE	site > "here" or (site = "here" and name >= "mark")

I get the results:

QUERY:

---

select *
from site
where site > "here" or (site = "here" and name >= "mark")

Estimated Cost: 2
Estimated # of Rows Returned: 3

1. marks.site: INDEX PATH

    (1) Index Keys: site name (Key-Only)

        Lower Index Filter: marks.site.site > 'here'

    (2) Index Keys: site name (Key-Only)

        Lower Index Filter: (marks.site.name >= 'mark' AND marks.site.site = 'he
re' )

Remember you DO need a composite index for your query. I ran this on OnLine version 5.03.UC1.

Cheers,

-- 
    Mark.

    +------------------------------------------------------------------+
    |Mark D. Stock - The West Solutions Group    http://www.west.co.za |
    |                     The Informix FAQ is at http://www.iiug.org   |
    |mailto:marks_at_west.co.za +-----------------------------------------+
    |Tel:   +27 11 803 2151  |If it doesn't work, force it!            |
    |Fax:   +27 11 803 2189  |If it breaks, it needed replacing anyway!|
    |Cell:  +27 83 250 2325  |Well, that's how I code anyway!          |
    +------------------------+-----------------------------------------+

Received on Thu Feb 27 1997 - 00:00:00 CST