RE: What impacts index build times on partitioned tables?

Hello Cherie

Your talk about other applications made me think that you allowed the DB to be accessed during this time.

Anyway, I think that you need to monitor CPU, I/O, page faults and all the other nice things in the machine.

Yechiel Adar, Mehish Computer Services
adary_at_mehish.co.il

> -----Original Message-----
> From: Cherie_Machler_at_gelco.com [SMTP:Cherie_Machler_at_gelco.com]
> Sent: Thu, February 07, 2002 6:56 PM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: What impacts index build times on partitioned tables?
>
>
> Yechiel,
>
> There is no other activity in this warehouse database while the index
> builds are being done.
> The users access is shut off, no batch jobs for this database are running,
> and the nightly
> data loads have already finished.
>
> Thanks,
>
> Cherie
>
>
>
>
> &alef;&dalet;&resh; &yod;&het;&yod;&alef;&lamed;
>
> <adary_at_mehish. To: Multiple recipients of
> list ORACLE-L <ORACLE-L_at_fatcity.com>
> co.il> cc:
>
> Sent by: Subject: RE: What impacts
> index build times on partitioned tables?
> root_at_fatcity.c
>
> om
>
>
>
>
>
> 02/07/02 10:20
>
> AM
>
> Please respond
>
> to ORACLE-L
>
>
>
>
>
>
>
>
>
> I think that it is related to the activity against the DB.
> Not only there is more load on the DB but access path that use the
> index can default to full table scan if the index is not available
> because you are rebuilding it just now.
>
> Yechiel Adar, Mehish Computer Services
> adary_at_mehish.co.il
>
> > -----Original Message-----
> > From: Cherie_Machler_at_gelco.com [SMTP:Cherie_Machler_at_gelco.com]
> > Sent: Thu, February 07, 2002 5:55 PM
> > To: Multiple recipients of list ORACLE-L
> > Subject: What impacts index build times on partitioned tables?
> >
> >
> > We drop and recreate many indexes on our large partitioned fact table in
> > our data warehouse nightly. The amount of data in the table increases
> > gradually with time but there aren't huge fluctuations in the amount of
> > data in the partitions from day to day.
> >
> > The problem is that the amount of time that it takes to recreate the
> > indexes varies widely. From 40 minutes to almost three hours. I am
> > inclined to believe that this is a scheduling issue related to the time
> of
> > the day and the day of the week. I think we may be experiencing
> > competition from other batch jobs from other applications. One
> > application, in particular, is sort of a black box because it is not on
> > Oracle and it belongs to people who reside in a remote site.
> >
> > However, I want to make sure I've considered all of the possibilities.
> > What factors might influence how long it takes to rebuild indexes on
> > existing partitions?
> > If I just did some maintenance to split out some data for a new time
> > period
> > into a new partition, might that make a difference in how long it takes
> to
> > build the indexes?
> >
> > These indexes are all locally partitioned indexes.
> >
> > Thanks for any insight you can lend.
> >
> > Cherie Machler
> > Oracle DBA
> > Gelco Information Network
> >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > --
> > Author:
> > INET: Cherie_Machler_at_gelco.com
> >
> > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> > San Diego, California -- Public Internet access / Mailing Lists
> > --------------------------------------------------------------------
> > To REMOVE yourself from this mailing list, send an E-Mail message
> > to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> > the message BODY, include a line containing: UNSUB ORACLE-L
> > (or the name of mailing list you want to be removed from). You may
> > also send the HELP command for other information (like subscribing).
> > -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
> > This e-mail was scanned by the eSafe Mail Gateway
> > -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: =?iso-8859-8?Q?=E0=E3=F8_=E9=E7=E9=E0=EC?=
> INET: adary_at_mehish.co.il
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
>
> >W��&kaf;z?^!&qof;&gimel;r&�9,B?
m��??(�?�?&&kaf;a��
> 0!z��1�!*&fkaf;&alef;zW(r?...j?��#^�
> +''��'�?|&het;9?�E
9&fpe;�?&het;NuI�??&tet;(
> �b~�&het;?�&mem;nX�"{^�wi?�??j)b�&gimel;�?!9Q2�&fkaf;&lamed;zW?�??+&j)b�
> b�?�?�?�-g��&die;�'&gimel;�?��&het;?�???&�<^pN?^-X�'??�?F����?���z?�&alef;N
> ^�r[�y�b�&het;(�?��)&alef;P?"&he;@�
> ��azv�z?&zayin;j)b� b�?&die;���?z���&qof;�~�&b<�k&��<�?axq
> =?&(tm)�?~�&tet;���w&tet;�f�?&vav;�G����&&gimel;

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: =?iso-8859-8?Q?&alef;&dalet;&resh;_&yod;&het;&yod;&alef;&lamed;?  INET: adary_at_mehish.co.il

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).