RE: AW: Index use vs Table Scan!

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:bob.news@xxxxxxx] 
> Sent: Friday, March 18, 2005 4:01 PM
> To: maxdb@xxxxxxxxxxxxxxx
> 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: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=bob.news@xxxxxxx
> > 
> 
> -- 
> 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/maxdb?unsub=alexander.schroeder@xxxxxxx
> 
> 

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