3 Replies Latest reply: Apr 5, 2012 8:13 AM by Mark Kelly-Oracle RSS

    Dynamic K mean clustring

    BilalKhan
      Hello Every one.
      Currently i m working in k mean clustering in oracle 10g, i create the static clustered, follow the following procedure.

      1) Test cases values are dumpted as table : n0
      2) Next n1.sql selects unique random values, generates average and dumps as table : n1
      3) Then n2.sql Takes each groups and regroups based on average as table : n2
      4) the above steps is repeated (n[3-9].sql) till the values remian in the same group (tables careated as n3, n4, n5, n6, n7, n8,n9).

      For example we have 24 value in a table
      C1
       
      25
      28
      32
      37
      38
      20
      34
      39
      42
      49
      30
      55
      45
      47
      52
      20
      18
      46
      21
      40
      39
      51
      37
      45
       
      Then i create cluster in the flowing manner
      create n1.sql
      -------------
       
      drop table n1;
      create table n1 as
      select * from 
      (with a1 as
           (select c1 from n0 order by c1
           ),
           r1 as
           (select c1 from (select c1 from a1 order by dbms_random.value) where rownum=1
           ),
           g1 as
           (select r1.c1 rn, a1.c1, 'Grp1' grp1, 1 step1
            from a1, r1 where a1.c1 between r1.c1-5 and r1.c1+5
           ),
           r2 as
           (select c1 from (select c1 from
                                   (select c1 from a1
                                      where c1 not in (select c1 from g1))
                           order by dbms_random.value) where rownum=1
           ),
           g2 as
           (select r2.c1 rn, a11.c1, 'Grp2' grp1, 1 step1
            from (select c1 from a1
                  where c1 not in (select c1 from g1)) a11, r2
            where a11.c1 between r2.c1-5 and r2.c1+5
           ),
           r3 as
           (select c1 from (select c1 from
                                      (select c1 from a1
                                    where c1 not in
                                   (select c1 from g1
                                     union all
                                     select c1 from g2)
                                ) order by dbms_random.value) where rownum=1
           ),
           g3 as
           (select r3.c1 rn, a11.c1, 'Grp3' grp1, 1 step1
            from (select c1 from a1
                      where c1 not in
                              (select c1 from g1
                               union all
                               select c1 from g2)
                    ) a11, r3
            where a11.c1 between r3.c1-5 and r3.c1+5
           ),
           r4 as
           (select c1 from (select c1 from
                                   (select c1 from a1
                                    where c1 not in
                                     (select c1 from g1
                                       union all
                                       select c1 from g2
                                       union all
                                       select c1 from g3)
                          ) order by dbms_random.value) where rownum=1
           ),
           g4 as
           (select r4.c1 rn, a11.c1, 'Grp4' grp1, 1 step1
            from (select c1 from a1
                      where c1 not in
                         (select c1 from g1
                              union all
                              select c1 from g2
                              union all
                              select c1 from g3)
                ) a11, r4
            where a11.c1 between r4.c1-5 and r4.c1+5
           ),
           r5 as
           (select c1 from (select c1 from
                                   (select c1 from a1
                                    where c1 not in
                                     (select c1 from g1
                                       union all
                                       select c1 from g2
                                       union all
                                       select c1 from g3
                                       union all
                                       select c1 from g4)
                           ) order by dbms_random.value) where rownum=1
           ),
           g5 as
           (select r5.c1 rn, a11.c1, 'Grp5' grp1, 1 step1 
            from (select c1 from a1
                      where c1 not in
                              (select c1 from g1
                               union all
                               select c1 from g2
                            union all
                           select c1 from g3
                               union all
                               select c1 from g4)
                    ) a11, r5
            where a11.c1 between r5.c1-5 and r5.c1+5
           ),
           r6 as
           (select c1 from (select c1 from
                                   (select c1 from a1
                                    where c1 not in
                                     (select c1 from g1
                                       union all
                                       select c1 from g2
                                       union all
                                       select c1 from g3
                                       union all
                                       select c1 from g4
                                       union all
                                       select c1 from g5)
                           ) order by dbms_random.value) where rownum=1
           ),
           g6 as
           (select r6.c1 rn, a11.c1, 'Grp6' grp1, 1 step1
            from (select c1 from a1
                      where c1 not in
                            (select c1 from g1
                               union all
                              select c1 from g2
                           union all
                            select c1 from g3
                            union all
                            select c1 from g4
                               union all
                              select c1 from g5)
                    ) a11, r6
            where a11.c1 between r6.c1-5 and r6.c1+5
           ),
           bal as
           (select 0 rn, c1, 'Rest' grp1, 1 step1
            from a1
            where (0,c1) not in
                      (select 0, c1 from g1
                         union all
                         select 0, c1 from g2
                         union all
                         select 0, c1 from g3
                         union all
                         select 0, c1 from g4
                         union all
                         select 0, c1 from g5
                         union all
                         select 0, c1 from g6)
           )
      select a.*, round(avg(c1) over (partition by rn),3) average1 from g1 a
      union all
      select a.*, round(avg(c1) over (partition by rn),3) average1 from g2 a
      union all
      select a.*, round(avg(c1) over (partition by rn),3) average1 from g3 a
      union all
      select a.*, round(avg(c1) over (partition by rn),3) average1 from g4 a
      union all
      select a.*, round(avg(c1) over (partition by rn),3) average1 from g5 a
      union all
      select a.*, round(avg(c1) over (partition by rn),3) average1 from g6 a
      union all
      select a.*, round(avg(c1) over (partition by rn),3) average1 from bal a
      order by 3,2
      );
      =============
      Create n2.sql
      -------------
      drop table n2;
      create table n2 as
      select * from 
      (with a1 as
           (select * from n1
           ),
           g1 as
           (select distinct average1 avrg from a1 where grp1='Grp1' and step1=1
           ),
           g12 as
           (select c1, 'Grp1' grp2, 2 step2
            from a1, g1
            where c1 between g1.avrg-5 and g1.avrg+5
           ),
           g2 as
           (select distinct average1 avrg from a1 where grp1='Grp2' and step1=1
           ),
           g22 as
           (select c1, 'Grp2' grp2, 2 step2
            from a1, g2
            where c1 between g2.avrg-5 and g2.avrg+5
           ),
           g3 as
           (select distinct average1 avrg from a1 where grp1='Grp3' and step1=1
           ),
           g32 as
           (select c1, 'Grp3' grp2, 2 step2
            from a1, g3
            where c1 between g3.avrg-5 and g3.avrg+5
           ),
           g4 as
           (select distinct average1 avrg from a1 where grp1='Grp4' and step1=1
           ),
           g42 as
           (select c1, 'Grp4' grp2, 2 step2
            from a1, g4
            where c1 between g4.avrg-5 and g4.avrg+5
           ),
           g5 as
           (select distinct average1 avrg from a1 where grp1='Grp5' and step1=1
           ),
           g52 as
           (select c1, 'Grp5' grp2, 2 step2
            from a1, g5
            where c1 between g5.avrg-5 and g5.avrg+5
           ),
           g6 as
           (select distinct average1 avrg from a1 where grp1='Grp6' and step1=1
           ),
           g62 as
           (select c1, 'Grp6' grp2, 2 step2
            from a1, g6
            where c1 between g6.avrg-5 and g6.avrg+5
           )
      select a.*, round(avg(c1) over (order by grp2),3) average2 from g12 a
      union
      select a.*, round(avg(c1) over (order by grp2),3) average2 from g22 a
      union
      select a.*, round(avg(c1) over (order by grp2),3) average2 from g32 a
      union
      select a.*, round(avg(c1) over (order by grp2),3) average2 from g42 a
      union
      select a.*, round(avg(c1) over (order by grp2),3) average2 from g52 a
      union
      select a.*, round(avg(c1) over (order by grp2),3) average2 from g62 a
      order by 2, 1
      );
      =============
      Create n3.sql
      -------------
      drop table n3;
      create table n3 as
      select * from 
      (with a1 as
           (select * from n2
           ),
           g1 as
           (select distinct average2 avrg from a1 where grp2='Grp1' and step2=2
           ),
           g13 as
           (select c1, 'Grp1' grp3, 3 step3
            from a1, g1
            where c1 between g1.avrg-5 and g1.avrg+5
           ),
           g2 as
           (select distinct average2 avrg from a1 where grp2='Grp2' and step2=2
           ),
           g23 as
           (select c1, 'Grp2' grp3, 3 step3
            from a1, g2
            where c1 between g2.avrg-5 and g2.avrg+5
           ),
           g3 as
           (select distinct average2 avrg from a1 where grp2='Grp3' and step2=2
           ),
           g33 as
           (select c1, 'Grp3' grp3, 3 step3
            from a1, g3
            where c1 between g3.avrg-5 and g3.avrg+5
           ),
           g4 as
           (select distinct average2 avrg from a1 where grp2='Grp4' and step2=2
           ),
           g43 as
           (select c1, 'Grp4' grp3, 3 step3
            from a1, g4
            where c1 between g4.avrg-5 and g4.avrg+5
           ),
           g5 as
           (select distinct average2 avrg from a1 where grp2='Grp5' and step2=2
           ),
           g53 as
           (select c1, 'Grp5' grp3, 3 step3
            from a1, g5
            where c1 between g5.avrg-5 and g5.avrg+5
           ),
           g6 as
           (select distinct average2 avrg from a1 where grp2='Grp6' and step2=2
           ),
           g63 as
           (select c1, 'Grp6' grp3, 3 step3
            from a1, g6
            where c1 between g6.avrg-5 and g6.avrg+5
           )
      select a.*, round(avg(c1) over (order by grp3),3) average3 from g13 a
      union
      select a.*, round(avg(c1) over (order by grp3),3) average3 from g23 a
      union
      select a.*, round(avg(c1) over (order by grp3),3) average3 from g33 a
      union
      select a.*, round(avg(c1) over (order by grp3),3) average3 from g43 a
      union
      select a.*, round(avg(c1) over (order by grp3),3) average3 from g53 a
      union
      select a.*, round(avg(c1) over (order by grp3),3) average3 from g63 a
      order by 2,1
      );
      ==============
      Create n4.sql
      -------------
      drop table n4;
      create table n4 as
      select * from
      (with a1 as
           (select * from n3
           ),
           g1 as
           (select distinct average3 avrg from a1 where grp3='Grp1' and step3=3
           ),
           g14 as
           (select c1, 'Grp1' grp4, 4 step4
            from a1, g1
            where c1 between g1.avrg-5 and g1.avrg+5
           ),
           g2 as
           (select distinct average3 avrg from a1 where grp3='Grp2' and step3=3
           ),
           g24 as
           (select c1, 'Grp2' grp4, 4 step4
            from a1, g2
            where c1 between g2.avrg-5 and g2.avrg+5
           ),
           g3 as
           (select distinct average3 avrg from a1 where grp3='Grp3' and step3=3
           ),
           g34 as
           (select c1, 'Grp3' grp4, 4 step4
            from a1, g3
            where c1 between g3.avrg-5 and g3.avrg+5
           ),
           g4 as
           (select distinct average3 avrg from a1 where grp3='Grp4' and step3=3
           ),
           g44 as
           (select c1, 'Grp4' grp4, 4 step4
            from a1, g4
            where c1 between g4.avrg-5 and g4.avrg+5
           ),
           g5 as
           (select distinct average3 avrg from a1 where grp3='Grp5' and step3=3
           ),
           g54 as
           (select c1, 'Grp5' grp4, 4 step4
            from a1, g5
            where c1 between g5.avrg-5 and g5.avrg+5
           ),
           g6 as
           (select distinct average3 avrg from a1 where grp3='Grp6' and step3=3
           ),
           g64 as
           (select c1, 'Grp6' grp4, 4 step4
            from a1, g6
            where c1 between g6.avrg-5 and g6.avrg+5
           )
      select a.*, round(avg(c1) over (order by grp4),3) average4 from g14 a
      union
      select a.*, round(avg(c1) over (order by grp4),3) average4 from g24 a
      union
      select a.*, round(avg(c1) over (order by grp4),3) average4 from g34 a
      union
      select a.*, round(avg(c1) over (order by grp4),3) average4 from g44 a
      union
      select a.*, round(avg(c1) over (order by grp4),3) average4 from g54 a
      union
      select a.*, round(avg(c1) over (order by grp4),3) average4 from g64 a
      order by 2,1
      );
      ==============
      Create n5.sql
      -------------
      drop table n5;
      create table n5 as
      select * from
      (with a1 as
           (select * from n4
           ),
           g1 as
           (select distinct average4 avrg from a1 where grp4='Grp1' and step4=4
           ),
           g15 as
           (select c1, 'Grp1' grp5, 5 step5
            from a1, g1
            where c1 between g1.avrg-5 and g1.avrg+5
           ),
           g2 as
           (select distinct average4 avrg from a1 where grp4='Grp2' and step4=4
           ),
           g25 as
           (select c1, 'Grp2' grp5, 5 step5
            from a1, g2
            where c1 between g2.avrg-5 and g2.avrg+5
           ),
           g3 as
           (select distinct average4 avrg from a1 where grp4='Grp3' and step4=4
           ),
           g35 as
           (select c1, 'Grp3' grp5, 5 step5
            from a1, g3
            where c1 between g3.avrg-5 and g3.avrg+5
           ),
           g4 as
           (select distinct average4 avrg from a1 where grp4='Grp4' and step4=4
           ),
           g45 as
           (select c1, 'Grp4' grp5, 5 step5
            from a1, g4
            where c1 between g4.avrg-5 and g4.avrg+5
           ),
           g5 as
           (select distinct average4 avrg from a1 where grp4='Grp5' and step4=4
           ),
           g55 as
           (select c1, 'Grp5' grp5, 5 step5
            from a1, g5
            where c1 between g5.avrg-5 and g5.avrg+5
           ),
           g6 as
           (select distinct average4 avrg from a1 where grp4='Grp6' and step4=4
           ),
           g65 as
           (select c1, 'Grp6' grp5, 5 step5
            from a1, g6
            where c1 between g6.avrg-5 and g6.avrg+5
           )
      select a.*, round(avg(c1) over (order by grp5),3) average5 from g15 a
      union
      select a.*, round(avg(c1) over (order by grp5),3) average5 from g25 a
      union
      select a.*, round(avg(c1) over (order by grp5),3) average5 from g35 a
      union
      select a.*, round(avg(c1) over (order by grp5),3) average5 from g45 a
      union
      select a.*, round(avg(c1) over (order by grp5),3) average5 from g55 a
      union
      select a.*, round(avg(c1) over (order by grp5),3) average5 from g65 a
      order by 2,1
      );
      ===============
      Create n6.sql
      -------------
      drop table n6;
      create table n6 as
      select * from
      (with a1 as
           (select * from n5
           ),
           g1 as
           (select distinct average5 avrg from a1 where grp5='Grp1' and step5=5
           ),
           g16 as
           (select c1, 'Grp1' grp6, 6 step6
            from a1, g1
            where c1 between g1.avrg-5 and g1.avrg+5
           ),
           g2 as
           (select distinct average5 avrg from a1 where grp5='Grp2' and step5=5
           ),
           g26 as
           (select c1, 'Grp2' grp6, 6 step6
            from a1, g2
            where c1 between g2.avrg-5 and g2.avrg+5
           ),
           g3 as
           (select distinct average5 avrg from a1 where grp5='Grp3' and step5=5
           ),
           g36 as
           (select c1, 'Grp3' grp6, 6 step6
            from a1, g3
            where c1 between g3.avrg-5 and g3.avrg+5
           ),
           g4 as
           (select distinct average5 avrg from a1 where grp5='Grp4' and step5=5
           ),
           g46 as
           (select c1, 'Grp4' grp6, 6 step6
            from a1, g4
            where c1 between g4.avrg-5 and g4.avrg+5
           ),
           g5 as
           (select distinct average5 avrg from a1 where grp5='Grp5' and step5=5
           ),
           g56 as
           (select c1, 'Grp5' grp6, 6 step6
            from a1, g5
            where c1 between g5.avrg-5 and g5.avrg+5
           ),
           g6 as
           (select distinct average5 avrg from a1 where grp5='Grp6' and step5=5
           ),
           g66 as
           (select c1, 'Grp6' grp6, 6 step6
            from a1, g6
            where c1 between g6.avrg-5 and g6.avrg+5
           )
      select a.*, round(avg(c1) over (order by grp6),3) average6 from g16 a
      union
      select a.*, round(avg(c1) over (order by grp6),3) average6 from g26 a
      union
      select a.*, round(avg(c1) over (order by grp6),3) average6 from g36 a
      union
      select a.*, round(avg(c1) over (order by grp6),3) average6 from g46 a
      union
      select a.*, round(avg(c1) over (order by grp6),3) average6 from g56 a
      union
      select a.*, round(avg(c1) over (order by grp6),3) average6 from g66 a
      order by 2,1
      );
      =============
      Create n7.sql
      -------------
      drop table n7;
      create table n7 as
      select * from
      (with a1 as
           (select * from n6
           ),
           g1 as
           (select distinct average6 avrg from a1 where grp6='Grp1' and step6=6
           ),
           g17 as
           (select c1, 'Grp1' grp7, 7 step7
            from a1, g1
            where c1 between g1.avrg-5 and g1.avrg+5
           ),
           g2 as
           (select distinct average6 avrg from a1 where grp6='Grp2' and step6=6
           ),
           g27 as
           (select c1, 'Grp2' grp7, 7 step7
            from a1, g2
            where c1 between g2.avrg-5 and g2.avrg+5
           ),
           g3 as
           (select distinct average6 avrg from a1 where grp6='Grp3' and step6=6
           ),
           g37 as
           (select c1, 'Grp3' grp7, 7 step7
            from a1, g3
            where c1 between g3.avrg-5 and g3.avrg+5
           ),
           g4 as
           (select distinct average6 avrg from a1 where grp6='Grp4' and step6=6
           ),
           g47 as
           (select c1, 'Grp4' grp7, 7 step7
            from a1, g4
            where c1 between g4.avrg-5 and g4.avrg+5
           ),
           g5 as
           (select distinct average6 avrg from a1 where grp6='Grp5' and step6=6
           ),
           g57 as
           (select c1, 'Grp5' grp7, 7 step7
            from a1, g5
            where c1 between g5.avrg-5 and g5.avrg+5
           ),
           g6 as
           (select distinct average6 avrg from a1 where grp6='Grp6' and step6=6
           ),
           g67 as
           (select c1, 'Grp6' grp7, 7 step7
            from a1, g6
            where c1 between g6.avrg-5 and g6.avrg+5
           )
      select a.*, round(avg(c1) over (order by grp7),3) average7 from g17 a
      union
      select a.*, round(avg(c1) over (order by grp7),3) average7 from g27 a
      union
      select a.*, round(avg(c1) over (order by grp7),3) average7 from g37 a
      union
      select a.*, round(avg(c1) over (order by grp7),3) average7 from g47 a
      union
      select a.*, round(avg(c1) over (order by grp7),3) average7 from g57 a
      union
      select a.*, round(avg(c1) over (order by grp7),3) average7 from g67 a
      order by 2,1
      );
      ==============
      Create n8.sql
      --------------
      drop table n8;
      create table n8 as
      select * from
      (with a1 as
           (select * from n7
           ),
           g1 as
           (select distinct average7 avrg from a1 where grp7='Grp1' and step7=7
           ),
           g18 as
           (select c1, 'Grp1' grp8, 8 step8
            from a1, g1
            where c1 between g1.avrg-5 and g1.avrg+5
           ),
           g2 as
           (select distinct average7 avrg from a1 where grp7='Grp2' and step7=7
           ),
           g28 as
           (select c1, 'Grp2' grp8, 8 step8
            from a1, g2
            where c1 between g2.avrg-5 and g2.avrg+5
           ),
           g3 as
           (select distinct average7 avrg from a1 where grp7='Grp3' and step7=7
           ),
           g38 as
           (select c1, 'Grp3' grp8, 8 step8
            from a1, g3
            where c1 between g3.avrg-5 and g3.avrg+5
           ),
           g4 as
           (select distinct average7 avrg from a1 where grp7='Grp4' and step7=7
           ),
           g48 as
           (select c1, 'Grp4' grp8, 8 step8
            from a1, g4
            where c1 between g4.avrg-5 and g4.avrg+5
           ),
           g5 as
           (select distinct average7 avrg from a1 where grp7='Grp5' and step7=7
           ),
           g58 as
           (select c1, 'Grp5' grp8, 8 step8
            from a1, g5
            where c1 between g5.avrg-5 and g5.avrg+5
           ),
           g6 as
           (select distinct average7 avrg from a1 where grp7='Grp6' and step7=7
           ),
           g68 as
           (select c1, 'Grp6' grp8, 8 step8
            from a1, g6
            where c1 between g6.avrg-5 and g6.avrg+5
           )
      select a.*, round(avg(c1) over (order by grp8),3) average8 from g18 a
      union
      select a.*, round(avg(c1) over (order by grp8),3) average8 from g28 a
      union
      select a.*, round(avg(c1) over (order by grp8),3) average8 from g38 a
      union
      select a.*, round(avg(c1) over (order by grp8),3) average8 from g48 a
      union
      select a.*, round(avg(c1) over (order by grp8),3) average8 from g58 a
      union
      select a.*, round(avg(c1) over (order by grp8),3) average8 from g68 a
      order by 2, 1
      );
      ==============
      Create n9.sql
      --------------
      drop table n9;
      create table n9 as
      select * from
      (with a1 as
           (select * from n8
           ),
           g1 as
           (select distinct average8 avrg from a1 where grp8='Grp1' and step8=8
           ),
           g19 as
           (select c1, 'Grp1' grp9, 9 step9
            from a1, g1
            where c1 between g1.avrg-5 and g1.avrg+5
           ),
           g2 as
           (select distinct average8 avrg from a1 where grp8='Grp2' and step8=8
           ),
           g29 as
           (select c1, 'Grp2' grp9, 9 step9
            from a1, g2
            where c1 between g2.avrg-5 and g2.avrg+5
           ),
           g3 as
           (select distinct average8 avrg from a1 where grp8='Grp3' and step8=8
           ),
           g39 as
           (select c1, 'Grp3' grp9, 9 step9
            from a1, g3
            where c1 between g3.avrg-5 and g3.avrg+5
           ),
           g4 as
           (select distinct average8 avrg from a1 where grp8='Grp4' and step8=8
           ),
           g49 as
           (select c1, 'Grp4' grp9, 9 step9
            from a1, g4
            where c1 between g4.avrg-5 and g4.avrg+5
           ),
           g5 as
           (select distinct average8 avrg from a1 where grp8='Grp5' and step8=8
           ),
           g59 as
           (select c1, 'Grp5' grp9, 9 step9
            from a1, g5
            where c1 between g5.avrg-5 and g5.avrg+5
           ),
           g6 as
           (select distinct average8 avrg from a1 where grp8='Grp6' and step8=8
           ),
           g69 as
           (select c1, 'Grp6' grp9, 9 step9
            from a1, g6
            where c1 between g6.avrg-5 and g6.avrg+5
           )
      select a.*, round(avg(c1) over (order by grp9),3) average9 from g19 a
      union
      select a.*, round(avg(c1) over (order by grp9),3) average9 from g29 a
      union
      select a.*, round(avg(c1) over (order by grp9),3) average9 from g39 a
      union
      select a.*, round(avg(c1) over (order by grp9),3) average9 from g49 a
      union
      select a.*, round(avg(c1) over (order by grp9),3) average9 from g59 a
      union
      select a.*, round(avg(c1) over (order by grp9),3) average9 from g69 a
      order by 2, 1
      );
      ================
       
      After Creating all sqls, call all sqls from n.sql
       
      Create n.sql
      ------------
       
      @n1
      @n2
      @n3
      @n4
      @n5
      @n6
      @n7
      @n8
      @n9
       
      ==============
       
      Create table n0 with one column C1 and insert test values as column c1;
       
      Now run from sql prompt n.sql and tables n1-n9 are created. 
       
      And you find that last tables values remain in same groups (when I tested it was same
      in 7,8,9 tables)
       
      Now i want to implement the dynamic k mean crusting algorithm, so plz help me in this regards.

      Thanks.

      Regards
        • 1. Re: Dynamic K mean clustring
          BilalKhan
          Please help me in this regards.

          Thanks
          • 2. Re: Dynamic K mean clustring
            BilalKhan
            Hello every body,
            please suggest me to create function for the dynamic k mean clustring.
            • 3. Re: Dynamic K mean clustring
              Mark Kelly-Oracle
              Hi,
              If you are asking for advice on how to put together your own KMeans implementation, I don't think this is the right forum. If you want to understand how to use the Oracle KMeans implementation, then you are at the right place.
              Here is a sample script that runs on 11.2:

              set serveroutput on
              -- Create the settings table
              CREATE TABLE km_mk_model_settings (
              setting_name VARCHAR2(30),
              setting_value VARCHAR2(30));


              BEGIN
              INSERT INTO km_mk_model_settings (setting_name, setting_value) VALUES
              (dbms_data_mining.algo_name, dbms_data_mining.ALGO_KMEANS);
              INSERT INTO km_mk_model_settings (setting_name, setting_value) VALUES
              (dbms_data_mining.prep_auto,dbms_data_mining.prep_auto_on);
              COMMIT;
              END;
              /
              -- Create the model using the specified settings
              BEGIN
              DBMS_DATA_MINING.CREATE_MODEL(
              model_name => 'km_mk_model',
              mining_function => dbms_data_mining.clustering,
              data_table_name => 'mining_data_build_v',
              case_id_column_name => 'cust_id',
              settings_table_name => 'km_mk_model_settings');
              END;
              /


              Also, you can get the sample code, although it does not highlight the use of dbms_data_mining.prep_auto_on option shown above (delivered in 11.1).
              http://www.oracle.com/technetwork/database/options/advanced-analytics/odm/odm-samples-194497.html

              Look here for more documentation:
              http://www.oracle.com/technetwork/database/options/advanced-analytics/odm/odm-documentation-170506.html

              Thanks, Mark