oracle 笔记

创建用户
Create user 用户名 identified by 密码;(如果是数字则要加双引号”111111”,如果是字母就不用)
授权给某个用户
Grant connect,resource to 用户名;(只有用户有了connect 和 resource后才能操作其他表)
授DBA 权限
Grant dba to 用户名;
本用户读取其他用户对象的权限:
  select * from user_tab_privs;
本用户所拥有的系统权限:
  select * from user_sys_privs;
alter user sys identified by 123; (sys是用户名,123是密码)
--登入设置sql/plus
tpower/tpower@orcl
define _editor=vi
set serveroutput on size 1000000
set trimspool on
set long 5000
set linesize 100
set pagesize 9999
column plan_plus_exp format a80
column global_name new_value gname
set termout off
define gname=idle
column global_name new_value gname
select lower(user) || '@' || substr( global_name, 1, decode( dot, 0,
length(global_name), dot-1) ) global_name
  from (select global_name, instr(global_name,'.') dot from global_name );
set sqlprompt '&gname> '
set termout on

select dbms_metadata.get_ddl( 'TABLE', 'TCLOB' ) from dual;

http://www.cnblogs.com/g1mist/archive/2013/10/22/3382468.html

/*分为四步 */
/*第1步:创建临时表空间  */
create temporary tablespace myuser_temp 
tempfile 'E:/ORACLE/PRODUCT/10.1.0/ORADATA/ORATEST/TPOWERS01.DBF'
size 50m 
autoextend on 
next 50m maxsize 20480m 
extent management local; 
 
/*第2步:创建数据表空间  */
create tablespace myuserSp 
logging 
datafile 'E:/ORACLE/PRODUCT/10.1.0/ORADATA/ORATEST/myuserSp.DBF'
size 100M 
autoextend on 
next 50m maxsize 20480m 
extent management local; 
 
/*第3步:创建用户并指定表空间  */
create user username identified by password 
default tablespace user_data 
temporary tablespace user_temp; 
 
/*第4步:给用户授予权限  */
grant connect,resource,dba to username;

接下来,我们来看看如何介入这种锁定活动。有以下选择:
�� 通过一条SQL语句手动地锁定数据。
�� 通过DBMS_LOCK包创建我们自己的锁。
在后面的小节中,我们将简要地讨论这样做的目的。
。例如,倘若没有对外键加索引,Oracle会使用表锁来保证外键关系,如果你不知道这一点,你的应用就会性能很差。
如果你不知道如何查看数据字典来得出谁锁住了什么,可能永远也发现不了到底是怎么回 事。你可能只是认为数据库有
时会“挂起”。有时,面对一个看上去无法解决的挂起问题,我只是运行一个查询来检测外键是不是没有索引,
并建议对导致问题的外键加上索引,就能很好地解决问题。
这种情况太常见了,我想如果每次解决这样一个问题就能得到1美元的报酬的话,我肯定会成为一个富翁。

这一次触发器只触发了一次,而不是两次。这说明,:NEW和:OLD列值在触发器中引用时,也会被Oracle用于完成重启动检查。
在触发器中引用:NEW.X和:OLD.X时,会比较X的一致读值和当前读值,并发现二者不同。这就会带来一个重启动。
从触发器将这一列的引用去掉后,就没有重启动了。
所以,对此的原则是:WHERE子句中查找行所用的列集会与行触发器中引用的列进行比较。
行的一致读版本会与行的当前读版本比较,只要有不同,就会重启动修改。
根据这些信息,我们可以进一步理解为什么使用AFTER FOR EACH ROW触发器比使用BEFORE FOR EACH ROW更高效。
AFTER触发器不存在这些问题。
想 想看这会有什么潜在的影响。如果你有一个触发器会做一些非事务性的事情,这可能就是一个相当严重的问题。
例如,考虑这样一个触发器,它要发出一个更新(电 子邮件),电子邮件的正文是“这是数据以前的样子,
它已经修改成现在这个样子“。如果从触发器直接发送这个电子邮件,(在Oracle9i中使用UTL_SMTY,
或者在Oracle 10g及以上版本中使用UTL_MAIL),用户就会收到两个电子邮件,而且其中一个报告的更新从未实际发生过。
了解了这一点后,我几乎完全戒除在触发器里使用自治事 务,另外开始重新考虑我的一些应用应该如何实现。
例如,我不再从触发器直接发送电子邮件;相反,肯定会在我的事务提交之后用DBMS_JOB或新的Oracle 10g调度工具发
送电子邮件。这样能是电子邮件的发送是”事务性的“,也就是说,如果导致触发器触发和发送电子邮件的语句重启动了,
它完成的回滚就会回滚DBMS_JOB请求。我修改了在触发器里做的几乎所有非事务性工作,使之在事后的作业中完成,
从而得到事务一致性。

