| Maximum rows [message #11229] |
Mon, 15 March 2004 01:39  |
Kader
Messages: 5
Registered: January 2002
|
Junior Member |
|
|
Hi Pauls,
How to find out the table name which has the maximum number of rows?
Thanks in advance
Regards
Kader
|
|
|
|
| Re: Maximum rows [message #11236 is a reply to message #11229] |
Mon, 15 March 2004 03:36  |
 |
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
If your tables have been recently analyzed, so that the num_rows column of the all_tables table has been populated with the statistics gathered, then you can use something like this:
SELECT owner, table_name, num_rows
FROM all_tables
WHERE num_rows =
(SELECT MAX (num_rows)
FROM all_tables)
/
If your tables have not been recently analyzed, then you can use something like this:
CREATE OR REPLACE FUNCTION count_rows
(p_owner IN VARCHAR2,
p_table_name IN VARCHAR2)
RETURN NUMBER
AUTHID CURRENT_USER
AS
v_num_rows NUMBER;
BEGIN
EXECUTE IMMEDIATE
'SELECT COUNT (*) FROM "' || p_owner || '"."' || p_table_name ||'"'
INTO v_num_rows;
RETURN v_num_rows;
EXCEPTION
WHEN OTHERS THEN RETURN NULL;
END count_rows;
/
SELECT owner, table_name,
count_rows (owner, table_name) cnt
FROM all_tables
WHERE count_rows (owner, table_name) =
(SELECT MAX (count_rows (owner, table_name))
FROM all_tables)
/
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
