1.connect_by_leaf 找出叶子节点,这是个伪列,叶子节点为1,其他为0
connect_by_root 列名 找出跟节点上的某列,(对于树形结构,每个节点都对应一列)
sys_connect_by_path(列,‘形式’) 找出每条路径上的节点中的哪一列,连接形式
connect_by_iscycle 伪列,为0表示没有循环,为1表示有循环,需要和nocycle合用,connect by nocycle prior ..=..
2.select level from dual connect by 1=1;(无止尽写下去);
3.分区的依据: (1).表的大小超过2GB
(2).表已经超过1000万行并且随着增加的数据越来越多,SQL的操作越来越慢。
(3).对于你知道将来会变成大表的那些表,与其等表增长到影响性能时,将其重建为分区表,不如直接按照分区表来创建。
(4).划分记录为方便进行操作,如加载,归档,检索或备份和恢复
4.在最初创建表的时候最容易分区,而不是等到增长到难以处理的大小的时候再试图将其转换为分区表
5.查看分区表的信息:user_part_tables,查看分区信息:user_tab_partitions
6.単分区表格式
create table table_name()
partition by range|list|hash(分区列)(
partition p1 values [less than] ()
)
复合分区
create table table_name()
partition by range|list|hash(分区列)
subpartition by range|list|hash(分区列)
(
partition p1 values [less than] ()
(subpartition p1_sub1 values [less than] ()
subpartition p1_sub2 values [less than] ()),
partition p2 values [less than] ()
(subpartition p2_sub1 values [less than] ()
subpartition p2_sub2 values [less than] ())
);
7.复合分区的组合1.范围-(范围,列表,哈希)2.列表-(范围,列表,哈希)
8.间隔分区:只能对于日期和数字类型的;
numtoyminterval函数——数字转换函数
语法:NUMTOYMINTERVAL ( n , 'char_expr' )
char_expr:日期描述,可以是YEAR和MONTH;
比如n,后面如果是‘year’,则函数表示n年,月,则表示n月
对于day、hour、minute、second使用的是numtodsinterval函数,方法和numtoyminterval一样。
9.一般提示缺少右括号,问题是每个变量之间有缺少逗号
10.虚拟列:1.必须以原表的非虚拟列为基础、2.虚拟列中的值必须是确定的,每次返回必须是确定的
添加虚拟列 alter table table_name add(虚拟列名 as (a+b)*c);
创建表时:create table abc(
a number,
b number,
c number,
d number generated always as (a*b)
);
虚拟列里面的值必须由非虚拟列中的值返回,所以我们不能update虚拟列中的值!!!
11.系统分区:当我们需要在插入表的时候可以指定插入到哪些分区的。
create table abc(
a number)
partition by system(
partition q1,
partition p2,
partition p3);
插入和删除。更新时需要指明所在区;insert into abc partition(q1) values (1);
12.移动表的分区到指定的表空间上;alter table table_name move partition partition_name tablespace tablespace_name;
当我们移动表的时候(也就是说数据移动到别的数据块时,数据发生迁移的话 RowID会重新计算和映射的),表上的rowid也会发生变化,
这使得表上的索引失效,所以我们还需要重建索引,并重新指定所在的表空间
alter index index_name rebuild;
13.允许行迁移:当我们修改一张表上的键值的时候,修改完后,这一行可能属于别的分区,这是我们要先设置表能够行迁移,要不然oracle
是不允许这么做的。alter table table_name enable row movement;
14.将非分区别转换为分区表:(1).create table new_table_name(....)partition by..... as select * from old_table_name
(2).drop table old_table_name
(3).alter table new_table_name rename to old_table_name;
最后我们对新表创建约束,索引,授权,统计信息,可以使用empdp或者exp导出旧表的元数据,再用impdp或者imp来导入到新表上
15.PCTFREE(可用):为一个块保留的空间百分比,表示数据块在什么情况下可以被insert,默认是10,表示当数据块的可用空间低于10%后,
就不可以被insert了,只能被用于update;即:当使用一个block时,在达到pctfree之前,该block是一直可以被插入的,这个时候处在上升期。
PCTUSED(使用):是指当块里的数据低于多少百分比时,又可以重新被insert,一般默认是40,即40%,即:当数据低于40%时,又可以写入新的数据,
这个时候处在下降期。
16.当你对一张表进行表分区的时候,表所对应的索引表也会进行相同的索引分区,所以当你改变一个表分区所在的表空间的时候,同时也需要所对应的索引分区所在
的表空间,当你对一个表分区进行重命名的时候,那你也应该对它所对应的索引分区进行重命名,(表分区的名字和所对应的索引分区的名字是相同的),索引是随着表
的变化而变化的!!!!!!!!
17.alter table...............update indexes;来自动重建索引,
18.拆分分区:alter table table_name split partition partition_name values|less than() into
(partition partition_name1,partition partition_name2) update indexes;
一般是平均拆分;
19.合并分区:alter table tablename merge partitions a,b into partition c update indexes;(注意partition前面是加s了,所以写一次,后面合并成
一个,所以不加s);
20.对分区进行处理的时候一定要考虑索引的重建问题!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
21.对于分区的删除,是不能回退的,所以最好的方法是进行合并;
22.alter table table_name truncate partition partition_name;//delete from table_name partition(partition_name);
23.对分区进行dml操作的时候分区名都是这样非形式:partition(partition_name),而其他的操作则是:partition partition_name;
24.对于level伪列,只要有connect by level<n;就能形成n层层次结构,从1开始到n,from table_name,table_name是你需要使用的表,from不一定非要是dual;
connect_by_root 列名 找出跟节点上的某列,(对于树形结构,每个节点都对应一列)
sys_connect_by_path(列,‘形式’) 找出每条路径上的节点中的哪一列,连接形式
connect_by_iscycle 伪列,为0表示没有循环,为1表示有循环,需要和nocycle合用,connect by nocycle prior ..=..
2.select level from dual connect by 1=1;(无止尽写下去);
3.分区的依据: (1).表的大小超过2GB
(2).表已经超过1000万行并且随着增加的数据越来越多,SQL的操作越来越慢。
(3).对于你知道将来会变成大表的那些表,与其等表增长到影响性能时,将其重建为分区表,不如直接按照分区表来创建。
(4).划分记录为方便进行操作,如加载,归档,检索或备份和恢复
4.在最初创建表的时候最容易分区,而不是等到增长到难以处理的大小的时候再试图将其转换为分区表
5.查看分区表的信息:user_part_tables,查看分区信息:user_tab_partitions
6.単分区表格式
create table table_name()
partition by range|list|hash(分区列)(
partition p1 values [less than] ()
)
复合分区
create table table_name()
partition by range|list|hash(分区列)
subpartition by range|list|hash(分区列)
(
partition p1 values [less than] ()
(subpartition p1_sub1 values [less than] ()
subpartition p1_sub2 values [less than] ()),
partition p2 values [less than] ()
(subpartition p2_sub1 values [less than] ()
subpartition p2_sub2 values [less than] ())
);
7.复合分区的组合1.范围-(范围,列表,哈希)2.列表-(范围,列表,哈希)
8.间隔分区:只能对于日期和数字类型的;
numtoyminterval函数——数字转换函数
语法:NUMTOYMINTERVAL ( n , 'char_expr' )
char_expr:日期描述,可以是YEAR和MONTH;
比如n,后面如果是‘year’,则函数表示n年,月,则表示n月
对于day、hour、minute、second使用的是numtodsinterval函数,方法和numtoyminterval一样。
9.一般提示缺少右括号,问题是每个变量之间有缺少逗号
10.虚拟列:1.必须以原表的非虚拟列为基础、2.虚拟列中的值必须是确定的,每次返回必须是确定的
添加虚拟列 alter table table_name add(虚拟列名 as (a+b)*c);
创建表时:create table abc(
a number,
b number,
c number,
d number generated always as (a*b)
);
虚拟列里面的值必须由非虚拟列中的值返回,所以我们不能update虚拟列中的值!!!
11.系统分区:当我们需要在插入表的时候可以指定插入到哪些分区的。
create table abc(
a number)
partition by system(
partition q1,
partition p2,
partition p3);
插入和删除。更新时需要指明所在区;insert into abc partition(q1) values (1);
12.移动表的分区到指定的表空间上;alter table table_name move partition partition_name tablespace tablespace_name;
当我们移动表的时候(也就是说数据移动到别的数据块时,数据发生迁移的话 RowID会重新计算和映射的),表上的rowid也会发生变化,
这使得表上的索引失效,所以我们还需要重建索引,并重新指定所在的表空间
alter index index_name rebuild;
13.允许行迁移:当我们修改一张表上的键值的时候,修改完后,这一行可能属于别的分区,这是我们要先设置表能够行迁移,要不然oracle
是不允许这么做的。alter table table_name enable row movement;
14.将非分区别转换为分区表:(1).create table new_table_name(....)partition by..... as select * from old_table_name
(2).drop table old_table_name
(3).alter table new_table_name rename to old_table_name;
最后我们对新表创建约束,索引,授权,统计信息,可以使用empdp或者exp导出旧表的元数据,再用impdp或者imp来导入到新表上
15.PCTFREE(可用):为一个块保留的空间百分比,表示数据块在什么情况下可以被insert,默认是10,表示当数据块的可用空间低于10%后,
就不可以被insert了,只能被用于update;即:当使用一个block时,在达到pctfree之前,该block是一直可以被插入的,这个时候处在上升期。
PCTUSED(使用):是指当块里的数据低于多少百分比时,又可以重新被insert,一般默认是40,即40%,即:当数据低于40%时,又可以写入新的数据,
这个时候处在下降期。
16.当你对一张表进行表分区的时候,表所对应的索引表也会进行相同的索引分区,所以当你改变一个表分区所在的表空间的时候,同时也需要所对应的索引分区所在
的表空间,当你对一个表分区进行重命名的时候,那你也应该对它所对应的索引分区进行重命名,(表分区的名字和所对应的索引分区的名字是相同的),索引是随着表
的变化而变化的!!!!!!!!
17.alter table...............update indexes;来自动重建索引,
18.拆分分区:alter table table_name split partition partition_name values|less than() into
(partition partition_name1,partition partition_name2) update indexes;
一般是平均拆分;
19.合并分区:alter table tablename merge partitions a,b into partition c update indexes;(注意partition前面是加s了,所以写一次,后面合并成
一个,所以不加s);
20.对分区进行处理的时候一定要考虑索引的重建问题!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
21.对于分区的删除,是不能回退的,所以最好的方法是进行合并;
22.alter table table_name truncate partition partition_name;//delete from table_name partition(partition_name);
23.对分区进行dml操作的时候分区名都是这样非形式:partition(partition_name),而其他的操作则是:partition partition_name;
24.对于level伪列,只要有connect by level<n;就能形成n层层次结构,从1开始到n,from table_name,table_name是你需要使用的表,from不一定非要是dual;