可以看到,Oracle把Pro这个存储过程调用处理为一个原子语句。客户提交了一个代码块BEGIN P; END;,
Oracle在它外面包了一个SAVEPOINT。由于Pro失败了,Oracle将数据库恢复到调用这个存储过程之前的时间点.
如果代码中包含一个WHEN OTHERS异常处理器,但其中没有一个RAISE来重新引发异常,我认为这样的代码都是有bug的。
它会悄悄地忽略错误,这就改变了事务的语义。如果捕获WHEN OTHERS,并把异常转换为旧式的返回码,
这会改变数据库本该有的表现。

可以看到,提交得越多,花费的时间就越长(你的具体数据可能与这里报告的不同)。这只是单用户的情况,
如果有多个用户在做同样的工作,所有这些用户都过于频繁地提交,那么得到的数字将飞速增长。
在其他类似的情况下,我们也不止一次地听到过同样的“故事”。例如,我们已经知道,如果不使用绑定变量,
而且频繁地完成硬解析,这会严重地降低并发性,原因是存在库缓存竞争和过量的CPU占用。即使转而使用绑定变量,
如果过于频繁地软解析,也会带来大量的开销(导致过多软解析的原因可能是:执意地关闭游标,尽管稍后就会重用这些游标
)。必须在必要时才完成操作,COMMIT就是这样的一种操作。最好根据业务需求来确定事务的大小,
而不是错误地为了减少数据库上的资源使用而“压缩”事务。
在这个例子中,COMMIT的开销存在两个因素:
�� 显然会增加与数据库的往返通信。如果每个记录都提交,生成的往返通信量就会大得多。
�� 每次提交时,必须等待redo写至磁盘。这会导致“等待”。在这种情况下,等待称为“日志文件同步”(log file sync)。
�� 只需对这个Java应用稍做修改就可以观察到后面这一条。我们将做两件事情:
�� 增加一个DBMS_MONITOR调用,启用对等待事件的SQL跟踪。
在Oracle9i中,则要使用alter session set events ‘10046 trace name context forever, level 12’,
因为DBMS_MONITOR是Oracle 10g中新增的。
把con.commit()调用改为一条完成提交的SQL语句调用。如果使用内置的JDBC commit()调用,
这不会向跟踪文件发出SQL COMMIT语句,而TKPROF(用于格式化跟踪文件的工具)也不会报告完成COMMIT所花费的时间。
因此,即使我们有一个长时间运行的事务,但在提交之前,它生成的许多缓存重做日志已经刷新输出到磁盘了
(而不是全部等到提交时才刷新输出)。这也有不好的一面,COMMIT时,我们必须等待,直到尚未写出的所有缓存redo
都已经安全写到磁盘上才行。也就是说,对LGWR的调用是一个同步(synchronous)调用。尽管LGWR本身可以
使用异步I/O并行地写至日志文件,但是我们的事务会一直等待LGWR完成所有写操作,并收到数据都已在磁盘上的
确认才会返回。


现在你应该知道怎么来估计redo量,这是每一个开发人员应该具备的能力。你可以:
�� 估计你的“事务”大小(你要修改多少数据)。
�� 在要修改的数据量基础上再加10%~20%的开销,具体增加多大的开销取决于你要修改的行数。修改行越多,
    增加的开销就越小。
�� 对于UPDATE,要把这个估计值加倍。
如果你有如下的处理,就会受到块清除的影响:
�� 将大量新数据批量加载到数据仓库中;
�� 在刚刚加载的所有数据上运行UPDATE(产生需要清理的块);
�� 让人们查询这些数据。
必须知道,如果块需要清理,第一接触这个数据的查询将带来一些额外的处理。如果认识到这一点,你就应该在UPDATE之后
自己主动地“接触”数据。你刚刚加载或修改了大量的数据;现在至少需要分析这些数据。
可能要自行运行一些报告来验证数据已经加载。这些报告会完成块清 除,这样下一个查询就不必再做这个工作了。
更好的做法是:由于你刚刚批量加载了数据,现在需要以某种方式刷新统计。通过运行DBMS_STATS实用程序来收集统计,
就能很好地清理所有块,这是因为它只是使用SQL来查询信息,会在查询当中很自然地完成块清除。

