Re: Who can explain an AUTOINDEX
Auto index is created automatically by Informix Query Optimizer when indexing
on a column(s) is needed. This is also an informix clue that its better to
create physical index on the specified column.
Gursoy YERLI
dirk.hellmann@laufenberg.com wrote:
> Hi there,
> I see the following phenomenon on my Machine
> -> dgux j2000i1 R4.20MU01 generic AViiON PentiumPro
> -> INFORMIX-OnLine Version 7.21.UD1
>
> QUERY:
> ------
> SELECT A.plz
> FROM Patient P,
> OUTER (daMitAN DMA,
> OUTER Anschrift A)
> WHERE P.datenID = 164011
> AND DMA.datenID = 164011
> AND DMA.class_type = P.class_type
> AND A.wmtDummyA2_ID = DMA.datenID
> AND A.wmtDummyA2_CT = DMA.class_type>
> Estimated Cost: 826578
> ^^^^^^
> Estimated # of Rows Returned: 1
> 1) root.p: INDEX PATH
> (1) Index Keys: datenid class_type (Key-Only)
> Lower Index Filter: root.p.datenid = 164011
>
> 2) root.dma: INDEX PATH (1) Index Keys: datenid class_type (Key-Only)
> Lower Index Filter: (root.dma.datenid = 164011 AND root.dma.class_type =
> root.p.class_type )
>
> 3) root.a: AUTOINDEX PATH
> ^^^^^^^^^
> (1) Index Keys: wmtdummya2_ct wmtdummya2_id
> Lower Index Filter: (root.a.wmtdummya2_id = root.dma.datenid AND
> root.a.wmtdummya2_ct = root.dma.class_type )
>
> The estimated cost is _VERY_ high and an AUTOINDEX appear. What Online does
> is that every free page from online ist occupied by this select and it run
> and run and run .....
>
> Then i put an index into the DB with the Index Keys AUTOINDEX tell me and
> the query is fast as expected. Estimatet Cost: 20 and using INDEX PATH
>
> What is an AUTOINDEX exactly. I didn't found any hint in the Online-Docu
> (Or didn't look in the right index ;-)
>
> Thanks in advance
>
> Dirk Hellmann
> dirk.hellmann@laufenberg.com
>
> -----== Posted via Deja News, The Leader in Internet Discussion ==-----
> http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum