AW: Index use vs Table Scan!

Christoph Weser wrote:
> 
> Hello again!
> 
> I have real data in the relation and the EXPLAIN-statement states a huge
> page count and a table scan, when I use the <= or < operator in the
> statement
> 
>       explain select * from s_event where (eventtime < 1111056567313)
> 
> and a page count that is much more smaller and the use of an index, when I
> use the  statement
> 
>       explain select * from s_event where (eventtime = 1111056567313)
> 
> The manual and the section you mentioned doesn't explain this behaviour,
> at
> least I haven't found the explanation.
> 
> Any further hints/tipps?
> 
> Thanx a lot!

You should keep in mind that index-usage is not faster in EVERY case, because 
two different B*-trees (index and primary tree) have to be handled. If the cost 
based optimizer got the info that too many records have to be handled, then it 
is faster to use a table scan (just running through the primary tree, one page 
after the other) than to use the index and jump into the primary tree on a 
random base (key x for index-value 111103432145, key m for the next index-value 
111103432146, then key y for the next index-value 111103432147, jumping around 
like a rabbit (easter is near)), perhaps throwing one page out of the 
data-cache which will be needed for the next primary-key-access again.
Therefore sometimes with increasing number of expected number of result rows 
the switch from index-acces to table scan is done. And in your case the 
optimizer found out that the <-query will result in many more resultrows than 
the =-query.

Elke
SAP Labs Berlin
> 
> Chris
> 
> -----Ursprüngliche Nachricht-----
> Von: Grossmann, Gert [mailto:gert.grossmann@xxxxxxx]
> Gesendet: Freitag, 18. März 2005 09:07
> An: maxdb
> Betreff: AW: Index use vs Table Scan!
> 
> 
> Simple check access path with "EXPLAIN SELECT ... ". Now you can see if
> index or key is used. For further information please read manual section
> 'Background Knowledge/SQL Optimizer'.
> 
> Gert
> 
> -----Ursprüngliche Nachricht-----
> Von: Christoph Weser [mailto:Christoph.Weser@xxxxxxxxx]
> Gesendet: Freitag, 18. März 2005 08:50
> An: Grossmann, Gert
> Betreff: AW: Index use vs Table Scan!
> 
> 
> Hello Gert!
> 
> Thanx for your quick replay and a simple answer.....how do I check the
> access paths and how to interpret results?
> There are right now some real data in the table...a few million rows.
> 
> Thanx a lot for your help!
> 
> Chris
> 
> -----Ursprüngliche Nachricht-----
> Von: Grossmann, Gert [mailto:gert.grossmann@xxxxxxx]
> Gesendet: Freitag, 18. März 2005 07:02
> An: maxdb (E-Mail)
> Betreff: 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=christoph.weser@xxxxxxxxx
> 
> --
> MaxDB Discussion Mailing List
> For list archives: http://lists.mysql.com/maxdb
> To unsubscribe:
> http://lists.mysql.com/maxdb?unsub=christoph.weser@xxxxxxxxx
> 
> --
> MaxDB Discussion Mailing List
> For list archives: http://lists.mysql.com/maxdb
> To unsubscribe:    http://lists.mysql.com/maxdb?unsub=elke.zabach@xxxxxxx


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