日志竞争,但还是看到了这种日志文件等待,这就有其他原因了。其中最常见的原因如下:
�� redo放在一个慢速设备上:磁盘表现不佳。该购买速度更快的磁盘了。
�� redo与其他频繁访问的文件放在同一个设备上。redo设计为要采用顺序写,而且要放在专用的设备上。
如果系统的其他组件(甚至其他Oracle组件)试图与LGWR同时读写这个设备,你就会遭遇某种程度的竞争。
在此,只要有可能,你就会希望确保LGWR拥有这些设备的独占访问权限。
�� 已缓冲方式装载日志设备。你在使用一个“cooked”文件系统(而不是RAW磁盘)。
操作系统在缓冲数据,而数据库也在缓冲数据(重做日志缓冲区)。这种双缓冲会让速度慢下来。
如果可能,应该以一种“直接”方式了装载设备。具体操作依据操作系统和设备的不同而有所变化,但一般都可以直接装载。
�� redo采用了一种慢速技术,如RAID-5。RAID-5很合适读,但是用于写时表现则很差。
前面已经了解了COMMIT期间会发生什么,我们必须等待LGWR以确保数据写到磁盘上。
倘若使用的技术会导致这个工作变慢,这就不是一个好主意。


一 个表最多可以有1000列,不过我不鼓励设计中真的包含这么多列,除非存在某个硬性需求。
表中的列数远远少于1000列时才最有效。Oracle在内部会 把列数大于254的行存储在多个单独的行段(row piece)中,
这些行段相互指向,而且必须重新组装为完整的行影像。


高水平线(high-water mark,HWM)
HWM很重要,因为Oracle在全面扫描段时会扫描HWM之下的所有块,即使其中不包含任何数据。这会影响全面扫描的性能,
特别是当HWM之下的大多数块都为空时。要查看这种情况,只需创建一个有1,000,000行的表(或者创建其他有大量行的表),
然后对这个表执行一个SELECT COUNT(*)。 下面再删除(DELETE)这个表中的每一行,你会发现尽管SELECT COUNT(*)统计
出0行,但是它与统计出1,000,000所花的时间一样长(如果需要完成块清除,时间可能还会更长)。
这是因为Oracle在忙于读取HWM之下的所有块,查看其中是否包含数据。如果对这个表使用TRUNCATE而不是删除其中的每一行,
你可以比较 一下结果有什么不同。TRUNCATE会把表的HWM重置回“0”,还会截除表上的相关索引。由于以上原因,如果你打
算删除表中的所有行,就应该选择使用 TRUNCATE(如果可以使用的话)。

设置这些参数时,如果主观地采用一般经验很可能招致失败;必须根据具体的使用设置。可以考虑以 下做法(要记住,这里的“高”和“低”都是相对的;而且使用ASSM时仅PCTFREE适用):
1. 高PCTFREE,低PCTUSED:如果你插入了将要更新的大量数据,而且这些更新会频繁地增加行的大小,此时就适合采用这种设置。这种设置在插入后会在块上预留大量的空间(高PCTFREE),并使得将块放回到freelist之前必须几乎为空(低PCTUSED)。
2. 低PCTFREE,高PCTUSED:如果你只想对表完成INSERT或DELETE,或者如果你确实要完成UPDATE,但UPDATE只是缩小行的大小,此时这种设置就很适合。

select dbms_metadata.get_ddl('TABLE','T3') from dual;
这个技巧的好处是,它显示了CREATE TABLE语句的许多选项。我只需要提供数据类型,Oracle就会为我生成详细的“版本”(CREATE TABLE版本)。现在我可以定制这个详细的版本,可能把ENABLE STORAGE IN ROW改 成DISABLE STORAGE IN ROW,这样会禁用随结构化数据在行中存储LOB数据,而把LOB数据存储在另外一个段中。我一直都在使用这个技巧来节省我的时间,因为要从那个庞大的线 路图中找出该使用哪个选项很让人犯愁,如果不采用这个技巧,可能就会为此浪费好几分钟。使用这个技术还可以了解不同的情况下CREATE TABLE语句有哪些可用的选项.

