comp.databases.informix comp.databases.oracle

comp.databases.informix comp.databases.oracle

Post by DECRAENE Bru » Tue, 07 Apr 1998 04:00:00



If the field 'name' is an index of the table 'table', does this SQL
statement use the index or does it scan the whole database?

select adress from table where name like 'James*'

I've been told Oracle database use the index and Informix don't.
Any information ?

 
 
 

comp.databases.informix comp.databases.oracle

Post by Art S. Kage » Tue, 07 Apr 1998 04:00:00



> If the field 'name' is an index of the table 'table', does this SQL
> statement use the index or does it scan the whole database?

> select adress from table where name like 'James*'

> I've been told Oracle database use the index and Informix don't.
> Any information ?

Informix's optimizer is purely cost based.  It will probably use the
index on name for LIKE clauses as long as the match expression does not
start with a wildcard, although the optimizer is free, based on the
data distribution histograms, to decide that so many rows will match
that criteria that a table scan will actually save I/Os by not reading
the index pages.  So the answer is that it depends on your data and the
key value you are searching for.  If the match string begins with a
wild card character an index cannot be used.  (BTW for LIKE clauses the
ONLY ANSI SQL supported wildcard characters are '%' - match any number
of characters and '_' - match any one character.  The UNIX regular
expression syntax implied by the asterisk '*' in your string IS
supported by the Informix extention MATCHES clause along with character
ranges and other REGEX syntax).

FYI: This did not post to comp.databases.informix as I think you
intended.

Art S. Kagel

 
 
 

comp.databases.informix comp.databases.oracle

Post by Jerry Gitome » Tue, 07 Apr 1998 04:00:00


Since release 7.0 of Oracle (back in 1992) Oracle has had a cost based
optimizer which has improved with every release.  If using the index
will result in no more than 10% of the rows being retrieved the index
will be used.  If more than 10% of the rows will be retrieved the index
probably will not be used.  This is because if more than 10% of the rows
will be retrieved it is faster for Oracle to do a full table scan.

Incidentally, your SQL statement has an error in it.  You should use the
percent sign for the wild card instead of the asterisk.

Regards

Jerry


> If the field 'name' is an index of the table 'table', does this SQL
> statement use the index or does it scan the whole database?

> select adress from table where name like 'James*'

> I've been told Oracle database use the index and Informix don't.
> Any information ?

--
Jerry Gitomer           Since I know how to spell DBA I became one.

 
 
 

1. comp.databases.oracle comp.databases.sybase comp.databases.informix

I have just started working and my company is trying to be a Power Mac
organization and I am supposed to choose a database package which is
portable to Mac, PC and UNIX environment.

I was thinking about Powerbuildler or Gupta SQL windows which don't have
Mac versions till I don't know when.

If you can help me on this it will be great...

--

 Indu Navar                                    
 California State University, Chico

2. SP2 install failed can't revert to old version - win95 Desktop

3. alt.comp.databases,comp.databases,comp.databases.ms-sqlserver

4. Fastest way to do multiple INSERTs

5. Enterprise manager disappeared

6. comp.databases.theory comp.databases.object

7. JavaBlend 2.0

8. Rename comp.databases.pick to comp.databases.mv ?

9. Test comp.databases.comp

10. comp.databases.theory, comp.lang.prolog

11. comp.database or comp.clipper/dbase/paradox ?

12. Eleven FAQ's About comp.databases.informix and informix-list@iiug.org