㈠ 合作的基础
⑴ /* 全为 NULL 的列是无法存储到B*Tree里面的 */hr@ORCL> create table t (x number,y number);hr@ORCL> create index idx_t on t(x,y);hr@ORCL> insert into t values(1,1);hr@ORCL> insert into t values(1,null);hr@ORCL> insert into t values(null,1);hr@ORCL> insert into t values(null,null);hr@ORCL> commit;hr@ORCL> exec dbms_stats.gather_table_stats(ownname=>USER,tabname=>'T');hr@ORCL> exec dbms_stats.gather_index_stats(ownname=>USER,indname=>'IDX_T');/*插入了4行、而在索引中仅存储了3 行*/hr@ORCL> select index_name,num_rows from user_indexes where index_name='IDX_T';INDEX_NAME NUM_ROWS------------------------------ ----------IDX_T 3⑵ /*只有当索引键中至少有一个列定义为NOT NULL 时查询才会使用索引 */--走索引hr@ORCL> drop table t purge;hr@ORCL> create table t (x number,y number not null);hr@ORCL> create unique index idx_t on t(x,y);hr@ORCL> insert into t values(1,1);hr@ORCL> insert into t values(NULL,1);hr@ORCL> exec dbms_stats.gather_table_stats(ownname=>USER,tabname=>'T');hr@ORCL> exec dbms_stats.gather_index_stats(ownname=>USER,indname=>'IDX_T');hr@ORCL> set autot trace exphr@ORCL> select * from t where x IS NULL;Execution Plan----------------------------------------------------------Plan hash value: 2296882198--------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 5 | 1 (0)| 00:00:01 ||* 1 | INDEX RANGE SCAN| IDX_T | 1 | 5 | 1 (0)| 00:00:01 |--------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - access("X" IS NULL)--不走索引hr@ORCL> drop table t purge;hr@ORCL> create table t (x number,y number);hr@ORCL> create unique index idx_t on t(x,y); hr@ORCL> insert into t values(1,1);hr@ORCL> insert into t values(1,null);hr@ORCL> insert into t values(null,1);hr@ORCL> insert into t values(null,null);hr@ORCL> commit;hr@ORCL> exec dbms_stats.gather_table_stats(ownname=>USER,tabname=>'T');hr@ORCL> exec dbms_stats.gather_index_stats(ownname=>USER,indname=>'IDX_T');hr@ORCL> set autot trace exphr@ORCL> select * from t where x IS NULL;Execution Plan----------------------------------------------------------Plan hash value: 1601196873--------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 2 | 8 | 3 (0)| 00:00:01 ||* 1 | TABLE ACCESS FULL| T | 2 | 8 | 3 (0)| 00:00:01 |--------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter("X" IS NULL)
㈡ 互惠共赢
① 场景 某表、某倾斜列、且只有 2 个可取值 ② 需求 ● 索引访问少数行 ● 全表扫描访问多数行 ● 节省索引维护成本、降低索引存储开销 ③ 方案 有 2： ● 对多数行使用 NULL、而对少数行使用你希望的任何值 ● 使用函数索引、只索引函数的非NULL返回值、这是函数非常好的一个运用 ④ 例子 某张工单表t、status列只有处理(1)和未处理(0)两种状态、其中、未处理占：10%、已处理占90% 经常查询未处理的、下面演示这个例子
hr@ORCL> drop table t purge;hr@ORCL> create table t (x number,y number);hr@ORCL> create index idx_t on t (decode(x,0,0,NULL)); hr@ORCL> insert into t values(0,1);hr@ORCL> edWrote file afiedt.buf 1 begin 2 for i in 1..1000 3 loop 4 insert into t values(1,i); 5 end loop; 6 commit; 7* end; 8 /PL/SQL procedure successfully completed.hr@ORCL> exec dbms_stats.gather_table_stats(ownname=>USER,tabname=>'T');hr@ORCL> exec dbms_stats.gather_index_stats(ownname=>USER,indname=>'IDX_T');/*索引只存储了 1 行、节省了索引存储空间 */hr@ORCL> select index_name,num_rows from user_indexes where index_name='IDX_T';INDEX_NAME NUM_ROWS------------------------------ ----------IDX_T 1hr@ORCL> set autot trace exp/* CBO选择走索引*/hr@ORCL> select * from t where decode(x,0,0,NULL)=0;Execution Plan----------------------------------------------------------Plan hash value: 1594971208-------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 7 | 2 (0)| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 7 | 2 (0)| 00:00:01 ||* 2 | INDEX RANGE SCAN | IDX_T | 1 | | 1 (0)| 00:00:01 |-------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 2 - access(DECODE("X",0,0,NULL)=0)
By David Lin
2013-06-10
Good Luck
端午节快乐