idx.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 very useful script for quickly checking the indexes on a table.
— It is advised to connect as a DBA (or somebody able to SELECT from
— the various SYS tables), start with :
— ALTER SESSION SET CURRENT_SCHEMA=XXX;
— where XXX is the name of the schema where are located most tables
— of interest, and then ...
— 
— Usage : @idx [schema.]table
— 
— Beware : ’no row selected’ may mean ’view’ !!
—  clear breaks set verify off set autotrace off col "INDEX NAME" format A25 col "COLUMN NAME" format A20 col U format A1 col POS format 999 break on "INDEX NAME" on U on "DIST KEYS" select oi.name "INDEX NAME", i.distkey "DIST KEYS", decode(bitand(i.property, 1), 1, ’Y’, ’N’) U, ic.pos# pos, c.name "COLUMN NAME", h.distcnt "DIST VALUES" from sys.col$ c, sys.hist_head$ h, sys.icol$ ic , sys.obj$ oi, sys.ind$ i, sys.obj$ ot, sys.user$ u where u.name = decode(instr(’&1’, ’.’), 0, sys_context(’USERENV’, ’CURRENT_SCHEMA’), upper(substr(’&1’, 1, instr(’&1’, ’.’) - 1))) and u.user# = ot.owner# and ot.name = decode(instr(’&1’, ’.’), 0, upper(’&1’), upper(substr(’&1’, instr(’&1’, ’.’) + 1))) and ot.type# = 2 and i.bo# = ot.obj# and i.obj# = ic.obj# and i.bo# = ic.bo# and oi.obj# = i.obj# and ic.col# = c.col# and ic.bo# = c.obj# and ic.col# = h.col# (+) and ic.bo# = h.obj# (+) order by 1, 4 / clear breaks clear columns