no, don't agree.  First, you cannot direct path load clusters (that append is meaningless).  So, this is the same as "insert into t select"

the 512k extents -- not really a big deal.  It is a locally managed tablespace, there are no data dictionary changes here -- it is bitmap updates.

I think it is at least partially due to the fact you haven't sized your cluster (using SIZE on the create cluster) and that the initial load of data isn't sorted by the cluster key.  

Given a value for C1, C2, C3, C4 what would you expect this query:


select sum(vsize(mrs.C1 ))+
      sum(vsize(mrs.C2))+
      sum(vsize(mrs.C3))+
      sum(vsize(mrs.C4))+
      sum(vsize(mrs.C5))+
      sum(vsize('A'))+
      sum(vsize(999.99))+
      sum(vsize(a.r))+
      sum(vsize(1))+
      sum(vsize(1))
from puser.my_driver mrs
   , (select rownum r from all_objects where rownum <= 96) 
where c1 = :c1 and c2 = :c2 and c3 = :c3 and c4 = :c4


Assuming that c1,c2,c3,c4 are unique in mrs -- we would get 96 rows for each cluster key and you said 57 bytes, so that is about 6k/key.  so, if I build a driver table:


ops$tkyte@ORA920> create table my_driver
  2  as
  3  select rownum c1, mod(rownum,9999) c2, created c3, mod(rownum,999) c4, rownum c5
  4    from big_table
  5   where rownum <= 120000
  6  /

Table created.


ops$tkyte@ORA920> CREATE CLUSTER ICT_C
  2    ("C1" NUMBER(10, 0)
  3     , "C2" NUMBER(4, 0)
  4     , "C3" DATE
  5     , "C4" NUMBER(3, 0)
  6    )<b>
  7   size 6192</b>
  8   TABLESPACE "TEST_PARTITION_001"
  9  /

Cluster created.


And it is also related to the "order" of the data.  A cluster differs big time from a HEAP table in that data has a physical location.  If you look at how Oracle itself uses a cluster in the data dictionary -- you'll find that all of the rows for a cluster key more or less go into the database "at the same time".  that is when clusters work best.  During a bulk load of a cluster -- you really want to try and get the data into the table in key order.  It is not that the keys must be sorted -- just that all or most of the rows for a given key go in at about the same time.  This results in the most efficient load as well as most tightly packed cluster possible (least amount of chaining).

I modified your query to help facilitate this.  first, I lost all_objects and just use the simple my_driver table twice.  Additionally -- I used an ordered hint in this very special case to tell Oracle "read the MY_DRIVER table and join the first row to the 96 rows, then read the second row from MY_DRIVER and join to the 96 rows and so on".  So the net result was that the cluster was loaded by key:


ops$tkyte@ORA920> delete from plan_table;

7 rows deleted.

ops$tkyte@ORA920> explain plan for
  2  insert into ict
  3  (c1,c2,c3,c4,c5,c6,c7,interval,c8,c9)
  4  select /*+ ORDERED */ mrs.C1 , mrs.C2 , mrs.C3 , mrs.C4 , mrs.C5 , 'A' C6 , 999.99 C7 , a.r interval , 1 C8 , 1 C9
  5  from my_driver mrs
  6     , (select rownum r from my_driver where rownum <= 96) a;

Explained.

ops$tkyte@ORA920> set echo off

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------

---------------------------------------------------------------------
| Id  | Operation             |  Name       | Rows  | Bytes | Cost  |
---------------------------------------------------------------------
|   0 | INSERT STATEMENT      |             |    11M|   461M|  6240K|
|   1 |  MERGE JOIN CARTESIAN |             |    11M|   461M|  6240K|
|   2 |   TABLE ACCESS FULL   | MY_DRIVER   |   120K|  3398K|    52 |
|   3 |   BUFFER SORT         |             |    96 |  1248 |  6240K|
|   4 |    VIEW               |             |    96 |  1248 |       |
|*  5 |     COUNT STOPKEY     |             |       |       |       |
|   6 |      TABLE ACCESS FULL| MY_DRIVER   |   120K|       |    52 |
---------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - filter(ROWNUM<=96)

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------

Note: cpu costing is off

19 rows selected.

ops$tkyte@ORA920> set timing on
ops$tkyte@ORA920> insert into ict
  2  (c1,c2,c3,c4,c5,c6,c7,interval,c8,c9)
  3  select /*+ ORDERED */ mrs.C1 , mrs.C2 , mrs.C3 , mrs.C4 , mrs.C5 , 'A' C6 , 999.99 C7 , a.r interval , 1 C8 , 1 C9
  4  from my_driver mrs
  5     , (select rownum r from my_driver where rownum <= 96) a;

