oracle基本数据类型
char(n) n=1 to 2000字节
定长字符串，n字节长，如果不指定长度，缺省为1个字节长（一个汉字为2字节）
varchar2(n) n=1 to 4000字节
可变长的字符串，具体定义时指明最大长度n， 这种数据类型可以放数字、字母以及ASCII码字符集。
number(m,n) m=1 to 38 n=-84 to 127 可变长的数值列
允许0、正值及负值，m是所有有效数字的位数，n是小数点以后的位数。
如：number(5,2)，则这个字段的最大值是999.99，如果数值超出了位数限制就会被截取多余的位数。
如：number(5,2)，但在一行数据中的这个字段输入575.316，则真正保存到字段中的数值是575.32。
如：number(3,0)，输入575.316，真正保存的数据是575。
date
从公元前4712年1月1日到公元4712年12月31日的所有合法日期，
缺省格式为DD-MON-YY，如07-11月-00 表示2000年11月7日。
LONG类型在java中是长整型，在oracle中是可变长字符列，最大长度限制是2GB【oracle不推荐使用此类型，已经过期】
LONG是一种较老的数据类型，将来会逐渐被BLOB、CLOB、NCLOB等大的对象数据类型所取代。
LOB数据类型(最大存储大小都为4gb)
LOB又称为“大对象”数据类型：主要有CLOB，BLOB，BFILE，NCLOB
BFILE 二进制文件,存储在数据库外的操作系统文件，只读的。把此文件当二进制处理。
BLOB 二进制大对象。存储在数据库里的大对象，一般是图像声音等文件。
CLOB 字符型大对象。一般存储大数量文本信息。存储单字节，固定宽度的数据。
NCLOB 字节字符大对象。存储单字节大块，多字节固定宽度
02rowid、rownum：
表中会存在2个伪列：
rowid和rownum
rownum：每一行的唯一号
rowid：用于寻址定位【索引扫描】
rowid组成 = 对象号(6位)+文件号(3位)+块号(6位)+行号(3位)
举例：
SQL> create table t10(id int);
insert into t10 values(123);
insert into t10 values(1);
commit;
SQL> select rowid,t.* from t10 t;(rowid为表中的伪列 不能插入数据)
ROWID ID
------------------ --------------------
AAAMiX AAE AAAABA AAA 123
AAAMiX AAE AAAABA AAB 1
SQL> sqlplus scott/lipengfei
SQL> select rowid,t.* from emp t;
SQL> select dbms_rowid.rowid_object(rowid) from emp t;【同一表中所有记录的对象号一样的，因为在同一对象中，也就是同一张表中】
结果中dbms_rowid.rowid_object = 51148
验证 51148 是scott用户下的 emp表
SQL> sqlplus / as sysdba
SQL> select object_name,object_type,owner from dba_objects t where t.object_id=51148;
SQL> sqlplus scott/lipengfei
SQL> select dbms_rowid.rowid_relative_fno(rowid) from emp;【查看文件号？文件号4】
SQL> sqlplus / as sysdba
SQL> select * from dba_data_files where file_id=4;(查看当前所有文件)
SQL> select dbms_rowid.rowid_block_number(rowid) from emp;【emp表的14行记录，全在32号块】
定位：
我想找到表中的数据，找数据文件【文件号】，再找段【一个表对应一个段】，在段下面再找块【块号】
------------------------------------------
1.scott登陆
2.查看所在的数据文件
select dbms_rowid.rowid_object(rowid),dbms_rowid.rowid_relative_fno(rowid),t.*from emp t;
结果:dbms_rowid.rowid_relative_fno=4 (表示对应4号文件)
sys登陆(对应的文件)
SQL> select FILE_NAME,FILE_ID from dba_data_files;
3.查询所在块
select dbms_rowid.rowid_block_number(rowid),t.* from emp t;
dbms_rowid.rowid_block_number 对应的是所在的块
4.查看行号
select dbms_rowid.rowid_row_number(rowid),t.* from emp t;
dbms_rowid.rowid_row_number(rowid) 对应的是行号
在日常管理中rowid什么用？
1、判断行迁移
2、对于大表的快速更新
3、去掉重复数据
-------------分页查询--------------
分页查询，就是将过多的结果在有限的界面上分好多页来显示，这个是很多网站常用的功能，也是最基本的功能。
分页查询格式：
SELECT *
FROM (SELECT A.*, ROWNUM RN FROM emp A WHERE ROWNUM <= 10)
WHERE RN >= 6;
其中最内层的查询SELECT * FROM TABLE_NAME表示不进行翻页的原始查询语句。ROWNUM <= 40和RN >= 21控制分页查询的每页的范围。
上面给出的这个分页查询语句，在大多数情况拥有较高的效率。分页的目的就是控制输出结果集大小，将结果尽快的返回。
在上面的分页查询语句中，这种考虑主要体现在WHERE ROWNUM <= 40这句上。
选择第21到40条记录存在两种方法，一种是上面例子中展示的在查询的第二层通过ROWNUM <= 40来控制最大值，在查询的最外层控制最小值。
而另一种方式是去掉查询第二层的WHERE ROWNUM <= 40语句，在查询的最外层控制分页的最小值和最大值。这是，查询语句如下：
SELECT * FROM (SELECT A.*, ROWNUM RN FROM emp A) WHERE RN BETWEEN 6 AND 10
对比这两种写法，绝大多数的情况下，第一个查询的效率比第二个高得多。
这是由于CBO优化模式下，Oracle可以将外层的查询条件推到内层查询中，以提高内层查询的执行效率。
对于第一个查询语句，第二层的查询条件WHERE ROWNUM <= 40就可以被Oracle推入到内层查询中，
这样Oracle查询的结果一旦超过了ROWNUM限制条件，就终止查询将结果返回了。
而第二个查询语句，由于查询条件BETWEEN 21 AND 40是存在于查询的第三层，
而Oracle无法将第三层的查询条件推到最内层（即使推到最内层也没有意义，因为最内层查询不知道RN代表什么）。
因此，对于第二个查询语句，Oracle最内层返回给中间层的是所有满足条件的数据，而中间层返回给最外层的也是所有数据。
数据的过滤在最外层完成，显然这个效率要比第一个查询低得多。
补充：
MySQL数据库实现分页比较简单，提供了LIMIT函数
SQLServer数据库一般比较简单的方法是通过TOP函数来实现。
表分类详解：
1、堆组织表，就是普通的标准数据库表，数据以堆的方式管理。堆其实就是一个很大的空间，会一种随机的方式管理数据，数据会放在合适的地方。
例如建立一张表
create table t(id int,name varchar2(20));
插入三条记录
insert into t values(1,'a');
insert into t values(2,'b');
insert into t values(3,'c');
之后删除记录b;
delete from t where id=2;
再插入记录d
insert into t(4,'d');
d记录就可能放在记录b的位置。
2、索引组织表(IOT)不仅可以存储数据，还可以存储为表建立的索引。
索引组织表的数据是根据主键排序后的顺序进行排列的，这样就提高了访问的速度。
但是这是由牺牲插入和更新性能为代价的(每次写入和更新后都要重新进行重新排序)。
创建索引组织表（organization index）
SQL> create table t_iot(id int primary key,name varchar2(100))organization index;
创建普通表
SQL> create table t_h(id int,name varchar2(100)); {创建同样列的普通表}
往两个表中插入数据
set timing on
SQL> insert into t_iot select object_id,owner from dba_objects where rownum<10000;
SQL> insert into t_h select object_id,owner from dba_objects where rownum<10000;
分析下两个表的差异
1、查看表
SQL> select table_name, tablespace_name,blocks from user_tables where table_name in ('T_IOT','T_H');
结果分析：
首先Oracle承认IOT是一个数据表，其次，从段结构来看，Oracle明确不承认存在T_IOT段。因为如果有段segment对象，就意味有空间分配。但是数据表有数据，是存放在哪里呢？保存在索引段中
我们知道，给数据表添加主键的时候，Oracle会自动的添加一个唯一索引。那么我们去检查一下这部分的结构情况。
查看索引
SQL> select index_name, index_type, table_name from user_indexes where table_name in ('T_IOT','T_H');
注意两点：
● 创建IOT时，必须要设定主键，否则报错。
● 索引组织表实际上将所有数据都放入了索引中。
举例：
1.一个客户有很多地址信息，客户是一个表，客户地址信息是另外一个表。
读取一个客户地址信息的时候，如果这个客户的所有地址信息都存放在相邻的地方，读取速度就会快一些。
这个时候，客户地址信息表适合创建成IOT。
2. 经常查看一支股票的最近几天的信息，股票信息一般是千万级别的数据，如果能够把最近几天的信息存放在一起就会快很多。
3、索引聚簇表，聚簇是指一个或多个表的组。有相同聚簇值的行会相邻的物理存储。
oracle数据字典就大量使用这种表，这样可以将表、字典信息存储在一起，提高访问效率。
如果数据只要用于读，需要频繁地把一些表的信息连结在一起访问，可以考虑索引聚簇表。
但注意：聚簇会导致dml、全表扫描的效率底下，还有就是索引聚簇表是不能分区的。
cluster
4、临时表,临时表用来保存事务、会话中间结果集。临时表值对当前会话可见，可以创建基于会话的临时表，也可以创建基于事务的临时表。
1）、基于会话的临时表：【会话结束后，表还在,但是数据没了】
create global temporary table temp_s(id int,name varchar2(20)) on commit preserve rows;
insert into temp_s values(1,'yi');
select * from temp_s;
commit;
2）、基于事务的临时表：【执行commit后，表还在,但是数据没了】
create global temporary table temp_t(id int,name varchar2(20)) on commit delete rows;
insert into temp_t values(1,'yi');
select * from temp_t;
commit;
如果应用中需要临时存储一个行集合供其他表处理，可以考虑临时表。
5、分区表： 【一般超过2G大小的表，就可以用分区表。举例租碟部，最开始碟比较少，后来碟越来越多】
(1). 分区表的概念
分区表：
当表中的数据量不断增大，查询数据的速度就会变慢，应用程序的性能就会下降，这时就应该考虑对表进行分区。
表进行分区后，逻辑上表仍然是一张完整的表，只是将表中的数据在物理上存放到多个表空间(物理文件上)，这样查询数据时，不至于每次都扫描整张表。
表每个分区都产生一个segments，不同的segments存放在不同的表空间，不同表空间存放不同磁盘，可以减少I/O。
(2).表分区的具体作用
Oracle的表分区功能通过改善可管理性、性能和可用性，从而为各式应用程序带来了极大的好处。
通常，分区可以使某些查询以及维护操作的性能大大提高。
此外,分区还可以极大简化常见的管理任务，分区是构建千兆字节数据系统或超高可用性系统的关键工具。
分区功能能够将表、索引或索引组织表进一步细分为段，这些数据库对象的段叫做分区。
每个分区有自己的名称，还可以选择自己的存储特性。
从数据库 管理员的角度来看，一个分区后的对象具有多个段，这些段既可进行集体管理，也可单独管理，
这就使数据库管理员在管理分区后的对象时有相当大的灵活性。
但是，从应用程序的角度来看，分区后的表与非分区表完全相同，使用 SQL DML 命令访问分区后的表时，无需任何修改。
什么时候使用分区表？？？？
1、表的大小超过2GB。
2、表中包含历史数据，新的数据被增加都新的分区中。
(3).表分区的优缺点
优点：
1、改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索速度。
2、增强可用性：如果表的某个分区出现故障，表在其他分区的数据仍然可用；
3、维护方便：如果表的某个分区出现故障，需要修复数据，只修复该分区即可；
4、均衡I/O：可以把不同的分区映射到磁盘以平衡I/O，改善整个系统性能。
缺点：
分区表相关：已经存在的表没有方法可以直接转化为分区表。不过 Oracle 提供了在线重定义表的功能【自己编写存储过程也可以实现】。
(4).表分区的几种类型及操作方法
一.范围分区：
范围分区将数据基于范围映射到每一个分区，这个范围是你在创建分区时指定的分区键决定的。
这种分区方式是最为常用的，并且分区键经常采用日期。举个例子：你可能会将销售数据按照月份进行分区。
当使用范围分区时，请考虑以下几个规则：
1、每一个分区都必须有一个VALUES LESS THEN子句，它指定了一个不包括在该分区中的上限值。
分区键的任何值等于或者大于这个上限值的记录都会被加入到下一个高一些的分区中。
2、所有分区，除了第一个，都会有一个隐式的下限值，这个值就是此分区的前一个分区的上限值。
3、在最高的分区中，MAXVALUE被定义。MAXVALUE代表了一个不确定的值。
这个值高于其它分区中的任何分区键的值，也可以理解为高于任何分区中指定的VALUE LESS THEN的值，同时包括空值。
【例一：按员工号划分，单列范围分区】
create table lipengfei
(empno number(4),
ename varchar2(30),
sal number)
partition by range(empno)
(partition e1 values less than (1000) tablespace emp1,
partition e2 values less than (2000) tablespace emp2,
partition e3 values less than (3000) tablespace emp3,
partition e4 values less than (maxvalue) tablespace emp4);
insert into lipengfei values (100,'Tom',1000);
insert into lipengfei values (500,'Peter',2000);
insert into lipengfei values (1000,'Scott',3000);
insert into lipengfei values (1999,'Bill',4000);
insert into lipengfei values (5000,'Gates',6000);
commit;
从emp表中选择全部纪录如下：
QL> select * from lipengfei;
EMPNO ENAME SAL
---------- -----------
100 Tom 1000
500 Peter 2000
1000 Scott 3000
1999 Bill 4000
5000 Gates 6000
还可以按照分区进行选择：
SQL> select * from lipengfei partition (e1);
EMPNO ENAME SAL
---------- --------------
100 Tom 1000
500 Peter 2000
SQL> select * from lipengfei partition (e2)
EMPNO ENAME SAL
---------- --------------
1000 Scott 3000
1999 Bill 4000
SQL> select * from lipengfei partition (e3)
select * from lipengfei partition (e4);
EMPNO ENAME SAL
------ ------------------
5000 Gates 6000
【例二：多列范围分区主要是基于表中多个列值范围对数据进行分区】
drop table lipengfei;
create table lipengfei
(empno number(4),
ename varchar2(30),
sal number,
dd number not null,
mm number not null)
partition by range(month,day)
(partition e1 values less than (5,1) tablespace emp1,
partition e2 values less than (10,2) tablespace emp2,----10月2号
partition e3 values less than (maxvalue,maxvalue) tablespace emp3);
insert into lipengfei values (100,'Tom',1000,10,6);
insert into lipengfei values (100,'Tom',1000,10,9);
insert into lipengfei values (100,'Tom',1000,3,10);
insert into lipengfei values (200,'Peter',2000,3,1);
insert into lipengfei values (300,'Jane',3000,23,11);
commit;
【例三：按时间划分】
CREATE TABLE ORDER_ACTIVITIES
(
ORDER_ID NUMBER(7) NOT NULL,
ORDER_DATE DATE,
TOTAL_AMOUNT NUMBER,
CUSTOTMER_ID NUMBER(7),
PAID CHAR(1)
)
PARTITION BY RANGE (ORDER_DATE)
(
PARTITION ORD_ACT_PART01 VALUES LESS THAN (TO_DATE('01- MAY -2003','DD-MON-YYYY')) TABLESPACEORD_TS01,
PARTITION ORD_ACT_PART02 VALUES LESS THAN (TO_DATE('01-JUN-2003','DD-MON-YYYY')) TABLESPACE ORD_TS02,
PARTITION ORD_ACT_PART03 VALUES LESS THAN (TO_DATE('01-JUL-2003','DD-MON-YYYY')) TABLESPACE ORD_TS03
);
二.列表分区：
该分区的特点是某列的值只有几个，基于这样的特点我们可以采用列表分区。
这是Oracle 9i新特性，有了这种分区使得我们可以方便按照值来将数据分为更小片断。
【例一】
drop table lipengfei;
create table lipengfei (
empno number(4),
ename varchar2(30),
location varchar2(30))
partition by list (location)
(partition e1 values ('北京'),
partition e2 values ('上海','天津','重庆'),
partition e3 values ('广东','福建'));
insert into lipengfei values(123,'123','日本');
这里说明一下，列表分区不能有maxvalue，当你试图insert列表中不存在值时候，Oracle会拒绝这条纪录（ORA-14400）。
三.散列分区：
这类分区是在列值上使用散列算法，以确定将行放入哪个分区中。当列的值没有合适的条件时，建议使用散列分区。
散列分区为通过指定分区编号来均匀分布数据的一种分区类型，因为通过在I/O设备上进行散列分区，使得这些分区大小一致。
【例一】
CREATE TABLE HASH_TABLE
(
COL NUMBER(8),
INF VARCHAR2(100)
)
PARTITION BY HASH (COL)
(
PARTITION PART01 TABLESPACE HASH_TS01,
PARTITION PART02 TABLESPACE HASH_TS02,
PARTITION PART03 TABLESPACE HASH_TS03
);
简写：
CREATE TABLE emp
(
empno NUMBER (4),
ename VARCHAR2 (30),
sal NUMBER
)
PARTITION BY HASH (empno)
PARTITIONS 8 STORE IN (emp1,emp2,emp3,emp4,emp5,emp6,emp7,emp8);
hash分区最主要的机制是根据hash算法来计算具体某条纪录应该插入到哪个分区中,hash算法中最重要的是hash函数，
Oracle中如果你要使用hash分区，只需指定分区的数量即可。
建议分区的数量采用2的n次方，这样可以使得各个分区间数据分布更加均匀。
emp1,emp2,emp3,emp4等是表空间名称。
四.复合分区
【例一】
drop table emp;
create table emp (
empno number(4),
ename varchar2(30),
hiredate date)
partition by range (hiredate)
subpartition by hash (empno)
(partition e1 values less than (to_date('20020501','YYYYMMDD')),
partition e2 values less than (to_date('20021001','YYYYMMDD')),
partition e3 values less than (maxvalue));
上面例子中将雇员表先按照雇佣时间hiredate进行了范围分区，然后再把每个分区分为两个子hash分区。
例子中一共将产生6个分区，分区之中的分区被称为子分区。