AW: Index use vs Table Scan!

MaxDB has a cost based optimizer. Without any data in relations there is no 
difference between table scan vs. index access.
Fill your releation "s_event" with real data and then check access pathes.

Gert

-----Ursprüngliche Nachricht-----
Von: Christoph Weser [mailto:Christoph.Weser@xxxxxxxxx] 
Gesendet: Donnerstag, 17. März 2005 17:29
An: maxdb (E-Mail)
Betreff: Index use vs Table Scan!


Hello All!

I'm running 7.4.3.32 on a W2K system.

I have the table: 

create table s_event (
       EID FIXED(38,0) NOT NULL, 
       ETID FIXED(38,0) NOT NULL,
       SID FIXED(38,0) NOT NULL,
       CID FIXED(38,0),
       eventtime FIXED(38,0) NOT NULL,
       data LONG BYTE,

       FOREIGN KEY(ETID) references s_eventtype(ETID)
)

//
create index s_event_x1 on s_event(EID)
//
create index s_event_x2 on s_event(ETID)
//
create index s_event_x3 on s_event(SID)
//
create index s_event_x4 on s_event(CID)
//
create index s_event_x5 on s_event(eventtime)

When I do an 

        explain select * from s_event where (eventtime < 1111056567313)

the result is a table scan!

On the other hand, when I do an 

        explain select * from s_event where (eventtime = 1111056567313)

The DB does an index scan and it uses the index s_event_x5.


Is there a way for the first statement, that the db also does an index scan
and not the expensive table scan???
Why isn't an index also used in the first statement? Any explanations?

Thanx a lot to everyone!!!

Chris

-- 
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe:    http://lists.mysql.com/maxdb?unsub=gert.grossmann@xxxxxxx


--
MaxDB Discussion Mailing List
For list archives: http://lists.mysql.com/maxdb
To unsubscribe:    http://lists.mysql.com/maxdb?unsub=gcdm-maxdb@xxxxxxxxxxx