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  |
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.
|
|
|
|
Goto Forum:
Current Time: Sat Feb 15 07:38:20 CST 2025
|