IDS Forum

Re: RES: 10 Years of Innovation at IBM

Posted By: Fernando Nunes
Date: Wednesday, 11 May 2011, at 11:55 a.m.

In Response To: Re: RES: 10 Years of Innovation at IBM (John Miller iii)

I think that the btree cleaners can't "clean" the indexes properly in the
sense I'm thinking about.
This of course is not an expertise opinion, but the result of some past
experiences.

My feeling is that if your index has some special characteristics it will
become worst with time. Specifically I'm thinking in indexes containing
timestamps. The insert order will mean that new records will have keys
"bigger" than all the others... I don't know how to explain this
technically, but you'll see your performance suffer in some particular
situations (update stats low is the most obvious). And yes, I know this
particular problem is being analyzed/fixed, and I'm aware that it's not
trivial (clustered value for the optimizer).
But the same causes can also cause performance degradation in queries that
retrieve large number of keys from the index (key first). Although SKIP SCAN
should solve this...

Now... If you have some custom magic that will make the btree scaners solve
this issue I'm all hears... unfortunately I won't be at the conference... :(

Regards

On Wed, May 11, 2011 at 4:43 PM, John Miller iii <miller3@us.ibm.com> wrote:

> If your ultimate goal is to "clean" the index then it sounds like you
> should
> ensure the btree scanners are properly tuned and then request that they=
>
> clean all of your indexes. This will compact your indexes online wit=
> hout
> having to rebuild the index. While this functionality of cleaning the
> indexes
> has been in for quite some time, there were big improvement to this
> cleaning functionality in version 11.50.xC2.
>
> If you want more details, I will be talking about this in my performan=
> ce
> tutorial at the IIUG conference this Sunday. -:)
>
> John F. Miller III
> STSM, Embedability Architect
> miller3@us.ibm.com
> 503-578-5645
> IBM Informix Dynamic Server (IDS)
>
> ids-bounces@iiug.org wrote on 05/11/2011 08:20:21 AM:
>
> > From:
> >
> > "Celso Cabral Coimbra" <ccoimbra@cleartech.com.br>
> >
> > To:
> >
> > ids@iiug.org
> >
> > Date:
> >
> > 05/11/2011 08:21 AM
> >
> > Subject:
> >
> > RES: 10 Years of Innovation at IBM [23648]
> >
> > Sent by:
> >
> > ids-bounces@iiug.org
> >
> > Fernando,
> >
> > Yes, it is exactly what I'm talking about. I want to "clean" the inde=
> x.
> > Dropping and recreating the index, creates it smaller and faster, eve=
> n
> > creating it using the fillfactor like 70 or smaller. But I have a
> overhead of
> > days to recreate the PK and mainly FK after recreating the index.
> > This feature exists in others RDBMS and is very helpful.
> >
> > Regards,
> >
> > Celso Cabral Coimbra
> > Administrador de Banco de Dados
> > ClearTech Ltda
> > "Trust at the heart of Communications"
> > Tel. (11) 3576-4509
> >
> > -----Mensagem original-----
> > De: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] Em nome de
> Fernando
> > Nunes
> > Enviada em: quarta-feira, 11 de maio de 2011 12:08
> > Para: ids@iiug.org
> > Assunto: Re: 10 Years of Innovation at IBM [23647]
> >
> > Not sure if I'm following this... Why would we drop and index and cre=
> ate
> it
> > again?
> > One situation comes to mind that is, if we want to "clean" the index.=
> For
>
> > that, the "rebuild" would be potencially helpful since we could skip =
> the
> > ORDER of the records.
> > Is this what you're talking about?
> >
> > Also, sometimes exclsuive locks could potencially be exchanges bey sh=
> ared
>
> > locks (no changes)
> > Regards.
> >
> > On Wed, May 11, 2011 at 4:01 PM, Celso Cabral Coimbra <
> > ccoimbra@cleartech.com.br> wrote:
> >
> > > Yes, I can do that. But imagine dropping a index in a table with 5 =
> bi
> rows
> > > that support a Foreign key to a table with 2 bi rows.
> > > Using a supposed "rebuild index online feature", it would recreate =
> a
> index
> > > reading directly the existent index create the new index using othe=
> r
> disk
> > > space and the drop the old index. The constraints will always have =
> the
> > > index
> > > to be supported.
> > > When I drop a index that support a PK or FK it will create another
> index
> > > automatically to support those constraint, if I'm not mistaken.
> > >
> > > Thank you Art,
> > >
> > > Celso Cabral Coimbra
> > > Administrador de Banco de Dados
> > > ClearTech Ltda
> > > "Trust at the heart of Communications"
> > > Tel. (11) 3576-4509
> > >
> > > -----Mensagem original-----
> > > De: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] Em nome de A=
> rt
> > > Kagel
> > > Enviada em: quarta-feira, 11 de maio de 2011 11:37
> > > Para: ids@iiug.org
> > > Assunto: Re: 10 Years of Innovation at IBM [23644]
> > >
> > > Celso:
> > >
> > > You can drop and index and build the index online since version 10.=
> 00
> by
> > > adding the ONLINE keyword:
> > >
> > > begin work;
> > > drop index fred_pk;
> > > create index fred_pk on fred( serial_col ) online;
> > > commit work;
> > >
> > > Art
> > >
> > > Art S. Kagel
> > > 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 an=
> y
> other
> > > organization with which I am associated either explicitly, implicit=
> ly,
> or
> > > by
> > > inference. Neither do those opinions reflect those of other individ=
> uals
>
> > > affiliated with any entity with which I am affiliated nor those of =
> the
> > > entities themselves.
> > >
> > > On Wed, May 11, 2011 at 10:18 AM, Celso Cabral Coimbra <
> > > ccoimbra@cleartech.com.br> wrote:
> > >
> > > > Jerry,
> > > >
> > > > In the sequence
> > > > 1 - Large chunks
> > > > 2 - Multiple page size and buffer size
> > > > 3 - Storage Optimization
> > > > 4 - Multiple secondary servers
> > > > 5 - Expand Warehouse Infrastructure
> > > > 6 - Reduced Overhead For Foreign Key Constraints
> > > >
> > > > BTW, I would like to see "index rebuild" (online) in the next
> version!
> > > >
> > > > Best Regards,
> > > >
> > > > Celso Cabral Coimbra
> > > > Administrador de Banco de Dados
> > > > ClearTech Ltda
> > > > "Trust at the heart of Communications"
> > > > Tel. (11) 3576-4509
> > > >
> > > > -----Mensagem original-----
> > > > De: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] Em nome de=
>
> Jerry
> > > > Keesee
> > > > Enviada em: ter=E7a-feira, 10 de maio de 2011 17:06
> > > > Para: ids@iiug.org
> > > > Assunto: 10 Years of Innovation at IBM [23627]
> > > >
> > > > July 1st will be the 10th anniversary of the IBM acquisition of t=
> he
> > > > Informix assets. I recently surveyed several of the Informix
> architects
> > > > and asked them several questions about the evolution of the Infor=
> mix
> > > > technology since the acquisition. I've heard from them. Now I wou=
> ld
> like
> > > > to get your thoughts.
> > > >
> > > > What do you believe were the top 5 features or enhancements to th=
> e
> > > > Informix portfolio since 2001?
> > > >
> > > > What do you believe were the top 3 under appreciated features
> released
> > > > over the last 10 years?
> > > >
> > > > I appreciate your feedback. I will include some of this feedback =
> in
> my
> > > > keynote next week at the Informix conference in Lenexa and then a=
> gain
> in
> > > > the Chat with the Lab on the 18th of May.
> > > >
> > > > Regards,
> > > >
> > > > Jerry Keesee
> > > > World Wide Director, Informix Software
> > > >
> > > >
> > > >
> > > >
> > >
> > >
> > >
> >
> >
> ***********************************************************************=
> ********
>
> > > > Forum Note: Use "Reply" to post a response in the discussion foru=
> m.
> > > >
> > > >
> > > >
> > > >
> > >
> > >
> > >
> >
> >
> ***********************************************************************=
> ********
>
> > > > Forum Note: Use "Reply" to post a response in the discussion foru=
> m.
> > > >
> > > >
> > >
> > > --20cf3071c6d2861a6b04a300ff82
> > >
> > >
> > >
> > >
> >
> >
> ***********************************************************************=
> ********
>
> > > Forum Note: Use "Reply" to post a response in the discussion forum.=
>
> > >
> > >
> > >
> > >
> >
> >
> ***********************************************************************=
> ********
>
> > > Forum Note: Use "Reply" to post a response in the discussion forum.=
>
> > >
> > >
> >
> > --
> > Fernando Nunes
> > Portugal
> >
> > http://informix-technology.blogspot.com
> > My email works... but I don't check it frequently...
> >
> > --001485f911f676649104a3016e07
> >
> >
> >
> ***********************************************************************=
> ********
>
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
> >
> ***********************************************************************=
> ********
>
> > Forum Note: Use "Reply" to post a response in the discussion forum.=
>
> >=
>
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>

