Thanks for the information about Informix VTI. Because I'm not
familiar to Informix, I might have missed your point. Would you mind
telling me more about Informix VTI?
On Mon, 15 Nov 2010 08:45:14 -0800
Eric Davies <eric@barrodale.com> wrote:
> With Informix VTI, indexing is the same for native tables as for
> virtual tables, except the interpretation of the 32 bit rowid is left
> up to the developer. When you define the VTI class, you optionally
> supply a method that can fetch data based on a 32 bit rowid, and it's
> the responsibility of your non-indexed scanning methods to provide
> rowids along with the row tuple.
ISTM that index on a VTI table could be inconsistent when original
(remote) data was changed in the way other than VTI. Is it assumed
that the data source is never updated without VTI interface?
> Having local indexes can be very useful if you have a user that
> issues queries like:
> select count(*) from some_external_table where .... ;
> With VTI, the "count" aggregate doesn't get pushed down, meaning that
> without a local index, your scanning method has to return as many
> tuples as match the where clause, which can be very slow.
How can Informix server optimize such kind of query? Counts the index
tuple which match the WHERE clause? If so, such optimization seems to
be limited to "count" and wouldn't be able to be useful for "max" or
"sum". Or, specialized index or VTI class is responsible to the
optimization?
> Local indexes also affords the opportunity of using specialized
> indexes built into the database. My guess is that without some form
> of rowids being passed back and forth, you couldn't define
> non-materialized views of virtual tables that could be indexed.
>
> That said, we implemented our own btree-like index that used the
> pushed down predicates because fetching data one row at a time wasn't
> desirable with our design choices, and we wanted to support virtual
> tables with more than 4 billion rows.
I couldn't see the way to handle virtual table with more than 4
billion rows with 32 bit rowids in local index. Do you mean that your
"btree-like index" searches result rows by predicates directly and
skips getbyid()?
Regards,
--
Shigeru Hanada