67 lines
2.1 KiB
SQL
67 lines
2.1 KiB
SQL
@@header
|
|
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Check if incremental stats have been used
|
|
* Parameters : 1 - owner (% - wildchar, \ - escape char)
|
|
* 2 - Object name (% - wildchar, \ - escape char)
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 22-Jun-12 Vishal Gupta Created
|
|
*
|
|
*/
|
|
|
|
/************************************
|
|
* INPUT PARAMETERS
|
|
************************************/
|
|
UNDEFINE owner
|
|
UNDEFINE table_name
|
|
|
|
DEFINE owner="&&1"
|
|
DEFINE table_name="&&2"
|
|
|
|
|
|
COLUMN _owner NEW_VALUE owner NOPRINT
|
|
COLUMN _table_name NEW_VALUE table_name NOPRINT
|
|
set term off
|
|
SELECT CASE
|
|
WHEN INSTR('&&owner','.') != 0 THEN SUBSTR(UPPER('&&owner'),1,INSTR('&&owner','.')-1)
|
|
ELSE DECODE(UPPER('&&owner'),'','%',UPPER('&&owner'))
|
|
END "_owner"
|
|
, CASE
|
|
WHEN INSTR('&&owner','.') != 0 THEN SUBSTR(UPPER('&&owner'),INSTR('&&owner','.')+1)
|
|
ELSE DECODE(UPPER('&&table_name'),'','%',UPPER('&&table_name'))
|
|
END "_table_name"
|
|
FROM DUAL;
|
|
set term on
|
|
|
|
|
|
COLUMN owner HEADING "Owner" FORMAT a20
|
|
COLUMN table_name HEADING "TableName" FORMAT a30
|
|
COLUMN column_name HEADING "ColumnName" FORMAT a30
|
|
COLUMN column_name HEADING "ColumnName" FORMAT a30
|
|
COLUMN INCREMENTAL HEADING "Incremental|Stats" FORMAT a20
|
|
|
|
SELECT u.name owner
|
|
, o.name table_name
|
|
, c.name column_name
|
|
, decode(bitand(h.spare2,8),8,'YES','NO') INCREMENTAL
|
|
FROM sys.hist_head$ h
|
|
, sys.obj$ o
|
|
, sys.col$ c
|
|
, sys.user$ u
|
|
WHERE h.obj# = o.obj#
|
|
AND o.subname is null
|
|
AND h.obj# = c.obj#
|
|
AND h.intcol# = c.intcol#
|
|
AND o.owner# = u.user#
|
|
AND u.name LIKE '&&owner' ESCAPE '\'
|
|
AND o.name LIKE '&&table_name' ESCAPE '\'
|
|
;
|
|
|
|
@@footer |