--
Fernando Nunes
Portugal

http://informix-technology.blogspot.com
My email works... but I don't check it frequently...

--001636c5c22d11603504a302180c

Messages In This Thread

• 10 Years of Innovation at IBM
  Jerry Keesee -- Tuesday, 10 May 2011, at 4:05 p.m.
  • RE: 10 Years of Innovation at IBM
    Joerg Volz -- Tuesday, 10 May 2011, at 4:27 p.m.
    • RE: 10 Years of Innovation at IBM
      amarini@fazenda.ms.gov.br -- Tuesday, 10 May 2011, at 8:33 p.m.
  • Re: 10 Years of Innovation at IBM
    Art Kagel -- Tuesday, 10 May 2011, at 4:56 p.m.
    • Re: 10 Years of Innovation at IBM
      rroussey@comcast.net -- Wednesday, 11 May 2011, at 8:54 a.m.
  • RES: 10 Years of Innovation at IBM
    Celso Cabral Coimbra -- Wednesday, 11 May 2011, at 10:18 a.m.
    • Re: 10 Years of Innovation at IBM
      Art Kagel -- Wednesday, 11 May 2011, at 10:36 a.m.
      • RES: 10 Years of Innovation at IBM
        Celso Cabral Coimbra -- Wednesday, 11 May 2011, at 11:01 a.m.
        • Re: 10 Years of Innovation at IBM
          Fernando Nunes -- Wednesday, 11 May 2011, at 11:07 a.m.
          • RES: 10 Years of Innovation at IBM
            Celso Cabral Coimbra -- Wednesday, 11 May 2011, at 11:20 a.m.
            • Re: RES: 10 Years of Innovation at IBM
              John Miller iii -- Wednesday, 11 May 2011, at 11:43 a.m.
              • Re: RES: 10 Years of Innovation at IBM
                Fernando Nunes -- Wednesday, 11 May 2011, at 11:55 a.m.
        • Re: 10 Years of Innovation at IBM
          Art Kagel -- Wednesday, 11 May 2011, at 11:31 a.m.
  • Re: 10 Years of Innovation at IBM
    Peter_Logan@spartanstores.com -- Wednesday, 11 May 2011, at 11:06 a.m.
  • Re: 10 Years of Innovation at IBM
    Fernando Nunes -- Wednesday, 11 May 2011, at 5:07 p.m.
  • Re: 10 Years of Innovation at IBM
    Obnoxio The Clown -- Wednesday, 11 May 2011, at 5:50 p.m.
  • Re: 10 Years of Innovation at IBM
    Jack Parker -- Wednesday, 11 May 2011, at 10:37 p.m.
    • Re: 10 Years of Innovation at IBM
      Fernando Nunes -- Thursday, 12 May 2011, at 6:00 a.m.
      • Re: 10 Years of Innovation at IBM
        Jack Parker -- Thursday, 12 May 2011, at 7:41 a.m.
        • Re: 10 Years of Innovation at IBM
          Fernando Nunes -- Thursday, 12 May 2011, at 11:08 a.m.
          • Re: 10 Years of Innovation at IBM
            Madison Pruet -- Thursday, 12 May 2011, at 11:34 a.m.
            • Re: 10 Years of Innovation at IBM
              Fernando Nunes -- Thursday, 12 May 2011, at 11:50 a.m.
              • Re: 10 Years of Innovation at IBM
                Jack Parker -- Thursday, 12 May 2011, at 12:08 p.m.
  • Re: 10 Years of Innovation at IBM
    VICTOR VAT -- Thursday, 12 May 2011, at 11:44 a.m.
    • Re: 10 Years of Innovation at IBM
      Art Kagel -- Thursday, 12 May 2011, at 12:12 p.m.
      • Re: 10 Years of Innovation at IBM
        VICTOR VAT -- Thursday, 12 May 2011, at 12:37 p.m.
  • Re: 10 Years of Innovation at IBM
    FRANK -- Friday, 13 May 2011, at 5:27 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.