既然你知道了如何查看一个给定的CREATE TABLE语句可用的大多数选项,那么对于堆表来说,需要注意哪些重要的选项呢?在我看来,对于ASSM有两个重要选项,对于MSSM,重要选项有4个:
FREELISTS: 仅适用于MSSM。每个表都会在一个freelist上管理堆中分配的块。一个表可以有多个freelist。如果你认定会有多个并发用户对表执行大量的 插入,配置多个freelist可能会大大地改善性能(可能要以额外的存储空间为代价)。这个设置对性能可能产生的影响请参见“FREELISTS”一节 中的讨论和有关例子。
PCTFREE:ASSM和MSSM都适用。在INSERT过程中,会测量块的充满程度。如前所示,根据块当前充满的程度,这个参数用于控制能否将一行增加到一个块上。这个选项还可以控制因后续更新所导致的行迁移,要根据将如何使用表来适当地设置。
PCTUSED:仅适用于MSSM。度量一个块必须为多空才允许再次插入行。如果块中已用的空间小于PCTUSED,就可以插入新行了。同样地,类似于PCTFREE,必须考虑你将如何使用表,从而适当地设置这个选项。
INITRANS:ASSM 和MSSM都适合。为块初始分配的事务槽数。如果这个选项设置得太低(默认值为2,这也是最小值),可能导致多个用户访问的一个块上出现并发问题。如果一 个数据块机会已满,而且事务表无法动态扩展,会话就会排队等待这个块,因为每个并发事务都需要一个事务槽。如果你认为会对同样的块完成多个并发更新,就应 该考虑增大这个值。

聚簇(cluster)是指:如果一组表有一些共同的列,则将这样一组表存储在相同 的数据库块中;聚簇还表示把相关的数据存储在同一个块上

临 时表(Temporary table)用于保存事务或会话期间的中间结果集。临时表中保存的数据只对当前会话可见,所有会话都看不到其他会话的数据;即使当前会话已经提交 (COMMIT)了数据,别的会话也看不到它的数据。对于临时表,不存在多用户并发问题,因为一个会话不会因为使用一个临时表而阻塞另一个会话。即使我们 “锁住”了临时表,也不会妨碍其他会话使用它们自己的临时表。我们在第9章了解到,临时表比常规表生成的redo少得多。不过,由于临时表必须为其中包含 的数据生成undo信息,所以也会生成一定的redo。UPDATE和DELETE会生成最多的undo;INSERT和SELECT生成的undo最 少.
Oracle的临时表是“静态”定义的。每个数据库只创建一次临时表,而不是为数据库中的每个存储 过程都创建一次.

ON COMMIT PRESERVE ROWS子句使得这是一个基于会话的临时表.
ON COMMIT DELETE ROWS子句使得这是一个基于事务的临时表.

create global temporary table temp_table_session
 on commit preserve rows
 as
 select * from scott.emp where 1=0
如果你曾在SQL Server和/或Sybase中用过临时表,现在所要考虑的主要问题是:不应该执行SELECT X, Y, Z INTO #TEMP FROM SOME_TABLE来动态创建和填充一个临时表,而应该:
1. 将所有全局临时表只创建一次,作为应用安装的一部分,就像是创建永久表一样。
2. 在你的过程中,只需执行INSERT INTO TEMP(X, Y, Z) SELECT X, Y, Z FROM SOME_TABLE。

select session_cnt, transaction_cnt
 from
 ( select count(*) session_cnt from temp_table_session ),
 ( select count(*) transaction_cnt from temp_table_transaction );
在许多情况下,正确的解决方案是根本不使用临时表,而是使用一个 INLINE VIEW(如上查询)
在一个查询中引用多个表是可以的;Oracle中在这个方面不需要临时表的帮助。
例如,我曾经编写过一个PALM同步应用程序,将Palm Pilot上的日期簿与Oracle中存储的日历信息同步。Palm会为我提供自最后一次热同步以来修改的所有记录的列表,我必须取得这些记录,把它们与 数据库中的当前数据相比较,更新数据库记录,然后生成一个修改列表,应用到Palm。这是一个展示临时表用处的绝好例子。我使用一个临时表在数据库中存储 Palm上所做的修改。然后运行一个存储过程,它将Palm生成的修改与当前的永久表(非常大)相比较,发现需要对Oracle数据做哪些修改,然后找出 Oracle数据库中的哪些修改需要再应用到Palm上。我必须对这个数据做两趟处理。首先,要发现仅在Palm上修改的记录,并在Oracle中做相应 的修改。接下来,要发现自最后一次同步和修改以来Palm和数据库中都经过修改的所有记录。如何发现仅在数据库中经过修改的所有记录。并将其修改放在临时 表中。最后,Palm同步应用程序从临时表拉出这些修改,把它们应用于Palm设备本身,断开连接时,临时表会消失。
不 过,我遇到的问题是,由于会分析永久表,所以使用了CBO。但是临时表上没有统计信息(尽管可以分析临时表,但不会收集统计信息),因此CBO会对它做出 很多“猜测”。作为一名开发人员,我知道可能的平均行数、数据的分布、查询选择的列等。我需要一种方法来告诉优化器这些更准确的猜测。可以有3中种方法向 优化器提供关于全局临时表的统计信息。一种方法是通过动态采样(只是Oracle9i Release 2及以上版本中新增的特性),另一种方法是使用DBMS_STATS包,它有两种做法。

