ORACLE Histograms (鐩存柟鍥_数据库

ORACLE Histograms (鐩存柟鍥?

涓?.浣曡皳鐩存柟鍥撅細

鐩存柟鍥炬槸涓?绉嶇粺璁″?涓婄殑宸ュ叿锛屽苟闈濷racle涓撴湁銆傞?氬父鐢ㄤ簬瀵硅?绠＄悊瀵硅薄鐨勬煇涓?柟闈㈢殑璐ㄩ噺鎯呭喌杩涜?绠＄悊锛岄?氬父鎯呭喌涓嬪畠浼氳〃鐜颁负涓?绉嶅嚑浣曞浘褰㈣〃锛岃繖涓?浘褰㈣〃鏄?牴鎹?粠瀹為檯鐜??涓?墍鏀堕泦鏉ョ殑琚??鐞嗗?璞℃煇涓?柟闈㈢殑璐ㄩ噺鍒嗗竷鎯呭喌鐨勬暟鎹?墍缁樺埗鎴愮殑锛岄?氬父浼氱敾鎴愪互鏁伴噺涓哄簳杈癸紝浠ラ?搴︿负楂樺害鐨勪竴绯诲垪杩炴帴璧锋潵鐨勭煩褰㈠浘锛屽洜姝ょ洿鏂瑰浘鍦ㄧ粺璁″?涓婁篃绉颁负璐ㄩ噺鍒嗗竷鍥俱?傛瘮濡備笅鍥炬墍绀猴紝鏄?竴涓?互鍏冲?鐢熷寲瀛﹁?冭瘯鎴愮哗鍒嗘暟鍒嗗竷鎯呭喌缁樺埗鐨勭洿鏂瑰浘锛?/p>

聽

浜?Oracle涓?洿鏂瑰浘鐨勪綔鐢?細

鏃㈢劧鐩存柟鍥炬槸涓?绉嶅?琚??鐞嗗?璞℃煇涓?鏂归潰璐ㄩ噺杩涜?绠＄悊鐨勬弿杩板伐鍏凤紝閭ｄ箞鍦∣racle涓?嚜鐒跺畠涔熸槸瀵筄racle涓?煇涓??璞¤川閲忕殑鎻忚堪宸ュ叿锛岃繖涓??璞″氨鏄疧racle涓?渶閲嶈?鐨勪笢瑗库?斺?斺?滄暟鎹??濄??/p>

鍦∣racle涓?洿鏂瑰浘鏄?竴绉嶅?鏁版嵁鍒嗗竷璐ㄩ噺鎯呭喌杩涜?鎻忚堪鐨勫伐鍏枫?傚畠浼氭寜鐓ф煇涓?鍒椾笉鍚屽?煎嚭鐜版暟閲忓?灏戯紝浠ュ強鍑虹幇鐨勯?鐜囬珮浣庢潵缁樺埗鏁版嵁鐨勫垎甯冩儏鍐碉紝浠ヤ究鑳藉?鎸囧?浼樺寲鍣ㄦ牴鎹?暟鎹?殑鍒嗗竷鍋氬嚭姝ｇ‘鐨勯?夋嫨銆傚湪鏌愪簺鎯呭喌涓嬶紝琛ㄧ殑鍒椾腑鐨勬暟鍊煎垎甯冨皢浼氬奖鍝嶄紭鍖栧櫒浣跨敤绱㈠紩杩樻槸鎵ц?鍏ㄨ〃鎵?弿鐨勫喅绛栥?傚綋 where 瀛愬彞鐨勫?煎叿鏈変笉鎴愭瘮渚嬫暟閲忕殑鏁板?兼椂锛屽皢鍑虹幇杩欑?鎯呭喌锛屼娇寰楀叏琛ㄦ壂鎻忔瘮绱㈠紩璁块棶鐨勬垚鏈?洿浣庛?傝繖绉嶆儏鍐典笅濡傛灉where 瀛愬彞鐨勮繃婊よ皳璇嶅垪涔嬩笂涓婃湁涓?涓?悎鐞嗙殑姝ｇ‘鐨勭洿鏂瑰浘锛屽皢浼氬?浼樺寲鍣ㄥ仛鍑烘?纭?殑閫夋嫨鍙戞尌宸ㄥぇ鐨勪綔鐢?紝浣垮緱SQL璇?彞鎵ц?鎴愭湰鏈?浣庝粠鑰屾彁鍗囨?ц兘銆?/p>

涓?Oracle涓?娇鐢ㄧ洿鏂瑰浘鐨勫満鍚堬細

鍦ㄥ垎鏋愯〃鎴栫储寮曟椂锛岀洿鏂瑰浘鐢ㄤ簬璁板綍鏁版嵁鐨勫垎甯冦?傞?氳繃鑾峰緱璇ヤ俊鎭?紝鍩轰簬鎴愭湰鐨勪紭聽鍖栧櫒灏卞彲浠ュ喅瀹氫娇鐢ㄥ皢杩斿洖灏戦噺琛岀殑绱㈠紩锛岃?岄伩鍏嶄娇鐢ㄥ熀浜庨檺鍒舵潯浠惰繑鍥炶?澶氳?鐨勭储寮曘?傜洿鏂瑰浘鐨勪娇鐢ㄤ笉鍙楃储寮曠殑闄愬埗锛屽彲浠ュ湪琛ㄧ殑浠讳綍鍒椾笂鏋勫缓鐩存柟鍥俱??/p>

鏋勯?犵洿鏂瑰浘鏈?涓昏?鐨勫師鍥犲氨鏄?府鍔╀紭鍖栧櫒鍦ㄨ〃涓?暟鎹?弗閲嶅亸鏂滄椂鍋氬嚭鏇村ソ鐨勮?鍒掞細渚嬪?锛屽?鏋滀竴鍒颁袱涓??兼瀯鎴愪簡琛ㄤ腑鐨勫ぇ閮ㄥ垎鏁版嵁(鏁版嵁鍋忔枩)锛岀浉鍏崇殑绱㈠紩灏卞彲鑳芥棤娉曞府鍔╁噺灏戞弧瓒虫煡璇㈡墍闇?鐨処/O鏁伴噺銆傚垱寤虹洿鏂瑰浘鍙?互璁╁熀浜庢垚鏈?殑浼樺寲鍣ㄧ煡閬撲綍鏃朵娇鐢ㄧ储寮曟墠鏈?鍚堥?傦紝鎴栦綍鏃跺簲璇ユ牴鎹甒HERE瀛愬彞涓?殑鍊艰繑鍥炶〃涓?0锛呯殑璁板綍銆?/p>

閫氬父鎯呭喌涓嬪湪浠ヤ笅鍦哄悎涓?缓璁?娇鐢ㄧ洿鏂瑰浘锛?/p>

聽锛?锛夈?佸綋Where瀛愬彞寮曠敤浜嗗垪鍊煎垎甯冨瓨鍦ㄦ槑鏄惧亸宸?殑鍒楁椂锛氬綋杩欑?鍋忓樊鐩稿綋鏄庢樉鏃讹紝浠ヨ嚦浜?WHERE 瀛愬彞涓?殑鍊煎皢浼氫娇浼樺寲鍣ㄩ?夋嫨涓嶅悓鐨勬墽琛岃?鍒掋?傝繖鏃跺簲璇ヤ娇鐢ㄧ洿鏂瑰浘鏉ュ府鍔╀紭鍖栧櫒鏉ヤ慨姝ｆ墽琛岃矾寰勩?傦紙娉ㄦ剰锛氬?鏋滄煡璇?笉寮曠敤璇ュ垪锛屽垯鍒涘缓鐩存柟鍥炬病鏈夋剰涔夈?傝繖绉嶉敊璇?緢甯歌?锛岃?澶?DBA 浼氬湪鍋忓樊鍒椾笂鍒涘缓鏌辩姸鍥撅紝鍗充娇娌℃湁浠讳綍鏌ヨ?寮曠敤璇ュ垪銆傦級

锛?锛夈?佸綋鍒楀?煎?鑷翠笉姝ｇ‘鐨勫垽鏂?椂锛氳繖绉嶆儏鍐甸?氬父浼氬彂鐢熷湪澶氳〃杩炴帴鏃讹紝渚嬪?锛屽亣璁炬垜浠?湁涓?涓?簲椤圭殑琛ㄨ仈鎺ワ紝鍏剁粨鏋滈泦鍙?湁 10 琛屻?侽racle 灏嗕細浠ヤ竴绉嶄娇绗?竴涓?仈鎺ョ殑缁撴灉闆嗭紙闆嗗悎鍩烘暟锛夊敖鍙?兘灏忕殑鏂瑰紡灏嗚〃鑱旀帴璧锋潵銆傞?氳繃鍦ㄤ腑闂寸粨鏋滈泦涓?惡甯︽洿灏戠殑璐熻浇锛屾煡璇㈠皢浼氳繍琛屽緱鏇村揩銆備负浜嗕娇涓?棿缁撴灉鏈?灏忓寲锛屼紭鍖栧櫒灏濊瘯鍦?SQL 鎵ц?鐨勫垎鏋愰樁娈佃瘎浼版瘡涓?粨鏋滈泦鐨勯泦鍚堝熀鏁般?傚湪鍋忓樊鐨勫垪涓婃嫢鏈夌洿鏂瑰浘灏嗕細鏋佸ぇ鍦板府鍔╀紭鍖栧櫒浣滃嚭姝ｇ‘鐨勫喅绛栥?傚?浼樺寲鍣ㄥ?涓?棿缁撴灉闆嗙殑澶у皬浣滃嚭涓嶆?纭?殑鍒ゆ柇锛屽畠鍙?兘浼氶?夋嫨涓?绉嶆湭杈惧埌鏈?浼樺寲鐨勮〃鑱旀帴鏂规硶銆傚洜姝ゅ悜璇ュ垪娣诲姞鐩存柟鍥剧粡甯镐細鍚戜紭鍖栧櫒鎻愪緵浣跨敤鏈?浣宠仈鎺ユ柟娉曟墍闇?鐨勪俊鎭???/p>

鍥?鐩存柟鍥炬湁涓ょ?绫诲埆锛岀瓑棰戠洿鏂瑰浘涓庣瓑楂樼洿鏂瑰浘銆?/p>

榛樿?鐨勶紝濡傛灉涓?涓??炬枩鍒椾笂鐨勫敮涓?鍊艰秴杩囦簡254涓?紝閭ｄ箞ORACLE浼氬?姝ゅ垪寤虹珛绛夐珮鐩存柟鍥撅紝鍚﹀垯寤虹珛绛夐?鐩存柟鍥俱??/p>

閫氳繃濡備笅鏂瑰紡锛屽缓绔嬭〃TAB锛屾洿鏂板瓧娈礏锛岃?鍒桞浜х敓鍊炬枩銆傚苟鍦˙鍒椾笂鍒涘缓绱㈠紩銆?/p>

SQL> spool d:\hist.txt

SQL> create table tab (a number, b number);

聽

琛ㄥ凡鍒涘缓銆?/p>

聽

SQL>

SQL> begin

聽 2 聽聽聽聽 for i in 1..10000 loop

聽 3 聽聽聽聽聽 insert into tab values (i, i);

聽 4 聽聽聽聽 end loop;

聽 5 聽聽聽聽 commit;

聽 6 聽聽聽 end;

聽 7 聽聽聽 /

聽

PL/SQL 杩囩▼宸叉垚鍔熷畬鎴愩??/p>

聽

SQL> update tab set b=5 where b between 6 and 9995;

聽

宸叉洿鏂?990琛屻??/p>

SQL> commit;

聽

鎻愪氦瀹屾垚銆?/p>

聽

SQL> create index ix_tab_b on tab(b);

聽

绱㈠紩宸插垱寤恒??/p>

聽

鐒跺悗鍒嗘瀽琛?紝寮哄埗浣垮垪B涓嶄骇鐢熺洿鏂瑰浘銆?/p>

BEGIN

聽 DBMS_STATS.GATHER_TABLE_STATS(OWNNAME 聽聽=> 'SCOTT',

聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽 TABNAME 聽聽=> 'TAB',

聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽 CASCADE 聽聽=> TRUE,

聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽 METHOD_OPT => 'FOR 聽COLUMNS B SIZE 1 ');

END;

鏌ョ湅瑙嗗浘USER_TAB_HISTOGRAMS锛屽垪B涓婂彧鏈夋渶澶у?硷紝鏈?灏忓?间袱鏉¤?褰曞垎鍒??搴旂?鐐瑰彿锛坋ndpoint_number锛?鍜?锛岃繖绉嶆樉绀鸿?鏄庡垪B娌℃湁鐩存柟鍥句俊鎭???/p>

SQL>SELECT table_name,column_name,endpoint_number,endpoint_value FROM USER_TAB_HISTOGRAMS WHERE TABLE_NAME='TAB' 锛?/p>

聽

TABLE_NAME 聽聽聽聽聽聽聽聽聽聽 COLUMN_NAME 聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽ENDPOINT_NUMBER ENDPOINT_VALUE

------------------------------ ---------------------------------------- --------------- --------------

TAB 聽聽聽聽聽聽聽聽聽聽聽聽聽聽B 聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽0 聽聽聽聽聽聽聽1

TAB 聽聽聽聽聽聽聽聽聽聽聽聽聽聽B 聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽1 聽聽聽聽聽10000

聽

鍦ㄦ病鏈夌洿鏂瑰浘鐨勬儏鍐典笅锛屽湪B鍒椾笂杩涜?绛夊?兼煡璇㈢殑鏃跺?欙紝閮芥槸绱㈠紩鑼冨洿鎵?弿銆?/p>

聽

SQL> select * from tab where b=1;

聽

鎵ц?璁″垝

----------------------------------------------------------

Plan hash value: 439197569

聽

----------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------

| 聽 0 | SELECT STATEMENT 聽聽聽聽聽聽| 聽聽聽聽聽| 聽1000 | 聽6000 | 聽聽 4 聽 (0)| 00:00:01 |

| 聽 1 | 聽TABLE ACCESS BY INDEX ROWID| TAB 聽聽聽| 聽1000 | 聽6000 | 聽聽 4 聽 (0)| 00:00:01 |

|* 聽2 | 聽 INDEX RANGE SCAN 聽聽聽聽聽| IX_TAB_B | 聽1000 | 聽聽聽 | 聽聽 2 聽 (0)| 00:00:01 |

----------------------------------------------------------------------------------------

聽

SQL> select * from tab where b=5;

聽

宸查?夋嫨9991琛屻??/p>

聽

鎵ц?璁″垝

----------------------------------------------------------

Plan hash value: 439197569

聽

----------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------

| 聽 0 | SELECT STATEMENT 聽聽聽聽聽聽| 聽聽聽聽聽| 聽1000 | 聽6000 | 聽聽 4 聽 (0)| 00:00:01 |

| 聽 1 | 聽TABLE ACCESS BY INDEX ROWID| TAB 聽聽聽| 聽1000 | 聽6000 | 聽聽 4 聽 (0)| 00:00:01 |

|* 聽2 | 聽 INDEX RANGE SCAN 聽聽聽聽聽| IX_TAB_B | 聽1000 | 聽聽聽 | 聽聽 2 聽 (0)| 00:00:01 |

----------------------------------------------------------------------------------------

鏀堕泦鐩存柟鍥句俊鎭??傜湅鐪嬫槸浠?涔堟晥鏋溿?傜敱浜庡垪B鍞?竴鍊肩殑涓?暟娌℃湁瓒呰繃254鍥犳?浜х敓鐨勬槸绛夐?鐩存柟鍥俱??/p>

BEGIN

聽 DBMS_STATS.GATHER_TABLE_STATS(OWNNAME 聽聽=> 'SCOTT',

聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽 TABNAME 聽聽=> 'TAB',

聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽 CASCADE 聽聽=> TRUE,

聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽 METHOD_OPT => 'FOR ALL COLUMNS 聽SIZE AUTO ');

END;

聽

鍦˙=1鏃跺?欓噰鐢ㄧ储寮曟壂鎻忥紝鑰孊=5鏃跺?欙紝宸茬粡閲囩敤鍏ㄨ〃鎵?弿浜嗭紝璇存槑鐩存柟鍥捐捣浜嗕綔鐢ㄣ??/p>

SQL> select * from tab where b=1;

聽

鎵ц?璁″垝

----------------------------------------------------------

Plan hash value: 439197569

聽

----------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------

| 聽 0 | SELECT STATEMENT 聽聽聽聽聽聽| 聽聽聽聽聽| 聽聽 1 | 聽聽 6 | 聽聽 2 聽 (0)| 00:00:01 |

| 聽 1 | 聽TABLE ACCESS BY INDEX ROWID| TAB 聽聽聽| 聽聽 1 | 聽聽 6 | 聽聽 2 聽 (0)| 00:00:01 |

|* 聽2 | 聽 INDEX RANGE SCAN 聽聽聽聽聽| IX_TAB_B | 聽聽 1 | 聽聽聽 | 聽聽 1 聽 (0)| 00:00:01 |

----------------------------------------------------------------------------------------

聽

SQL> select * from tab where b=5;

聽

宸查?夋嫨9991琛屻??/p>

聽

鎵ц?璁″垝

----------------------------------------------------------

Plan hash value: 1995730731

聽

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 聽 0 | SELECT STATEMENT 聽| 聽聽聽| 聽9991 | 59946 | 聽聽 6 聽 (0)| 00:00:01 |

|* 聽1 | 聽TABLE ACCESS FULL| TAB 聽| 聽9991 | 59946 | 聽聽 6 聽 (0)| 00:00:01 |

--------------------------------------------------------------------------

聽

鏌ョ湅姝ゆ椂鐨勭洿鏂瑰浘淇℃伅锛?/p>

SQL>SELECT TABLE_NAME, COLUMN_NAME, ENDPOINT_NUMBER, ENDPOINT_VALUE FROM USER_TAB_HISTOGRAMS

WHERE TABLE_NAME = 'TAB'锛?/p>

TABLE_NAME 聽聽聽聽聽聽聽聽聽聽 COLUMN_NAME 聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽ENDPOINT_NUMBER ENDPOINT_VALUE

------------------------------ ---------------------------------------- --------------- --------------

TAB 聽聽聽聽聽聽聽聽聽聽聽聽聽聽B 聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽1 聽聽聽聽聽聽聽1

TAB 聽聽聽聽聽聽聽聽聽聽聽聽聽聽B 聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽2 聽聽聽聽聽聽聽2

TAB 聽聽聽聽聽聽聽聽聽聽聽聽聽聽B 聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽3 聽聽聽聽聽聽聽3

TAB 聽聽聽聽聽聽聽聽聽聽聽聽聽聽B 聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽4 聽聽聽聽聽聽聽4

TAB 聽聽聽聽聽聽聽聽聽聽聽聽聽聽B 聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽 9995 聽聽聽聽聽聽聽5

TAB 聽聽聽聽聽聽聽聽聽聽聽聽聽聽B 聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽 9996 聽聽聽聽聽 9996

TAB 聽聽聽聽聽聽聽聽聽聽聽聽聽聽B 聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽 9997 聽聽聽聽聽 9997

TAB 聽聽聽聽聽聽聽聽聽聽聽聽聽聽B 聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽 9998 聽聽聽聽聽 9998

TAB 聽聽聽聽聽聽聽聽聽聽聽聽聽聽B 聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽 9999 聽聽聽聽聽 9999

TAB 聽聽聽聽聽聽聽聽聽聽聽聽聽聽B 聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽10000 聽聽聽聽聽10000

聽

鍏朵腑EDNPOINT_NUMBER鏄?疮璁″?笺?侲DNPOINT_VALUE鏄?垪鐨勫?笺?傚彲浠ョ湅鍑鸿繖绉嶇瓑棰戠洿鏂瑰浘缁熻?鐨勫垪鐨勪俊鎭?槸闈炲父绮剧‘鐨勩?傚畠涓烘瘡涓?涓?垪鍊煎垎閰嶄簡涓?涓?《銆備粠鎵ц?璁″垝鐨凴OWS閮ㄥ垎涔熷彲浠ョ湅鍑篛RACLE璁＄畻鍑烘潵鐨刢ardinality鏄?991锛屽拰瀹為檯鐨勬儏鍐靛畬鍏ㄥ惢鍚堛??/p>

濡傛灉鎯崇煡閬撴瘡涓?涓?垪鍊煎?搴旂殑鏁伴噺鏄??灏戯紝闇?瑕佸仛涓?涓嬬畝鍗曠殑鍑忔硶杩愮畻锛?/p>

鍋囧?鎯崇煡閬撳垪鍊肩瓑浜?鐨勪釜鏁帮紝閭ｄ箞鍙?互閫氳繃锛?/p>

9995-4=9991寰楀埌銆傝繖灏辨槸ENDPOINT_NUMBER绱??鍊肩殑鍚?箟銆?/p>

鍦ㄧ湅鐪嬬瓑楂樼洿鏂瑰浘鐨勬儏鍐点??/p>

BEGIN

聽 DBMS_STATS.GATHER_TABLE_STATS(OWNNAME 聽聽=> 'SCOTT',

聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽 TABNAME 聽聽=> 'TAB',

聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽 CASCADE 聽聽=> TRUE,

聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽 METHOD_OPT => 'FOR 聽COLUMNS B SIZE 8 ');

END;

聽

鐢变簬鍒桞鏈?0涓?敮涓?鍊硷紝閫氳繃涓婇潰鐨剆ize 8鍙?互寮哄埗ORACLE浣跨敤绛夐珮鐩存柟鍥俱??/p>

鏌ョ湅鐩存柟鍥句俊鎭?

SQL>SELECT TABLE_NAME, COLUMN_NAME, ENDPOINT_NUMBER, ENDPOINT_VALUE FROM USER_TAB_HISTOGRAMS

WHERE TABLE_NAME = 'TAB'锛?/p>

聽

TABLE_NAME 聽聽聽聽聽聽聽聽聽聽 COLUMN_NAME 聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽ENDPOINT_NUMBER ENDPOINT_VALUE

------------------------------ ---------------------------------------- --------------- --------------

TAB 聽聽聽聽聽聽聽聽聽聽聽聽聽聽B 聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽0 聽聽聽聽聽聽聽1

TAB 聽聽聽聽聽聽聽聽聽聽聽聽聽聽B 聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽7 聽聽聽聽聽聽聽5

TAB 聽聽聽聽聽聽聽聽聽聽聽聽聽聽B 聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽聽8 聽聽聽聽聽10000

聽

浠庢煡璇㈢粨鏋滄儕濂囩殑鍙戠幇鍙?湁涓変釜妗? 7 8锛屽師鏉?RACLE浼氳嚜鍔ㄧ渷鍘籈DNPOINT_VALUE鍊肩浉鍚屼笖ENDPOINT_NUMBER鐩搁偦鐨勬《鐨勫?笺??/p>

鐪佸幓浜嗘《锛圗DNPOINT_NUMBER锛変负1 2 3 4 5 6 锛孍DNPOINT_VALUE涓?鐨勫叚鏉″唴瀹广??/p>

聽

璇存槑锛氬湪绛夐珮鐩存柟鍥句腑锛孍DNPOINT_NUMBER浠ｈ〃妗跺彿,杩欎竴鐐逛笌绛夐?鐩存柟鍥句笉鍚屻??/p>

聽

鍐嶇湅绛夐珮鐩存柟鍥句笅鐨勬墽琛岃?鍒掞細

聽

SQL> select * from tab where b=5;

宸查?夋嫨9991琛屻??/p>

聽

鎵ц?璁″垝

----------------------------------------------------------

Plan hash value: 1995730731

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 聽 0 | SELECT STATEMENT 聽| 聽聽聽| 聽9982 | 59892 | 聽聽 6 聽 (0)| 00:00:01 |

|* 聽1 | 聽TABLE ACCESS FULL| TAB 聽| 聽9982 | 59892 | 聽聽 6 聽 (0)| 00:00:01 |

--------------------------------------------------------------------------

聽

鏈夋病鏈夊彂鐜颁粈涔堬紵

聽

鎵ц?璁″垝鐨凴OWS閮ㄥ垎锛孫RACLE璁＄畻鍑烘潵鐨刢ardinality涓嶆槸鐗瑰埆绮剧‘鐨勩??991鎵嶆槸绮剧‘鍊笺?傝?岀瓑棰戠洿鏂瑰浘鍙?互绮剧‘鍒?991锛屽洜姝ゅ彲浠ヨ?绛夐?鐩存柟鍥炬瘮绛夐珮鐩存柟鍥剧ǔ瀹氾紝绮剧‘銆?/p>

鍙?槸鐜板疄寰堝?鏃跺?欙紝鍒楃殑鍞?竴鍊兼槸瓒呰繃254鐨勩?傚彧鑳戒娇鐢ㄧ瓑楂樼洿鏂瑰浘浜嗐??/p>