11520000 rows created.

Elapsed: 00:14:06.33


ops$tkyte@ORA920> select count(*) from user_extents where segment_name = 'ICT_C';

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



Now that was 14 minutes on a stock, $800 compaq desktop pc with an icky IDE single disk drive and 512meg of RAM running Linux.  I would assume your server would probably best that.


clusters are used to provide high performance retrievals -- they have necessary overhead on the load in order to do that.  They are more efficient on the buffer cache since all of the rows for a key will be on as few blocks as possible.  Don't be discouraged by a slow load time -- as the payback at query retrieve time can far outweigh any negative effects there.  Pay me once up front and reap the benefits on every query -- or pay me over and over with each query.




 

I used ORDERED in this case to have the database read the BIG table with the cluster key and join each row in that table with the 96 rows in the other table.  It gave me all 96 rows for a cluster key all at once.  It did not sort the data, just processed the tables in the from clause in the ORDER i wanted.  If you 

set pagsize 100
set pause on
select ....  (the query with the ordered hint)

you'll see 96 rows with the same cluster key, then 96 rows with the next key and so on.  

The sum was to get the size of the data associated with a cluster key value -- I had where c1 = .. c2 = ... and so on in there.  It would have summed up the vsizes of all the of the columns associated with a cluster key.

Yes, I forgot to subtract out the cluster key in the calculation -- you are correct.


If you load up a couple of representative rows and query:

select avg(vsize(c1)+vsize(c2)+vsize(c3)+vsize(4)+sz)
from 
( select c1, c2, c3, c4, sum( vsize(c5)+vsize(c6)+ ..... vsize(cN) ) sz
    from t
   group by c1, c2, c3, c4 )

eg:

AVG(VSIZE(C1)+VSIZE(C2)+VSIZE(C3)+VSIZE(C4)+SZ)
-----------------------------------------------
                                     1447.72682

ops$tkyte@ORA920> l
  1  select avg( vsize(c1)+vsize(c2)+vsize(c3)+vsize(c4) + sz )
  2    from (
  3  select c1, c2, c3, c4, sum(vsize(c5)+vsize(c6)+vsize(c7)+vsize(interval)+vsize(c8)+vsize(c9)) sz
  4    from (
  5  select *
  6    from (
  7  select /*+ ORDERED */ mrs.C1 , mrs.C2 , mrs.C3 , mrs.C4 , mrs.C5 , 'A' C6 , 999.99 C7 , a.r interval , 1 C8 , 1 C9
  8    from my_driver mrs
  9    , (select rownum r from my_driver where rownum <= 96) a
 10         )
 11         )
 12   group by c1, c2, c3, c4
 13*        )
ops$tkyte@ORA920> /

AVG(VSIZE(C1)+VSIZE(C2)+VSIZE(C3)+VSIZE(C4)+SZ)
-----------------------------------------------
                                     1447.72682


for the data I generated (YMMV)...

It is less of an issue if this is read mostly data that is bulk loaded -- or if the keys and related rows are all created more or less at the same time (eg: when you do a CREATE TABLE -- you load up a cluster with all of the data at once).  There, right sizing the cluster key size isn't as important since all of the values go in at once.  

If loading out of order -- it is much much more important.
 

no it shouldn't, :n is a bind variable.

it is most likely that dbms_random is not in your database.  did you check that out


ops$tkyte@ORA817DEV> create or replace procedure gen_data( p_tname in varchar2, p_records in number )
  2  authid current_user
  3  as
  4      l_insert long;
  5      l_rows   number default 0;
  6  begin
  7
  8          dbms_application_info.set_client_info( 'gen_data ' || p_tname );
  9      l_insert := 'insert /*+ append */ into ' || p_tname ||
 10                  ' select ';
 11
 12      for x in ( select data_type, data_length,
 13                  nvl(rpad( '9',data_precision,'9')/power(10,data_scale),9999999999) maxval
 14                   from user_tab_columns
 15                  where table_name = upper(p_tname)
 16                  order by column_id )
 17      loop
 18          if ( x.data_type in ('NUMBER', 'FLOAT' ))
 19          then
 20              l_insert := l_insert || 'dbms_random.value(1,' || x.maxval || '),';
 21          elsif ( x.data_type = 'DATE' )
 22          then
 23              l_insert := l_insert ||
 24                    'sysdate+dbms_random.value+dbms_random.value(1,1000),';
 25          else
 26              l_insert := l_insert || 'dbms_random.string(''A'',' ||
 27                                         x.data_length || '),';
 28          end if;
 29      end loop;
 30      l_insert := rtrim(l_insert,',') ||
 31                    ' from all_objects where rownum <= :n';
 32
 33          dbms_output.put_line( l_insert );
 34      loop
 35          execute immediate l_insert using p_records - l_rows;
 36          l_rows := l_rows + sql%rowcount;
 37                  commit;
 38                  dbms_application_info.set_module( l_rows || ' rows of ' || p_records, '' );
 39          exit when ( l_rows >= p_records );
 40      end loop;
 41  end;
 42  /

