ORACLE常用问题1000问全集(二)

原创 2004年08月13日 09:54:00
101. 如何搜索出前N条记录?                                             
  SELECT * FROM empLOYEE WHERE ROWNUM < n

  ORDER BY empno;                                               
  102. 如何知道机器上的Oracle支持多少并发用户数?                   
    SQL>conn internal ;

  SQL>show parameter processes ;

  103. db_block_size可以修改吗?                             
    一般不可以﹐不建议这样做的。                                             
  104. 如何统计两个表的记录总数?                                         
    select (select c
dual;
ount(id) from aa)+(select co

unt(id) from bb) 总数 from

  105. 怎样用Sql语句实现查找一列中第N大值?                           
    select * from                                               
  (select t.*,dense_r
ank() over (order by sal) ra
nk from employee)
  where rank = N;                                               
  106. 如何在给现有的日期加上2年?(                                     
    select add_month
s(sysdate,24) from dual;

  107. USED_UBLK为负值表示什么意思?                             
    It is "harmless"。                                       
  108. Connect string是指什么?                             
      应该是tnsnames.ora中的服务名后面的内容                   
  109. 怎样扩大REDO LOG的大小?                                   
      建立一个临时的redolog组,
然后切换日志,删除以前的日志,建立新的日志。
  110. tablespace 是否不能大于4G?                           
    没有限制。                                                               
  111. 返回大于等于N的最小整数值?                                       
    SELECT CEIL(N) FROM DUAL;                       
  112. 返回小于等于N的最小整数值?                                       
    SELECT FLOOR(N) FROM DUAL;                     
  113. 返回当前月的最后一天?                                             
    SELECT LAST_DAY(SYSDATE) FROM DUAL;   
  114. 如何不同用户间数据导入?                                           
    IMP SYSTEM/MANAGER FILE=AA.D
INDEXES=Y ;
MP FROMUSER=USER_OLD TOUSER=USER_NEW ROWS=Y

  115. 如何找数据库表的主键字段的名称?                                   
    SQL>SELECT * FROM user_constraints WHERE CONSTRAINT_TYPE='P' and table_name='TABLE_NAME';

  116. 两个结果集互加的函数?                                             
    SQL>SELECT * FROM BSEMPMS_OLD INTERSECT SELECT * FROM BSEMPMS_NEW;

  SQL>SELECT * FROM BSEMPMS_OLD UNION SELECT * FROM BSEMPMS_NEW

  SQL>SELECT * FROM BSEMPMS_OLD UNION ALL SELECT * FROM BSEMPMS_NEW;

  117. 两个结果集互减的函数?                                             
  SQL>SELECT * FROM BSEMPMS_OLD MINUS SELECT * FROM BSEMPMS_NEW;

  118. 如何配置Sequence?                                         
    建sequence seq_custid                                 
    create sequence
seq_custid start 1 incrememt
by 1;
    建表时:                                                                 
    create table cust                                       
    { cust_id smallint not null,                 
    ...}                                                                 
    insert 时:                                                       
    insert into table cust                             
    values( seq_cust.nextval,...)               
  日期的各部分的常用的的写法                                                   
  119. 取时间点的年份的写法:                                             
     SELECT TO_CHAR(SYSDATE,'YY
YY') FROM DUAL;
  120. 取时间点的月份的写法:                                             
     SELECT TO_CHAR(
SYSDATE,'MM') FROM DUAL;

  121. 取时间点的日的写法:                                               
     SELECT TO_CHAR(
SYSDATE,'DD') FROM DUAL;

  122. 取时间点的时的写法:                                               
     SELECT TO_CHAR(
SYSDATE,'HH24') FROM DUAL;

  123. 取时间点的分的写法:                                               
     SELECT TO_CHAR(SYSDATE,'MI
') FROM DUAL;
  124. 取时间点的秒的写法:                                               
     SELECT TO_CHAR(SYSDATE,'SS
') FROM DUAL;
  125. 取时间点的日期的写法:                                             
     SELECT TRUNC(SYSDATE) FROM DUAL;       
  126. 取时间点的时间的写法:                                             
     SELECT TO_CHAR(
SYSDATE,'HH24:MI:SS') FRO
M DUAL;
  127. 日期,时间形态变为字符形态:                                       
     SELECT TO_CHAR(SYSDATE) FROM DUAL;   
  128. 将字符串转换成日期或时间形态:                                     
     SELECT TO_DATE(
'2003/08/01') FROM DUAL;

  129. 返回参数的星期几的写法:                                           
     SELECT TO_CHAR(SYSDATE,'D'
) FROM DUAL;
  130. 返回参数一年中的第几天的写法:                                     
     SELECT TO_CHAR(
SYSDATE,'DDD') FROM DUAL;

  131. 返回午夜和参数中指定的时间值之间的秒数的写法:                     
     SELECT TO_CHAR(
SYSDATE,'SSSSS') FROM DUAL

  132. 返回参数中一年的第几周的写法:                                     
     SELECT TO_CHAR(SYSDATE,'WW
') FROM DUAL;
  虚拟字段                                                                     
  133. CURRVAL 和 nextval                                 
    为表创建序列                                                             
    CREATE SEQUENCE EMPSEQ ... ;                 
    SELECT empseq.currval FROM DUAL ;       
    自动插入序列的数值                                                       
    INSERT INTO emp                                           
      VALUES (emps
eq.nextval, 'LEWIS', 'CLERK'
,
          7902, SYSDATE, 1
200, NULL, 20) ;
  134. ROWNUM                                                       
    按设定排序的行的序号                                                     
    SELECT * FROM emp WHERE ROWNUM < 10 ;

  135. ROWID                                                         
    返回行的物理地址                                                         
    SELECT ROWID, en
ame FROM emp WHERE deptno =
20 ;
  136. 将N秒转换为时分秒格式?                                           
    set serverout on                                         
    declare                                                           
     N number := 1000000;                               
     ret varchar2(100);                                   
    begin                                                               
     ret := trunc(
600),'sssss'),'fmmi"分 "
n/3600) || '小时' || to_char
ss"秒"') ;
(to_date(mod(n,3

     dbms_output.put_line(ret);                   
    end;                                                                 
  137. 如何查询做比较大的排序的进程?                                     
    SELECT b.tablespace, b.segfi
a.username, a.osuser, a.status
le#, b.segblk#, b.blocks, a.sid, a.serial#,

    FROM v$session a,v$sort_usage b           
    WHERE a.saddr = b.session_addr             
    ORDER BY b.table
space, b.segfile#, b.segblk#
, b.blocks ;
  138. 如何查询做比较大的排序的进程的SQL语句?                         
    select /*+ ORDERED */ sql_te
xt from v$sqltext a
    where a.hash_value = (                             
     select sql_hash_value from
v$session b
     where b.sid =
&sid and b.serial# = &serial
)
    order by piece asc ;                                 
  139. 如何查找重复记录?                                                 
    SELECT * FROM TABLE_NAME                         
    WHERE ROWID!=(SE
LECT MAX(ROWID) FROM TABLE_N
AME D
     WHERE TABLE_NAME.COL1=D.CO
L1 AND TABLE_NAME.COL2=D.COL2);
  140. 如何删除重复记录?                                                 
    DELETE FROM TABLE_NAME                             
    WHERE ROWID!=(SELECT MAX(ROW
ID) FROM TABLE_NAME D
    WHERE TABLE_NAME
.COL1=D.COL1 AND TABLE_NAME.
COL2=D.COL2);
  141. 如何快速编译所有视图?                                             
    SQL >SPOOL VIEW1.SQL

    SQL >SELECT ‘ALTER VIEW ‘||TNAME||’

    COMPILE;’ FROM TAB;                                   
    SQL >SPOOL OFF

    然后执行VIEW1.SQL即可。                                         
    SQL >@VIEW1.SQL;

  142. ORA-01555 SNAPSHOT TOO OLD的解决办法     
    增加MINEXTENTS的
值,增加区的大小,设置一个高
的OPTIMAL值。
  143. 事务要求的回滚
到达参数 MAXEXTENTS的值
段空间不够,表现为表空间用满
(ORA-01628)的解决办法。
(ORA-01560错误),回滚段扩展

    向回滚段表空间添加文件或使已有的文件变大;增加MAXEXTENTS的值。
  144. 如何加密ORACLE的存储过程?                                   
    下列存储过程内容放在AA.SQL文件中                                   
    create or replac
e procedure testCCB(i in num
ber) as
    begin                                                               
     dbms_output.pu
t_line('输入参数是'||to_char
(i));
    end;                                                                 
    SQL>wrap iname=a.sql;

    PL/SQL Wrapper: Release 8.1
2001
.7.0.0 - Production on Tue Nov 27 22:26:48

    Copyright (c) Or
acle Corporation 1993, 2000.
All Rights Reserved。
    Processing AA.sql to AA.plb                   
    运行AA.plb                                                         
    SQL> @AA.plb ;

  145. 如何监控事例的等待?                                               
    select event,sum
(decode(wait_Time,0,0,1)) "P
rev",
    sum(decode(wait_Time,0,1,0))
"Curr",count(*) "Tot"
    from v$session_Wait                                   
    group by event order by 4;                     
  146. 如何回滚段的争用情况?                                             
    select name, wai
ts, gets, waits/gets "Ratio"

    from v$rollstat C, v$rollname D           
    where C.usn = D.usn;                                 
  147. 如何监控表空间的 I/O 比例?                                   
    select B.tablespace_name nam
A.phyblkrd pbr, A.phywrts pyw, A.phy
e, B.file_name "file", A.phyrds pyr,
blkwrt pbw
    from v$filestat A, dba_data_files B   
    where A.file# = B.file_id                       
    order by B.tablespace_name;                   
  148. 如何监控文件系统的 I/O 比例?                                 
    select substr(C.
C.bytes, D.phyrds, D.phy
file#,1,2) "#", substr(C.nam
wrts
e,1,30) "Name", C.status,

    from v$datafile C, v$filestat D           
    where C.file# = D.file#;                         
  149. 如何在某个用户下找所有的索引?                                     
    select user_inde
column_name
xes.table_name, user_indexes

.index_name,uniqueness,

    from user_ind_columns, user_indexes   
    where user_ind_columns.index
_name = user_indexes.index_name
     and user_ind_c
olumns.table_name = user_ind
exes.table_name
    order by user_indexes.table_
type, user_indexes.table_name,
    user_indexes.index_name, col
umn_position;
  150. 如何监控 SGA 的命中率?                                       
    select a.value +
b.value "logical_reads", c.
value "phys_reads",
    round(100 * ((a.value+b.valu
RATIO"
e)-c.value) / (a.value+b.value)) "BUFFER HIT

    from v$sysstat a, v$sysstat
b, v$sysstat c
    where a.statistic# = 38 and
b.statistic# = 39
     and c.statistic# = 40;                           
  151. 如何监控 SGA 中字典缓冲区的命中率?                           
    select parameter, gets,Getmi
ratio",
sses , getmisses/(gets+getmisses)*100 "miss

    (1-(sum(getmisses)/ (sum(get
s)+sum(getmisses))))*100 "Hit ratio"
    from v$rowcache                                           
    where gets+getmisses <>0

    group by parameter, gets, getmisses;
  152. 如何监控 SGA 中共享缓存区的命中率,应该小于1% ?           
    select sum(pins)
"Total Pins", sum(reloads)
"Total Reloads",
    sum(reloads)/sum(pins) *100 libcache
    from v$librarycache;                                 
    select sum(pinhi
"reload percent"
ts-reloads)/sum(pins) "hit r

adio",sum(reloads)/sum(pins)

    from v$librarycache;                                 
  153. 如何显示所有数据库对象的类别和大小?                               
    select count(name) num_insta
nces ,type ,sum(source_size) source_size ,
    sum(parsed_size) parsed_size
error_size,
,sum(code_size) code_size ,sum(error_size)

    sum(source_size) +sum(parsed
size_required
_size) +sum(code_size) +sum(error_size)

    from dba_object_size                                 
    group by type order by 2;                       
  154. 监控 SGA 中重做日志缓存区的命中率,应该小于1%               
    SELECT name, gets, misses, i
mmediate_gets, immediate_misses,
    Decode(gets,0,0,
misses/gets*100) ratio1,

    Decode(immediate_gets+immedi
ate_misses,0,0,
    immediate_misses/(immediate_
gets+immediate_misses)*100) ratio2
    FROM v$latch WHE
RE name IN ('redo allocation
', 'redo copy');
  155. 监控内存和硬盘的排序比率,
最好使它小于 .10,增加 sort_area_size
    SELECT name, val
'sorts(disk)');
ue FROM v$sysstat WHERE name

IN ('sorts (memory)',

  156. 如何监控当前数据库谁在运行什么SQL语句?                         
    SELECT osuser, username, sql
_text from v$session a, v$sqltext b
    where a.sql_address =b.addre
ss order by address, piece;
  157. 如何监控字典缓冲区?                                               
    SELECT (SUM(PINS

- RELOADS)) / SUM(PINS) "LI

B CACHE" FROM V$LIBRARYCACHE;

    SELECT (SUM(GETS - GETMISSES
FROM V$ROWCACHE;
- USAGE - FIXED)) / SUM(GETS) "ROW CACHE"

    SELECT SUM(PINS) "EXECUTIONS
EXECUTING" FROM V$LIBRARYCACHE;
", SUM(RELOADS) "CACHE MISSES WHILE

    后者除以前者,此比率小于1%,接近0%为好。                             
    SELECT SUM(GETS)
MISSES"
"DICTIONARY GETS",SUM(GETMI

SSES) "DICTIONARY CACHE GET

    FROM V$ROWCACHE                                           
  158. 监控 MTS                                                       
    select busy/(busy+idle) "sha
red servers busy" from v$dispatcher;
    此值大于0.5时,参数需加大                                             
    select sum(wait)
type='dispatcher';
/sum(totalq) "dispatcher wai

ts" from v$queue where

    select count(*) from v$dispatcher;     
    select servers_highwater from v$mts;
    servers_highwater接近mts_max
_servers时,参数需加大
  159. 如何知道当前用户的ID号?                                         
    SQL>SHOW USER;

    或 SQL>select user from dual;

  160. 如何查看碎片程度高的表?                                           
    SELECT segment_name table_na
me , COUNT(*) extents
    FROM dba_segment
segment_name
s WHERE owner NOT IN ('SYS',

'SYSTEM') GROUP BY

    HAVING COUNT(*)
segment_name);
= (SELECT MAX( COUNT(*) ) FR

OM dba_segments GROUP BY

  162. 如何知道表在表空间中的存储情况?                                   
    select segment_n
ame,sum(bytes),count(*) ext_
quan from dba_extents where
    tablespace_name='&tablespace
tablespace_name,segment_name;
_name' and segment_type='TABLE' group by

  163. 如何知道索引在表空间中的存储情况?                                 
    select segment_name,count(*)
and owner='&owner'
from dba_extents where segment_type='INDEX'

    group by segment_name;                             
  164、如何知道使用CPU多的用户session?                           
    11是cpu used by this session                   
    select a.sid,spi
nal,osuser,value/60/100
d,status,substr(a.program,1,
value
40) prog,a.termi

    from v$session a
,v$process b,v$sesstat c

    where c.statisti
desc;
c#=11 and c.sid=a.sid and a.

paddr=b.addr order by value

  165. 如何知道监听器日志文件?                                           
    以8I为例                                                               
    $ORACLE_HOME/NET
WORK/LOG/LISTENER.LOG

  166. 如何知道监听器参数文件?                                           
    以8I为例                                                               
    $ORACLE_HOME/NETWORK/ADMIN/L
ISTENER.ORA
  167. 如何知道TNS 连接文件?                                         
    以8I为例                                                               
    $ORACLE_HOME/NET
WORK/ADMIN/TNSNAMES.ORA

  168. 如何知道Sql*Net 环境文件?                                 
    以8I为例                                                               
    $ORACLE_HOME/NET
WORK/ADMIN/SQLNET.ORA

  169. 如何知道警告日志文件?                                             
    以8I为例                                                               
    $ORACLE_HOME/ADMIN/SID/BDUMP
/SIDALRT.LOG
  170. 如何知道基本结构?                                                 
    以8I为例                                                               
    $ORACLE_HOME/RDB
MS/ADMIN/STANDARD.SQL

  171. 如何知道建立数据字典视图?                                         
    以8I为例                                                               
    $ORACLE_HOME/RDBMS/ADMIN/CATALOG.SQL
  172. 如何知道建立审计用数据字典视图?                                   
    以8I为例                                                               
    $ORACLE_HOME/RDB
MS/ADMIN/CATAUDIT.SQL

  173. 如何知道建立快照用数据字典视图?                                   
    以8I为例                                                               
    $ORACLE_HOME/RDBMS/ADMIN/CATSNAP.SQL
  SQL语句的优化方法                                                         
  174. /*+ALL_ROWS*/                                         
     表明对语句块选

择基于开销的优化方法,并获得

最佳吞吐量,使资源消耗最小化。

     例如:                                                                 
     SELECT /*+ALL+_ROWS*/ EMP_N
EMP_NO='CCBZZP';
O,EMP_NAM,DAT_IN FROM BSEMPMS WHERE

  175. /*+FIRST_ROWS*/                                     
     表明对语句块选择基于开销的

优化方法,并获得最佳响应时间,使资源消耗最小化

     例如:                                                                 
     SELECT /*+FIRST_ROWS*/ EMP_
EMP_NO='CCBZZP';
NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE

  176. /*+CHOOSE*/                                             
     表明如果数据字典中有访问表
吞吐量;
的统计信息,将基于开销的优化方法,并获得最佳的

     表明如果数据字典中没有访问表的统计信息,将基于规则开销的优化方法;     
     例如:                                                                 
     SELECT /*+CHOOSE*/ EMP_NO,
EMP_NO='CCBZZP';
EMP_NAM,DAT_IN FROM BSEMPMS WHERE

  177. /*+RULE*/                                                 
     表明对语句块选择基于规则的优化方法。                                   
     例如:                                                                 
     SELECT /*+ RULE */ EMP_NO,
EMP_NO='CCBZZP';
EMP_NAM,DAT_IN FROM BSEMPMS WHERE

  178. /*+FULL(TABLE)*/                                   
     表明对表选择全局扫描的方法。                                           
     例如:                                                                 
     SELECT /*+FULL(

A)*/ EMP_NO,EMP_NAM FROM BS

EMPMS A WHERE EMP_NO='CCBZZP'

  179. /*+ROWID(TABLE)*/                                 
     提示明确表明对指定表根据ROWID进行访问。                           
     例如:                                                                 
     SELECT /*+ROWID(BSEMPMS)*/ * FROM BSEMPMS WHERE ROWID>='AAAAAAAAAAAAAA'

     AND EMP_NO='CCBZZP';                               
  180. /*+CLUSTER(TABLE)*/                             
     提示明确表明对指定表选择簇扫描的访问方法,它只对簇对象有效。           
     例如:                                                                 
     SELECT /*+CLUSTER */ BSEMPM
S.EMP_NO,DPT_NO FROM BSEMPMS,BSDPTMS
     WHERE DPT_NO='TEC304' AND B
SEMPMS.DPT_NO=BSDPTMS.DPT_NO;
  181. /*+INDEX(TABLE INDEX_NAME)*/           
     表明对表选择索引的扫描方法。                                           
     例如:                                                                 
     SELECT /*+INDEX(BSEMPMS SEX
FEWMALE BSEMPMS */ FROM BSEMPMS WHER
_INDEX) USE SEX_INDEX BECAUSE THERE ARE
E SEX='M';
  182. /*+INDEX_ASC(TABLE INDEX_NAME)*/   
     表明对表选择索引升序的扫描方法。                                       
     例如:                                                                 
     SELECT /*+INDEX_ASC(BSEMPMS
DPT_NO='CCBZZP';
PK_BSEMPMS) */ FROM BSEMPMS WHERE

  183. /*+INDEX_COMBINE*/                               
     为指定表选择位图访问路经,
选择出位图索引的
如果INDEX_COMBINE中没有提供作为参数的索引,将

     布尔组合方式。                                                         
     例如:                                                                 
     SELECT /*+INDEX_COMBINE(BSE
MPMS SAL_BMI HIREDATE_BMI)*/ * FROM BSEMPMS
     WHERE SAL<5000000 AND HIREDATE

  184. /*+INDEX_JOIN(TABLE INDEX_NAME)*/
     提示明确命令优化器使用索引作为访问路径。                               
     例如:                                                                 
     SELECT /*+INDEX_JOIN(BSEMPM
S SAL_HMI HIREDATE_BMI)*/ SAL,HIREDATE
     FROM BSEMPMS WHERE SAL<60000;

  185. /*+INDEX_DESC(TABLE INDEX_NAME)*/
     表明对表选择索引降序的扫描方法。                                       
     例如:                                                                 
     SELECT /*+INDEX
DPT_NO='CCBZZP';
_DESC(BSEMPMS PK_BSEMPMS) */

FROM BSEMPMS WHERE

  186. /*+INDEX_FFS(TABLE INDEX_NAME)*/   
     对指定的表执行快速全索引扫描,而不是全表扫描的办法。                   
     例如:                                                                 
     SELECT /*+INDEX
DPT_NO='TEC305';
_FFS(BSEMPMS IN_EMPNAM)*/ *

FROM BSEMPMS WHERE

  187. /*+ADD_EQUAL TABLE INDEX_NA
M1,INDEX_NAM2,...*/
     提示明确进行执行规划的选择,将几个单列索引的扫描合起来。               
     例如:                                                                 
     SELECT /*+INDEX
WHERE EMP_NO='CCBZZP' AN
_FFS(BSEMPMS IN_DPTNO,IN_EM
D DPT_NO='TDC306';
PNO,IN_SEX)*/ * FROM BSEMPMS

  188. /*+USE_CONCAT*/                                     
     对查询中的WHERE
后面的OR条件进行转换为UNION
ALL的组合查询。
     例如:                                                                 
     SELECT /*+USE_C

ONCAT*/ * FROM BSEMPMS WHERE

DPT_NO='TDC506' AND SEX='M';

  189. /*+NO_EXPAND*/                                       
     对于WHERE后面的OR 或者IN-LI
进行扩展。
ST的查询语句,NO_EXPAND将阻止其基于优化器对其

     例如:                                                                 
     SELECT /*+NO_EXPAND*/ * FRO
M BSEMPMS WHERE DPT_NO='TDC506' AND SEX='M';
  190. /*+NOWRITE*/                                           
     禁止对查询块的查询重写操作。                                           
  191. /*+REWRITE*/                                           
     可以将视图作为参数。                                                   
  192. /*+MERGE(TABLE)*/                                 
     能够对视图的各个查询进行相应的合并。                                   
     例如:                                                                 
     SELECT /*+MERGE(V) */ A.EMP_NO,A.EMP_NAM,B.DPT_NO FROM BSEMPMS A (SELET DPT_NO,AVG(SAL) AS AVG_SAL FROM BSEMPMS B GROUP BY DPT_NO) V WHERE A.DPT_NO=V.DPT_NO AND A.SAL>V.AVG_SAL;

  193. /*+NO_MERGE(TABLE)*/                           
     对于有可合并的视图不再合并。                                           
     例如:                                                                 
     SELECT /*+NO_MERGE(V) */ A.EMP_NO,A.EMP_NAM,B.DPT_NO FROM BSEMPMS A (SELET DPT_NO,AVG(SAL) AS AVG_SAL FROM BSEMPMS B GROUP BY DPT_NO) V WHERE A.DPT_NO=V.DPT_NO AND A.SAL>V.AVG_SAL;

   194. /*+ORDERED*/                                         
     根据表出现在FRO
M中的顺序,ORDERED使ORACLE依
此顺序对其连接。
     例如:                                                                 
     SELECT /*+ORDERED*/ A.COL1
TABLE3 C WHERE A.COL1=B.COL1 AND B.C
,B.COL2,C.COL3 FROM TABLE1 A,TABLE2 B,
OL1=C.COL1;
  195. /*+USE_NL(TABLE)*/                               
     将指定表与嵌套的连接的行源进行连接,并把指定表作为内部表。             
     例如:                                                                 
     SELECT /*+ORDER
BSEMPMS.EMP_NAM FROM BSE
ED USE_NL(BSEMPMS)*/ BSDPTMS
MPMS,BSDPTMS WHERE BSEMPMS.
.DPT_NO,BSEMPMS.EMP_NO,
DPT_NO=BSDPTMS.DPT_NO;
  196. /*+USE_MERGE(TABLE)*/                         
     将指定的表与其它行源通过合并排序连接方式连接起来。                     
     例如:                                                                 
     SELECT /*+USE_M
BSEMPMS.DPT_NO=BSDPTMS.D
ERGE(BSEMPMS,BSDPTMS)*/ * F
PT_NO;
ROM BSEMPMS,BSDPTMS WHERE

  197. /*+USE_HASH(TABLE)*/                           
     将指定的表与其它行源通过哈希连接方式连接起来。                         
     例如:                                                                 
     SELECT /*+USE_H
BSEMPMS.DPT_NO=BSDPTMS.D
ASH(BSEMPMS,BSDPTMS)*/ * FR
PT_NO;
OM BSEMPMS,BSDPTMS WHERE

  198. /*+DRIVING_SITE(TABLE)*/                   
     强制与ORACLE所选择的位置不同的表进行查询执行。                   
     例如:                                                                 
     SELECT /*+DRIVING_SITE(DEPT
BSEMPMS.DPT_NO=DEPT.DPT_NO;199. /*+
)*/ * FROM BSEMPMS,DEPT@BSDPTMS WHERE
LEADING(TABLE)*/
     将指定的表作为连接次序中的首表。                                       
                                                                              
  200. /*+CACHE(TABLE)*/                                 
     当进行全表扫描时,CACHE提示
LRU的最近使用端
能够将表的检索块放置在缓冲区缓存中最近最少列表

     例如:                                                                 
     SELECT /*+FULL(BSEMPMS) CAH
E(BSEMPMS) */ EMP_NAM FROM BSEMPMS;
收藏助手
不良信息举报
您举报文章:ORACLE常用问题1000问全集(二)
举报原因:
原因补充:

(最多只允许输入30个字)