The following statement is blindingly fast:

   SELECT count FROM SYSTAB WHERE table_name = 'whatever'
but the result can be wildly incorrect if many rows have been inserted or deleted since the last CHECKPOINT.

Forcing a CHECKPOINT is a simple solution in some contexts (e.g., single-user embedded database) but it is a [cough] unattractive solution for a heavily-used network database.

Is there any other way to obtain an approximately-correct table row count? ( surely the query optimizer knows the answer :)

asked 15 Jun, 05:24

Breck%20Carter's gravatar image

Breck Carter
accept rate: 20%

edited 15 Jun, 05:27

...and you implicitly state that a simple query like "SELECT COUNT_BIG(*) FROM <whatever> WITH (READUNCOMMITTED)" is too expensive, right?

(15 Jun, 08:24) Volker Barth
Replies hidden

Yeah... that's good for demonstrating a runaway connection :)

(16 Jun, 07:58) Breck Carter

I don't know the answer, but is it possible to get the table size in bytes and divide it by the row size? Maybe this will be not so expensive…

(16 Jun, 08:30) Vlad
Replies hidden

Good idea but... many tables have widely varying row sizes. Many data types can vary in size depending on content; e.g., DECIMAL and all the string types. Also, NULL values are represented by single bits.

In fact, "How do I precisely and accurately calculate individual row sizes?" is a question that doesn't have a simple answer IMO.

Foxhound calculates an overall average "row size" by dividing the sum of all the space allocated to table and index data by the row count. That kind of row size is valuable for growth estimates and the like, but it is far from "precise and accurate".

...alas, we've come full circle; "how do I quickly calculate row count without doing a CHECKPOINT?" :)

(17 Jun, 15:52) Breck Carter

FWIW: What is the use case - a Foxhound feature?

(17 Jun, 16:32) Volker Barth

Yes, exactly. The Foxhound "Display Schema" feature shows row counts and bytes per row for all the tables, plus a "Checkpoint and redisplay" button to bring the counts up to date. However, more and more clients are sensitive about security and don't want to grant Foxhound more privileges than necessary... not to mention the [cough] problems with poorly-timed CHECKPOINT statements.

(18 Jun, 12:21) Breck Carter
showing 2 of 6 show all flat view
Be the first one to answer this question!
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 15 Jun, 05:24

question was seen: 50 times

last updated: 18 Jun, 12:21