| 澶辨晥閾炬帴澶勭悊 |
|
Oracle璇彞浼樺寲53涓鍒欒瑙?PDF 涓嬭澆
鏈珯鏁寸悊涓嬭澆錛?/strong>
閾炬帴錛?a target="_blank">https://pan.baidu.com/s/19DKU1d15BjVt9kbKPvI4NA
鎻愬彇鐮侊細sqbx
鐩稿叧鎴浘錛?/strong>
涓昏鍐呭錛?/strong>
1 銆侀€夌敤閫傚悎鐨?ORACLE 浼樺寲鍣?/div>
ORACLE 鐨勪紭鍖栧櫒鍏辨湁 3 縐嶏細
1) RULE(鍩轟簬瑙勫垯)
2) COST(鍩轟簬鎴愭湰)
3) CHOOSE(閫夋嫨鎬?
璁劇疆緙虹渷鐨勪紭鍖栧櫒錛屽彲浠ラ€氳繃瀵筰nit.ora鏂囦歡涓璒PTIMIZER_MODE鍙傛暟鐨勫悇縐嶅０鏄庯紝
濡?RULE錛孋OST錛孋HOOSE錛孉LL_ROWS錛孎IRST_ROWS銆備綘褰撶劧涔熷彲浠ュ湪 SQL 鍙ョ駭鎴栨槸浼?/div>
璇?session)綰у鍏惰繘琛岃鐩栥€?/div>
涓轟簡浣跨敤鍩轟簬鎴愭湰鐨勪紭鍖栧櫒(CBO錛孋ost-Based Optimizer)錛屼綘蹇呴』緇忓父榪愯
analyze 鍛戒護錛屼互澧炲姞鏁版嵁搴撲腑鐨勫璞＄粺璁′俊鎭?object statistics)鐨勫噯紜€с€?/div>
濡傛灉鏁版嵁搴撶殑浼樺寲鍣ㄦā寮忚緗負閫夋嫨鎬?CHOOSE)錛岄偅涔堝疄闄呯殑浼樺寲鍣ㄦā寮忓皢鍜屾槸鍚﹁繍
琛岃繃 analyze 鍛戒護鏈夊叧銆傚鏋?table 宸茬粡琚?analyze 榪囷紝浼樺寲鍣ㄦā寮忓皢鑷姩鎴愪負 CBO錛?/div>
鍙嶄箣錛屾暟鎹簱灝嗛噰鐢?RULE 褰㈠紡鐨勪紭鍖栧櫒銆?/div>
鍦ㄧ己鐪佹儏鍐典笅錛孫RACLE 閲囩敤 CHOOSE 浼樺寲鍣紝涓轟簡閬垮厤閭ｄ簺涓嶅繀瑕佺殑鍏ㄨ〃鎵弿(full
table scan)錛屼綘蹇呴』灝介噺閬垮厤浣跨敤 CHOOSE 浼樺寲鍣紝鑰岀洿鎺ラ噰鐢ㄥ熀浜庤鍒欐垨鑰呭熀浜庢垚鏈?/div>
鐨勪紭鍖栧櫒銆?/div>
2 銆佽闂?Table 鐨勬柟寮?/div>
ORACLE 閲囩敤涓ょ璁塊棶琛ㄤ腑璁板綍鐨勬柟寮忥細
1) 鍏ㄨ〃鎵弿
鍏ㄨ〃鎵弿灝辨槸欏哄簭鍦拌闂〃涓瘡鏉¤褰曘€侽RACLE 閲囩敤涓€嬈¤鍏ュ涓暟鎹潡(database
block)鐨勬柟寮忎紭鍖栧叏琛ㄦ壂鎻忋€?/div>
2) 閫氳繃 ROWID 璁塊棶琛?/div>
浣犲彲浠ラ噰鐢ㄥ熀浜?ROWID 鐨勮闂柟寮忔儏鍐碉紝鎻愰珮璁塊棶琛ㄧ殑鏁堢巼錛孯OWID 鍖呭惈浜嗚〃涓褰?/div>
鐨勭墿鐞嗕綅緗俊鎭紝ORACLE 閲囩敤绱㈠紩(INDEX)瀹炵幇浜嗘暟鎹拰瀛樻斁鏁版嵁鐨勭墿鐞嗕綅緗?ROWID)
涔嬮棿鐨勮仈緋匯€傞€氬父绱㈠紩鎻愪緵浜嗗揩閫熻闂?ROWID 鐨勬柟娉曪紝鍥犳閭ｄ簺鍩轟簬绱㈠紩鍒楃殑鏌ヨ灝卞彲浠?/div>
寰楀埌鎬ц兘涓婄殑鎻愰珮銆?/div>
3 銆佸叡浜?SQL 璇彞
涓轟簡涓嶉噸澶嶈В鏋愮浉鍚岀殑 SQL 璇彞錛屽湪絎竴嬈¤В鏋愪箣鍚庯紝ORACLE 灝?SQL 璇彞瀛樻斁鍦ㄥ唴
瀛樹腑銆傝繖鍧椾綅浜庣郴緇熷叏灞€鍖哄煙 SGA(system global area)鐨勫叡浜睜(shared buffer
pool)涓殑鍐呭瓨鍙互琚墍鏈夌殑鏁版嵁搴撶敤鎴峰叡浜€傚洜姝わ紝褰撲綘鎵ц涓€涓?SQL 璇彞(鏈夋椂琚О涓?/div>
涓€涓父鏍?鏃訛紝濡傛灉瀹冨拰涔嬪墠鐨勬墽琛岃繃鐨勮鍙ュ畬鍏ㄧ浉鍚岋紝ORACLE 灝辮兘寰堝揩鑾峰緱宸茬粡琚В鏋?/div>
鐨勮鍙ヤ互鍙婃渶濂界殑鎵ц璺緞銆侽RACLE 鐨勮繖涓姛鑳藉ぇ澶у湴鎻愰珮浜?SQL 鐨勬墽琛屾€ц兘騫惰妭鐪佷簡鍐?/div>
瀛樼殑浣跨敤銆?/div>
鍙儨鐨勬槸 ORACLE 鍙綆€鍗曠殑琛ㄦ彁渚涢珮閫熺紦鍐?cache buffering)錛岃繖涓姛鑳藉茍涓?/div>
閫傜敤浜庡琛ㄨ繛鎺ユ煡璇€?/div>
鏁版嵁搴撶鐞嗗憳蹇呴』鍦?init.ora 涓負榪欎釜鍖哄煙璁劇疆鍚堥€傜殑鍙傛暟錛屽綋榪欎釜鍐呭瓨鍖哄煙瓚婂ぇ錛?/div>
灝卞彲浠ヤ繚鐣欐洿澶氱殑璇彞錛屽綋鐒惰鍏變韓鐨勫彲鑳芥€т篃灝辮秺澶т簡銆?/div>
褰撲綘鍚?ORACLE 鎻愪氦涓€涓?SQL 璇彞錛孫RACLE 浼氶鍏堝湪榪欏潡鍐呭瓨涓煡鎵劇浉鍚岀殑璇彞銆?/div>
榪欓噷闇€瑕佹敞鏄庣殑鏄紝ORACLE 瀵逛袱鑰呴噰鍙栫殑鏄竴縐嶄弗鏍煎尮閰嶏紝瑕佽揪鎴愬叡浜紝SQL 璇彞蹇?/div>
欏誨畬鍏ㄧ浉鍚?鍖呮嫭絀烘牸錛屾崲琛岀瓑)銆?/div>
鍏變韓鐨勮鍙ュ繀欏繪弧瓚充笁涓潯浠訛細
1) 瀛楃綰х殑姣旇緝錛屽綋鍓嶈鎵ц鐨勮鍙ュ拰鍏變韓姹犱腑鐨勮鍙ュ繀欏誨畬鍏ㄧ浉鍚屻€?/div>
渚嬪錛?/div>
SELECT * FROM EMP;
鍜屼笅鍒楁瘡涓€涓兘涓嶅悓錛?/div>
SELECT * from EMP;
Select * From Emp;
SELECT* FROM EMP;
2) 涓や釜璇彞鎵€鎸囩殑瀵硅薄蹇呴』瀹屽叏鐩稿悓銆?/div>
渚嬪錛?/div>
鐢ㄦ埛 瀵硅薄鍚?濡備綍璁塊棶
Jack sal_limit private synonym
Work_city public synonym
Plant_detail public synonym
Jill sal_limit private synonym
Work_city public synonym
Plant_detail table owner
|
| Java1234瀹樻柟緹?5錛?/td> | 
|
| Java1234瀹樻柟緹?5錛?/td> | 838462530 |
鑻忓叕緗戝畨澶?32061202001004鍙?/p>
