Re: Yes, this is probably a stupid question but...

From: Pat <>
Date: Sat, 12 Apr 2008 17:01:00 -0700 (PDT)
Message-ID: <>

On Apr 12, 4:44 pm, "Ana C. Dent" <> wrote:
> Pat <> wrote in news:0653963d-1d32-4e85-9bae-
> > Question I have is:
> > Short of constructing a summary table (which is probably a non starter
> > in this environment), is there anything I could to to speed up the
> > return time of a count query like the above?
> Ready, Fire, Aim!
> If you don't know specifically where the time is being spent,
> you'd only be guessing what change is most optimal.
> Run your query with SQL_TRACE=TRUE & run the results thru tkprof.
> Post the results & EXPLAIN PLAN back here is you still need more asisstance

I'm working over a remote desktop at the moment so I can't cut and paste the plan so I'll transcribe:

select count(*) as recordcount from (a inner join b on a.sys_id = b.sys_id) where = 1

Table has aprox 180k rows, of which 151k have active = true


___INDEX RANGE SCAN (index on, a.sys_id)
___INDEX UNIQUE SCAN (index on b.sys_id)

Total Query time: 2.39 seconds

I've got a covering index on the primary table and an index on the child table's join condition, so there's no actual table reads going on here e.g. it's just an index join. I have current stats on the table (as of last night). From what I can see this is not an absurdly bad optimizer plan, but then I suspect there's something I'm not seeing, hence my post here. Received on Sat Apr 12 2008 - 19:01:00 CDT

Original text of this message