10、管理数据库存储(行迁移及行连接)

管理数据库存储

1block=8192bytes

案例1:行迁移

1、表中数据如何存储

create table test as select * from hr.employees;

create index idx_test on test(employee_id);

只看执行计划,不执行结果。

set autotrace traceonly statistics;

select * from test where employee_id>0;

强制走索引

select /*+index(test,idx_test_id) */ * from test where employee_id>0;

SQL> select /*+index(test,idx_test_id) */ * from test where employee_id>0;

107 rows selected.

Statistics

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

7  recursive calls

0  db block gets

19  consistent gets

0  physical reads

0  redo size

9096  bytes sent via SQL*Net to client

601  bytes received via SQL*Net from client

9  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

107  rows processed

解读:19  consistent gets 需要19个block才能存107行数据

set autotrace off;

alter table test modify FIRST_NAME  varchar2(1000);

alter table test modify LAST_NAME  varchar2(1000);

alter table test modify EMAIL  varchar2(1000);

alter table test modify PHONE_NUMBER  varchar2(1000);

update test set LAST_NAME=lpad(LAST_NAME,1000,‘*‘),FIRST_NAME=lpad(FIRST_NAME,1000,‘*‘),EMAIL=lpad(EMAIL,1000,‘*‘),PHONE_NUMBER=lpad(PHONE_NUMBER,1000,‘*‘);

SQL> set autotrace traceonly statistics;

SQL> select /*+index(test,idx_test_id) */ * from test where employee_id>0;

107 rows selected.

Statistics

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

9  recursive calls

0  db block gets

288  consistent gets

0  physical reads

0  redo size

438255  bytes sent via SQL*Net to client

601  bytes received via SQL*Net from client

9  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

107  rows processed

SQL> set autotrace off;

SQL> @/u01/app/oracle/product/11.2.0/db_1/rdbms/admin/utlchain.sql

Table created.

其中表结构为:

create table CHAINED_ROWS (

owner_name         varchar2(30),

table_name         varchar2(30),

cluster_name       varchar2(30),

partition_name     varchar2(30),

subpartition_name  varchar2(30),

head_rowid         rowid,

analyze_timestamp  date

);

把test表行行迁移信息放到这张表 CHAINED_ROWS 中

analyze table test list chained rows into CHAINED_ROWS;

select table_name,count(*) from CHAINED_ROWS group by table_name;

SQL> select table_name,count(*) from CHAINED_ROWS group by table_name;

TABLE_NAME COUNT(*)

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

TEST      105

这里表示 TEST 表有107行数据,其中105行数据搬家了。发生了行迁移。

如何消除行迁移???

1、把发生行迁移的行放到一个临时表中

create table test_temp as select * from test where rowid in (select head_rowid from CHAINED_ROWS);

SQL> select count(*) from test_temp;

COUNT(*)

----------

105

2、删除行迁移的行

select rowid,employee_id from test;

delete from test where rowid in (select head_rowid from CHAINED_ROWS)

SQL> delete from test where rowid in (select head_rowid from CHAINED_ROWS);

105 rows deleted.

SQL> select count(*) from test;

COUNT(*)

----------

2

3、插入

insert into test  select * from test_temp;

SQL> select count(*) from test;

  COUNT(*)
----------
       107

commit;

truncate table CHAINED_ROWS;

查看有没有行迁移

analyze table test list chained rows into CHAINED_ROWS;

select table_name,count(*) from CHAINED_ROWS group by table_name;

set autotrace traceonly statistics;

select /*+index(test,idx_test_id) */ * from test where employee_id>0;

结果为:

SQL> select /*+index(test,idx_test_id) */ * from test where employee_id>0;

107 rows selected.

Statistics

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

0  recursive calls

0  db block gets

116  consistent gets

0  physical reads

0  redo size

437625  bytes sent via SQL*Net to client

601  bytes received via SQL*Net from client

9  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

107  rows processed

结论:对比一下行迁移之前读了288 block  现在只要读了116个 block;

案例2:行连接  说明一行的数据超过8192bytes

truncate table CHAINED_ROWS;

drop table test_temp purge;

drop table test purge;

set autotrace off;

create table test as select * from hr.employees;

create index idx_test_id on test(employee_id);

set autotrace traceonly statistics;

select /*+ index(test,idx_test_id)*/ * from test;

set autotrace off;

alter table test modify last_name varchar2(2000);

alter table test modify first_name varchar2(2000);

alter table test modify email varchar2(2000);

alter table test modify phone_number varchar2(2000);

