Re: indexing issue

On Mar 21, 1:06 am, "news.verizon.net" <kenned..._at_verizon.net> wrote:
> "Henry" <ggk..._at_gmail.com> wrote in message
>
> news:676f748e-fc68-4a67-9f20-ad9d6eea1db8_at_u72g2000hsf.googlegroups.com...
>
> > Hi guys,
>
> > Which is the recommended way of creating index?
> > May i know is there any difference between :-
>
> > 1) drop & recreate table+indexes and then populate the table.
> > 2) drop table, populate the table and then create the index.
>
> > Thanks a lot!
>
> Why drop the table in either case?  Please explain what business problem you
> are trying to solve.  In a lot of data warehouse environments they drop the
> indexes and load the table and build the indexes.
> Jim

If the table already exists just add the index.

If the table does not have any data yet and if you want to know which is better to load the table then create the indexes or to load the table with the indexes already defined then in most cases it does not matter. As part of the initial load of the table when the total number of rows X row length to be inserted is large then it can be faster to load the data and then add the indexes but on small to moderate size tables the total time will not vary much. What size qualifies as moderate to large depends in part on your hardware and perceptions. That is, is 5 minutes a small task or a large task. In many shops anything under an hour could be considered a short task because that shop deals in tables that are hundreds of gigabytes or bigger while another shop's largest table may be 5G.

In warehouse systems where bitmap indexes exist on the table dropping the indexes, loading the data, and then re-indexing is common.

HTH -- Mark D Powell -- Received on Fri Mar 21 2008 - 10:09:25 CDT