Procedure created.

ops$tkyte@ORA817DEV> drop table test;

Table dropped.

ops$tkyte@ORA817DEV> create table test( x varchar2(10) );

Table created.

ops$tkyte@ORA817DEV> exec gen_data('TEST', 2 )
insert /*+ append */ into TEST select dbms_random.string('A',10) from all_objects where rownum <= :n

PL/SQL procedure successfully completed.

ops$tkyte@ORA817DEV> select * from test;

X
----------
\JlLWWKogS
IUnoXAtHOR

ops$tkyte@ORA817DEV> 

ops$tkyte@ORA920> INSERT INTO locations (country, region, city, loc_id)
  2  select country, region, city,  loc_id
  3  from (
  4  SELECT country, region, city, rownum loc_id, rr, dbms_random.value(1,9) rand FROM
  5  (
  6  SELECT 'Country'||rownum country
  7  FROM big_table Where rownum < 21
  8  ),
  9  (
 10  SELECT 'Region'||rownum region
 11  FROM big_table Where rownum < 5
 12  ),
 13  (
 14  SELECT 'City'||rownum city, rownum RR
 15  FROM big_table Where rownum < 9
 16  )
 17  )
 18  where rr <= rand;
 
347 rows created.
 
ops$tkyte@ORA920>
ops$tkyte@ORA920> select min(cnt), max(cnt), avg(cnt)
  2    from (
  3  select country, region, count(*) cnt
  4    from locations
  5   group by country, region
  6         );
 
  MIN(CNT)   MAX(CNT)   AVG(CNT)
---------- ---------- ----------
         2          7     4.3375
 

ops$tkyte@ORA920> INSERT INTO locations (country, region, city, loc_id)
  2  select country, region, city,  loc_id
  3  from (
  4  SELECT country, region, city, rownum loc_id, rr, dbms_random.value(1,9) 
rand FROM
  5  (
  6  SELECT 'Country'||rownum country
  7  FROM big_table Where rownum < 21
  8  ),
  9  (
 10  SELECT 'Region'||rownum region
 11  FROM big_table Where rownum < 5
 12  ),
 13  (
 14  SELECT 'City'||rownum city, rownum RR
 15  FROM big_table Where rownum < 9
 16  )
 17  )
 18  where rr <= rand;

look at the query 

where rr <= rand

rr = rownum from "city"
rr <= threshold

we are getting the first N cities, not a random N, the FIRST n

you already have it! 

yes, i guess that is true -- sorry about that.

you can use row_number() in the outer query instead of rownum in the inner query to build the city name.


ops$tkyte@ORA920LAP> select country, region, 'City' || row_number() over (partition by country, region order by loc_id) city, loc_id
  2    from (
  3  select country, region, loc_id
  4    from (
  5    SELECT country, region, city, rownum loc_id, rr, dbms_random.value(1,9)
  6  rand FROM
  7     (
  8     SELECT 'Country'||rownum country
  9     FROM emp, emp, emp Where rownum < 21
 10     ),
 11     (
 12     SELECT 'Region'||rownum region
 13     FROM emp, emp, emp Where rownum < 5
 14     ),
 15     (
 16     SELECT 'City'||rownum city, rownum RR
 17     FROM emp, emp, emp Where rownum < 9
 18     )
 19     )
 20     where rr <= rand
 21     )
 22  order by 1, 2, 3
 23  /

COUNTRY    REGION     CITY           LOC_ID
---------- ---------- ---------- ----------
Country1   Region1    City1               1
Country1              City2              81
Country1              City3             161
Country1              City4             241
Country1              City5             321

Country1   Region2    City1              21
Country1              City2             101
Country1              City3             181
Country1              City4             261
Country1              City5             421
 

ops$tkyte@ORA9IR2> create table t ( x int );
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> declare
  2          type numArray is table of number index by binary_integer;
  3
  4          l_deptnos  numArray;
  5                  l_cnt      number;
  6  begin
  7          select deptno bulk collect into l_deptnos from scott.dept;
  8
  9                  l_cnt := l_deptnos.count;
 10          for i in 1 .. 5
 11          loop
 12                   insert into t values ( l_deptnos( dbms_random.value( 1, l_cnt ) ) );
 13          end loop;
 14  end;
 15  /
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> select * from t;
 
         X