update test set LAST_NAME=lpad(‘1‘,2000,‘*‘),FIRST_NAME=lpad(‘1‘,2000,‘*‘),EMAIL=lpad(‘1‘,2000,‘*‘),PHONE_NUMBER=lpad(‘1‘,2000,‘*‘);

commit;

SQL> desc test;

Name   Null?    Type

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

EMPLOYEE_ID    NUMBER(6)

FIRST_NAME    VARCHAR2(2000)

LAST_NAME   NOT NULL VARCHAR2(2000)

EMAIL   NOT NULL VARCHAR2(2000)

PHONE_NUMBER    VARCHAR2(2000)

HIRE_DATE   NOT NULL DATE

JOB_ID   NOT NULL VARCHAR2(10)

SALARY    NUMBER(8,2)

COMMISSION_PCT    NUMBER(2,2)

MANAGER_ID    NUMBER(6)

DEPARTMENT_ID    NUMBER(4)

set autotrace traceonly statistics;

select /*+ index(test,idx_test_id)*/ * from test;

107 rows selected.

Statistics

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

28  recursive calls

0  db block gets

399  consistent gets

0  physical reads

0  redo size

869120  bytes sent via SQL*Net to client

601  bytes received via SQL*Net from client

9  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

107  rows processed

set autotrace off;

analyze table test list chained rows into CHAINED_ROWS;

select table_name,count(*) from CHAINED_ROWS group by table_name;

SQL> select table_name,count(*) from CHAINED_ROWS group by table_name;

TABLE_NAME COUNT(*)

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

TEST      214

那如何消除行连接呢?

SQL> show parameter 16k

NAME     TYPE VALUE

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

db_16k_cache_size     big integer 0

alter system set db_16k_cache_size=20m;

select name from v$datafile;

SQL> select name from v$datafile;

NAME

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

+DATA/orcl/datafile/system.256.943301251

+DATA/orcl/datafile/sysaux.257.943301251

+DATA/orcl/datafile/undotbs1.258.943301251

+DATA/orcl/datafile/users.259.943301251

+DATA/orcl/datafile/example.265.943301433

创建16K的表空间(之前默认1个block是8192bytes字节)

create tablespace  tbs_16k blocksize 16K datafile ‘+DATA/orcl/datafile/tbs.dbf‘ size 10m;

alter table test move tablespace tbs_16k;

truncate table CHAINED_ROWS;

analyze table test list chained rows into CHAINED_ROWS;

select table_name,count(*) from CHAINED_ROWS group by table_name;

因为刚才对表空间进行了move,因此test的索引生效了,需要重建索引。

alter index idx_test_id rebuild;

set autotrace traceonly statistics;

select /*+ index(test,idx_test_id)*/ * from test;

SQL> select /*+ index(test,idx_test_id)*/ * from test;

107 rows selected.

Statistics

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

36  recursive calls

0  db block gets

177  consistent gets

0  physical reads

0  redo size

867337  bytes sent via SQL*Net to client

601  bytes received via SQL*Net from client

9  SQL*Net roundtrips to/from client

0  sorts (memory)

0  sorts (disk)

107  rows processed

结论:读107行数据, 对比之前读了399个block,现在只读了177个block

时间: 05-29

10、管理数据库存储(行迁移及行连接)的相关文章

mysql数据库存储过程数据迁移案例与比较

cursor 与 insert ...select 对比: cursor:安全,不会造成死锁,可以在服务运行阶段跑,比较稳定. insert...select :速度快,但是可能造成死锁,相比cursor能够成倍提升,在服务停止的情况下迁移,速度快 数据迁移案例: 首先数据的迁移绝对不是一朝一夕能够快速迁移完成的 ,如果可以很快完成的 dump便可以搞定,没必要大费周折了. 既然不是一朝一夕能完成的,那么有关键的日志记录表能够良好的反应数据迁移的过程 迁移日志表脚本: DROP TABLE IF

深入解析SQL Server行迁移记录

总长未超过8060字节,变长和定长列存储在同一页/记录中的记录称为行内数据记录.行内记录最常见了,因为一般的记录长度都是在8060字节内的.行内记录理论上都是存放在一个页面中的,这种记录的结构相对简单,格式也相对固定. 行内记录会包含一些常见数据类型的列,如int.char(n).varchar(n).datetime2.real.money等.这些类型的数据有变长的也有定长的,所以在更新变长列的数据时,会使记录长度增大,到超出空闲空间容量时,这样即使此记录小于8060字节,也会造成此记录在这一

