index on tables rows
spatial db
IBM Informix Dynamic Server Version 11.50.UC9 S
S Name Linux
OS Release 2.6.18-348.12.1.el5
OS Node Name wldbp655
OS Version #1 SMP Mon Jul 1 17:54:12 EDT 2013
OS Machine x86_64
spatial.8.21.UC5
question on indexes
should an index have rows in it . we have a table with 3 indexes 1 has rows
the other 2 dont
number of rows in table 219252
question 2
when I look at a explain,out i see sequential reads on table .It mentions an
indexes that dont not exist, why is this ?. I did a dbschema of whole db to
check against
Estimated Cost: 322030
Estimated # of Rows Returned: 216720
1) stby1113.placefc: SEQUENTIAL SCAN
2) stby1113.place: INDEX PATH
(1) Index Name: stby1113.c_fdbid_ix
Index Keys: foreigndbid (Serial, fragments: ALL)
Lower Index Filter: stby1113.placefc.foreigndbid = stby1113.place.foreigndbid
NESTED LOOP JOIN
cant find stby1113.c_fdbid_ix
in the schema at all
If you do this:
SELECT *
FROM sysindices
WHERE idxname = 'c_fdbid_ix';
Is the index found? What's the output look like?
As far as not using an index for the placefc table, that doesn't mean that
the engine didn't find an index, it just means that the engine determined
that so many of the rows in the table would be included in the result set
that it would be more efficient and faster to just scan the table and
ignore the indexes. You didn't post the query so I can't guess whether
that's true or not. The alternative is that the estimate of 216,720 rows
is way off base because your data distributions are stale. Again, I cannot
tell that because you didn't post the actual statistical summary from the
end of the sqexplain.out output either. According to you the table has
219,252 rows which indicates that the query doesn't have any filter on the
rows for this table since that's close to the estimate (only 5% off) so
it's likely that the optimizer is doing the right thing by performing a
sequential scan. If the row estimate is off, then running update
statistics HIGH, MEDIUM, and LOW using the protocols listed in the
Performance Guide or as implemented by my dostats utility will convince the
optimizer to use an index.
If you want better help, post better information.
Art
Art S. Kagel, Principal Consultant
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
other organization with which I am associated either explicitly,
implicitly, or by inference. Neither do those opinions reflect those of
other individuals affiliated with any entity with which I am affiliated nor
those of the entities themselves.
On Tue, Nov 26, 2013 at 4:01 PM, KARL OLIVER <karl.oliver@maf.govt.nz>wrote:
> spatial db
> IBM Informix Dynamic Server Version 11.50.UC9 S
>
> S Name Linux
> OS Release 2.6.18-348.12.1.el5
> OS Node Name wldbp655
> OS Version #1 SMP Mon Jul 1 17:54:12 EDT 2013
> OS Machine x86_64
>
> spatial.8.21.UC5
>
> question on indexes
> should an index have rows in it . we have a table with 3 indexes 1 has rows
> the other 2 dont
>
> number of rows in table 219252
>
> question 2
> when I look at a explain,out i see sequential reads on table .It mentions
> an
> indexes that dont not exist, why is this ?. I did a dbschema of whole db to
> check against
>
> Estimated Cost: 322030
> Estimated # of Rows Returned: 216720
>
> 1) stby1113.placefc: SEQUENTIAL SCAN
>
> 2) stby1113.place: INDEX PATH
>
> (1) Index Name: stby1113.c_fdbid_ix
>
> Index Keys: foreigndbid (Serial, fragments: ALL)
>
> Lower Index Filter: stby1113.placefc.foreigndbid =
> stby1113.place.foreigndbid
> NESTED LOOP JOIN
>
> cant find stby1113.c_fdbid_ix
> in the schema at all
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--001a11c3674250e51c04ec1b0143