----------
        20
        10
        30
        30
        10



do you have a counter example to work from? 

ahh -- we are comparing elephants to rocks.  And there is nothing to compare!!!


in dynamic sql, you have to BIND inputs.  the plsql variables are not in scope in the execute immediate, we must pass them to that "function" (think of execute immediate as a function -- no function would be able to 'see' your variables)

Also, native dynamic sql can only see SQL types -- never PLSQL types as binds.

And we cannot subscript a nested table in sql.


so, you would fill an array with the values and forall i insert that array.


ops$tkyte@ORA9IR2> declare
  2      type numArray is table of number;
  3      l_deptnos           numArray;
  4      l_deptnos_to_insert numArray := numArray();
  5      l_rows              number := 5;
  6  begin
  7      select deptno bulk collect into l_deptnos from scott.dept;
  8
  9      l_deptnos_to_insert.extend(l_rows);
 10      for i in 1 .. l_rows
 11      loop
 12          l_deptnos_to_insert(i) := l_deptnos( dbms_random.value(1,l_deptnos.count) );
 13      end loop;
 14
 15      forall i in 1 .. l_rows
 16          execute immediate
 17         'insert into t values ( :deptno )' using l_deptnos_to_insert(i);
 18  end;
 19  /
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select * from t;
 
         X
----------
        20
        20
        30
        40
        30
 

 

1) ops$tkyte@ORA9IR2> create table test(c1 number(4) primary key,c2 varchar2(20),c3 varchar2(25),c4
  2  date,c5 number(5,3));
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> exec gen_data( 'TEST', 5 );
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> exec clone( 'test', 3 )
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> select * from clone_test;
 
        C1 C2                   C3                        C4                C5
---------- -------------------- ------------------------- --------- ----------
      1085 IwxPRkQbNUNevniexfek weZuELTMCMMBWRqrsEUGkrTdl 10-MAY-05     85.896
      1656 VasXzANHQbTxdcklDVnr CkhXyNhYqnNhtEZMMOehRLqhx 08-AUG-05     91.128
      8022 RcwCZgUoavqnboKgtJqr PTVheZAbDSofKUtNtBifUCApT 01-FEB-07     10.242
 


worked fine for me, perhaps you tweaked the code??


2) you'd have to modify clone to do so (eg: write code)
 

ops$tkyte@ORA9IR2> select id,
  2         start_date+r,
  3         max( decode( type, 'A', amt )) A_amt,
  4         max( decode( type, 'B', amt )) B_amt
  5    from test_data,
  6         (select rownum-1 r from all_objects where rownum<=1000)
  7   where r <= (end_date-start_date+1)
  8   group by id, start_date+r
  9  having max( decode( type, 'A', amt )) <> max( decode( type, 'B', amt ))
 10         or
 11         max( decode( type, 'A', amt )) is null and max( decode( type, 'B', amt )) is not null
 12         or
 13         max( decode( type, 'A', amt )) is not null and max( decode( type, 'B', amt )) is null
 14   order by 2
 15  /
 
        ID START_DAT      A_AMT      B_AMT
---------- --------- ---------- ----------
         1 06-JAN-00                   115
         1 07-JAN-00                   115
         1 08-JAN-00                   115
         1 09-JAN-00                   115
         1 10-JAN-00                   115
         1 11-JAN-00                   115
         1 12-JAN-00                   115

.....
         1 31-DEC-00                   115
         1 01-JAN-01                   115
         1 02-JAN-01                   115
         1 28-DEC-01        122        119
 
364 rows selected.


change rownum<=1000 to be the max "spread" between end/start_date at least (and make sure the table you select it from has at least that many rows!  or use a pipelined function to generate as many rows as you need) 

there are over 29,000 questions and answers in my database.  Sorry but that one is eluding me right now.

but do you mean something like this:

ops$tkyte-ORA9IR2> create table t
  2  ( x int,
  3    a char(2000),
  4    b char(2000),
  5    c char(2000),
  6    d varchar2(2000)
  7  ) tablespace &1;
old   7: ) tablespace &1
new   7: ) tablespace USERS
 
Table created.
 
ops$tkyte-ORA9IR2> pause
 
ops$tkyte-ORA9IR2>
ops$tkyte-ORA9IR2> insert into t (x) values (1);
 
1 row created.
 
ops$tkyte-ORA9IR2> insert into t (x,a) values (2,'x');
 
1 row created.
 
