{"id":707,"date":"2008-09-08T18:58:55","date_gmt":"2008-09-08T10:58:55","guid":{"rendered":"http:\/\/jason695.why3s.tw\/wordpress\/?p=707"},"modified":"2008-09-13T23:32:30","modified_gmt":"2008-09-13T15:32:30","slug":"oracle%e5%b8%b8%e8%a6%8b%e5%95%8f%e9%a1%8c%e9%9b%86%e4%b8%89","status":"publish","type":"post","link":"https:\/\/jason695.why3s.tw\/wordpress\/2008\/09\/08\/oracle%e5%b8%b8%e8%a6%8b%e5%95%8f%e9%a1%8c%e9%9b%86%e4%b8%89\/","title":{"rendered":"Oracle\u5e38\u898b\u554f\u984c\u96c6(\u4e09)"},"content":{"rendered":"


\n151. \u5982\u4f55\u76e3\u63a7 SGA \u4e2d\u5b57\u5178\u7de9\u885d\u5340\u7684\u547d\u4e2d\u7387\uff1f<\/strong><\/p>\n

select parameter, gets,Getmisses , getmisses\/(gets+getmisses)*100<\/p>\n

“miss ratio”,<\/p>\n

(1-(sum(getmisses)\/ (sum(gets)+sum(getmisses))))*100 “Hit ratio”<\/p>\n

from v$rowcache<\/p>\n

where gets+getmisses <>0<\/p>\n

group by parameter, gets, getmisses;<\/p>\n

152. \u5982\u4f55\u76e3\u63a7 SGA \u4e2d\u5171\u7528\u7de9\u5b58\u5340\u7684\u547d\u4e2d\u7387\uff0c\u61c9\u8a72\u5c0f\u65bc1% \uff1f<\/strong><\/p>\n

select sum(pins) “Total Pins”, sum(reloads) “Total Reloads”,<\/p>\n

sum(reloads)\/sum(pins) *100 libcache<\/p>\n

from v$librarycache;<\/p>\n

select sum(pinhits-reloads)\/sum(pins) “hit<\/p>\n

radio”,sum(reloads)\/sum(pins) “reload percent”<\/p>\n

from v$librarycache;<\/p>\n

153. \u5982\u4f55\u986f\u793a\u6240\u6709\u8cc7\u6599\u5eab\u7269\u4ef6\u7684\u985e\u5225\u548c\u5927\u5c0f\uff1f<\/strong><\/p>\n

select count(name) num_instances ,type ,sum(source_size)<\/p>\n

source_size ,<\/p>\n

sum(parsed_size) parsed_size ,sum(code_size) code_size<\/p>\n

,sum(error_size) error_size,<\/p>\n

sum(source_size) +sum(parsed_size) +sum(code_size)<\/p>\n

+sum(error_size) size_required<\/p>\n

from dba_object_size<\/p>\n

group by type order by 2;<\/p>\n

154. \u76e3\u63a7 SGA \u4e2d\u91cd\u505a\u65e5\u8a8c\u7de9\u5b58\u5340\u7684\u547d\u4e2d\u7387\uff0c\u61c9\u8a72\u5c0f\u65bc1%<\/strong><\/p>\n

SELECT name, gets, misses, immediate_gets, immediate_misses,<\/p>\n

Decode(gets,0,0,misses\/gets*100) ratio1,<\/p>\n

Decode(immediate_gets+immediate_misses,0,0,<\/p>\n

immediate_misses\/(immediate_gets+immediate_misses)*100) ratio2<\/p>\n

FROM v$latch WHERE name IN (‘redo allocation’, ‘redo copy’);<\/p>\n

155. \u76e3\u63a7\u8a18\u61b6\u9ad4\u548c\u786c\u789f\u7684\u6392\u5e8f\u6bd4\u7387\uff0c\u6700\u597d\u4f7f\u5b83\u5c0f\u65bc .10\uff0c\u589e\u52a0 sort_area_size<\/strong><\/p>\n

SELECT name, value FROM v$sysstat WHERE name IN (‘sorts (memory)’,<\/p>\n

‘sorts (disk)’);<\/p>\n

156. \u5982\u4f55\u76e3\u63a7\u7576\u524d\u8cc7\u6599\u5eab\u8ab0\u5728\u904b\u884c\u4ec0\u9ebdSQL\u8a9e\u53e5\uff1f<\/strong><\/p>\n

SELECT osuser, username, sql_text from v$session a, v$sqltext b<\/p>\n

where a.sql_address =b.address order by address, piece;<\/p>\n

157. \u5982\u4f55\u76e3\u63a7\u5b57\u5178\u7de9\u885d\u5340\uff1f<\/strong><\/p>\n

SELECT (SUM(PINS – RELOADS)) \/ SUM(PINS) “LIB CACHE” FROM<\/p>\n

V$LIBRARYCACHE;<\/p>\n

SELECT (SUM(GETS – GETMISSES – USAGE – FIXED)) \/ SUM(GETS) “ROW<\/p>\n

CACHE” FROM V$ROWCACHE;<\/p>\n

SELECT SUM(PINS) “EXECUTIONS”, SUM(RELOADS) “CACHE MISSES WHILE<\/p>\n

EXECUTING” FROM V$LIBRARYCACHE;<\/p>\n

\u5f8c\u8005\u9664\u4ee5\u524d\u8005,\u6b64\u6bd4\u7387\u5c0f\u65bc1%,\u63a5\u8fd10%\u7232\u597d\u3002<\/p>\n

SELECT SUM(GETS) “DICTIONARY GETS”,SUM(GETMISSES) “DICTIONARY<\/p>\n

CACHE GET MISSES”<\/p>\n

FROM V$ROWCACHE<\/p>\n

158. \u76e3\u63a7 MTS<\/strong><\/p>\n

select busy\/(busy+idle) “shared servers busy” from v$dispatcher;<\/p>\n

\u6b64\u503c\u5927\u65bc0.5\u6642\uff0c\u53c3\u6578\u9700\u52a0\u5927<\/p>\n

select sum(wait)\/sum(totalq) “dispatcher waits” from v$queue where<\/p>\n

type=’dispatcher’;<\/p>\n

select count(*) from v$dispatcher;<\/p>\n

select servers_highwater from v$mts;<\/p>\n

servers_highwater\u63a5\u8fd1mts_max_servers\u6642\uff0c\u53c3\u6578\u9700\u52a0\u5927<\/p>\n

159. \u5982\u4f55\u77e5\u9053\u7576\u524d\u7528\u6236\u7684ID\u865f<\/strong><\/p>\n

SQL>SHOW USER;<\/p>\n

OR<\/p>\n

SQL>select user from dual;<\/p>\n

160. \u5982\u4f55\u67e5\u770b\u788e\u7247\u7a0b\u5ea6\u9ad8\u7684\u8868<\/strong><\/p>\n

SELECT segment_name table_name , COUNT(*) extents<\/p>\n

FROM dba_segments WHERE owner NOT IN (‘SYS’, ‘SYSTEM’) GROUP BY<\/p>\n

segment_name<\/p>\n

HAVING COUNT(*) = (SELECT MAX( COUNT(*) ) FROM dba_segments GROUP<\/p>\n

BY segment_name);<\/p>\n

162. \u5982\u4f55\u77e5\u9053\u8868\u5728\u8868\u7a7a\u9593\u4e2d\u7684\u5b58\u5132\u60c5\u6cc1<\/strong><\/p>\n

select segment_name,sum(bytes),count(*) ext_quan from dba_extents<\/p>\n

where<\/p>\n

tablespace_name=’&tablespace_name’ and segment_type=’TABLE’ group<\/p>\n

by tablespace_name,segment_name;<\/p>\n

163. \u5982\u4f55\u77e5\u9053\u7d22\u5f15\u5728\u8868\u7a7a\u9593\u4e2d\u7684\u5b58\u5132\u60c5\u6cc1<\/strong><\/p>\n

select segment_name,count(*) from dba_extents where<\/p>\n

segment_type=’INDEX’ and owner=’&owner’<\/p>\n

group by segment_name;<\/p>\n

164\u3001\u5982\u4f55\u77e5\u9053\u4f7f\u7528CPU\u591a\u7684\u7528\u6236session<\/strong><\/p>\n

11\u662fcpu used by this session<\/p>\n

select a.sid,spid,status,substr(a.program,1,40)<\/p>\n

prog,a.terminal,osuser,value\/60\/100 value<\/p>\n

from v$session a,v$process b,v$sesstat c<\/p>\n

where c.statistic#=11 and c.sid=a.sid and a.paddr=b.addr order by<\/p>\n

value desc;<\/p>\n

165. \u5982\u4f55\u77e5\u9053\u76e3\u807d\u5668\u65e5\u8a8c\u6587\u4ef6<\/strong><\/p>\n

\u4ee58I\u7232\u4f8b<\/p>\n

$ORACLE_HOME\/NETWORK\/LOG\/LISTENER.LOG<\/p>\n

166. \u5982\u4f55\u77e5\u9053\u76e3\u807d\u5668\u53c3\u6578\u6587\u4ef6<\/strong><\/p>\n

\u4ee58I\u7232\u4f8b<\/p>\n

$ORACLE_HOME\/NETWORK\/ADMIN\/LISTENER.ORA<\/p>\n

167. \u5982\u4f55\u77e5\u9053TNS \u9023\u63a5\u6587\u4ef6<\/strong><\/p>\n

\u4ee58I\u7232\u4f8b<\/p>\n

$ORACLE_HOME\/NETWORK\/ADMIN\/TNSNAMES.ORA<\/p>\n

168. \u5982\u4f55\u77e5\u9053Sql*Net \u74b0\u5883\u6587\u4ef6<\/strong><\/p>\n

\u4ee58I\u7232\u4f8b<\/p>\n

$ORACLE_HOME\/NETWORK\/ADMIN\/SQLNET.ORA<\/p>\n

169. \u5982\u4f55\u77e5\u9053\u8b66\u544a\u65e5\u8a8c\u6587\u4ef6<\/strong><\/p>\n

\u4ee58I\u7232\u4f8b<\/p>\n

$ORACLE_HOME\/ADMIN\/SID\/BDUMP\/SIDALRT.LOG<\/p>\n

170. \u5982\u4f55\u77e5\u9053\u57fa\u672c\u7d50\u69cb<\/strong><\/p>\n

\u4ee58I\u7232\u4f8b<\/p>\n

$ORACLE_HOME\/RDBMS\/ADMIN\/STANDARD.SQL<\/p>\n

171. \u5982\u4f55\u77e5\u9053\u5efa\u7acb\u8cc7\u6599\u5b57\u5178\u8996\u5716<\/strong><\/p>\n

\u4ee58I\u7232\u4f8b<\/p>\n

$ORACLE_HOME\/RDBMS\/ADMIN\/CATALOG.SQL<\/p>\n

172. \u5982\u4f55\u77e5\u9053\u5efa\u7acb\u5be9\u8a08\u7528\u8cc7\u6599\u5b57\u5178\u8996\u5716<\/strong><\/p>\n

\u4ee58I\u7232\u4f8b<\/p>\n

$ORACLE_HOME\/RDBMS\/ADMIN\/CATAUDIT.SQL<\/p>\n

173. \u5982\u4f55\u77e5\u9053\u5efa\u7acb\u5feb\u7167\u7528\u8cc7\u6599\u5b57\u5178\u8996\u5716<\/strong><\/p>\n

\u4ee58I\u7232\u4f8b<\/p>\n

$ORACLE_HOME\/RDBMS\/ADMIN\/CATSNAP.SQL<\/p>\n

\u672c\u8b1b\u4e3b\u8981\u8b1b\u7684\u662fSQL\u8a9e\u53e5\u7684\u512a\u5316\u65b9\u6cd5! \u4e3b\u8981\u57fa\u65bcORACLE9I\u7684.<\/p>\n

174. \/*+ALL_ROWS*\/<\/strong><\/p>\n

\u8868\u660e\u5c0d\u8a9e\u53e5\u584a\u9078\u64c7\u57fa\u65bc\u958b\u92b7\u7684\u512a\u5316\u65b9\u6cd5,\u4e26\u7372\u5f97\u6700\u4f73\u541e\u5410\u91cf,\u4f7f\u8cc7\u6e90\u6d88\u8017\u6700\u5c0f\u5316.<\/p>\n

\u4f8b\u5982:<\/p>\n

SELECT \/*+ALL+_ROWS*\/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE<\/p>\n

EMP_NO=’CCBZZP’;<\/p>\n

175. \/*+FIRST_ROWS*\/<\/strong><\/p>\n

\u8868\u660e\u5c0d\u8a9e\u53e5\u584a\u9078\u64c7\u57fa\u65bc\u958b\u92b7\u7684\u512a\u5316\u65b9\u6cd5,\u4e26\u7372\u5f97\u6700\u4f73\u56de\u61c9\u6642\u9593,\u4f7f\u8cc7\u6e90\u6d88\u8017\u6700\u5c0f\u5316.<\/p>\n

\u4f8b\u5982:<\/p>\n

SELECT \/*+FIRST_ROWS*\/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE<\/p>\n

EMP_NO=’CCBZZP’;<\/p>\n

176. \/*+CHOOSE*\/<\/strong><\/p>\n

\u8868\u660e\u5982\u679c\u8cc7\u6599\u5b57\u5178\u4e2d\u6709\u8a2a\u554f\u8868\u7684\u7d71\u8a08\u8cc7\u8a0a,\u5c07\u57fa\u65bc\u958b\u92b7\u7684\u512a\u5316\u65b9\u6cd5,\u4e26\u7372\u5f97\u6700\u4f73\u7684\u541e\u5410\u91cf;<\/p>\n

\u8868\u660e\u5982\u679c\u8cc7\u6599\u5b57\u5178\u4e2d\u6c92\u6709\u8a2a\u554f\u8868\u7684\u7d71\u8a08\u8cc7\u8a0a,\u5c07\u57fa\u65bc\u898f\u5247\u958b\u92b7\u7684\u512a\u5316\u65b9\u6cd5;<\/p>\n

\u4f8b\u5982:<\/p>\n

SELECT \/*+CHOOSE*\/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE<\/p>\n

EMP_NO=’CCBZZP’;<\/p>\n

177. \/*+RULE*\/<\/strong><\/p>\n

\u8868\u660e\u5c0d\u8a9e\u53e5\u584a\u9078\u64c7\u57fa\u65bc\u898f\u5247\u7684\u512a\u5316\u65b9\u6cd5.<\/p>\n

\u4f8b\u5982:<\/p>\n

SELECT \/*+ RULE *\/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE<\/p>\n

EMP_NO=’CCBZZP’;<\/p>\n

178. \/*+FULL(TABLE)*\/<\/strong><\/p>\n

\u8868\u660e\u5c0d\u8868\u9078\u64c7\u5168\u5c40\u6383\u63cf\u7684\u65b9\u6cd5.<\/p>\n

\u4f8b\u5982:<\/p>\n

SELECT \/*+FULL(A)*\/ EMP_NO,EMP_NAM FROM BSEMPMS A WHERE<\/p>\n

EMP_NO=’CCBZZP’;<\/p>\n

179. \/*+ROWID(TABLE)*\/<\/strong><\/p>\n

\u63d0\u793a\u660e\u78ba\u8868\u660e\u5c0d\u6307\u5b9a\u8868\u6839\u64daROWID\u9032\u884c\u8a2a\u554f.<\/p>\n

\u4f8b\u5982:<\/p>\n

SELECT \/*+ROWID(BSEMPMS)*\/ * FROM BSEMPMS WHERE<\/p>\n

ROWID>=’AAAAAAAAAAAAAA’<\/p>\n

AND EMP_NO=’CCBZZP’;<\/p>\n

180. \/*+CLUSTER(TABLE)*\/<\/strong><\/p>\n

\u63d0\u793a\u660e\u78ba\u8868\u660e\u5c0d\u6307\u5b9a\u8868\u9078\u64c7\u7c07\u6383\u63cf\u7684\u8a2a\u554f\u65b9\u6cd5,\u5b83\u53ea\u5c0d\u7c07\u7269\u4ef6\u6709\u6548.<\/p>\n

\u4f8b\u5982:<\/p>\n

SELECT \/*+CLUSTER *\/ BSEMPMS.EMP_NO,DPT_NO FROM BSEMPMS,BSDPTMS<\/p>\n

WHERE DPT_NO=’TEC304′ AND BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;<\/p>\n

181. \/*+INDEX(TABLE INDEX_NAME)*\/<\/strong><\/p>\n

\u8868\u660e\u5c0d\u8868\u9078\u64c7\u7d22\u5f15\u7684\u6383\u63cf\u65b9\u6cd5.<\/p>\n

\u4f8b\u5982:<\/p>\n

SELECT \/*+INDEX(BSEMPMS SEX_INDEX) USE SEX_INDEX BECAUSE THERE ARE<\/p>\n

FEWMALE BSEMPMS *\/ FROM BSEMPMS WHERE SEX=’M’;<\/p>\n

182. \/*+INDEX_ASC(TABLE INDEX_NAME)*\/<\/strong><\/p>\n

\u8868\u660e\u5c0d\u8868\u9078\u64c7\u7d22\u5f15\u6607\u51aa\u7684\u6383\u63cf\u65b9\u6cd5.<\/p>\n

\u4f8b\u5982:<\/p>\n

SELECT \/*+INDEX_ASC(BSEMPMS PK_BSEMPMS) *\/ FROM BSEMPMS WHERE<\/p>\n

DPT_NO=’CCBZZP’;<\/p>\n

183. \/*+INDEX_COMBINE*\/<\/strong><\/p>\n

\u7232\u6307\u5b9a\u8868\u9078\u64c7\u9ede\u9663\u5716\u8a2a\u554f\u8def\u7d93,\u5982\u679cINDEX_COMBINE\u4e2d\u6c92\u6709\u63d0\u4f9b\u4f5c\u7232\u53c3\u6578\u7684\u7d22\u5f15,\u5c07\u9078\u64c7\u51fa\u9ede\u9663\u5716\u7d22\u5f15\u7684<\/p>\n

\u5e03\u6797\u7d44\u5408\u65b9\u5f0f.<\/p>\n

\u4f8b\u5982:<\/p>\n

SELECT \/*+INDEX_COMBINE(BSEMPMS SAL_BMI HIREDATE_BMI)*\/ * FROM<\/p>\n

BSEMPMS<\/p>\n

WHERE SAL<5000000 AND HIREDATE< SPAN><\/p>\n

184. \/*+INDEX_JOIN(TABLE INDEX_NAME)*\/<\/strong><\/p>\n

\u63d0\u793a\u660e\u78ba\u547d\u4ee4\u512a\u5316\u5668\u4f7f\u7528\u7d22\u5f15\u4f5c\u7232\u8a2a\u554f\u8def\u5f91.<\/p>\n

\u4f8b\u5982:<\/p>\n

SELECT \/*+INDEX_JOIN(BSEMPMS SAL_HMI HIREDATE_BMI)*\/ SAL,HIREDATE<\/p>\n

FROM BSEMPMS WHERE SAL<60000;<\/p>\n

185. \/*+INDEX_DESC(TABLE INDEX_NAME)*\/<\/strong><\/p>\n

\u8868\u660e\u5c0d\u8868\u9078\u64c7\u7d22\u5f15\u964d\u51aa\u7684\u6383\u63cf\u65b9\u6cd5.<\/p>\n

\u4f8b\u5982:<\/p>\n

SELECT \/*+INDEX_DESC(BSEMPMS PK_BSEMPMS) *\/ FROM BSEMPMS WHERE<\/p>\n

DPT_NO=’CCBZZP’;<\/p>\n

186. \/*+INDEX_FFS(TABLE INDEX_NAME)*\/<\/strong><\/p>\n

\u5c0d\u6307\u5b9a\u7684\u8868\u57f7\u884c\u5feb\u901f\u5168\u7d22\u5f15\u6383\u63cf,\u800c\u4e0d\u662f\u5168\u8868\u6383\u63cf\u7684\u8fa6\u6cd5.<\/p>\n

\u4f8b\u5982:<\/p>\n

SELECT \/*+INDEX_FFS(BSEMPMS IN_EMPNAM)*\/ * FROM BSEMPMS WHERE<\/p>\n

DPT_NO=’TEC305′;<\/p>\n

187. \/*+ADD_EQUAL TABLE INDEX_NAM1,INDEX_NAM2,…*\/<\/strong><\/p>\n

\u63d0\u793a\u660e\u78ba\u9032\u884c\u57f7\u884c\u898f\u5283\u7684\u9078\u64c7,\u5c07\u5e7e\u500b\u55ae\u5217\u7d22\u5f15\u7684\u6383\u63cf\u5408\u8d77\u4f86.<\/p>\n

\u4f8b\u5982:<\/p>\n

SELECT \/*+INDEX_FFS(BSEMPMS IN_DPTNO,IN_EMPNO,IN_SEX)*\/ * FROM<\/p>\n

BSEMPMS WHERE EMP_NO=’CCBZZP’ AND DPT_NO=’TDC306′;<\/p>\n

188. \/*+USE_CONCAT*\/<\/strong><\/p>\n

\u5c0d\u67e5\u8a62\u4e2d\u7684WHERE\u5f8c\u9762\u7684OR\u689d\u4ef6\u9032\u884c\u8f49\u63db\u7232UNION ALL\u7684\u7d44\u5408\u67e5\u8a62.<\/p>\n

\u4f8b\u5982:<\/p>\n

SELECT \/*+USE_CONCAT*\/ * FROM BSEMPMS WHERE DPT_NO=’TDC506′ AND<\/p>\n

SEX=’M’;<\/p>\n

189. \/*+NO_EXPAND*\/<\/strong><\/p>\n

\u5c0d\u65bcWHERE\u5f8c\u9762\u7684OR \u6216\u8005IN-LIST\u7684\u67e5\u8a62\u8a9e\u53e5,NO_EXPAND\u5c07\u963b\u6b62\u5176\u57fa\u65bc\u512a\u5316\u5668\u5c0d\u5176\u9032\u884c\u64f4\u5c55.<\/p>\n

\u4f8b\u5982:<\/p>\n

SELECT \/*+NO_EXPAND*\/ * FROM BSEMPMS WHERE DPT_NO=’TDC506′ AND<\/p>\n

SEX=’M’;<\/p>\n

190. \/*+NOWRITE*\/<\/strong><\/p>\n

\u7981\u6b62\u5c0d\u67e5\u8a62\u584a\u7684\u67e5\u8a62\u91cd\u5beb\u64cd\u4f5c.<\/p>\n

191. \/*+REWRITE*\/<\/strong><\/p>\n

\u53ef\u4ee5\u5c07\u8996\u5716\u4f5c\u7232\u53c3\u6578.<\/p>\n

192. \/*+MERGE(TABLE)*\/<\/strong><\/p>\n

\u80fd\u5920\u5c0d\u8996\u5716\u7684\u5404\u500b\u67e5\u8a62\u9032\u884c\u76f8\u61c9\u7684\u5408\u4f75.<\/p>\n

\u4f8b\u5982:<\/p>\n

SELECT \/*+MERGE(V) *\/ A.EMP_NO,A.EMP_NAM,B.DPT_NO FROM BSEMPMS A<\/p>\n

(SELET DPT_NO<\/p>\n

,AVG(SAL) AS AVG_SAL FROM BSEMPMS B GROUP BY DPT_NO) V WHERE<\/p>\n

A.DPT_NO=V.DPT_NO<\/p>\n

AND A.SAL>V.AVG_SAL;<\/p>\n

193. \/*+NO_MERGE(TABLE)*\/<\/strong><\/p>\n

\u5c0d\u65bc\u6709\u53ef\u5408\u4f75\u7684\u8996\u5716\u4e0d\u518d\u5408\u4f75.<\/p>\n

\u4f8b\u5982:<\/p>\n

SELECT \/*+NO_MERGE(V) *\/ A.EMP_NO,A.EMP_NAM,B.DPT_NO FROM BSEMPMS<\/p>\n

A (SELET DPT_NO<\/p>\n

,AVG(SAL) AS AVG_SAL FROM BSEMPMS B GROUP BY DPT_NO) V WHERE<\/p>\n

A.DPT_NO=V.DPT_NO<\/p>\n

AND A.SAL>V.AVG_SAL;<\/p>\n

194. \/*+ORDERED*\/<\/strong><\/p>\n

\u6839\u64da\u8868\u51fa\u73fe\u5728FROM\u4e2d\u7684\u9806\u5e8f,ORDERED\u4f7fORACLE\u4f9d\u6b64\u9806\u5e8f\u5c0d\u5176\u9023\u63a5.<\/p>\n

\u4f8b\u5982:<\/p>\n

SELECT \/*+ORDERED*\/ A.COL1,B.COL2,C.COL3 FROM TABLE1 A,TABLE2<\/p>\n

B,TABLE3 C<\/p>\n

WHERE A.COL1=B.COL1 AND B.COL1=C.COL1;<\/p>\n

195. \/*+USE_NL(TABLE)*\/<\/strong><\/p>\n

\u5c07\u6307\u5b9a\u8868\u8207\u5d4c\u5957\u7684\u9023\u63a5\u7684\u884c\u6e90\u9032\u884c\u9023\u63a5,\u4e26\u628a\u6307\u5b9a\u8868\u4f5c\u7232\u5167\u90e8\u8868.<\/p>\n

\u4f8b\u5982:<\/p>\n

SELECT \/*+ORDERED USE_NL(BSEMPMS)*\/<\/p>\n

BSDPTMS.DPT_NO,BSEMPMS.EMP_NO,BSEMPMS.EMP_NAM FROM BSEMPMS,BSDPTMS<\/p>\n

WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;<\/p>\n

196. \/*+USE_MERGE(TABLE)*\/<\/strong><\/p>\n

\u5c07\u6307\u5b9a\u7684\u8868\u8207\u5176\u4ed6\u884c\u6e90\u901a\u904e\u5408\u4f75\u6392\u5e8f\u9023\u63a5\u65b9\u5f0f\u9023\u63a5\u8d77\u4f86.<\/p>\n

\u4f8b\u5982:<\/p>\n

SELECT \/*+USE_MERGE(BSEMPMS,BSDPTMS)*\/ * FROM BSEMPMS,BSDPTMS<\/p>\n

WHERE<\/p>\n

BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;<\/p>\n

197. \/*+USE_HASH(TABLE)*\/<\/strong><\/p>\n

\u5c07\u6307\u5b9a\u7684\u8868\u8207\u5176\u4ed6\u884c\u6e90\u901a\u904e\u54c8\u5e0c\u9023\u63a5\u65b9\u5f0f\u9023\u63a5\u8d77\u4f86.<\/p>\n

\u4f8b\u5982:<\/p>\n

SELECT \/*+USE_HASH(BSEMPMS,BSDPTMS)*\/ * FROM BSEMPMS,BSDPTMS WHERE<\/p>\n

BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;<\/p>\n

198. \/*+DRIVING_SITE(TABLE)*\/<\/strong><\/p>\n

\u5f37\u5236\u8207ORACLE\u6240\u9078\u64c7\u7684\u4f4d\u7f6e\u4e0d\u540c\u7684\u8868\u9032\u884c\u67e5\u8a62\u57f7\u884c.<\/p>\n

\u4f8b\u5982:<\/p>\n

SELECT \/*+DRIVING_SITE(DEPT)*\/ * FROM BSEMPMS,DEPT@BSDPTMS WHERE<\/p>\n

BSEMPMS.DPT_NO=DEPT.DPT_NO;<\/p>\n

199. \/*+LEADING(TABLE)*\/<\/strong><\/p>\n

\u5c07\u6307\u5b9a\u7684\u8868\u4f5c\u7232\u9023\u63a5\u6b21\u5e8f\u4e2d\u7684\u9996\u8868.<\/p>\n

200. \/*+CACHE(TABLE)*\/<\/strong><\/p>\n

\u7576\u9032\u884c\u5168\u8868\u6383\u63cf\u6642,CACHE\u63d0\u793a\u80fd\u5920\u5c07\u8868\u7684\u6aa2\u7d22\u584a\u653e\u7f6e\u5728\u7de9\u885d\u5340\u7de9\u5b58\u4e2d\u6700\u8fd1\u6700\u5c11\u5217\u8868LRU\u7684\u6700\u8fd1\u4f7f\u7528\u7aef<\/p>\n

\u4f8b\u5982:<\/p>\n

SELECT \/*+FULL(BSEMPMS) CAHE(BSEMPMS) *\/ EMP_NAM FROM BSEMPMS;<\/p>\n

201. \/*+NOCACHE(TABLE)*\/<\/strong><\/p>\n

\u7576\u9032\u884c\u5168\u8868\u6383\u63cf\u6642,CACHE\u63d0\u793a\u80fd\u5920\u5c07\u8868\u7684\u6aa2\u7d22\u584a\u653e\u7f6e\u5728\u7de9\u885d\u5340\u7de9\u5b58\u4e2d\u6700\u8fd1\u6700\u5c11\u5217\u8868LRU\u7684\u6700\u8fd1\u4f7f\u7528\u7aef<\/p>\n

\u4f8b\u5982:<\/p>\n

SELECT \/*+FULL(BSEMPMS) NOCAHE(BSEMPMS) *\/ EMP_NAM FROM BSEMPMS;<\/p>\n

202. \/*+APPEND*\/<\/strong><\/p>\n

\u76f4\u63a5\u63d2\u5165\u5230\u8868\u7684\u6700\u5f8c,\u53ef\u4ee5\u63d0\u9ad8\u901f\u5ea6.<\/p>\n

insert \/*+append*\/ into test1 select * from test4 ;<\/p>\n

203. \/*+NOAPPEND*\/<\/strong><\/p>\n

\u901a\u904e\u5728\u63d2\u5165\u8a9e\u53e5\u751f\u5b58\u671f\u5167\u505c\u6b62\u4e26\u884c\u6a21\u5f0f\u4f86\u5553\u52d5\u5e38\u898f\u63d2\u5165.<\/p>\n

insert \/*+noappend*\/ into test1 select * from test4 ;<\/p>\n

——————————————-<\/p>\n

1.ORACLE\u512a\u5316\u5668<\/strong><\/p>\n

. \u9078\u7528\u9069\u5408\u7684ORACLE\u512a\u5316\u5668<\/p>\n

ORACLE\u7684\u512a\u5316\u5668\u5171\u67093\u7a2e:<\/p>\n

a. RULE (\u57fa\u65bc\u898f\u5247) b. COST (\u57fa\u65bc\u6210\u672c) c. CHOOSE (\u9078\u64c7\u6027)<\/p>\n

\u8a2d\u7f6e\u7f3a\u7701\u7684\u512a\u5316\u5668,\u53ef\u4ee5\u901a\u904e\u5c0dinit.ora\u6587\u4ef6\u4e2dOPTIMIZER_MODE\u53c3\u6578\u7684\u5404\u7a2e\u8072\u660e,\u5982RULE,COST,CHOOSE,ALL_ROWS,FIRST_ROWS<\/p>\n

. \u4f60\u7576\u7136\u4e5f\u5728SQL\u53e5\u7d1a\u6216\u662f\u6703\u8a71(session)\u7d1a\u5c0d\u5176\u9032\u884c\u8986\u84cb.<\/p>\n

\u7232\u4e86\u4f7f\u7528\u57fa\u65bc\u6210\u672c\u7684\u512a\u5316\u5668(CBO, Cost-Based Optimizer) , \u4f60\u5fc5\u9808\u7d93\u5e38\u904b\u884canalyze<\/p>\n

\u547d\u4ee4,\u4ee5\u589e\u52a0\u8cc7\u6599\u5eab\u4e2d\u7684\u7269\u4ef6\u7d71\u8a08\u8cc7\u8a0a(object statistics)\u7684\u6e96\u78ba\u6027.<\/p>\n

\u5982\u679c\u8cc7\u6599\u5eab\u7684\u512a\u5316\u5668\u6a21\u5f0f\u8a2d\u7f6e\u7232\u9078\u64c7\u6027(CHOOSE),\u90a3\u9ebd\u5be6\u969b\u7684\u512a\u5316\u5668\u6a21\u5f0f\u5c07\u548c\u662f\u5426\u904b\u884c\u904eanalyze\u547d\u4ee4\u6709\u95dc.<\/p>\n

\u5982\u679ctable\u5df2\u7d93\u88abanalyze\u904e, \u512a\u5316\u5668\u6a21\u5f0f\u5c07\u81ea\u52d5\u6210\u7232CBO , \u53cd\u4e4b,\u8cc7\u6599\u5eab\u5c07\u63a1\u7528RULE\u5f62\u5f0f\u7684\u512a\u5316\u5668.<\/p>\n

\u5728\u7f3a\u7701\u60c5\u6cc1\u4e0b,ORACLE\u63a1\u7528CHOOSE\u512a\u5316\u5668, \u7232\u4e86\u907f\u514d\u90a3\u4e9b\u4e0d\u5fc5\u8981\u7684\u5168\u8868\u6383\u63cf(full table scan) ,<\/p>\n

\u4f60\u5fc5\u9808\u5118\u91cf\u907f\u514d\u4f7f\u7528CHOOSE\u512a\u5316\u5668,\u800c\u76f4\u63a5\u63a1\u7528\u57fa\u65bc\u898f\u5247\u6216\u8005\u57fa\u65bc\u6210\u672c\u7684\u512a\u5316\u5668.<\/p>\n

2. \u8a2a\u554fTable\u7684\u65b9\u5f0f<\/strong><\/p>\n

ORACLE \u63a1\u7528\u5169\u7a2e\u8a2a\u554f\u8868\u4e2d\u8a18\u9304\u7684\u65b9\u5f0f:<\/p>\n

a. \u5168\u8868\u6383\u63cf<\/p>\n

\u5168\u8868\u6383\u63cf\u5c31\u662f\u9806\u5e8f\u5730\u8a2a\u554f\u8868\u4e2d\u6bcf\u689d\u8a18\u9304. ORACLE\u63a1\u7528\u4e00\u6b21\u8b80\u5165\u591a\u500b\u8cc7\u6599\u584a(database block)\u7684\u65b9\u5f0f\u512a\u5316\u5168\u8868\u6383\u63cf.<\/p>\n

b. \u901a\u904eROWID\u8a2a\u554f\u8868<\/p>\n

\u4f60\u53ef\u4ee5\u63a1\u7528\u57fa\u65bcROWID\u7684\u8a2a\u554f\u65b9\u5f0f\u60c5\u6cc1,\u63d0\u9ad8\u8a2a\u554f\u8868\u7684\u6548\u7387, ,<\/p>\n

ROWID\u5305\u542b\u4e86\u8868\u4e2d\u8a18\u9304\u7684\u7269\u7406\u4f4d\u7f6e\u8cc7\u8a0a..ORACLE\u63a1\u7528\u7d22\u5f15(INDEX)\u5be6\u73fe\u4e86\u8cc7\u6599\u548c\u5b58\u653e\u8cc7\u6599\u7684\u7269\u7406\u4f4d\u7f6e(ROWID)\u4e4b\u9593\u7684\u806f\u7e6b.<\/p>\n

\u901a\u5e38\u7d22\u5f15\u63d0\u4f9b\u4e86\u5feb\u901f\u8a2a\u554fROWID\u7684\u65b9\u6cd5,\u56e0\u6b64\u90a3\u4e9b\u57fa\u65bc\u7d22\u5f15\u5217\u7684\u67e5\u8a62\u5c31\u53ef\u4ee5\u5f97\u5230\u6027\u80fd\u4e0a\u7684\u63d0\u9ad8.<\/p>\n

3. \u5171\u7528SQL\u8a9e\u53e5<\/strong><\/p>\n

\u7232\u4e86\u4e0d\u91cd\u5fa9\u89e3\u6790\u76f8\u540c\u7684SQL\u8a9e\u53e5,\u5728\u7b2c\u4e00\u6b21\u89e3\u6790\u4e4b\u5f8c, ORACLE\u5c07SQL\u8a9e\u53e5\u5b58\u653e\u5728\u8a18\u61b6\u9ad4\u4e2d.\u9019\u584a\u4f4d\u65bc\u7cfb\u7d71\u5168\u5c40\u5340\u57dfSGA(system<\/p>\n

global area)\u7684\u5171\u7528\u6c60(shared buffer pool)\u4e2d\u7684\u8a18\u61b6\u9ad4\u53ef\u4ee5\u88ab\u6240\u6709\u7684\u8cc7\u6599\u5eab\u7528\u6236\u5171\u7528.<\/p>\n

\u56e0\u6b64,\u7576\u4f60\u57f7\u884c\u4e00\u500bSQL\u8a9e\u53e5(\u6709\u6642\u88ab\u7a31\u7232\u4e00\u500b\u6e38\u6a19)\u6642,\u5982\u679c\u5b83<\/p>\n

\u548c\u4e4b\u524d\u7684\u57f7\u884c\u904e\u7684\u8a9e\u53e5\u5b8c\u5168\u76f8\u540c, ORACLE\u5c31\u80fd\u5f88\u5feb\u7372\u5f97\u5df2\u7d93\u88ab\u89e3\u6790\u7684\u8a9e\u53e5\u4ee5\u53ca\u6700\u597d\u7684<\/p>\n

\u57f7\u884c\u8def\u5f91. ORACLE\u7684\u9019\u500b\u529f\u80fd\u5927\u5927\u5730\u63d0\u9ad8\u4e86SQL\u7684\u57f7\u884c\u6027\u80fd\u4e26\u7bc0\u7701\u4e86\u8a18\u61b6\u9ad4\u7684\u4f7f\u7528.<\/p>\n

\u53ef\u60dc\u7684\u662fORACLE\u53ea\u5c0d\u7c21\u55ae\u7684\u8868\u63d0\u4f9b\u9ad8\u901f\u7de9\u885d(cache buffering) ,\u9019\u500b\u529f\u80fd\u4e26\u4e0d\u9069\u7528\u65bc\u591a\u8868\u9023\u63a5\u67e5\u8a62.<\/p>\n

\u8cc7\u6599\u5eab\u7ba1\u7406\u54e1\u5fc5\u9808\u5728init.ora\u4e2d\u7232\u9019\u500b\u5340\u57df\u8a2d\u7f6e\u5408\u9069\u7684\u53c3\u6578,\u7576\u9019\u500b\u8a18\u61b6\u9ad4\u5340\u57df\u8d8a\u5927,\u5c31\u53ef\u4ee5\u4fdd\u7559\u66f4\u591a\u7684\u8a9e\u53e5,\u7576\u7136\u88ab\u5171\u7528\u7684\u53ef\u80fd\u6027\u4e5f\u5c31\u8d8a\u5927\u4e86.<\/p>\n

\u7576\u4f60\u5411ORACLE \u63d0\u4ea4\u4e00\u500bSQL\u8a9e\u53e5,ORACLE\u6703\u9996\u5148\u5728\u9019\u584a\u8a18\u61b6\u9ad4\u4e2d\u67e5\u627e\u76f8\u540c\u7684\u8a9e\u53e5.<\/p>\n

\u9019\u88cf\u9700\u8981\u6ce8\u660e\u7684\u662f,ORACLE\u5c0d\u5169\u8005\u63a1\u53d6\u7684\u662f\u4e00\u7a2e\u56b4\u683c\u5339\u914d,\u8981\u9054\u6210\u5171\u7528,SQL\u8a9e\u53e5\u5fc5\u9808<\/p>\n

\u5b8c\u5168\u76f8\u540c(\u5305\u62ec\u7a7a\u683c,\u63db\u884c\u7b49).<\/p>\n

\u5171\u7528\u7684\u8a9e\u53e5\u5fc5\u9808\u6eff\u8db3\u4e09\u500b\u689d\u4ef6:<\/p>\n

A. \u5b57\u5143\u7d1a\u7684\u6bd4\u8f03:<\/p>\n

\u7576\u524d\u88ab\u57f7\u884c\u7684\u8a9e\u53e5\u548c\u5171\u7528\u6c60\u4e2d\u7684\u8a9e\u53e5\u5fc5\u9808\u5b8c\u5168\u76f8\u540c.<\/p>\n

\u4f8b\u5982:<\/p>\n

SELECT * FROM EMP;<\/p>\n

\u548c\u4e0b\u5217\u6bcf\u4e00\u500b\u90fd\u4e0d\u540c<\/p>\n

SELECT * from EMP;<\/p>\n

Select * From Emp;<\/p>\n

SELECT * FROM EMP;<\/p>\n

B. \u5169\u500b\u8a9e\u53e5\u6240\u6307\u7684\u7269\u4ef6\u5fc5\u9808\u5b8c\u5168\u76f8\u540c:<\/p>\n

\u4f8b\u5982:<\/p>\n

\u7528\u6236 \u7269\u4ef6\u540d \u5982\u4f55\u8a2a\u554f<\/p>\n

Jack sal_limit private synonym<\/p>\n

Work_city public synonym<\/p>\n

Plant_detail public synonym<\/p>\n

Jill sal_limit private synonym<\/p>\n

Work_city public synonym<\/p>\n

Plant_detail table owner<\/p>\n

\u8003\u616e\u4e00\u4e0b\u4e0b\u5217SQL\u8a9e\u53e5\u80fd\u5426\u5728\u9019\u5169\u500b\u7528\u6236\u4e4b\u9593\u5171\u7528.<\/p>\n

SQL<\/p>\n

\u80fd\u5426\u5171\u7528<\/p>\n

\u539f\u56e0<\/p>\n

select max(sal_cap) from sal_limit;<\/p>\n

\u4e0d\u80fd<\/p>\n

\u6bcf\u500b\u7528\u6236\u90fd\u6709\u4e00\u500bprivate synonym – sal_limit , \u5b83\u5011\u662f\u4e0d\u540c\u7684\u7269\u4ef6<\/p>\n

select count(*0 from work_city where sdesc like ‘NEW%’;<\/p>\n

\u80fd<\/p>\n

\u5169\u500b\u7528\u6236\u8a2a\u554f\u76f8\u540c\u7684\u7269\u4ef6public synonym – work_city<\/p>\n

select a.sdesc,b.location from work_city a , plant_detail b where<\/p>\n

a.city_id = b.city_id<\/p>\n

\u4e0d\u80fd<\/p>\n

\u7528\u6236jack \u901a\u904eprivate synonym\u8a2a\u554fplant_detail \u800cjill \u662f\u8868\u7684\u6240\u6709\u8005,\u7269\u4ef6\u4e0d\u540c.<\/p>\n

C. \u5169\u500bSQL\u8a9e\u53e5\u4e2d\u5fc5\u9808\u4f7f\u7528\u76f8\u540c\u7684\u540d\u5b57\u7684\u7d81\u5b9a\u8b8a\u6578(bind variables)<\/p>\n

\u4f8b\u5982\uff1a<\/p>\n

\u7b2c\u4e00\u7d44\u7684\u5169\u500bSQL\u8a9e\u53e5\u662f\u76f8\u540c\u7684(\u53ef\u4ee5\u5171\u7528),\u800c\u7b2c\u4e8c\u7d44\u4e2d\u7684\u5169\u500b\u8a9e\u53e5\u662f\u4e0d\u540c\u7684(\u5373\u4f7f\u5728\u904b\u884c\u6642,\u8ce6\u65bc\u4e0d\u540c\u7684\u7d81\u5b9a\u8b8a\u6578\u76f8\u540c\u7684\u503c)<\/p>\n

a.<\/p>\n

select pin , name from people where pin = :blk1.pin;<\/p>\n

select pin , name from people where pin = :blk1.pin;<\/p>\n

b.<\/p>\n

select pin , name from people where pin = :blk1.ot_ind;<\/p>\n

select pin , name from people where pin = :blk1.ov_ind;<\/p>\n

4. \u9078\u64c7\u6700\u6709\u6548\u7387\u7684\u8868\u540d\u9806\u5e8f(\u53ea\u5728\u57fa\u65bc\u898f\u5247\u7684\u512a\u5316\u5668\u4e2d\u6709\u6548)<\/strong><\/p>\n

ORACLE\u7684\u89e3\u6790\u5668\u6309\u7167\u5f9e\u53f3\u5230\u5de6\u7684\u9806\u5e8f\u8655\u7406FROM\u5b50\u53e5\u4e2d\u7684\u8868\u540d,\u56e0\u6b64FROM\u5b50\u53e5\u4e2d\u5beb\u5728\u6700\u5f8c\u7684\u8868(\u57fa\u790e\u8868 driving<\/p>\n

table)\u5c07\u88ab\u6700\u5148\u8655\u7406. \u5728FROM\u5b50\u53e5\u4e2d\u5305\u542b\u591a\u500b\u8868\u7684\u60c5\u6cc1\u4e0b,\u4f60\u5fc5\u9808\u9078\u64c7\u8a18\u9304\u689d\u6578\u6700\u5c11\u7684\u8868\u4f5c\u7232\u57fa\u790e\u8868.\u7576ORACLE\u8655\u7406\u591a\u500b\u8868\u6642,<\/p>\n

\u6703\u904b\u7528\u6392\u5e8f\u53ca\u5408\u4f75\u7684\u65b9\u5f0f\u9023\u63a5\u5b83\u5011.\u9996\u5148,\u6383\u63cf\u7b2c\u4e00\u500b\u8868(FROM\u5b50\u53e5\u4e2d\u6700\u5f8c\u7684\u90a3\u500b\u8868)\u4e26\u5c0d\u8a18\u9304\u9032\u884c\u6d3e\u5e8f,\u7136\u5f8c\u6383\u63cf\u7b2c\u4e8c\u500b\u8868(FROM\u5b50\u53e5\u4e2d\u6700\u5f8c\u7b2c\u4e8c\u500b\u8868),\u6700\u5f8c\u5c07\u6240\u6709\u5f9e\u7b2c\u4e8c\u500b\u8868\u4e2d\u6aa2\u7d22\u51fa\u7684\u8a18\u9304\u8207\u7b2c\u4e00\u500b\u8868\u4e2d\u5408\u9069\u8a18\u9304\u9032\u884c\u5408\u4f75.<\/p>\n

\u4f8b\u5982:<\/p>\n

\u8868 TAB1 16,384 \u689d\u8a18\u9304<\/p>\n

\u8868 TAB2 1 \u689d\u8a18\u9304<\/p>\n

\u9078\u64c7TAB2\u4f5c\u7232\u57fa\u790e\u8868 (\u6700\u597d\u7684\u65b9\u6cd5)<\/p>\n

select count(*) from tab1,tab2 \u57f7\u884c\u6642\u95930.96\u79d2<\/p>\n

\u9078\u64c7TAB2\u4f5c\u7232\u57fa\u790e\u8868 (\u4e0d\u4f73\u7684\u65b9\u6cd5)<\/p>\n

select count(*) from tab2,tab1 \u57f7\u884c\u6642\u959326.09\u79d2<\/p>\n

\u5982\u679c\u67093\u500b\u4ee5\u4e0a\u7684\u8868\u9023\u63a5\u67e5\u8a62, \u90a3\u5c31\u9700\u8981\u9078\u64c7\u4ea4\u53c9\u8868(intersection table)\u4f5c\u7232\u57fa\u790e\u8868,<\/p>\n

\u4ea4\u53c9\u8868\u662f\u6307\u90a3\u500b\u88ab\u5176\u4ed6\u8868\u6240\u5f15\u7528\u7684\u8868.<\/p>\n

\u4f8b\u5982:<\/p>\n

EMP\u8868\u63cf\u8ff0\u4e86LOCATION\u8868\u548cCATEGORY\u8868\u7684\u4ea4\u96c6.<\/p>\n

SELECT *<\/p>\n

FROM LOCATION L ,<\/p>\n

CATEGORY C,<\/p>\n

EMP E<\/p>\n

WHERE E.EMP_NO BETWEEN 1000 AND 2000<\/p>\n

AND E.CAT_NO = C.CAT_NO<\/p>\n

AND E.LOCN = L.LOCN<\/p>\n

\u5c07\u6bd4\u4e0b\u5217SQL\u66f4\u6709\u6548\u7387<\/p>\n

SELECT *<\/p>\n

FROM EMP E ,<\/p>\n

LOCATION L ,<\/p>\n

CATEGORY C<\/p>\n

WHERE E.CAT_NO = C.CAT_NO<\/p>\n

AND E.LOCN = L.LOCN<\/p>\n

AND E.EMP_NO BETWEEN 1000 AND 2000<\/p>\n

5. WHERE\u5b50\u53e5\u4e2d\u7684\u9023\u63a5\u9806\u5e8f<\/strong><\/p>\n

ORACLE\u63a1\u7528\u81ea\u4e0b\u800c\u4e0a\u7684\u9806\u5e8f\u89e3\u6790WHERE\u5b50\u53e5,\u6839\u64da\u9019\u500b\u539f\u7406,\u8868\u4e4b\u9593\u7684\u9023\u63a5\u5fc5\u9808\u5beb\u5728\u5176\u4ed6WHERE\u689d\u4ef6\u4e4b\u524d,<\/p>\n

\u90a3\u4e9b\u53ef\u4ee5\u904e\u6ffe\u6389\u6700\u5927\u6578\u91cf\u8a18\u9304\u7684\u689d\u4ef6\u5fc5\u9808\u5beb\u5728WHERE\u5b50\u53e5\u7684\u672b\u5c3e.<\/p>\n

\u4f8b\u5982:<\/p>\n

(\u4f4e\u6548,\u57f7\u884c\u6642\u9593156.3\u79d2)<\/p>\n

SELECT \u2026<\/p>\n

FROM EMP E<\/p>\n

WHERE SAL > 50000<\/p>\n

AND JOB = \u2018MANAGER\u2019<\/p>\n

AND 25 < (SELECT COUNT(*) FROM EMP<\/p>\n

WHERE MGR=E.EMPNO);<\/p>\n

(\u9ad8\u6548,\u57f7\u884c\u6642\u959310.6\u79d2)<\/p>\n

SELECT \u2026<\/p>\n

FROM EMP E<\/p>\n

WHERE 25 < (SELECT COUNT(*) FROM EMP<\/p>\n

WHERE MGR=E.EMPNO)<\/p>\n

AND SAL > 50000<\/p>\n

AND JOB = \u2018MANAGER\u2019;<\/p>\n

6. SELECT\u5b50\u53e5\u4e2d\u907f\u514d\u4f7f\u7528 \u2018 * \u2018<\/strong><\/p>\n

\u7576\u4f60\u60f3\u5728SELECT\u5b50\u53e5\u4e2d\u5217\u51fa\u6240\u6709\u7684COLUMN\u6642,\u4f7f\u7528\u52d5\u614bSQL\u5217\u5f15\u7528 \u2018*\u2019<\/p>\n

\u662f\u4e00\u500b\u65b9\u4fbf\u7684\u65b9\u6cd5.\u4e0d\u5e78\u7684\u662f,\u9019\u662f\u4e00\u500b\u975e\u5e38\u4f4e\u6548\u7684\u65b9\u6cd5. \u5be6\u969b\u4e0a,ORACLE\u5728\u89e3\u6790\u7684\u904e\u7a0b\u4e2d, \u6703\u5c07\u2019*\u2019 \u4f9d\u6b21\u8f49\u63db\u6210\u6240\u6709\u7684\u5217\u540d,<\/p>\n

\u9019\u500b\u5de5\u4f5c\u662f\u901a\u904e\u67e5\u8a62\u8cc7\u6599\u5b57\u5178\u5b8c\u6210\u7684, \u9019\u610f\u5473\u8457\u5c07\u8017\u8cbb\u66f4\u591a\u7684\u6642\u9593.<\/p>\n

7. \u6e1b\u5c11\u8a2a\u554f\u8cc7\u6599\u5eab\u7684\u6b21\u6578<\/strong><\/p>\n

\u7576\u57f7\u884c\u6bcf\u689dSQL\u8a9e\u53e5\u6642, ORACLE\u5728\u5167\u90e8\u57f7\u884c\u4e86\u8a31\u591a\u5de5\u4f5c: \u89e3\u6790SQL\u8a9e\u53e5, \u4f30\u7b97\u7d22\u5f15\u7684\u5229\u7528\u7387, \u7d81\u5b9a\u8b8a\u6578 , \u8b80\u8cc7\u6599\u584a\u7b49\u7b49.<\/p>\n

\u7531\u6b64\u53ef\u898b, \u6e1b\u5c11\u8a2a\u554f\u8cc7\u6599\u5eab\u7684\u6b21\u6578 , \u5c31\u80fd\u5be6\u969b\u4e0a\u6e1b\u5c11ORACLE\u7684\u5de5\u4f5c\u91cf.<\/p>\n

\u4f8b\u5982,<\/p>\n

\u4ee5\u4e0b\u6709\u4e09\u7a2e\u65b9\u6cd5\u53ef\u4ee5\u6aa2\u7d22\u51fa\u96c7\u54e1\u865f\u7b49\u65bc0342\u62160291\u7684\u8077\u54e1.<\/p>\n

\u65b9\u6cd51 (\u6700\u4f4e\u6548)<\/p>\n

SELECT EMP_NAME , SALARY , GRADE<\/p>\n

FROM EMP<\/p>\n

WHERE EMP_NO = 342;<\/p>\n

SELECT EMP_NAME , SALARY , GRADE<\/p>\n

FROM EMP<\/p>\n

WHERE EMP_NO = 291;<\/p>\n

\u65b9\u6cd52 (\u6b21\u4f4e\u6548)<\/p>\n

DECLARE<\/p>\n

CURSOR C1 (E_NO NUMBER) IS<\/p>\n

SELECT EMP_NAME,SALARY,GRADE<\/p>\n

FROM EMP<\/p>\n

WHERE EMP_NO = E_NO;<\/p>\n

BEGIN<\/p>\n

OPEN C1(342);<\/p>\n

FETCH C1 INTO \u2026,..,.. ;<\/p>\n

\u2026..<\/p>\n

OPEN C1(291);<\/p>\n

FETCH C1 INTO \u2026,..,.. ;<\/p>\n

CLOSE C1;<\/p>\n

END;<\/p>\n

\u65b9\u6cd53 (\u9ad8\u6548)<\/p>\n

SELECT A.EMP_NAME , A.SALARY , A.GRADE,<\/p>\n

B.EMP_NAME , B.SALARY , B.GRADE<\/p>\n

FROM EMP A,EMP B<\/p>\n

WHERE A.EMP_NO = 342<\/p>\n

AND B.EMP_NO = 291;<\/p>\n

\u6ce8\u610f:<\/p>\n

\u5728SQL*Plus , SQL*Forms\u548cPro*C\u4e2d\u91cd\u65b0\u8a2d\u7f6eARRAYSIZE\u53c3\u6578, \u53ef\u4ee5\u589e\u52a0\u6bcf\u6b21\u8cc7\u6599\u5eab\u8a2a\u554f\u7684\u6aa2\u7d22\u8cc7\u6599\u91cf<\/p>\n

,\u5efa\u8b70\u503c\u7232200<\/p>\n

8. \u4f7f\u7528DECODE\u51fd\u6578\u4f86\u6e1b\u5c11\u8655\u7406\u6642\u9593<\/strong><\/p>\n

\u4f7f\u7528DECODE\u51fd\u6578\u53ef\u4ee5\u907f\u514d\u91cd\u5fa9\u6383\u63cf\u76f8\u540c\u8a18\u9304\u6216\u91cd\u5fa9\u9023\u63a5\u76f8\u540c\u7684\u8868.<\/p>\n

\u4f8b\u5982:<\/p>\n

SELECT COUNT(*)\uff0cSUM(SAL)<\/p>\n

FROM\u3000EMP<\/p>\n

WHERE DEPT_NO = 0020<\/p>\n

AND ENAME LIKE\u3000\u2018SMITH%\u2019;<\/p>\n

SELECT COUNT(*)\uff0cSUM(SAL)<\/p>\n

FROM\u3000EMP<\/p>\n

WHERE DEPT_NO = 0030<\/p>\n

AND ENAME LIKE\u3000\u2018SMITH%\u2019;<\/p>\n

\u4f60\u53ef\u4ee5\u7528DECODE\u51fd\u6578\u9ad8\u6548\u5730\u5f97\u5230\u76f8\u540c\u7d50\u679c<\/p>\n

SELECT COUNT(DECODE(DEPT_NO,0020,\u2019X\u2019,NULL)) D0020_COUNT,<\/p>\n

COUNT(DECODE(DEPT_NO,0030,\u2019X\u2019,NULL)) D0030_COUNT,<\/p>\n

SUM(DECODE(DEPT_NO,0020,SAL,NULL)) D0020_SAL,<\/p>\n

SUM(DECODE(DEPT_NO,0030,SAL,NULL)) D0030_SAL<\/p>\n

FROM EMP WHERE ENAME LIKE \u2018SMITH%\u2019;<\/p>\n

\u985e\u4f3c\u7684,DECODE\u51fd\u6578\u4e5f\u53ef\u4ee5\u904b\u7528\u65bcGROUP BY \u548cORDER BY\u5b50\u53e5\u4e2d.<\/p>\n

9. \u6574\u5408\u7c21\u55ae,\u7121\u95dc\u806f\u7684\u8cc7\u6599\u5eab\u8a2a\u554f<\/strong><\/p>\n

\u5982\u679c\u4f60\u6709\u5e7e\u500b\u7c21\u55ae\u7684\u8cc7\u6599\u5eab\u67e5\u8a62\u8a9e\u53e5,\u4f60\u53ef\u4ee5\u628a\u5b83\u5011\u6574\u5408\u5230\u4e00\u500b\u67e5\u8a62\u4e2d(\u5373\u4f7f\u5b83\u5011\u4e4b\u9593\u6c92\u6709\u95dc\u4fc2)<\/p>\n

\u4f8b\u5982:<\/p>\n

SELECT NAME<\/p>\n

FROM EMP<\/p>\n

WHERE EMP_NO = 1234;<\/p>\n

SELECT NAME<\/p>\n

FROM DPT<\/p>\n

WHERE DPT_NO = 10 ;<\/p>\n

SELECT NAME<\/p>\n

FROM CAT<\/p>\n

WHERE CAT_TYPE = \u2018RD\u2019;<\/p>\n

\u4e0a\u9762\u76843\u500b\u67e5\u8a62\u53ef\u4ee5\u88ab\u5408\u4f75\u6210\u4e00\u500b:<\/p>\n

SELECT E.NAME , D.NAME , C.NAME<\/p>\n

FROM CAT C , DPT D , EMP E,DUAL X<\/p>\n

WHERE NVL(\u2018X\u2019,X.DUMMY) = NVL(\u2018X\u2019,E.ROWID(+))<\/p>\n

AND NVL(\u2018X\u2019,X.DUMMY) = NVL(\u2018X\u2019,D.ROWID(+))<\/p>\n

AND NVL(\u2018X\u2019,X.DUMMY) = NVL(\u2018X\u2019,C.ROWID(+))<\/p>\n

AND E.EMP_NO(+) = 1234<\/p>\n

AND D.DEPT_NO(+) = 10<\/p>\n

AND C.CAT_TYPE(+) = \u2018RD\u2019;<\/p>\n

(\u8b6f\u8005\u6309: \u96d6\u7136\u63a1\u53d6\u9019\u7a2e\u65b9\u6cd5,\u6548\u7387\u5f97\u5230\u63d0\u9ad8,\u4f46\u662f\u7a0b\u5f0f\u7684\u53ef\u8b80\u6027\u5927\u5927\u964d\u4f4e,\u6240\u4ee5\u8b80\u8005 \u9084\u662f\u8981\u6b0a\u8861\u4e4b\u9593\u7684\u5229\u5f0a)<\/p>\n

10. \u522a\u9664\u91cd\u5fa9\u8a18\u9304<\/strong><\/p>\n

\u6700\u9ad8\u6548\u7684\u522a\u9664\u91cd\u5fa9\u8a18\u9304\u65b9\u6cd5 ( \u56e0\u7232\u4f7f\u7528\u4e86ROWID)<\/p>\n

DELETE FROM EMP E<\/p>\n

WHERE E.ROWID > (SELECT MIN(X.ROWID)<\/p>\n

FROM EMP X<\/p>\n

WHERE X.EMP_NO = E.EMP_NO);<\/p>\n

11. \u7528TRUNCATE\u66ff\u4ee3DELETE<\/strong><\/p>\n

\u7576\u522a\u9664\u8868\u4e2d\u7684\u8a18\u9304\u6642,\u5728\u901a\u5e38\u60c5\u6cc1\u4e0b, \u56de\u6efe\u6bb5(rollback segments ) \u7528\u4f86\u5b58\u653e\u53ef\u4ee5\u88ab\u6062\u5fa9\u7684\u8cc7\u8a0a.<\/p>\n

\u5982\u679c\u4f60\u6c92\u6709COMMIT\u4e8b\u52d9,ORACLE\u6703\u5c07\u8cc7\u6599\u6062\u5fa9\u5230\u522a\u9664\u4e4b\u524d\u7684\u72c0\u614b(\u6e96\u78ba\u5730\u8aaa\u662f<\/p>\n

\u6062\u5fa9\u5230\u57f7\u884c\u522a\u9664\u547d\u4ee4\u4e4b\u524d\u7684\u72c0\u6cc1)<\/p>\n

\u800c\u7576\u904b\u7528TRUNCATE\u6642,<\/p>\n

\u56de\u6efe\u6bb5\u4e0d\u518d\u5b58\u653e\u4efb\u4f55\u53ef\u88ab\u6062\u5fa9\u7684\u8cc7\u8a0a.\u7576\u547d\u4ee4\u904b\u884c\u5f8c,\u8cc7\u6599\u4e0d\u80fd\u88ab\u6062\u5fa9.\u56e0\u6b64\u5f88\u5c11\u7684\u8cc7\u6e90\u88ab\u8abf\u7528,\u57f7\u884c\u6642\u9593\u4e5f\u6703\u5f88\u77ed.<\/p>\n

(\u8b6f\u8005\u6309: TRUNCATE\u53ea\u5728\u522a\u9664\u5168\u8868\u9069\u7528,TRUNCATE\u662fDDL\u4e0d\u662fDML)<\/p>\n

12. \u5118\u91cf\u591a\u4f7f\u7528COMMIT<\/strong><\/p>\n

\u53ea\u8981\u6709\u53ef\u80fd,\u5728\u7a0b\u5f0f\u4e2d\u5118\u91cf\u591a\u4f7f\u7528COMMIT, \u9019\u6a23\u7a0b\u5f0f\u7684\u6027\u80fd\u5f97\u5230\u63d0\u9ad8,\u9700\u6c42\u4e5f\u6703\u56e0\u7232COMMIT\u6240\u91cb\u653e\u7684\u8cc7\u6e90\u800c\u6e1b\u5c11:<\/p>\n

COMMIT\u6240\u91cb\u653e\u7684\u8cc7\u6e90:<\/p>\n

a. \u56de\u6efe\u6bb5\u4e0a\u7528\u65bc\u6062\u5fa9\u8cc7\u6599\u7684\u8cc7\u8a0a.<\/p>\n

b. \u88ab\u7a0b\u5f0f\u8a9e\u53e5\u7372\u5f97\u7684\u9396<\/p>\n

c. redo log buffer \u4e2d\u7684\u7a7a\u9593<\/p>\n

d. ORACLE\u7232\u7ba1\u7406\u4e0a\u8ff03\u7a2e\u8cc7\u6e90\u4e2d\u7684\u5167\u90e8\u82b1\u8cbb<\/p>\n

(\u8b6f\u8005\u6309: \u5728\u4f7f\u7528COMMIT\u6642\u5fc5\u9808\u8981\u6ce8\u610f\u5230\u4e8b\u52d9\u7684\u5b8c\u6574\u6027,\u73fe\u5be6\u4e2d\u6548\u7387\u548c\u4e8b\u52d9\u5b8c\u6574\u6027\u5f80\u5f80\u662f\u9b5a\u548c\u718a\u638c\u4e0d\u53ef\u5f97\u517c)<\/p>\n

13. \u8a08\u7b97\u8a18\u9304\u689d\u6578<\/strong><\/p>\n

\u548c\u4e00\u822c\u7684\u89c0\u9ede\u76f8\u53cd, count(*) \u6bd4count(1)\u7a0d\u5feb , \u7576\u7136\u5982\u679c\u53ef\u4ee5\u901a\u904e\u7d22\u5f15\u6aa2\u7d22,\u5c0d\u7d22\u5f15\u5217\u7684\u8a08\u6578\u4ecd\u820a\u662f\u6700\u5feb\u7684. \u4f8b\u5982<\/p>\n

COUNT(EMPNO)<\/p>\n

(\u8b6f\u8005\u6309: \u5728CSDN\u8ad6\u58c7\u4e2d,\u66fe\u7d93\u5c0d\u6b64\u6709\u904e\u76f8\u7576\u71b1\u70c8\u7684\u8a0e\u8ad6,<\/p>\n

\u4f5c\u8005\u7684\u89c0\u9ede\u4e26\u4e0d\u5341\u5206\u6e96\u78ba,\u901a\u904e\u5be6\u969b\u7684\u6e2c\u8a66,\u4e0a\u8ff0\u4e09\u7a2e\u65b9\u6cd5\u4e26\u6c92\u6709\u986f\u8457\u7684\u6027\u80fd\u5dee\u5225)<\/p>\n

14. \u7528Where\u5b50\u53e5\u66ff\u63dbHAVING\u5b50\u53e5<\/strong><\/p>\n

\u907f\u514d\u4f7f\u7528HAVING\u5b50\u53e5, HAVING \u53ea\u6703\u5728\u6aa2\u7d22\u51fa\u6240\u6709\u8a18\u9304\u4e4b\u5f8c\u624d\u5c0d\u7d50\u679c\u96c6\u9032\u884c\u904e\u6ffe. \u9019\u500b\u8655\u7406\u9700\u8981\u6392\u5e8f,\u7e3d\u8a08\u7b49\u64cd\u4f5c.<\/p>\n

\u5982\u679c\u80fd\u901a\u904eWHERE\u5b50\u53e5\u9650\u5236\u8a18\u9304\u7684\u6578\u76ee,\u90a3\u5c31\u80fd\u6e1b\u5c11\u9019\u65b9\u9762\u7684\u958b\u92b7.<\/p>\n

\u4f8b\u5982:<\/p>\n

\u4f4e\u6548:<\/p>\n

SELECT REGION\uff0cAVG(LOG_SIZE)<\/p>\n

FROM LOCATION<\/p>\n

GROUP BY REGION<\/p>\n

HAVING REGION REGION != \u2018SYDNEY\u2019<\/p>\n

AND REGION != \u2018PERTH\u2019<\/p>\n

\u9ad8\u6548<\/p>\n

SELECT REGION\uff0cAVG(LOG_SIZE)<\/p>\n

FROM LOCATION<\/p>\n

WHERE REGION REGION != \u2018SYDNEY\u2019<\/p>\n

AND REGION != \u2018PERTH\u2019<\/p>\n

GROUP BY REGION<\/p>\n

(\u8b6f\u8005\u6309: HAVING \u4e2d\u7684\u689d\u4ef6\u4e00\u822c\u7528\u65bc\u5c0d\u4e00\u4e9b\u96c6\u5408\u51fd\u6578\u7684\u6bd4\u8f03,\u5982COUNT() \u7b49\u7b49.<\/p>\n

\u9664\u6b64\u800c\u5916,\u4e00\u822c\u7684\u689d\u4ef6\u61c9\u8a72\u5beb\u5728WHERE\u5b50\u53e5\u4e2d)<\/p>\n

15. \u6e1b\u5c11\u5c0d\u8868\u7684\u67e5\u8a62<\/strong><\/p>\n

\u5728\u542b\u6709\u5b50\u67e5\u8a62\u7684SQL\u8a9e\u53e5\u4e2d,\u8981\u7279\u5225\u6ce8\u610f\u6e1b\u5c11\u5c0d\u8868\u7684\u67e5\u8a62.<\/p>\n

\u4f8b\u5982:<\/p>\n

\u4f4e\u6548<\/p>\n

SELECT TAB_NAME<\/p>\n

FROM TABLES<\/p>\n

WHERE TAB_NAME = ( SELECT TAB_NAME<\/p>\n

FROM TAB_COLUMNS<\/p>\n

WHERE VERSION = 604)<\/p>\n

AND\u3000DB_VER= ( SELECT DB_VER<\/p>\n

FROM TAB_COLUMNS<\/p>\n

WHERE VERSION = 604)<\/p>\n

\u9ad8\u6548<\/p>\n

SELECT TAB_NAME<\/p>\n

FROM TABLES<\/p>\n

WHERE (TAB_NAME,DB_VER)<\/p>\n

= ( SELECT TAB_NAME,DB_VER)<\/p>\n

FROM TAB_COLUMNS<\/p>\n

WHERE VERSION = 604)<\/p>\n

Update \u591a\u500bColumn \u4f8b\u5b50:<\/p>\n

\u4f4e\u6548:<\/p>\n

UPDATE EMP<\/p>\n

SET EMP_CAT = (SELECT MAX(CATEGORY) FROM EMP_CATEGORIES),<\/p>\n

SAL_RANGE = (SELECT MAX(SAL_RANGE) FROM EMP_CATEGORIES)<\/p>\n

WHERE EMP_DEPT = 0020;<\/p>\n

\u9ad8\u6548:<\/p>\n

UPDATE EMP<\/p>\n

SET (EMP_CAT, SAL_RANGE)<\/p>\n

= (SELECT MAX(CATEGORY) , MAX(SAL_RANGE)<\/p>\n

FROM EMP_CATEGORIES)<\/p>\n

WHERE EMP_DEPT = 0020;<\/p>\n

16. \u901a\u904e\u5167\u90e8\u51fd\u6578\u63d0\u9ad8SQL\u6548\u7387<\/strong><\/p>\n

SELECT H.EMPNO,E.ENAME,H.HIST_TYPE,T.TYPE_DESC,COUNT(*)<\/p>\n

FROM HISTORY_TYPE T,EMP E,EMP_HISTORY H<\/p>\n

WHERE H.EMPNO = E.EMPNO<\/p>\n

AND H.HIST_TYPE = T.HIST_TYPE<\/p>\n

GROUP BY H.EMPNO,E.ENAME,H.HIST_TYPE,T.TYPE_DESC;<\/p>\n

\u901a\u904e\u8abf\u7528\u4e0b\u9762\u7684\u51fd\u6578\u53ef\u4ee5\u63d0\u9ad8\u6548\u7387.<\/p>\n

FUNCTION LOOKUP_HIST_TYPE(TYP IN NUMBER) RETURN VARCHAR2<\/p>\n

AS<\/p>\n

TDESC VARCHAR2(30);<\/p>\n

CURSOR C1 IS<\/p>\n

SELECT TYPE_DESC<\/p>\n

FROM HISTORY_TYPE<\/p>\n

WHERE HIST_TYPE = TYP;<\/p>\n

BEGIN<\/p>\n

OPEN C1;<\/p>\n

FETCH C1 INTO TDESC;<\/p>\n

CLOSE C1;<\/p>\n

RETURN (NVL(TDESC,\u2019 \u2019));<\/p>\n

END;<\/p>\n

FUNCTION LOOKUP_EMP(EMP IN NUMBER) RETURN VARCHAR2<\/p>\n

AS<\/p>\n

ENAME VARCHAR2(30);<\/p>\n

CURSOR C1 IS<\/p>\n

SELECT ENAME<\/p>\n

FROM EMP<\/p>\n

WHERE EMPNO=EMP;<\/p>\n

BEGIN<\/p>\n

OPEN C1;<\/p>\n

FETCH C1 INTO ENAME;<\/p>\n

CLOSE C1;<\/p>\n

RETURN (NVL(ENAME,\u2019 \u2019));<\/p>\n

END;<\/p>\n

SELECT H.EMPNO,LOOKUP_EMP(H.EMPNO),<\/p>\n

H.HIST_TYPE,LOOKUP_HIST_TYPE(H.HIST_TYPE),COUNT(*)<\/p>\n

FROM EMP_HISTORY H<\/p>\n

GROUP BY H.EMPNO , H.HIST_TYPE;<\/p>\n

(\u8b6f\u8005\u6309: \u7d93\u5e38\u5728\u8ad6\u58c7\u4e2d\u770b\u5230\u5982 \u2019\u80fd\u4e0d\u80fd\u7528\u4e00\u500bSQL\u5beb\u51fa\u2026.\u2019 \u7684\u8cbc\u5b50, \u6b8a\u4e0d\u77e5\u8907\u96dc\u7684SQL\u5f80\u5f80\u72a7\u7272\u4e86\u57f7\u884c\u6548\u7387.<\/p>\n

\u80fd\u5920\u638c\u63e1\u4e0a\u9762\u7684\u904b\u7528\u51fd\u6578\u89e3\u6c7a\u554f\u984c\u7684\u65b9\u6cd5\u5728\u5be6\u969b\u5de5\u4f5c\u4e2d\u662f\u975e\u5e38\u6709\u610f\u7fa9\u7684)<\/p>\n

17. \u4f7f\u7528\u8868\u7684\u5225\u540d(Alias)<\/strong><\/p>\n

\u7576\u5728SQL\u8a9e\u53e5\u4e2d\u9023\u63a5\u591a\u500b\u8868\u6642,<\/p>\n

\u8acb\u4f7f\u7528\u8868\u7684\u5225\u540d\u4e26\u628a\u5225\u540d\u5b57\u9996\u65bc\u6bcf\u500bColumn\u4e0a.\u9019\u6a23\u4e00\u4f86,\u5c31\u53ef\u4ee5\u6e1b\u5c11\u89e3\u6790\u7684\u6642\u9593\u4e26\u6e1b\u5c11\u90a3\u4e9b\u7531Column\u6b67\u7fa9\u5f15\u8d77\u7684\u8a9e\u6cd5\u932f\u8aa4.<\/p>\n

(\u8b6f\u8005\u6ce8:<\/p>\n

Column\u6b67\u7fa9\u6307\u7684\u662f\u7531\u65bcSQL\u4e2d\u4e0d\u540c\u7684\u8868\u5177\u6709\u76f8\u540c\u7684Column\u540d,\u7576SQL\u8a9e\u53e5\u4e2d\u51fa\u73fe\u9019\u500bColumn\u6642,SQL\u89e3\u6790\u5668\u7121\u6cd5\u5224\u65b7\u9019\u500bColumn\u7684\u6b78\u5c6c)<\/p>\n

18. \u7528EXISTS\u66ff\u4ee3IN<\/strong><\/p>\n

\u5728\u8a31\u591a\u57fa\u65bc\u57fa\u790e\u8868\u7684\u67e5\u8a62\u4e2d,\u7232\u4e86\u6eff\u8db3\u4e00\u500b\u689d\u4ef6,\u5f80\u5f80\u9700\u8981\u5c0d\u53e6\u4e00\u500b\u8868\u9032\u884c\u806f\u63a5.\u5728\u9019\u7a2e\u60c5\u6cc1\u4e0b, \u4f7f\u7528EXISTS(\u6216NOT<\/p>\n

EXISTS)\u901a\u5e38\u5c07\u63d0\u9ad8\u67e5\u8a62\u7684\u6548\u7387.<\/p>\n

\u4f4e\u6548:<\/p>\n

SELECT *<\/p>\n

FROM EMP (\u57fa\u790e\u8868)<\/p>\n

WHERE EMPNO > 0<\/p>\n

AND DEPTNO IN (SELECT DEPTNO<\/p>\n

FROM DEPT<\/p>\n

WHERE LOC = \u2018MELB\u2019)<\/p>\n

\u9ad8\u6548:<\/p>\n

SELECT *<\/p>\n

FROM EMP (\u57fa\u790e\u8868)<\/p>\n

WHERE EMPNO > 0<\/p>\n

AND EXISTS (SELECT \u2018X\u2019<\/p>\n

FROM DEPT<\/p>\n

WHERE DEPT.DEPTNO = EMP.DEPTNO<\/p>\n

AND LOC = \u2018MELB\u2019)<\/p>\n

(\u8b6f\u8005\u6309: \u76f8\u5c0d\u4f86\u8aaa,\u7528NOT EXISTS\u66ff\u63dbNOT IN \u5c07\u66f4\u986f\u8457\u5730\u63d0\u9ad8\u6548\u7387,\u4e0b\u4e00\u7bc0\u4e2d\u5c07\u6307\u51fa)<\/p>\n

19. \u7528NOT EXISTS\u66ff\u4ee3NOT IN<\/p>\n

\u5728\u5b50\u67e5\u8a62\u4e2d,NOT IN\u5b50\u53e5\u5c07\u57f7\u884c\u4e00\u500b\u5167\u90e8\u7684\u6392\u5e8f\u548c\u5408\u4f75. \u7121\u8ad6\u5728\u54ea\u7a2e\u60c5\u6cc1\u4e0b,NOT IN\u90fd\u662f\u6700\u4f4e\u6548\u7684<\/p>\n

(\u56e0\u7232\u5b83\u5c0d\u5b50\u67e5\u8a62\u4e2d\u7684\u8868\u57f7\u884c\u4e86\u4e00\u500b\u5168\u8868\u904d\u66c6). \u7232\u4e86\u907f\u514d\u4f7f\u7528NOT IN ,\u6211\u5011\u53ef\u4ee5\u628a\u5b83\u6539\u5beb\u6210\u5916\u9023\u63a5(Outer Joins)\u6216NOT<\/p>\n

EXISTS.<\/p>\n

\u4f8b\u5982:<\/p>\n

SELECT \u2026<\/p>\n

FROM EMP<\/p>\n

WHERE DEPT_NO NOT IN (SELECT DEPT_NO<\/p>\n

FROM DEPT<\/p>\n

WHERE DEPT_CAT=\u2019A\u2019);<\/p>\n

\u7232\u4e86\u63d0\u9ad8\u6548\u7387.\u6539\u5beb\u7232:<\/p>\n

(\u65b9\u6cd5\u4e00: \u9ad8\u6548)<\/p>\n

SELECT \u2026.<\/p>\n

FROM EMP A,DEPT B<\/p>\n

WHERE A.DEPT_NO = B.DEPT(+)<\/p>\n

AND B.DEPT_NO IS NULL<\/p>\n

AND B.DEPT_CAT(+) = \u2018A\u2019<\/p>\n

(\u65b9\u6cd5\u4e8c: \u6700\u9ad8\u6548)<\/p>\n

SELECT \u2026.<\/p>\n

FROM EMP E<\/p>\n

WHERE NOT EXISTS (SELECT \u2018X\u2019<\/p>\n

FROM DEPT D<\/p>\n

WHERE D.DEPT_NO = E.DEPT_NO<\/p>\n

AND DEPT_CAT = \u2018A\u2019);<\/p>\n

20. \u7528\u8868\u9023\u63a5\u66ff\u63dbEXISTS<\/strong><\/p>\n

\u901a\u5e38\u4f86\u8aaa , \u63a1\u7528\u8868\u9023\u63a5\u7684\u65b9\u5f0f\u6bd4EXISTS\u66f4\u6709\u6548\u7387<\/p>\n

SELECT ENAME<\/p>\n

FROM EMP E<\/p>\n

WHERE EXISTS (SELECT \u2018X\u2019<\/p>\n

FROM DEPT<\/p>\n

WHERE DEPT_NO = E.DEPT_NO<\/p>\n

AND DEPT_CAT = \u2018A\u2019);<\/p>\n

(\u66f4\u9ad8\u6548)<\/p>\n

SELECT ENAME<\/p>\n

FROM DEPT D,EMP E<\/p>\n

WHERE E.DEPT_NO = D.DEPT_NO<\/p>\n

AND DEPT_CAT = \u2018A\u2019 ;<\/p>\n

(\u8b6f\u8005\u6309: \u5728RBO\u7684\u60c5\u6cc1\u4e0b,\u524d\u8005\u7684\u57f7\u884c\u8def\u5f91\u5305\u62ecFILTER,\u5f8c\u8005\u4f7f\u7528NESTED LOOP)<\/p>\n","protected":false},"excerpt":{"rendered":"","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[41],"tags":[],"class_list":["post-707","post","type-post","status-publish","format-standard","hentry","category-oracle","no-featured-image"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/jason695.why3s.tw\/wordpress\/wp-json\/wp\/v2\/posts\/707","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/jason695.why3s.tw\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jason695.why3s.tw\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jason695.why3s.tw\/wordpress\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/jason695.why3s.tw\/wordpress\/wp-json\/wp\/v2\/comments?post=707"}],"version-history":[{"count":0,"href":"https:\/\/jason695.why3s.tw\/wordpress\/wp-json\/wp\/v2\/posts\/707\/revisions"}],"wp:attachment":[{"href":"https:\/\/jason695.why3s.tw\/wordpress\/wp-json\/wp\/v2\/media?parent=707"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jason695.why3s.tw\/wordpress\/wp-json\/wp\/v2\/categories?post=707"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jason695.why3s.tw\/wordpress\/wp-json\/wp\/v2\/tags?post=707"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}