Prev: using composite keys in an IN clause
Next: AUTHENTICATION=DATA_ENCRYPT
From: Tonkuma on 17 Feb 2010 09:29
> Yes we have indexes.  If we have a somewhat fragmented database it can
> take 45 secondsto get the count.  After the reorg/runstats it takes
> only a second or two.
The reason of getting faster after reorg might be buffer hit ratio
getting high.
I don't think that scanning fragmented table/index take more than 10
times slower than reorged table/index.

After you took 45 seconds to get the count, if you repeat the count,
you may get the count in shorter time.


From: Hardy on 17 Feb 2010 19:57

"Lennart" <erik.lennart.jonsson(a)gmail.com> 写入消息
news:04c69e0c-619d-4f9a-a5a0-4dae105a9f80(a)b7g2000yqd.googlegroups.com...
> On 15 Feb, 21:44, shorti <lbrya...(a)juno.com> wrote:
>> I am running DB2 V9.5 on AIX. We have a fairly small database (2
>> million records max on main table). We use COUNT(*) periodically. We
>> also schedule runstats and online reorg periodically. At times
>> COUNT(*) can be very slow and although a reorg/runstats speeds it up
>> we can have a window when the count is needed but reorg/runstats has
>> not yet run. Also, I cannot run reorg/runstats at the time a count is
>> requested since it would take longer to do it than it would be to just
>> wait for the count to return.
>>
>> Any suggestions on what else can be used to get the count of a table?
>> The cardinality isnt accurante until a runstats is done so that is not
>> useful. Is there a quick "rowcount" type way to get this info? I
>> just need a speedy way to get number of records.
>>
>
> If you want exact numbers I don't think theres much you can do ( I
> assume mqt's is out of the question for this ). However, if you accept
> approximative numbers you can use table samples as in
>
> select 100*count(*) from T tablesample system(1)
>
> There is also a Bernoulli sample which is more accurate, but slower.
> See:
>
> http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/com.ibm.db2.luw.admin.perf.doc/doc/c0010970.html?resultof=%22%74%61%62%6c%65%73%61%6d%70%6c%65%22%20%22%73%79%73%74%65%6d%22%20
>
> /Lennart
>
>

No silver bullet for exact numbers.

for approximate size of tables, check output of db2pd -tcbstat.

From: Ian on 18 Feb 2010 16:29
On 2/17/10 7:29 AM, Tonkuma wrote:
>> Yes we have indexes. If we have a somewhat fragmented database it can
>> take 45 secondsto get the count. After the reorg/runstats it takes
>> only a second or two.

> I don't think that scanning fragmented table/index take more than 10
> times slower than reorged table/index.

Why not? If FPAGES > NPAGES * 10 (or 30 or 45) it's certainly possible.

The OP should do a runstats when they're getting the problem and post
the output of REORGCHK for that table.






First  |  Prev  | 
Pages: 1 2
Prev: using composite keys in an IN clause
Next: AUTHENTICATION=DATA_ENCRYPT