Color Lipstick For Blonde Fair Skin

In the past I have addressed the problem of related columns and sampling dynamic as an alternative.

In 11g, using a new method of collecting statistics, you can specify the column groups which to measure the correlation, in some ways an extension of the concept of uneven distribution of data (skewness) .

To illustrate more general than that In the past, where the correlation is less obvious. In the example of dynamic sampling on the post I had used two columns with equal values \u200b\u200bfor each row, but now use a more real case: imagine that we have three sets of values, the values \u200b\u200bof the first around 1000, the second around 2000, third round 3000:

 SQL> desc CORREL 
 Name Null? Type ------------------- -------- -------------- 
 
 TAG NOT NULL VARCHAR2 (16 ) 
 VAL NOT NULL NUMBER (38) 
 
 SQL> insert into CORREL select 'TYPE1', 1000 + TRUNC (DBMS_RANDOM.VALUE (0,100)) from all_objects; 
 
 61122 rows created. 
 
 SQL>  select * from correl where rownum  < 10;
  
 TAG                     VAL 
 ---------------- ---------- 
 TIPO1                  1035 
 TIPO1                  1012 
 TIPO1                  1003 
 TIPO1                  1090 
 TIPO1                  1070 
 TIPO1                  1061 
 TIPO1                  1004 
 TIPO1                  1020 
 TIPO1                  1032 
  
 9 rows selected. 
  
 SQL> insert into correl select 'TIPO2', 2000+TRUNC(DBMS_RANDOM.VALUE(0,100)) from all_objects; 
  
 61122 rows created. 
  
 SQL> insert into correl select 'TIPO3', 3000+TRUNC(DBMS_RANDOM.VALUE(0,100)) from all_objects; 
  
 61122 rows created. 
 
 SQL> commit; 
 
 Commit complete. 
 

At this point we take the standard statistics and see that the optimizer, for any value (not applicable) in the range max-min of the column, predicts that there are 204 lines:

 SQL> exec dbms_stats . gather_table_stats (user, 'CORREL', estimate_percent => 100); 
 
 PL / SQL procedure successfully completed. 
 
 SQL> explain plan for select * from CORREL WHERE tag = 'TYPE2' and val = 1200; 
 
 Explained. 
 
 SQL> select * from table (dbms_xplan.display) 
 
 PLAN_TABLE_OUTPUT 
 -------------------------------- -------------------------------------------------- --------------------------------------------------  
 Plan hash value: 469411154 
  
 ---------------------------------------------------------------------------- 
   AND "TAG"='TIPO2') 
  
 13 rows selected. 
  
  
 Ma ora prendiamo le statistiche utilizzando i gruppi di colonne: 
  
 SQL> exec dbms_stats.gather_table_stats(user, 'CORREL', method_opt => 'FOR COLUMNS (TAG,VAL) SIZE SKEWONLY', estimate_percent => 100); 
  
 PL/SQL procedure successfully completed. 
  
 SQL> explain plan for select * from correl where tag = 'TIPO2' and val = 1201; 
  

Explained.

SQL> select * from table(dbms_xplan.display);

  
 PLAN_TABLE_OUTPUT 
 ------------------------------------------------------------------------------------------------------------------------------------  
 Plan hash value: 469411154 
  
 ---------------------------------------------------------------------------- 
  AND "TAG" = 'TYPE2') 
 
 13 rows selected. 
 
 
 is clear that things are much better: the estimate is dell'optimizer of 611 lines, although there are no rows with the value val is 1201. 
 
 But the big advantage is that now the estimate of about 600 lines are valid for ranges of numbers: 
 
 SQL> explain plan for select * from CORREL WHERE tag = 'TYPE2' and val = 2023; 
 
 Explained. 
 
 SQL> select * from table (dbms_xplan.display) 
 
 PLAN_TABLE_OUTPUT 
 -------------------------------- -------------------------------------------------- -------------------------------------------------- 
 Plan hash value:  469411154 
  
 ---------------------------------------------------------------------------- 
  
 

13 rows selected.

SQL> select count (*) from CORREL WHERE tag = 'TYPE2' and val = 2023;

COUNT (*) ----------

 
 
 
 645 1 row selected. 
 
 
 probably with some action on the histogram of the group of columns is also possible to correct the false estimate due to the very uneven distribution of numbers in column val.