Re: Index clarification please...

In article: <43rmvj$sku_at_aplinfo.jhuapl.edu> Stan Novinsky <stan_novinsky_at_jhuapl.edu> writes:

> In the Oracle DBA guide and Application Developers guide, it
> states that an index or indexes for a table should be created
> after the rows are inserted into the table.

Actually, the recommendation is that imports will be faster if you create the indexes after importing the data.

> Questions based
> on the following table:
>
> CREATE TABLE sdata
> (s_no number(7)
> CONSTRAINT pk_sdat
> PRIMARY KEY
> USING INDEX TABLESPACE
> data_idx
> PCTFREE 5
> STORAGE
> (INITIAL 10k
> NEXT 10K
> MINEXTENTS 1
> MAXEXTENTS 110),
> name char(5)
> CONSTRAINT ck_name
> CHECK (name = UPPER(name)),
> .
> .
> );
>
>
>
> a. If the SDATA table will be growing, (i.e. rows
> inserted every day), does this mean the table should
> not have an index ?
>

Not really. We have many large tables with continuous data entry. There is not an appreciable overhead in having many indexes on each table. Obviously if you have no indexes then inserts WILL be faster, but you have to balance that against the time taken to create the indexes when they are needed. And if you need to query the data while it is being inserted then you will need the indexes.

> b. Will the above table have an index named PK_SDATA
> located in the DATA_IDX tablespace ?

Yes.

>
> b. If the first paragraph is true, should I remove the
> PRIMARY KEY CONSTRAINT clause in the SDATA table and
> use the CREATE INDEX command after rows are entered
> into the table ?
>

See answer above.

> As a note, the majority of my tables will have rows being entered and
 retrieved the majority of the time. Tables will vary from
> 400,000 rows as low as 100 rows.
>

Ah. Well then. You will need indexes. Full table scan retrievals dent performance hundreds of times more than inserts into tables with indexes.

We have a table with 500,000 rows. It has four 200 meg indexes. A Full Table Scan for one row takes, ooh, half an hour, and grinds the CPU (Alpha/VMS) to a halt. An insert takes 0.06 secs CPU (YMMV).

-- 

------------------------------------------------------------------------
|  Peter Moore - Database Administrator - MAT Transport Ltd, London, UK
|  pt_at_chaff.demon.co.uk : +44 (171) 410 6373
|  "With a little study you'll go a long ways & I wish you'd start now!"