home *** CD-ROM | disk | FTP | other *** search
/ Liren Large Software Subsidy 5 / 05.iso / a / a524 / 28.ddi / admin / oneidxs.sql < prev    next >
Encoding:
Text File  |  1991-03-04  |  8.5 KB  |  236 lines
  1. rem
  2. rem $Header: oneidxs.sql,v 6002700.1 89/10/12 18:42:50 cyang Exp $ oneidxs.sql Copyr (c) 1989 Oracle
  3. rem
  4. Rem Copyright (c) 1989 by Oracle Corporation
  5. Rem NAME
  6. Rem   ONEIDXS.SQL
  7. Rem  FUNCTION
  8. Rem    See below and IDXSTAT.DOC
  9. Rem  NOTES
  10. Rem    See below
  11. Rem  MODIFIED
  12. Rem   Aporter    09/24/89 - Creation
  13. Rem   Porter    09/23/89 - Change to 8-character filenames
  14. Rem   Porter    04/04/89 - Commenting, cleanup
  15. Rem   Porter    03/27/89 - Creation
  16. Rem
  17. Rem-------------------------------------------------------------------   
  18. Rem                           ONEIDXS.SQL 
  19. Rem
  20. Rem    Use this procedure to find out information about how 
  21. Rem    selective columns are. Use it to:
  22. Rem
  23. Rem      1. Identify prospective columns for new indexes
  24. Rem      2. Determine how selective a current index is
  25. Rem      3. Determine whether a current index is useful or not
  26. Rem
  27. Rem    SQL> START ONE_INDEX_STATS TABLE_NAME COLUMN_NAME
  28. Rem
  29. Rem    NOTE: This procedure requires SQLPLUS version 3.0.3.1 or greater,
  30. Rem          in order for the NEW_VALUE statement to be implemented 
  31. Rem          correctly.
  32. Rem-------------------------------------------------------------------
  33.  
  34. Rem 
  35. Rem *** Set up variables ***
  36. Rem
  37. Set Heading Off
  38. Set Verify Off                     
  39. Set Feedback Off
  40. column table_name new_value one_table_name
  41. column column_name new_value one_column_name
  42. SELECT upper('&1') table_name, upper('&2') column_name FROM DUAL;
  43. Set Heading On
  44. Set Verify On
  45. Set Feedback On
  46.  
  47. SET TERMOUT OFF
  48.  
  49. Rem
  50. Rem     Table statistics:
  51. Rem
  52. Rem     If an indexed column has nulls in it, then care must be used
  53. Rem     in analyzing performance of that index:
  54. Rem       1. A query for a null value will do a table-scan, and
  55. Rem          will never use an index.
  56. Rem       2. A query for a non-null value will only have to search
  57. Rem          an index that has no null entries in it.  Thus, no 
  58. Rem          performance will be lost between a table with 100 not-null
  59. Rem          entries and 100 not-null and 1000 null entries, as long
  60. Rem          as queries for not-null values are made.
  61. Rem
  62.  
  63.  
  64. Rem 
  65. Rem *** Create user statistics tables, if not already created ***
  66. Rem
  67. CREATE TABLE INDEX$INDEX_STATS (
  68.   TABLE_NAME        CHAR(30) NOT NULL,
  69.   COLUMN_NAME       CHAR(30) NOT NULL,
  70.   STAT_NAME         CHAR(30) NOT NULL,
  71.   STAT_VALUE        NUMBER   NOT NULL);
  72.  
  73. create table INDEX$BADNESS_STATS
  74.   (table_name            char(30) not null,
  75.    column_name           char(30) not null,
  76.    badness_factor        number(9),
  77.    keys_with_badness     number(9),
  78.    row_percent           number(9,3),
  79.    row_blk_fail          number(9),
  80.    row_blk_succ          number(9),
  81.    key_percent           number(9,3));
  82.  
  83. Rem
  84. Rem *** Get rid of any current lines in the tables ***
  85. Rem
  86. delete from index$index_stats
  87.   where table_name = '&one_table_name' and column_name = '&one_column_name';
  88. delete from index$badness_stats
  89.   where table_name = '&one_table_name' and column_name = '&one_column_name';
  90.  
  91. Rem
  92. Rem *** GET STATISTICS ***
  93. Rem
  94.  
  95. COLUMN RECORDS NEW_VALUE TOT_ROWS;
  96. SELECT COUNT(*) RECORDS 
  97.   FROM &one_table_name;
  98. INSERT INTO INDEX$INDEX_STATS VALUES('&one_table_name','&one_column_name',
  99.                                      'Rows - Total',
  100.                                      &tot_rows);
  101.  
  102. COLUMN NULLS NEW_VALUE TOT_NULLS;
  103. SELECT COUNT(*) NULLS 
  104.   FROM &one_table_name 
  105.   WHERE &one_column_name IS NULL;
  106. INSERT INTO INDEX$INDEX_STATS VALUES('&one_table_name','&one_column_name',
  107.                                      'Rows - Null',
  108.                                      &tot_nulls);
  109.  
  110. COLUMN DISTINCT_KEYS NEW_VALUE TOT_DISTINCT_KEYS
  111. SELECT COUNT(DISTINCT &one_column_name) DISTINCT_KEYS 
  112.   FROM &one_table_name 
  113.   WHERE &one_column_name IS NOT NULL;
  114. INSERT INTO INDEX$INDEX_STATS VALUES('&one_table_name','&one_column_name',
  115.                                      'Total Distinct Keys',
  116.                                      &TOT_DISTINCT_KEYS);
  117.  
  118. COLUMN NOT_NULLS NEW_VALUE TOT_NOT_NULLS;
  119. SELECT &TOT_ROWS - &TOT_NULLS NOT_NULLS 
  120.   FROM DUAL;
  121. INSERT INTO INDEX$INDEX_STATS ('&one_table_name','&one_column_name',
  122.                                'Rows - Not null',
  123.                                &TOT_NOT_NULLS);
  124.  
  125. Rem
  126. Rem     Following are statistics for the distribution of the keys,
  127. Rem     without null values, since null values can perturb the
  128. Rem     statistics, and indexes don't use nulls.
  129. Rem
  130. Rem     Of particular interest here is the average number of 
  131. Rem     rows per key, since this is a general measure of the
  132. Rem     selectivity of the column.
  133. Rem
  134.  
  135. column average  new_value average
  136. column minimum  new_value minimum
  137. column maximum  new_value maximum 
  138. column std_dev  new_value std_dev
  139. SELECT nvl(avg(COUNT(*)),0)      AVERAGE,
  140.        nvl(min(COUNT(*)),0)      MINIMUM,
  141.        nvl(max(COUNT(*)),0)      MAXIMUM ,
  142.        nvl(stddev(count(*)),0)   STD_DEV
  143.   FROM &one_table_name
  144.   WHERE &one_column_name IS NOT NULL
  145.   GROUP BY &one_column_name;
  146. INSERT INTO INDEX$INDEX_STATS VALUES('&one_table_name','&one_column_name',
  147.                                      'Rows per key - avg',&average);
  148. INSERT INTO INDEX$INDEX_STATS VALUES('&one_table_name','&one_column_name',
  149.                                      'Rows per key - min',&minimum);
  150. INSERT INTO INDEX$INDEX_STATS VALUES('&one_table_name','&one_column_name',
  151.                                      'Rows per key - max',&maximum);
  152. INSERT INTO INDEX$INDEX_STATS VALUES('&one_table_name','&one_column_name',
  153.                                      'Rows per key - dev',&std_dev);
  154.  
  155. Rem
  156. Rem      The following table is a histogram of key selectivity in the
  157. Rem      column.  This can be used to determine what the overall 
  158. Rem      'badness' (a technical term) of the column is. NOTE: This
  159. Rem      key selectivity is measured WITHOUT NULLS, since nulls don't
  160. Rem      use indexes.
  161. Rem 
  162. Rem      The 'badness_factor' column tells how many times a key was 
  163. Rem      repeated.
  164. Rem      The 'keys_with_badness' column tells how many keys were
  165. Rem      repeated 'badness_factor' times.
  166. Rem 
  167. Rem      Higher badness factors are detrimental to the selectivity
  168. Rem      of the column.
  169. Rem
  170. Rem      This table takes on different meanings depending upon 
  171. Rem      which access method is assumed:
  172. Rem      1. Access distributed equally across rows
  173. Rem         In this case, the user should look at the 'ROW_PERC' 
  174. Rem         column to determine what percentage of the queries will
  175. Rem         have high badness factors. 
  176. Rem      2. Access distributed equally across key values
  177. Rem         In this case, the user should look at the 'KEY_PERC' 
  178. Rem         column to determine what percentage of the queries will
  179. Rem         have high badness factors.
  180. Rem  
  181.  
  182. drop view index$badness;
  183. create view index$badness as
  184.    (select count(&one_column_name) repeat_count  
  185.    from &one_table_name 
  186.    group by &one_column_name);
  187.  
  188. insert into index$badness_stats (table_name,column_name,
  189.     badness_factor, keys_with_badness, row_percent, 
  190.     row_blk_fail, row_blk_succ, key_percent)
  191.   select '&one_table_name' table_name,'&one_column_name' column_name,
  192.     repeat_count badness_factor,
  193.     count(repeat_count) keys_with_badness,
  194.     (count(repeat_count)*repeat_count/&tot_not_nulls)*100 row_percent,
  195.     (count(repeat_count)*repeat_count*repeat_count) row_blk_fail,
  196.     (count(repeat_count)*repeat_count*ceil(repeat_count/2)) row_blk_succ,
  197.     (count(repeat_count)/&tot_distinct_keys)*100 key_percent
  198.   from index$badness
  199.   where repeat_count <> 0
  200.   group by repeat_count;
  201.  
  202. Rem
  203. Rem     The following two statistics attempt to determine
  204. Rem     how expensive it is to access keys in this table.
  205. Rem     A 'miss' will have to scan all the rows for a key value,
  206. Rem     and a 1-row hit will, on the average, only have to scan
  207. Rem     half of them.
  208. Rem   
  209. Rem     These gets are the the gets to read data from the table.
  210. Rem     Gets needed to access branch and leaf nodes of the index
  211. Rem     are not counted here.
  212. Rem
  213.  
  214. column gets_per_miss_by_row new_value miss_gets
  215. column gets_per_1_row_hit_by_row new_value hit_gets
  216. select nvl((sum(row_blk_fail)/&tot_not_nulls),0) gets_per_miss_by_row,
  217.        nvl((sum(row_blk_succ)/&tot_not_nulls),0) gets_per_1_row_hit_by_row
  218.   from index$badness_stats
  219.   where table_name like '&one_table_name' AND
  220.         column_name like '&one_column_name';  
  221. INSERT INTO INDEX$INDEX_STATS VALUES('&one_table_name','&one_column_name',
  222.                                      'db_gets_per_key_miss',&miss_gets);
  223. INSERT INTO INDEX$INDEX_STATS VALUES('&one_table_name','&one_column_name',
  224.                                      'db_gets_per_key_hit',&hit_gets);
  225.  
  226. drop view index$badness;
  227. SET TERMOUT ON
  228.  
  229. Rem
  230. Rem Clean up
  231. Rem 
  232. undefine 1
  233. undefine 2
  234. undefine one_table_name
  235. undefine one_column_name
  236.