B*树索引有以下子类型:
    索引组织表(index organized table):索引组织表以B*树结构存储。堆表的数据行是以一种无组织的方式存储的(只要有可用的空间,就可以放数据),而IOT与之不同,IOT中的数据要按主键的顺序存储和排序。对应用来说,IOT表现得与“常规“表并无二致;需要使用SQL来正确地访问IOT。IOT对信息获取、空间系统和OLAP应用最为有用。IOT在上一章已经详细地讨论过。
    B*树聚簇索引(B*tree cluster index)这些是传统B*树索引的一个变体(只是稍有变化)。B*树聚簇索引用于对聚簇键建立索引(见第11.章中“索引聚簇表“一节),所以这一章不再讨论。在传统B*树中,键都指向一行;而B*树聚簇不同,一个聚簇键会指向一个块,其中包含与这个聚簇键相关的多行。
    降序索引(descending index):降序索引允许数据在索引结构中按“从大到小“的顺序(降序)排序,而不是按”从小到大“的顺序(升序)排序。我们会解释为什么降序索引很重要,并说明降序索引如何工作.
    反向键索引(reverse key index):这也是B*树索引,只不过键中的字节会“反转“。利用反向键索引,如果索引中填充的是递增的值,索引条目在索引中可以得到更均匀的分布。例如,如果使用一个序列来生成主键,这个序列将生成诸如987500、987501、987502等值。这些值是顺序的,所以倘若使用一个传统的B*树索引,这些值就可能放在同一个右侧块上,这就加剧了对这一块的竞争。利用反向键,Oracle则会逻辑地对205789、105789、005789等建立索引。Oracle将数据放在索引中之前,将先把所存储数据的字节反转,这样原来可能在索引中相邻放置的值在字节反转之后就会相距很远。通过反转字节,对索引的插入就会分布到多个块上。
    位图索引(bitmap index):在一颗B*树中,通常索引条目和行之间存在一种一对一的关系:一个索引条目就指向一行。而对于位图索引,一个索引条目则使用一个位图同时指向多行。位图索引适用于高度重复而且通常只读的数据(高度重复是指相对于表中的总行数,数据只有很少的几个不同值)。考虑在一个有100万行的表中,每个列只有3个可取值:Y、N和NULL。举例来说,如果你需要频繁地统计多少行有值Y,这就很适合建立位图索引。不过并不是说如果这个表中某一列有11.000个不同的值就不能建立位图索引,这一列当然也可以建立位图索引。在一个OLTP数据库中,由于存在并发性相关的问题,所以不能考虑使用位图索引(后面我们就会讨论这一点)。注意,位图索引要求使用Oracle企业版或个人版
    位图联结索引(bitmap join index):这为索引结构(而不是表)中的数据提供了一种逆规范化的方法。例如,请考虑简单的EMP和DEPT表。有人可能会问这样一个问题:“多少人在位于波士顿的部门工作?“EMP有一个指向DEPT的外键,要想统计LOC值为Boston的部门中的员工人数,通常必须完成表联结,将LOC列联结至EMP记录来回答这个问题。通过使用位图联结索引,则可以在EMP表上对LOC列建立索引。

select a.index_name,
b.num_rows,
b.blocks,
a.clustering_factor
 from user_indexes a, user_tables b
where index_name = 'COLOCATED_PK'
 and a.table_name = b.table_name;--查看表索引等信息
可以把聚簇因子(clustering factor)看作是通过索引读取整个表时对表执行的逻辑I/O次数。也就是说,CLUSTERING_FACTOR指示了表相对于索引本身的有序程度

这实际上两个因素的一个函数,其中一个因素是通过索引需要访问表中多少数据(占多大的百分比),另一个因素是数据如何布局。如 果能完全使用索引“回答问题“(而不用表),那么访问大量的行(占很大的百分比)就是有意义的,因为这样可以避免读表所带来的额外的分散I/O。如果使用索引来访问表,可能就要确保只处理整个表中的很少一部分(只占很小的百分比)

