| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Counting number of rows
Sorry my previous example was to know if there was records or not in a
table, this is a better example to estimate rows in a table.
Selecting a Sample: Examples The following query estimates the number of
orders in the oe.orders table:
SELECT COUNT(*) * 100 FROM orders SAMPLE BLOCK (1);
The following example creates a sampled subset of the sample table
hr.employees table and then joins the resulting sampled table with
departments. This operation circumvents the restriction that you cannot specify
the sample_clause in join queries:
CREATE TABLE sample_emp AS
SELECT employee_id, department_id FROM employees SAMPLE(10);
SELECT e.employee_id FROM sample_emp e, departments d
WHERE e.department_id = d.department_id
AND d.department_name = 'Sales';
Assuming you don't need a very accurate answer, you have the option of using the SAMPLE() clause - do a count(*) with (for example) a 1% sample then multiply by 100
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
The educated person is not the person
who can answer the questions, but the
person who can question the answers -- T. Schick Jr
Next public appearances:
March 2004 Hotsos Symposium - The Burden of Proof
March 2004 Charlotte NC OUG - CBO Tutorial
April 2004 Iceland
One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html
Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____UK___February
____UK___June
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
Is there a quick way to count number of rows in a table? Don't want to do select count(*) from..... on a table (such as GL_BALANCES) with more than 250 million rows.
 |
 |