[20140621]Cluster Nulls.txt
[20140621]Cluster Nulls.txt
重複測試:
SCOTT@test01p> @ver
BANNER CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0
drop table tc1;
drop cluster c including tables;
purge recyclebin;
create cluster c(val number);
create index c_idx on cluster c;
create table tc1 (val number, n1 number, padding varchar2(100)) cluster c(val);
insert into tc1
select
decode(rownum,1,to_number(null),rownum), rownum, rpad('x',100)
from
all_objects
where
rownum <= 100
;
insert into tc1 select * from tc1;
insert into tc1 select * from tc1;
insert into tc1 select * from tc1;
insert into tc1 select * from tc1;
insert into tc1 select * from tc1;
commit;
analyze cluster c compute statistics;
execute dbms_stats.gather_table_stats(user,'tc1');
set autotrace traceonly explain
select * from tc1 where val = 2;
SCOTT@test01p> select * from tc1 where val = 2;
Execution Plan
----------------------------------------------------------
Plan hash value: 3039613725
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 32 | 3424 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS CLUSTER| TC1 | 32 | 3424 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | C_IDX | 1 | | 0 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("VAL"=2)
select * from tc1 where val is null;
SCOTT@test01p> select * from tc1 where val is null;
Execution Plan
----------------------------------------------------------
Plan hash value: 1199729187
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 32 | 3424 | 30 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TC1 | 32 | 3424 | 30 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("VAL" IS NULL)
--可以發現val is null選擇的是全表掃描.
set autotrace off
create index tc1_null on tc1(nvl2(val,null,0));
execute dbms_stats.gather_table_stats(user,'tc1', method_opt=>'for all hidden columns size 1');
select * from tc1 where nvl2(val,null,0) = 0;
SCOTT@test01p> @dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID d1fns9ushrfa2, child number 0
-------------------------------------
select * from tc1 where nvl2(val,null,0) = 0
Plan hash value: 3636020997
------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 2 (100)|
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TC1 | 32 | 2 (0)|
|* 2 | INDEX RANGE SCAN | TC1_NULL | 32 | 1 (0)|
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("TC1"."SYS_NC00004$"=0)
--建立函式索引才避免全表掃描.
2.看看索引的轉儲:
SCOTT@test01p> select object_name,object_id,data_object_id from dba_objects where owner=user and object_name='C_IDX';
OBJECT_NAME OBJECT_ID DATA_OBJECT_ID
------------ ---------- --------------
C_IDX 95499 95499
SCOTT@test01p> alter session set events 'immediate trace name treedump level 95499';
Session altered.
*** 2014-06-21 20:42:41.711
----- begin tree dump
leaf: 0x24000db 37748955 (0: nrow: 100 rrow: 100)
----- end tree dump
Session altered.
SCOTT@test01p> @dfb 24000db
RFILE# BLOCK#
---------- ----------
9 219
TEXT
------------------------------------------------
alter system dump datafile 9 block 219 ;
SCOTT@test01p> select segment_name,header_file,header_block from dba_segments where owner=user and segment_name='C_IDX';
SEGMENT_NAME HEADER_FILE HEADER_BLOCK
-------------------- ----------- ------------
C_IDX 9 218
SCOTT@test01p> alter system dump datafile 9 block 219;
System altered.
Block header dump: 0x024000db
Object id on Block? Y
seg/obj: 0x1750b csc: 0x00.602546 itc: 2 flg: E typ: 2 - INDEX
brn: 0 bdba: 0x24000d8 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x02 0x0007.001.0000106f 0x014000e3.0247.10 --U- 1 fsc 0x0000.00602547
Leaf block dump
===============
header address 40390756=0x2685064
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 1
kdxcosdc 0
kdxconro 100
kdxcofbo 236=0xec
kdxcofeo 6738=0x1a52
kdxcoavs 6502
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 0=0x0
kdxledsz 8
kdxlebksz 8036
row#0[8012] flag: -------, lock: 0, len=13, data:(8): 02 40 00 d6 00 00 01 00
col 0; len 2; (2): c1 03
row#1[7999] flag: -------, lock: 0, len=13, data:(8): 02 40 00 d7 00 00 01 00
col 0; len 2; (2): c1 04
...........
row#97[6751] flag: -------, lock: 0, len=13, data:(8): 02 40 01 72 00 00 01 00
col 0; len 2; (2): c1 64
row#98[6738] flag: -------, lock: 2, len=13, data:(8): 02 40 01 73 00 00 01 00
col 0; len 2; (2): c2 02
row#99[8025] flag: -------, lock: 0, len=11, data:(8): 02 40 00 d5 00 00 01 00
col 0; NULL
----- end of leaf block Logical dump -----
----- end of leaf block dump -----
End dump data blocks tsn: 3 file#: 9 minblk 219 maxblk 219
--可以發現索引是儲存NULL的.但是執行val is null時不用該索引.
--取前4位16進位制.
SCOTT@test01p> @dfb 024000d5
RFILE# BLOCK#
---------- ----------
9 213
TEXT
------------------------------------------
alter system dump datafile 9 block 213 ;
SCOTT@test01p> select rowid,tc1.* from tc1 where val is null and rownum<=1 ;
ROWID VAL N1 PADDING
------------------ ---------- ---------- ----------
AAAXUKAAJAAAADVAAA 1 x
SCOTT@test01p> @lookup_rowid AAAXUKAAJAAAADVAAA
OBJECT FILE BLOCK ROW DBA TEXT
---------- ---------- ---------- ---------- -------------------- ----------------------------------------
95498 9 213 0 9,213 alter system dump datafile 9 block 213 ;
--後面的00000100不知道表示什麼?
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/267265/viewspace-1191460/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- redis.cluster/memcached.cluster/wmware esxiRedis
- MySQL ClusterMySql
- Cluster for MySQLMySql
- MySQL NDB Cluster,Galera Cluster和Percona XtraDB Cluster優缺點總結MySql
- Redis Cluster原理Redis
- redis cluster 搭建Redis
- Redis Cluster 3.0Redis
- Redis cluster on WindowsRedisWindows
- Linux ClusterLinux
- Cluster簡介
- redis cluster 配置Redis
- oracle簇clusterOracle
- MySQL-ClusterMySql
- cilium Cluster Mesh
- Redis-cluster命令 cluster info 引數資訊解釋Redis
- 【Redis】redis-cluster到redis-cluster的快速複製Redis
- Weblogic中如何增加cluster並加入新cluster成員Web
- 7.49 CLUSTER_PROBABILITY
- 7.46 CLUSTER_DETAILSAI
- Redis cluster 叢集Redis
- redis cluster 擴容Redis
- node Cluster 模組分析
- Oracle Cluster Time ManagementOracle
- redis-cluster心得Redis
- Redis Cluster(叢集)Redis
- MySQL Cluster安裝MySql
- MySQL Cluster研究(1)MySql
- MySQL Cluster相關MySql
- mysql安裝-clusterMySql
- akka-typed(6) - cluster:group router, cluster-load-balancing
- 9. MySQL Galera Cluster全解析 Part 9 監控Galera ClusterMySql
- 7. MySQL Galera Cluster全解析 Part 7 Galera Cluster部署指南MySql
- 1. MySQL Galera Cluster全解析 Part 1 Galera Cluster 簡介MySql
- MySQL Galera Cluster全解析 Part 4 Galera Cluster 安裝前準備MySql
- Node js 叢集(cluster)JS
- 1.2 Physiacel Structure of Database ClusterStructDatabase
- oracle中的cluster表Oracle
- mongodb 3.0.3 sharded cluster 配置MongoDB