ops$tkyte-ORA9IR2> insert into t (x,a,b,c,d) values (3,'a', 'y', 'z', rpad('x',500,'x') );
 
1 row created.
 
ops$tkyte-ORA9IR2> insert into t (x,d) values (4,rpad('x',725,'x') );
 
1 row created.
 
ops$tkyte-ORA9IR2> pause
 
ops$tkyte-ORA9IR2>
ops$tkyte-ORA9IR2> clear screen
 
ops$tkyte-ORA9IR2> select dbms_rowid.rowid_block_number(rowid), x from t;
                                                                                                    
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)          X
------------------------------------ ----------
                                 143          1
                                 143          2
                                 143          4
                                 144          3
                                                                                                    
ops$tkyte-ORA9IR2> commit;
                                                                                                    
Commit complete.

<b>1,2 >>>4<<<<, 3  'out of order', because rows in heaps have no order</b>
                                                                                                    
ops$tkyte-ORA9IR2> pause
                                                                                                    
ops$tkyte-ORA9IR2>
ops$tkyte-ORA9IR2> delete from t where x = 2;
                                                                                                    
1 row deleted.
                                                                                                    
ops$tkyte-ORA9IR2> commit;
                                                                                                    
Commit complete.
                                                                                                    
ops$tkyte-ORA9IR2> insert into t (x) values (5);
                                                                                                    
1 row created.
                                                                                                    
ops$tkyte-ORA9IR2> commit;
                                                                                                    
Commit complete.
                                                                                                    
ops$tkyte-ORA9IR2> pause
                                                                                                    
ops$tkyte-ORA9IR2> clear screen
                                                                                                    
                                                                                                    
ops$tkyte-ORA9IR2> select dbms_rowid.rowid_block_number(rowid), x from t;
                                                                                                    
DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)          X
------------------------------------ ----------
                                 143          1
                                 143          4
                                 143          5
                                 144          3
                                                                                                    
ops$tkyte-ORA9IR2> pause

<b>now even more "out of order"</b> 

well, there isn't really "the first rows" of anything - they are sets.

but...

ops$tkyte@ORA9IR2> create table t1 ( col1 int );

Table created.

ops$tkyte@ORA9IR2> create table t2 ( col1 int );

Table created.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> insert into t1 select rownum from all_users;

32 rows created.

ops$tkyte@ORA9IR2> insert into t2 select object_id from all_objects;

30913 rows created.

ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> declare
  2      type rid_array is table of rowid;
  3      l_rid  rid_array;
  4      type int_array is table of int;
  5      l_new  int_array;
  6  begin
  7      select t1.rid, t2.col1
  8        bulk collect into l_rid, l_new
  9        from (select rownum r, rowid rid
 10                from t1 ) t1,
 11             (select rownum r, col1
 12                from t2
 13               where rownum <= (select count(*) from t1)
 14             ) t2
 15       where t1.r = t2.r;
 16
 17      forall i in 1 .. l_rid.count
 18          update t1 set col1 = l_new(i) where rowid = l_rid(i);
 19  end;
 20  /

PL/SQL procedure successfully completed.



If T1 is large - you'll need to use an explicit cursor, use the LIMIT clause on the fetch - and iterate over the set. 

ops$tkyte%ORA11GR2> with data ( cpu_num, type, num_logical_processors, num_processors )
  2  as
  3  ( select 0 cpu_num, type, num_logical_processors, num_processors
  4      from test_cpu
  5     where num_logical_processors = 4
  6     union all
  7    select cpu_num+1, type, num_logical_processors, num_processors
  8      from data
  9     where cpu_num+1 < num_processors )
 10  select 'CPU' || cpu_num cpu, type, num_logical_processors, num_processors
 11    from data
 12  /

CPU   TYPE                                               NUM_LOGICAL_PROCESSORS NUM_PROCESSORS
----- -------------------------------------------------- ---------------------- --------------
CPU0  Intel(R) Core(TM)2 Duo CPU U9400  @ 1.40GHz                             4              4
CPU1  Intel(R) Core(TM)2 Duo CPU U9400  @ 1.40GHz                             4              4
CPU2  Intel(R) Core(TM)2 Duo CPU U9400  @ 1.40GHz                             4              4
CPU3  Intel(R) Core(TM)2 Duo CPU U9400  @ 1.40GHz                             4              4

ops$tkyte%ORA11GR2> 

create table t as
  select cast(
           dbms_random.string('a', dbms_random.value(2, 10)) 
           as varchar2(10)
         ) c1
  from dual connect by level <= 5;

select * from t;

C1         
Aqaktg     
ksgTLbAdc  
bhfN       
yJSceaPjp  
plhSEOceO