orcale 锁与分区

 

                                                                                                       锁     
  锁分为:行级锁,表级锁 。 
                  行级锁:
                                 INSERT ,UPDATE,SELECT....FOR UPDATE, DELETE自动应用行级锁。
                                 SELECT....FOR UPDATE可以锁定多行进行更行。
                                 COMMIT,ROLLBACK释放锁。
                                 SELECT * FROM {表名}[{查询条件}]FOR UPDATE WAIT[NOT WAIT] 5;查询等待5秒或者不等待 ;
                  表级锁:
                               表级锁分为:行共享,共享,排他,行排他,共享行排他。
                               语法:LOCK TABLE table_name IN mode MODE; 
                               
==============================================================================================================
                                                                                                表分区
  表分区分为:范围分区,散列分区,列表分区,复合分区。
                  范围分区:以表中的某些列的值的分区。
                  代码示例:
                                      CREATE TABLE Sales
                                                                           (
                                                                            procduct_id varchar2(5),
                                                                            sales_cost number(10)  
                                                                           )
                                                 PARTITION BY RANGE(sales_cost)
                                                    (
                                                      PARTITION  P1  VALUES  LESS  THAN(1000),
                                                      PARTITION  P2  VALUES  LESS  THAN(2000),
                                                      PARTITION  P3  VALUES  LESS  THAN(3000),
                                                      PARTITION  P4  VALUES  LESS  THAN(4000),
                                                      PARTITION  P5  VALUES  LESS  THAN(MAXVALUE)
                                                    );
                    散列分区:  没有逻辑范围,用HASH算法平均分配到不同的分区。
                    代码示例:
                                        CREATE TABLE Emloyee
                                                                           (
                                                                            Emp_id  number(4),
                                                                            Emp_name varchar2(14)  
                                                                           )   
                                                       PARTITION BY HASH(DEPARTMENT)
                                                              PARTITIONS 4;//平均分为四个区;
                 
                   列表分区:
                   代码示例:
                                    CREATE TABLE Emloyee
                                                                           (
                                                                            Emp_id  number(4),
                                                                            Emp_name varchar2(14) 
                                                                            Emp_Adress varchar2(14) 
                                                                           )
                                                        PARTITION BY LIST(Emp_Adress)
                                                (
                                                     PARTITION  north  VALUES('北京'),
                                                     PARTITION  west  VALUES('天津','上海')
                                                ) ;
                   复合分区:
                   代码示例:
                                    CREATE TABLE Sales
                                                                           (
                                                                            procduct_id varchar2(5),
                                                                            sales_cost number(10),
                                                                            sales_date date  
                                                                           )
                                                    PARTITION  BY  RANGE(sales_date)
                                                    SUBPARTITION BY HASH(procduct_id) 
                                                    SUBPARTITIONS 5 //将每一个小区再分为5个区
                                                  (
                                                        PARTITION  S1   VALUES  LESS  THAN(TO_DATE('01/4/2001','DD/MON/YYYY')),
                                                        PARTITION  S2   VALUES  LESS  THAN(TO_DATE('01/5/2001','DD/MON/YYYY'))
                                                  );                      
  
                 查询分区:SELECT * FROM {表明} PARTITION (P3)。
                 删除分区中的某一条数据:DELETE {表明} [{条件}] PARTITION (P4)。
                 添加分区:ALTER TABLE {表明} ADD PARTITION  (Pn)  PARTITION  P4  VALUES  LESS  THAN(6000)。
                 删除分区:ALTER TABLE {表明} DROP PARTITION (P4)。
                 截断分区:ALTER TABLE {表明} TRUNCATE PARTITION  P3。
                 合并相邻分区:ALTER TABLE {表明}P1,P2 INTO PARTITION P2。
                 拆分区:ALTER TABLE {表明}SPILT  PARTITION   P2 AT (1500) INTO(PARTITION P21 ,PARTITION  P22)。

                

 

 


                        


版权声明:本文为dengzhouit原创文章,遵循CC 4.0 BY-SA版权协议,转载请附上原文出处链接和本声明。