Re: Accessing the data is kind-of slow here

"Martin Pein" <martin.pein_at_fernuni-hagen.de> wrote in message news:aalhi6$4ud$1_at_beech.fernuni-hagen.de...
> Hi there,
>
> I've got a generated table here (in an Oracla 8 db) with about 400 columns
> and about 4000 sets. About 95% of the fields are empty. One of the columns
> is a unique and indexed and not-null'd ID.
>
> Accessing the data is kind-of slow: a "select count(*)" takes about 5
> seconds.
> It also takes about 5 seconds for a "select id from .. where id=17" to
> return with the result (17 :-). The same is basically true for the other
> fields in the row.
>
> All the other tables with fewer columns are much quicker.
> From you experience, is that likely to be an architectural or a technical
> problem?
>
> Thanks,
>
> Martin
>
>
>
>
>
>

Might be both.
If you design a table to have records bigger than db_block_size, you will get all your records chained in multiple blocks. This will result in 2 I/Os for one record.
Also, without any further proof your select count(*) is executing a index fast full scan, your full table scan will always continue to the highwater mark of the table, and deleting records doesn't reset the highwater mark. Also, as you don't provide any details on the rest of your configuration: your system might be slow, with insufficient memory, an insufficient buffer cache (what is your hit ratio?) and all your database files on one single disk.

Too few clues, too many things that could have been causing it. Try running explain plan first on the affected statement.

Hth

--
Sybrand Bakker
Senior Oracle DBA

to reply remove '-verwijderdit' from my e-mail address