Home » SQL & PL/SQL » SQL & PL/SQL » insert into clustered table (10.0 win xp)
insert into clustered table [message #347874] Sun, 14 September 2008 14:49 Go to next message
durgadas.menon
Messages: 365
Registered: December 2007
Location: Nowhere
Senior Member
Hi,

I had a question regarding insert into clustered (B*Tree) table. Here is the what I was working on


Quote:
/*creating cluster and index over it*/

Quote:
Create cluster user_objects_cluster_btree (username varchar2(30)) size 1024
/
Create index user_objects_idx on cluster user_objects_cluster_btree
/


Quote:
/*creating table with the cluster*/

create table user_info (username, user_id, account_status, look_date, 
expiry_Date, default_tablespace, temporary_tablespace, created, profile)     cluster user_objects_cluster_btree(username) as select username. user_id, account_status,
 lock_Date, expiry_date, default_Tablesapce, temporary_tablespace. created, profile from dba_users
where 1=0

  create table user_objects (owner, object_name, object_id, object_type,. created, ,last_ddl_time, timestamp, status)  cluster user_objects_cluster_btree(owner)
  as
select owner, object_name, object_id, object_type, created, last_ddl_time, timestamp, status from dba_objects where 1=0;



insert 
[B] when (r=1) then[/B]
   into user_info  (username, user_id, account_status, look_date, expiry_Date, default_tablespace, temporary_tablespace, created, profile) 
   values 
(username, user_id, account_status, look_date, expiry_Date, default_tablespace, temporary_tablespace, created, profile)

[B]when (1=1) then[/B]

   into user_objects (owner, object_name, object_id, object_type,. created, ,last_ddl_time, timestamp, status)
 values (owner, object_name, object_id, object_type,. created, ,last_ddl_time, timestamp, status)

select a.*(all the columns), b.*(all the columns), row_number() over partition by owner order by object_id) r from dba_objects b, dba_users a 
  where a.username=b.owner and a.username <> 'SYS'



I had question regarding this line "when r=1", does this mean that the data in partition 1 will go into the first table (user_info)
and the data in the rest of the partitions will go into the second table user_objects "when 1=1". If not then can you throw some light on this usage.

Re: insert into clustered table [message #347883 is a reply to message #347874] Sun, 14 September 2008 22:01 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
According to the documentation, INSERT ALL is the default for conditional inserts, so all rows will be inserted into user_objects, but only those with r=1 will be inserted into user_info.

Ross Leishman
Previous Topic: need hierarchical list from root node to leaf node; reading left to right
Next Topic: System variable for the current procedure/function
Goto Forum:
  


Current Time: Sun Dec 04 00:20:40 CST 2016

Total time taken to generate the page: 0.10784 seconds