Questions on Query Performance (IDS7.3)

I have a table, let's say STATABLE, which is expression-fragmented on date. Every fragment holds one day's data. I want to access a certain day's data, such as 1999-11-10. I have 2 ways to to do this. I can write query like: select ... from STATABLE, ANOTHERTABLE where STATABLE.timestamp=date('1999-11-10') and ... Or, I create a view as the following: create view VSTAT as select * from STATABLE where timestamp=date('1999-11-10')then I access the view instead of the table, just like select ... from VSTAT, ANOTHERTABLE where ... The table STATABLE is very large, and has no index on timestamp column. Now, my question is : How will informix's query optimizer deal with this 2 querys ? Especially how does informix deal with view(s) ? Is there a performance difference between this 2 querys ?

In article <382C4D40.B6200DD7@crosskeys.com>, Harry Sheng <hsheng@crosskeys.com> wrote: > I have a table, let's say STATABLE, > which is expression-fragmented on date. > Every fragment holds one day's data. > > I want to access a certain day's data, such as 1999-11-10. > I have 2 ways to to do this. > > I can write query like: > select ... from STATABLE, ANOTHERTABLE > where STATABLE.timestamp=date('1999-11-10') > and ...> > Or, I create a view as the following: > create view VSTAT as > select * from STATABLE where timestamp=date('1999-11-10')> then I access the view instead of the table, just like > select ... from VSTAT, ANOTHERTABLE > where ...> > The table STATABLE is very large, and has no index on timestamp column. > Now, my question is : > > How will informix's query optimizer deal with this 2 querys ? > Especially how does informix deal with view(s) ? > Is there a performance difference between this 2 querys ? > > If the data is fragmented by the timestamp, then the optimizer will realize this and only scan the fragment that contains the correct timestamp's data. On that fragment, it will do a sequential scan, but since the fragmentation strategy guarantees only usable records in the fragment, this is ok. The view is nothing more than a query stored in the database, in essence. I would think that the optimizer strategy would be set at compile time. If that is correct, the view would be faster than a straight query because the optimizer would not need to figure out the correct path every time. However, this is only true if you update statistics regularly. If you don't the view could easily go after the data in an inefficient path, causing it to be slower. I'm assuming that views are treated similarly to stored procedures, so I could be wrong on that second part. Anybody else have any insights? -- # unrm / ksh: unrm: not found # man cpio Sent via Deja.com http://www.deja.com/ Before you buy.