你会发现,这里使用传统的B*树索引机制是不行的。如果你想使用一个索引来得到答案,就需要组合至少3~6个可能的B*树索引,才能通过索引访问数据。由于这3列或它们的任何子集都有可能出现,所以需要在以下列上建立很大的串联B*树索引:
�� GENDER、LOCATION和AGE_GROUP:对应使用了这3列、使用了GENDER和LOCATION或者仅使用GENDER的查询。
�� LOCATION、AGE_GROUP:对应使用了LOCATION和AGE_GROUP或者仅使用LOCATION的查询。
�� AGE_GROUP、GENDER:对应使用了AGE_GROUP和GENDER或者仅使用LOCATION的查询。
要减少搜索的数据量,还可以有其他排列,以减少所扫描索引结构的大小。这是因为在此忽略了这样一个重要事实:对这种低基数数据建立B*树索引并不明智。
这里位图索引就能派上用场了。利用分别建立在各个列上的3个较小的位图索引,就能高效地满足前面的所有谓词条件。对于引用了这3列(其中任何一例及任何子集)的任何谓词,Oracle只需对3个索引的位图使用函数AND、OR和NOT,就能得到相应的结果集

set autotrace traceonly explain


一般而言,如果是一个很大的环境,主要是只读操作,并且有大量即席查询,你所需要的可能正是一组位图索引。

create index emp_upper_idx on emp(upper(ename));
set autotrace traceonly explain
select *
  from emp
 where upper(ename) = 'KING';

在这个函数中,我们使用了一个新的关键字DETERMINISTIC。这就声明了:前面这个函数在给定相同的输入时,总会返回完全相同的输出。要在一个用户编写的函数上创建索引,这个关键字是必要的。我们必须告诉Oracle这个函数是确定性的(DETERMINISTIC),而且在给定相同输入的情况下总会返回一致的结果。通过这个关键字,就是在告诉Oracle:可以相信这个函数,给定相同的输入,不论做多少次调用,它肯定能返回相同的值。如果不是这样,通过索引访问数据时就会得到与全表扫描不同的答案。这种确定性设置表明在有些函数上是不能建立索引的,例如,我们无法在函数DBMS_RANDOM.RANDOM上创建索引,因为这是一个随机数生成器。函数DBMS_RANDOM.RANDOM的结果不是确定性的;给定相同的输入,我们会得到随机的输出。另一方面,第一个例子中所用的内置SQL函数UPPER则是确定性的,所有可以在列的UPPER值上创建一个索引。

create index emp_soundex_idx on
emp( substr(my_soundex(ename),1,6) );
由于知道MY_SOUNDEX最多返回6个字符,所以取前6个字符作为字串。
在这个CREATE INDEX命令中,有意思的是在此使用了SUBSTR函数。这是因为,我们在对一个返回串的函数建索引。如果对一个返回数字或日期的函数建索引,就没有必须使用这个SUBSTR。如果用户编写的函数返回一个串,之所以要对这样一个函数使用SUBSTR,原因是这种函数会返回VARCHAR2(4000)类型。这就太大了,无法建立索引,索引条目必须能在块大小的3/4中放得下.

位图索引不适用于并发修改。如果考虑到这个表中会不断地将N更新为Y,那位图就更不合适了,根本不应考虑,因为这个过程会完全串行化。
我们要让索引更小一些,而且要更易维护(更新期间的运行时开销更少)。采用基于函数的索引,我们可以编写一个函数,如果不想对某个给定行加索引,则这个函数就返回NULL;而对想加索引的行则返回一个非NULL值。例如,由于我们只对列值为N的记录感兴趣,所以只对这些记录加索引:

create index processed_flag_idx
on big_table( case temporary when 'N' then 'N' end );

假设有一个带版本信息的表,如项目表。项目有两种状态:要么为ACTIVE,要么为INACTIVE。需要保证以下规则:“活动的项目必须有一个惟一名;而不活动的项目无此要求。”也就是说,只有一个活动的“项目X”,但是如果你愿意,可以有多个名为X的不活动项目。
创建一个UNIQUE索引,基于这几点,可以很容易做到:
Create unique index active_projects_must_be_unique
On projects ( case when status = 'ACTIVE' then name end );
这就行了。状态(status)列是ACTIVE时,NAME列将建立惟一的索引。如果试图创建同名的活动项目,就会被检测到,而且这根本不会影响对这个表的并发访问。

