oracle±í·ÖÎöʾÀý

drop table test;
select count(*) from test;
--´´½¨²âÊÔ±í
create table test
(
id number(9),
nick varchar2(30)
);
--²åÈë²âÊÔÊý¾Ý
begin
  for i in 1..100000 loop
        insert into test(id) values(i);
  end loop;
  commit;
end;
select * from test;
--¸üÐÂnick×ֶΣ¬Ê¹Êý¾Ý·¢ÉúÑÏÖØÇãб
update test set nick='abc' where rownum<99999;
--´´½¨Ë÷Òý
create index idx_test_nick on test(nick);
update test set nick='def' where nick is null;
--Ö»¶ÔË÷Òý½øÐзÖÎö
analyze index idx_test_nick compute statistics;
select * from user_indexes;
--²é¿´Ë÷ÒýÃû,¶ÔÓ¦´æ´¢µÄÊý¾Ý¿é,²»Í¬µÄkeyÊýÁ¿,¼Ç¼Êý£¨ÐÐÊý£©µÄ·ÖÎöÐÅÏ¢
select index_name, LEAF_BLOCKS, DISTINCT_KEYS, NUM_ROWS
  from user_indexes
 where index_name = 'IDX_TEST_NICK';
--dba_tab_col_statistics
--²é¿´±íµÄͳ¼ÆÐÅÏ¢
select COLUMN_NAME, NUM_BUCKETS, num_distinct
  from USER_tab_columns
 where table_name = 'TEST';
select * from test where nick ='abc';
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEST'
   2    1     INDEX (RANGE SCAN) OF 'IDX_TEST_NICK' (NON-UNIQUE)
select * from test where nick ='def';
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEST'
   2    1     INDEX (RANGE SCAN) OF 'IDX_TEST_NICK' (NON-UNIQUE)
--ÓÉÉÏ¿ÉÒÔ¿´µ½,¶ÔË÷Òý·ÖÎöÖ®ºó,sqlµÄÖ´Ðз¾¶¶¼ÊÇ»ùÓÚ¹æÔòµÄ,Ë÷ÒýµÄ×ֶεÄÆ«ÒÆ
--Ïȸù¾ÝË÷ÒýÕÒµ½rowid,È»ºóÔÙ¸ù¾Ýrowid¶ÁÈ¡¼Ç¼,Õâ¸ö¹ý³Ì¿Ï¶¨±ÈÈ«±íɨÃè¶ÁÈ¡¼Ç¼ҪÂý
--user_part_col_statistics  ·ÖÇø·ÖÎöÐÅÏ¢
--·ÖÎö±íµÄµÚ¶þÁÐnick
analyze table test compute stat