Re: Defragmenting table

In article <8pta27$p0s$1_at_nnrp1.deja.com>,   FGuelfucci <guelfucci_at_my-deja.com> wrote:

Try to run the same query with SQL Trace on (alter session set SQL_TRACE = ON; before you query) and format the trace file with tkprof. It will show you what operations take the time. You can tune the query using this info.

> Thanks Dave,
> I tried using the primary keys with little or no improvement. The
> statistic were done this morning, (UK time).
> This morning I dumped the table and recreated it so everything should
> be pretty new, excluding the data itself.
> That why I think I should need to recreate the table but I don't know
> how to make sure that, what ever the problem is, it does not
> get 'imported' back in the table.
> I also checked if the table is fragmented but this doesn't appear to
 be
> a problem.
> Thanks again.
>
> In article <ss409ukqdc649_at_corp.supernews.com>,
> "Dave A" <dave_and_vanna_at_hotmail.com> wrote:
> > Not sure what the hang up is, but try doing a count(*) with a hint
 to
 use
> > the primary key index. Primary Keys cannot be null so the count
 should be
> > accurate and you will have to read only the index rather than the
 table.
> >
> > I would think your count(*) would use the index anyway, but perhaps
 the
> > statistics are old?
> >
> > Dave A
> >
> > "FGuelfucci" <fguelfucci_at_goalgroup.co.uk> wrote in message
> > news:8psl8q$3eq$1_at_nnrp1.deja.com...
> > > Hi there,
> > > I hope that someone could help me with a little problem.
> > > I often have to do a query on a table, +/-30000 rows, but for some
> > > reason the system takes between 10 to 40 secs to return from the
 select
> > > count(*), statement, (other tables are 5 times the size and 3
 times
> > > faster).
> > > There are 2 indexes and 2 primary keys. The size of the table
 would
> > > indicate that it should be very quick.
> > > For the purpose of the application I have to do a select count(*)
 often
> > > as the exact number of rows varies from time to time, does any one
 know
> > > a better way of doing it or a SQL statement that would defrag the
 table.
> > > Any help would be great, (and would make me look great at work).
> > > Thanks for all the help you can give.
> > > Florent.
> > >
> > >
> > > Sent via Deja.com http://www.deja.com/
> > > Before you buy.
> >
> >
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Sep 15 2000 - 15:29:23 CDT