Hello all, I just saw that my original mail didn't went to the list ...
I also suggested updating statistics, but in fact the non-use of the index was caused because of the queried value in the statement resulted in a result set containing the complete table. When Christoph changed the value in the condition, the index was properly used, as he reported. Regards Alexander Schr�der SAP DB, SAP Labs Berlin > -----Original Message----- > From: Robert Klemme [mailto:[EMAIL PROTECTED] > Sent: Friday, March 18, 2005 4:01 PM > To: [email protected] > Subject: Re: AW: Index use vs Table Scan! > > > 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? > > Did you update statistics? > > Also, you could try with a clustered index on column > eventtime. My guess > would be that that would be used for this type of query as it > is sorted and > thus allows for easier extraction of ranges. > > Kind regards > > robert > > > > > Thanx a lot! > > > > Chris > > > > -----Urspr�ngliche Nachricht----- > > Von: Grossmann, Gert [mailto:[EMAIL PROTECTED] > > 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:[EMAIL PROTECTED] > > 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:[EMAIL PROTECTED] > > 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:[EMAIL PROTECTED] > > 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/[EMAIL PROTECTED] > > > > > > -- > > MaxDB Discussion Mailing List > > For list archives: http://lists.mysql.com/maxdb > > To unsubscribe: > > http://lists.mysql.com/[EMAIL PROTECTED] > > > > -- > > MaxDB Discussion Mailing List > > For list archives: http://lists.mysql.com/maxdb > > To unsubscribe: > > http://lists.mysql.com/[EMAIL PROTECTED] > > > > -- > > MaxDB Discussion Mailing List > > For list archives: http://lists.mysql.com/maxdb > > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > > > -- > DSL Komplett von GMX +++ Superg�nstig und stressfrei einsteigen! > AKTION "Kein Einrichtungspreis" nutzen: http://www.gmx.net/de/go/dsl > > -- > MaxDB Discussion Mailing List > For list archives: http://lists.mysql.com/maxdb > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > -- MaxDB Discussion Mailing List For list archives: http://lists.mysql.com/maxdb To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
