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