rowcnt.sql


Print this article  Send the URL to a friend

— ==========================================================================

— 
— Copyright (C) RoughSea Ltd, 2004
— http://www.roughsea.com
— 
— This program is free software; you can redistribute it and/or modify
— it under the terms of the GNU General Public License as published by
— the Free Software Foundation; either version 2 of the License, or
— any later version.
— 
— This program is distributed in the hope that it will be useful,
— but WITHOUT ANY WARRANTY; without even the implied warranty of
— MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
— GNU General Public License for more details.
— 
— You should have received a copy of the GNU General Public License
— along with this program; if not, write to the Free Software
— Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
— 
— ==========================================================================
— 
— A script for quickly getting the respective numbers of rows
— (assuming that statistics are up-to-date) for all the tables
— involved in a complex query.
— 
— Usage : @rowcnt tab1[,tab2,...,tabn]
— 
— There are two big advantages over a classic
— select table_name, num_rows
— from dba_tables
— where table_name in (’TAB1’, ...,’TABn’)
— 
— 1) When you pass the list as an argument, you don’t have to type
— quotes.
— 2) The query is case insensitive, which makes it easy to cut
— and paste the various names from the text of the query.
— 
— It also demonstrate what we think is an interesting technique for
— binding a list of variables, when for instance users can select
— criteria for a query from a multiple-choice drop-down list. Even in
— such a case, you don’t have to hard-code a dynamically built query.
—  clear breaks set verify off set autotrace off set feedback off
— Bind variable list varchar2(256) begin :list := ’&&1’; end; / set feedback on select a.table_name, a.num_rows from dba_tables a, (select decode(x.rn, 1, decode(instr(:list, ’,’), 0, :list, substr(:list, 1, instr(:list, ’,’) - 1)), substr(:list, instr(:list, ’,’, 1, x.rn - 1) + 1, decode(instr(:list, ’,’, 1, x.rn), 0, length(:list), instr(:list, ’,’, 1, x.rn) - instr(:list, ’,’, 1, x.rn - 1) - 1))) tname from (select rownum rn from v$parameter) x where x.rn = 1 or instr(:list, ’,’, 1, x.rn - 1) > 0) b where a.owner = sys_context(’USERENV’,’CURRENT_SCHEMA’) and a.table_name = upper(b.tname) order by 1 / clear breaks clear columns