Oracle如何預估將要建立的索引和表的大小
Oracle如何預估將要建立的索引和表的大小
1. 對於表和索引空間的預估，可以使用DBMS_SPACE包的CREATE_TABLE_COST和CREATE_INDEX_COST儲存過程，雖然沒有看這兩個儲存過程的實現，但猜測平均行長演算法，會根據預計行數，做一些計算，欄位定義演算法，則會根據每個欄位的長度，和預計行數，做一些計算，其實和我們手工根據這些演算法，計算的方式類似，只是封裝起來，便於呼叫。
2. CREATE_TABLE_COST根據列欄位定義預估，是比較準確的，根據平均行長，並不很準確。需要注意的是，這裡計算的是欄位極限值，不會超過此值，但有可能實際用不了這些。
https://mp.weixin.qq.com/s?__biz=MzI2NzM1OTM4OA==&mid=2247484190&idx=1&sn=6135f4254ed6e84ac3ea161e3e5066a6&chksm=ea8146edddf6cffb7e089a36594321d4b5bbe28118f9f5d7a00b629b141ab27b45d35124b3ae&mpshare=1&scene=22&srcid=0705zFvXGOGQb52Sp8TaefxO#rd
oracle 提供了2種可以預估將要建立的索引大小的辦法：
① 利用包 Dbms_space.create_index_cost 直接得到
② 利用11g新特性 Note raised when explain plan for create index
下邊分別舉例說明。
1 環境說明
[oracle@rhel6_lhr ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Fri Dec 26 15:58:06 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> create table test_index_size as select * from dba_objects;
Table created.
SQL> EXEC DBMS_STATS.gather_table_stats(ownname => 'SYS',tabname => 'TEST_INDEX_SIZE');
PL/SQL procedure successfully completed.
2 第一種 Dbms_space.create_index_cost
指令碼：
declare
l_index_ddl varchar2(1000);
l_used_bytes number;
l_allocated_bytes number;
begin
dbms_space.create_index_cost(ddl => 'create index idx_t on sys.test_index_size(object_id) ',
used_bytes => l_used_bytes,
alloc_bytes => l_allocated_bytes);
dbms_output.put_line('used= ' || l_used_bytes || 'bytes' ||
' allocated= ' || l_allocated_bytes || 'bytes');
end;
/
實驗：
SQL> set serveroutput on
SQL> declare
2 l_index_ddl varchar2(1000);
3 l_used_bytes number;
4 l_allocated_bytes number;
5 begin
6 dbms_space.create_index_cost(ddl => 'create index idx_t on sys.test_index_size(object_id) ',
7 used_bytes => l_used_bytes,
8 alloc_bytes => l_allocated_bytes);
9 dbms_output.put_line('used= ' || l_used_bytes || 'bytes' ||
10 ' allocated= ' || l_allocated_bytes || 'bytes');
11 end;
12 /
used= 383105bytes allocated= 2097152bytes
PL/SQL procedure successfully completed.
SQL>
PL/SQL 過程已成功完成。
說明: used_bytes 給出索引資料實際表現的位元組數。
allocated 是當實際建立索引時在表空間中實際佔用的位元組數。
3 第二種11g新特性:Note raised when explain plan for create index
這是一個挺實用的小特性，在11g r2中使用explain plan for create index時Oracle會提示評估的索引大小(estimated index size)了：
SQL> set linesize 200 pagesize 1400;
SQL> explain plan for create index idx_t on sys.test_index_size(object_id) ;
Explained.
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
Plan hash value: 32582980
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | CREATE INDEX STATEMENT | | 76621 | 374K| 350(1)| 00:00:05 |
| 1 | INDEX BUILD NON UNIQUE| IDX_T | | | | |
| 2 | SORT CREATE INDEX | | 76621 | 374K| | |
| 3 | INDEX FAST FULL SCAN| IDX_T | | | | |
--------------------------------------------------------------------------------
Note
-----
- estimated index size: 2097K bytes
14 rows selected.
4 建立真實索引
SQL> create index idx_t on sys.test_index_size(object_id) ;
Index created.
SQL> analyze index IDX_T validate structure;
Index analyzed.
SQL> select bytes from dba_segments where segment_name='IDX_T';
BYTES
----------
2097152
可以看到2種辦法給出的索引評估大小與實際索引佔用空間差別不大，但這裡有個前提條件就是預估索引大小之前必須對錶進行分析過。
How to Estimate the Size of Tables and Indexes Before Being Created and Populated in the Database? (文件 ID 1585326.1)
|
|
In this Document
| Goal |
| Solution |
| Estimate The Size Of Tables |
| Example for using Version 1: |
| Example for using Version 2: |
| Estimate Table Size Using Enterprise Manager |
|
Estimate The Size Of Indexes |
| Estimate Index Size example : |
| References |
APPLIES TO:
Oracle Database - Enterprise Edition - Version 10.1.0.3 to 12.1.0.2 [Release 10.1 to 12.1]Information in this document applies to any platform.
***Checked for relevance on 23-May-2016***
GOAL
How TO Estimate The Size Of Tables and Indexes Before They Actually Gets Created And Populated In The Database , to Prepare Sufficient storage available .
SOLUTION
Estimate The Size Of Tables
The CREATE_TABLE_COST procedure of the DBMS_SPACE package helps to estimate the size of the table segment ,
This procedure is used in capacity planning to determine the size of the table given various attributes , The CREATE_TABLE_COST procedure has two versions :
- The first version takes the average row size of the table as argument and outputs the table size.
- The second version takes the column information of the table as argument and outputs the table size.
Both Versions also Requires the following input :
- the expected number of rows
- pct_free setting for the table
- the tablespace name where the table would be created.
=========
DBMS_SPACE.CREATE_TABLE_COST (
tablespace_name IN VARCHAR2,
avg_row_size IN NUMBER,
row_count IN NUMBER,
pct_free IN NUMBER,
used_bytes OUT NUMBER,
alloc_bytes OUT NUMBER);
Version 2
=========
DBMS_SPACE.CREATE_TABLE_COST (
tablespace_name IN VARCHAR2,
colinfos IN CREATE_TABLE_COST_COLUMNS,
row_count IN NUMBER,
pct_free IN NUMBER,
used_bytes OUT NUMBER,
alloc_bytes OUT NUMBER);
The output of the procedure contains used_bytes and alloc_bytes :
- The used_bytes : represent the actual bytes used by the data. This includes the overhead due to the block metadata, pctfree etc.
- The alloc_bytes : represent the size of the table segment when it is created in the tablespace. This takes into account, the size of the extents in the tablespace and tablespace extent management properties.
Example for using Version 1:
This methode requires creating the table first with some sample data to calculate the average row size of the table ,
INSERT INTO test VALUES (9999999999,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' ,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa',sysdate);
INSERT INTO test VALUES (9999999,'aaaaaaaaaaaaaaaaaaa' ,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa',sysdate);
INSERT INTO test VALUES (999999,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' ,'aaaaaaaaaaaaaaaaaa',sysdate);
exec DBMS_STATS.GATHER_TABLE_STATS (ownname=>'hr',tabname=>'test',estimate_percent=>100,block_sample=>true,method_opt=>'FOR ALL COLUMNS size 254');
select AVG_ROW_LEN from user_tables where TABLE_NAME='TEST';
AVG_ROW_LEN
-----------
68
Drop table test;
set serveroutput on
DECLARE
ub NUMBER;
ab NUMBER;
BEGIN
DBMS_SPACE.CREATE_TABLE_COST('USERS',68,100000,10,ub,ab);
DBMS_OUTPUT.PUT_LINE('Used Bytes = ' || TO_CHAR(ub));
DBMS_OUTPUT.PUT_LINE('Allocated Bytes = ' || TO_CHAR(ab));
END;
/
Used Bytes = 8036352
Allocated Bytes = 8388608
which is around 8 MB , now lets create and populate the actual table.
BEGIN
FOR i IN 1..100000 LOOP
INSERT INTO test VALUES (i,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' ,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa',sysdate);
END LOOP;
END;
/
select BYTES,SEGMENT_TYPE from user_segments where SEGMENT_NAME='TEST';
BYTES SEGMENT_TYPE
---------- ------------------
9437184 TABLE
which is around 9 MB
Example for using Version 2:
This example estimate a Table size with one column NUMBER (10) , two columns VARCHAR2 (30) and one column date , with pct_free=10 and row_count=100000 :
DECLARE
ub NUMBER;
ab NUMBER;
cl sys.create_table_cost_columns;
BEGIN
cl := sys.create_table_cost_columns( sys.create_table_cost_colinfo('NUMBER',10),
sys.create_table_cost_colinfo('CHAR',30),
sys.create_table_cost_colinfo('CHAR',30),
sys.create_table_cost_colinfo('DATE',NULL));
DBMS_SPACE.CREATE_TABLE_COST('USERS',cl,100000,10,ub,ab);
DBMS_OUTPUT.PUT_LINE('Used Bytes = ' || TO_CHAR(ub));
DBMS_OUTPUT.PUT_LINE('Allocated Bytes = ' || TO_CHAR(ab));
END;
/
Used Bytes = 9314304
Allocated Bytes = 9437184
which is around 9 MB , now lets create and populate the actual table.
BEGIN
FOR i IN 1..100000 LOOP
INSERT INTO test VALUES (i,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' ,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa',sysdate);
END LOOP;
END;
/
select BYTES,SEGMENT_TYPE from user_segments where SEGMENT_NAME='TEST';
BYTES SEGMENT_TYPE
---------- ------------------
9437184 TABLE
which is around 9 MB
You may notice that version 2 of The CREATE_TABLE_COST procedure is more accurate than version 1 , which is expected because version 2 estimates the size based on the table structure which includes the column data types and length , plus the storage attributes .
Estimate Table Size Using Enterprise Manager
During table creation with Enterprise Manager, you can estimate the size of the table. This action enables you to determine whether you have sufficient room in your database or on the available disks to store the table. If you do not have room, then you can still create the table but not populate it until you have obtained the necessary storage.
To estimate the table size:
1. In the Database Objects section of the Administration page, click Tables.
The Tables page appears.
2. Click Create.
The Create Table: Table Organization page appears.
3. Select Standard, Heap Organized and click Continue.
The Create Table page appears.
4. Enter the table and schema names as well as your column names and data types, then click Estimate Table Size.
The Estimate Table Size page appears.
5. In Projected Row Count, enter the projected number of rows in the table and click Estimate Table Size
Enterprise Manager returns its estimate in MB.
Estimate The Size Of Indexes
The CREATE_INDEX_COST Procedure of the DBMS_SPACE package helps to estimate the size of creating an index on an existing table.
The input is the DDL statement that will be used to create the index. The procedure will output the storage required to create the index.
Usage Notes :
- The table on which the index is created must already exist.
- The computation of the index size depends on statistics gathered on the segment.
- It is imperative that the table must have been analyzed recently.
- In the absence of correct statistics, the results may be inaccurate, although the procedure will not raise any errors.
The output of the procedure contains used_bytes and alloc_bytes :
- The used_bytes : how much space is for the index data
- The alloc_bytes : how much space is allocated within the tablespace for the index segment.
Estimate Index Size example :
BEGIN
FOR i IN 1..100000 LOOP
INSERT INTO test VALUES (9999999999,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' ,'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaa',sysdate);
END LOOP;
END;
/
exec DBMS_STATS.GATHER_TABLE_STATS (ownname=>'hr',tabname=>'test',estimate_percent=>100,block_sample=>true,method_opt=>'FOR ALL COLUMNS size 254');
set serveroutput on
declare
l_used_bytes number;
l_alloc_bytes number;
begin
dbms_space.create_index_cost (
ddl => 'create index test_indx on test (a,b) tablespace users',
used_bytes => l_used_bytes,
alloc_bytes => l_alloc_bytes
);
dbms_output.put_line ('Used Bytes = '||l_used_bytes);
dbms_output.put_line ('Allocated Bytes = '||l_alloc_bytes);
end;
/
Used Bytes = 3800000
Allocated Bytes = 6291456
which is around 6 MB , now lets create the actual index.
select BYTES,SEGMENT_TYPE from user_segments where SEGMENT_NAME='TEST_INDX';
BYTES SEGMENT_TYPE
---------- ------------------
6291456 INDEX
which is around 6 MB
Refer To :
==========
https://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_space.htm#sthref8857
https://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_space.htm#sthref8850
REFERENCES
http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_space.htm#sthref8850http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_space.htm#sthref8857
About Me
...............................................................................................................................
● 本文作者：小麥苗，只專注於資料庫的技術，更注重技術的運用
● 本文在itpub（http://blog.itpub.net/26736162）、部落格園（http://www.cnblogs.com/lhrbest）和個人微信公眾號（xiaomaimiaolhr）上有同步更新
● 本文itpub地址：http://blog.itpub.net/26736162/abstract/1/
● 本文部落格園地址：http://www.cnblogs.com/lhrbest
● 本文pdf版及小麥苗雲盤地址：http://blog.itpub.net/26736162/viewspace-1624453/
● 資料庫筆試面試題庫及解答：http://blog.itpub.net/26736162/viewspace-2134706/
● QQ群：230161599 微信群：私聊
● 聯絡我請加QQ好友(646634621)，註明新增緣由
● 於 2017-07-01 09:00 ~ 2017-07-31 22:00 在魔都完成
● 文章內容來源於小麥苗的學習筆記，部分整理自網路，若有侵權或不當之處還請諒解
● 版權所有，歡迎分享本文，轉載請保留出處
...............................................................................................................................
拿起手機使用微信客戶端掃描下邊的左邊圖片來關注小麥苗的微信公眾號：xiaomaimiaolhr，掃描右邊的二維碼加入小麥苗的QQ群，學習最實用的資料庫技術。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26736162/viewspace-1381160/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle如何估算即將建立的索引大小Oracle索引
- 【oracle 】如何估算即將建立的索引大小Oracle索引
- 查詢oracle 表的大小和表的建立時間Oracle
- Oracle 找出需要建立索引的表Oracle索引
- 獲取資料庫表的資訊(大小,索引大小,建立時間,行數)資料庫索引
- 【eygle】Oracle的分割槽表和Local索引建立與維護Oracle索引
- Oracle大表快速建立索引Oracle索引
- Oracle堆組織表的索引和索引組織表Oracle索引
- 【STAT】Oracle 表統計資訊被鎖，如何建立索引Oracle索引
- Oracle 表的移動和索引的重建Oracle索引
- ORACLE分析表和索引的指令碼Oracle索引指令碼
- Oracle如何建立B樹索引Oracle索引
- oracle 表分析和索引Oracle索引
- Oracle複合索引的建立和注意事項Oracle索引
- 【轉載】oracle全文索引的建立和使用Oracle索引
- MySQL 庫大小、表大小、索引大小查詢命令MySql索引
- oracle 索引的建立與管理Oracle索引
- 關於 Oracle 分割槽索引的建立和維護Oracle索引
- Oracle表與索引的分析及索引重建Oracle索引
- 【資料庫使用-oracle索引的建立和分類】二資料庫Oracle索引
- 【資料庫使用-oracle索引的建立和分類】一資料庫Oracle索引
- 【Oracle】-【建立索引】-建立索引的操作原理與一些體會Oracle索引
- 索引高度和索引段大小計算索引
- Oracle表與索引的分析及索引重建(轉）Oracle索引
- 修改Oracle資料庫表的大小Oracle資料庫
- PowerDesiner 15 在物理模型中建立表和索引模型索引
- 如何檢視Oracle資料庫表空間大小(空閒、已使用),是否要增加表空間的資料檔案...Oracle資料庫
- 11G新特性，explain plan 可以評估出索引大小AI索引
- Lucene中建立索引的效率和刪除索引的實現索引
- Oracle 分割槽表的建立Oracle
- 學習Oracle的索引、表的儲存Oracle索引
- 索引組織表上建立BITMAP索引（三）索引
- 索引組織表上建立BITMAP索引（二）索引
- 索引組織表上建立BITMAP索引（一）索引
- 檢視ORACLE中表、表空間的大小Oracle
- 如何檢視ORACLE的LOB(BLOB和CLOB)物件佔用的大小Oracle物件
- mongodb建立索引和刪除索引和背景索引backgroundMongoDB索引
- DB2頁大小、表大小和表空間大小限制DB2