Win2012R2 Hyper-V初级教程10 — 通过远程管理工具进行非共享存储实时迁移

在第8节中我们进行了非共享存储下的实时迁移测试,但是他们都是基于本地进行的迁移,这是一种很不方便的方法,当我们要迁移的时候必须登录到Hyper-V服务器本机,下面我们介绍一种简单的方法,即采用远程管理进行迁移,就是说我们可以在第三方的Hyper-V管理服务器上进行迁移Hyper-V虚拟服务器,下面我们来操作一下. 1.我们选择TestServer01服务器上的Win2012R2-Test03虚拟服务器,然后下方的"移动..." 2.打开移动向导,点击"下一步" 3.

MySql数据库2【常用命令行】

(一) 连接MYSQL: 格式: mysql -h主机地址 -u用户名 -p用户密码 1.连接到本机上的MYSQL 进入mysql安装目录下的bin目录下,再键入命令mysql -uroot -p,回车后提示你输密码, 如果刚安装好MYSQL,超级用户root是没有密码的,故直接回车即可进入到MYSQL中了 2.连接到远程主机上的MYSQL (远程:IP地址) 假设远程主机的IP为:10.0.0.1,用户名为root,密码为123.则键入以下命令: mysql -h10.0.0.1 -uroot

模拟行迁移试验

1.创建scott模式 [[email protected] dbs]$ sqlplus '/as sysdba' SQL*Plus: Release 10.2.0.1.0 - Production on Mon Feb 17 14:59:02 2014 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0

Innodb物理存储结构系列2 行记录格式

前一篇讨论了Innodb system,表空间,文件的关系及数据结构,这一篇记录下Innodb行记录的格式. 前提: 1. server层和innodb层都有自己对于record的记录格式,需要进行转换. 2. 物理文件上的记录存储,需要内存中的数据结构进行对应(任何数据都需要在内存中进行处理),进行存取的转换. 1. 测试case: create table `pp` ( `id` int(11) default null, `name1` varchar(100) default null,

8天掌握EF的Code First开发系列之3 管理数据库创建,填充种子数据以及LINQ操作详解

本篇目录 管理数据库创建 管理数据库连接 管理数据库初始化 填充种子数据 LINQ to Entities详解 什么是LINQ to Entities 使用LINQ to Entities操作实体 LINQ操作 懒加载和预加载 插入数据 更新数据 删除数据 本章小结 本人的实验环境是VS 2013 Update 5,windows 10,MSSQL Server 2008. 上一篇<Code First开发系列之领域建模和管理实体关系>,我们主要介绍了EF中“约定大于配置”的概念,如何创建数据

使用嵌入式关系型SQLite数据库存储数据

除了可以使用文件或SharedPreferences存储数据,还可以选择使用SQLite数据库存储数据. 在Android平台上,集成了一个嵌入式关系型数据库—SQLite, 1.SQLite3支持 NULL.INTEGER.REAL(浮点数字).TEXT(字符串文本)和BLOB(二进制对象)数据类型,虽然它支持的类型虽然只有五种,但实际上sqlite3也接受varchar(n).char(n).decimal(p,s) 等数据类型,只不过在运算或保存时会转成对应的五种数据类型. 2.SQLit

MySQL数据库存储引擎

MySQL数据库存储引擎 MySQL数据库是一种关系型数据库,数据时存放在数据库中的一张一张的表中的,各个表之间或有关联,表中的每行相关,每列是不相关.这是关系型数据库的基本存储方式. MySQL存储引擎是为了在各种不同的条件下对数据存放的可靠性.关联性.读写等方面做出的一个可供选择的存储模式. 当前使用较多的MySQL存储引擎主要有以下几个,MyISAM,InnoDB,BdB,Memory等,这些存储引擎主要在以下几个方面有所区分,锁定水平.处理方式.存储方式和索引技巧方面有所不同. mysq

安卓数据存储(3):SQLite数据库存储

SQLite简介 Google为Andriod的较大的数据处理提供了SQLite,他在数据存储.管理.维护等各方面都相当出色,功能也非常的强大.SQLite具备下列特点: 1.轻量级:使用 SQLite 只需要带一个动态库,就可以享受它的全部功能,而且那个动态库的尺寸想当小. 2.独立性:SQLite 数据库的核心引擎不需要依赖第三方软件,也不需要所谓的“安装”. 3.隔离性:SQLite 数据库中所有的信息(比如表.视图.触发器等)都包含在一个文件夹内,方便管理和维护. 4.跨平台:SQLit