倘若优化器没有使用你的基于函数的索引,而且你对此无法做出解释,不知道为什么没有使用你的函数,在这种情况下,就可以检查USER_IND_EXPRESSIONS视图,验证使用的函数是否正确。
 select column_expression
   from user_ind_expressions
  where index_name = 'T_IDX';


create table t ( x int, y int NOT NULL );
create unique index t_idx on t(x,y);
前面我说过,B*树索引中不存储完全为null的条目,而且你可以充分利用这一点,以上就展示了应当如何加以利用。假设你有一个表,其中每一列只有两个可取值。这些值分布得很不均匀,例如,90%以
566 / 860上的行(多数行)都取某个值,而另外不到11.%的行(少数行)取另外一个值。可以有效地对这个列建立索引,来快速访问那些少数行。如果你想使用一个索引访问少数行,同时又想通过全面扫描来访问多数行,另外还想节省空间,这个特性就很有用。解决方案是:对多数行使用null,而对少数行使用你希望的任何值;或者如前所示,使用一个基于函数的索引,只索引函数的非null返回值。

select *
from dept, emp
where emp.deptno = dept.deptno
and dept.dname = :X;
你会发现,如果没有索引会使查询减慢。由于同样的原因,我在第11.章曾建议对嵌套表中的NESTED_COLUMN_ID加索引。嵌套表的隐藏列NESTED_COLUMN_ID实际上就是一个外键。
那么,什么时候不需要对外键加索引呢?一般来说,如果满足以下条件则可如此:
�� 未删除父表中的行。
�� 不论是有意还是无意(如通过一个工具),总之未更新父表的惟一/主键值。
�� 不论从父表联结到子表,或者更一般地讲,外键列不支持子表的一个重要的访问途径,而且你在谓词中没有使用这些外键列从子表中选择数据(如DEPT到EMP)。
如果满足上述所有3个条件,就完全可以不加索引,也就是说,对外键加索引是不必要的,还会减慢子表上DML操作的速度。如果满足了其中某个条件,就要当心不加索引的后果。


情况1
我们在使用一个B*树索引,而且谓词中没有使用索引的最前列。如果是这种情况,可以假设有一个表T,在T(X,Y)上有一个索引。我们要做以下查询:SELECT * FROM T WHERE Y = 5。此时,优化器就不打算使用T(x,y)上的索引,因为谓词中不涉及X列。在这种情况下,倘若使用索引,可能就必须查看每一个索引条目(稍后我们会讨论一种索引跳跃式扫描,这是一种例外情况),而优化器通常更倾向于T对做一个全表扫描。但这并不完全排除使用索引。如果查询是SELECT X, Y FROM T WHERE Y = 5,优化器就会注意到,它不必全面扫描表来得到X或Y(X和Y都在索引中),对索引本身做一个快速的全面扫描会更合适,因为这个索引一般比底层表小得多。还要注意,仅CBO能使用这个访问路径。
    另一种情况下CBO也会使用T(x,y)上的索引,这就是索引跳跃式扫描。当且仅当索引的最前列(在上一个例子中,最前列就是Y)只有很少的几个不同值,而且优化器了解这一点,跳跃式扫描(skip scan)就能很好地发挥作用。例如,考虑(GENDER, EMPNO)上的一个索引,其中GENDER可取值有M和F,而且EMPNO是惟一的。对于以下查询:
select * from t where empno = 5;

情况2
我们在使用一个SELECT COUNT(*) FROM T查询(或类似的查询),而且在表T上有一个B*树索引。不过,优化器并不是统计索引条目,而是在全面扫描这个表(尽管索引比表要小)。在这种情况下,索引可能建立在一些允许有null值的列上。由于对于索引键完全为null的行不会建立相应的索引条目,所以索引中的行数可能并不是表中的行数。这里优化器的选择是对的,如若不然,倘若它使用索引来统计行数,则可能会得到错误的答案。

情况3
对于一个有索引的列,做以下查询:
select * from t where f(indexed_column) = value
却发现没有使用INDEX_COLUMN上的索引。原因是这个列上使用了函数。我们是对INDEX_COLUMN的值建立了索引,而不是对F(INDEXED_COLUMN)的值建索引。在此不能使用这个索引。如果愿意,可以另外对函数建立索引。

