[20140621]Cluster Nulls.txt

简介: [20140621]Cluster Nulls.txt https://jonathanlewis.wordpress.com/2014/06/17/cluster-nulls/ 重复测试: SCOTT@test01p> @ver BANNER...

[20140621]Cluster Nulls.txt

https://jonathanlewis.wordpress.com/2014/06/17/cluster-nulls/


重复测试:
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 ;

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 rownumROWID                     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不知道表示什么?

目录
打赏
0
0
0
0
36
分享
相关文章
Failed at step CHDIR spawning /opt/kubernetes/bin/etcd: No such file or directory
Failed at step CHDIR spawning /opt/kubernetes/bin/etcd: No such file or directory
93 0
【node错误】/usr/bin/env: node: No such file or directory
背景 安装了node后,执行npm run xxx的命令的时候,报错,提示如下: /usr/bin/env: node: No such file or directory   步骤 1.
6362 0
10-18|* * * * * /srv/salt/base/_modules/chmod_dir_sh.sh这个什么意思
10-18|* * * * * /srv/salt/base/_modules/chmod_dir_sh.sh这个什么意思
报错FileSystemException: /datas/nodes/0/indices/gtTXk-hnTgKhAcm-8n60Jw/1/index/.es_temp_file
首先我碰到的问题是服务器突然断电导致elasticsearch宕机,当我再次启动的时候 >FileSystemException: /data/elasticsearchDatas/datas/nodes/0/indices/gtTXk-hnTgKhAcm-8n60Jw/1/index/.es_temp_file: 结构需要清理
194 0
报错FileSystemException: /datas/nodes/0/indices/gtTXk-hnTgKhAcm-8n60Jw/1/index/.es_temp_file
Note that ‘/home/w/.local/share‘ is not in the search pathset by the XDG_DATA_HOME and XDG_DATA_DIRS
Note that ‘/home/w/.local/share‘ is not in the search pathset by the XDG_DATA_HOME and XDG_DATA_DIRS
376 0
Node(五)File System小案例(删除文件夹及下面的所有文件)
Node(五)File System小案例(删除文件夹及下面的所有文件)
93 0
hdfs mkdir报错Cannot create directory /usr. Name node is in safe mode.
hdfs mkdir报错Cannot create directory /usr. Name node is in safe mode.
440 0
解决报错:Couldn't create temporary file /tmp/apt.conf.IRqbCz
问题 操作容器应该是属于服务器开发同学的常规操作,经常我们会遇到系统缺少对应的工具的情况,比如我们进入容器后,想使用 vim 修改某个文件,但是发现该容器没有安装 vim 工具。这个时候,一般都需要自己手动安装,比如在 unbuntu 系统中,可以使用 apt-get 包管理命令。
774 0
解决报错:Couldn't create temporary file /tmp/apt.conf.IRqbCz
AI助理

你好,我是AI助理

可以解答问题、推荐解决方案等