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) 编辑收藏举报

刷新页面返回顶部

oracle 笔记

公告