情况4
我们已经对一个字符创建了索引。这个列只包含数值数据。如果所用以下语句来查询:
select * from t where indexed_column = 5
注意查询中的数字5是常数5(而不是一个字符串),此时就没有使用INDEX_COLUMN上的索引。这是因为,前面的查询等价于一些查询:
select * from t where to_number(indexed_column) = 5
我们对这个列隐式地应用了一个函数,如情况3所述,这就会禁止使用这个索引。
在此使用了索引,但是并不像我们想像中那样对索引完成惟一扫描(UNIQUE SCAN),而是完成了全面扫描(FULL SCAN)。原因从最后一行输出可以看出:filter(TO_NUMBER(“X”)=5)。这里对这个数据库列应用了一个隐式函数。X中存储的字符串必须转换为一个数字,之后才能与值5进行比较。在此无法把5转换为一个串,因为我们的NLS(国家语言支持)设置会控制5转换成串时的具体形式(而这是不确定的,不同的NLS设置会有不同的控制),所以应当把串转换为数字。

 

正是由于以下这些原因:定宽的存储空间可能导致表和相关索引比平常大出许多,还伴随着绑定变量问题,
所以无论什么场合我都会避免使用CHAR类型。即便是对单字符的字段,我也想不出有什么必要使用CHAR类型,
因为在这种情况下,这两种类型确实没有显著差异。VARCHAR2(1)和CHAR(1)从任何方面来讲都完全相同。
此时,使用CHAR类型并没有什么有说服力的理由,为了避免混淆,所以我“一律排斥“,即使是CHAR(1)字段
(即单字符字段)也不建议使用CHAR类型。


VARCHAR2(1)的单位是字节,而不是字符。这里确实只有一个Unicode字符,但是它在一个字节中放不下。
将应用从单字节定宽字符集移植到一个多字节字符集时,可能会发现原来在字段中能放下的文本现在却无法放下。
varchar2(1 char)单位是字符。

通过使用内置的CAST函数,可以对Oracle NUMBER类型执行一种实时的转换,
在对其执行复杂数学运算之前先将其转换为一种浮点数类型。这样一来,所用CPU时间就与
使用固有浮点类型所用的CPU时间非常接近:
select sum(ln(cast( num_type as binary_double ) )) from t
这说明,我们可以非常精确地存储数据,如果需要提供速度,浮点类型则远远超过Oracle NUMBER类型,
此时可以使用CAST函数来达到提速的目标。

使用Oracle DATE类型时,我有以下建议:
􀂉 使用NUMTODSINTERVAL内置函数来增加小时、分钟和秒。
DATE+NUMTODSINTERVAL(n,'second')--增加n秒
DATE+NUMTODSINTERVAL(n,'minute')--增加n分
DATE+NUMTODSINTERVAL(n,'hour')--增加n小时
􀂉 加一个简单的数来增加天。--date+n
􀂉 使用ADD_MONTHS内置函数来增加月和年。
N月 ADD_MONTHS(DATE,n)
N年 ADD_MONTHS(DATE,12.*n)
我建议不要使用NUMTOYMINTERVAL函数。其原因与这个函数如何处理月末日期有关。
select numtoyminterval
       (months_between(dt2,dt1),'month')
           years_months,
       numtodsinterval
           (dt2-add_months( dt1, trunc(months_between(dt2,dt1)) ),
            'day' )
           days_hours
  from (select to_date('29-feb-2000 01:02:03','dd-mon-yyyy hh24:mi:ss') dt1,
               to_date('15-mar-2001 11:22:33','dd-mon-yyyy hh24:mi:ss') dt2
          from dual )
/


如今,由于ROWID可能过一段时间会改变(因为它不再是不可变的),所以不建议把它们作为单独的列物理地存储在数据库表中。也就是说,使用ROWID作为一个数据库列的数据类型被认为是一种不好的实践做法。应当避免这种做法,而应使用行的主键(这应该是不可变的),另外引用完整性可以确保数据的完整性。对此用ROWID类型是做不到的,不能用ROWID创建从子表到一个父表的外键,而且不能保证跨表的完整性。你必须使用主键约束。
那ROWID类型有什么用呢?在允许最终用户与数据交互的应用中,ROWID还是有用的。ROWID作为行的一个物理地址,要访问任何表中的某一行,这是最快的方法。如果应用从数据库读出数据并将其提供给最终用户,它试图更新这一行时就可以使用ROWID。应用这种方式,只需最少的工作就可以更新
当前行(例如,不需要索引查找再次寻找行),并通过验证行值未被修改来确保这一行与最初读出的行是同一行。所以,在采用乐观锁定的应用中ROWID还是有用的。

posted @ 2014-04-10 11:03  英雄饶命啊  阅读(191)  评论(0编辑  收藏  举报