10G的CSV倒入Oracle数据库会占用多少空间？

本文主要是介绍10G的CSV倒入Oracle数据库会占用多少空间？，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

利用Oracle示例Schema中的sh.sales表，导出为csv文件。

建立外部表sales_ext，对应此csv文件：

CREATE TABLE sales_ext ("PROD_ID"        NUMBER,"CUST_ID"        NUMBER,"TIME_ID"        DATE,"CHANNEL_ID"     NUMBER,"PROMO_ID"       NUMBER,"QUANTITY_SOLD"  NUMBER(10, 2),"AMOUNT_SOLD"    NUMBER(10, 2)
)
ORGANIZATION EXTERNAL (TYPE ORACLE_LOADERDEFAULT DIRECTORY default_dir ACCESS PARAMETERS ()LOCATION ( 'SALES_DATA_TABLE.csv' )
);

最初的csv文件只有20MB，使用类似以下脚本放大到10G:

for i in {1..6}; do cat SALES_DATA_TABLE.csv >> /u01/tmp/SALES_DATA_TABLE.csv
done

查看文件：

$ ls -l /u01/tmp/SALES_DATA_TABLE.csv
-rw-r--r-- 1 oracle oinstall 10886524012 Jul 16 06:36 /u01/tmp/SALES_DATA_TABLE.csv

查询外部表的行数：

SQL> select count(*) from sales_ext;COUNT(*)
----------334458852

从外部表创建实体表：

set timing on
create table sales nologging as select * from sales_ext;

创建表耗时：

Elapsed: 00:22:49.06

表占用的空间：

SQL> set numformat 999,999,999,999
SQL> select bytes, blocks from user_segments where segment_name = 'SALES';BYTES           BLOCKS
---------------- ----------------13,237,223,424        1,615,872

这个比CSV文件多了2G，多了20%的开销：

SQL> select 13237223424 - 10886524012 from dual;13237223424-10886524012
-----------------------2350699412SQL> select 13237223424/10886524012 from dual;13237223424/10886524012
-----------------------1.21592745

启用压缩：

SQL> set timing on
SQL> alter table sales move compress;

启用压缩耗时：

Elapsed: 00:18:57.82

只用到4G了，压缩效果不错：

SQL> set numformat 999,999,999,999
SQL> select bytes, blocks from user_segments where segment_name = 'SALES';BYTES           BLOCKS
---------------- ----------------4,429,185,024          540,672

查询性能。：

SQL> select count(*) from sales;COUNT(*)
----------334458852Elapsed: 00:02:23.75
SQL> select count(*) from sales;COUNT(*)
----------334458852Elapsed: 00:02:46.51
SQL> select count(*) from sales;COUNT(*)
----------334458852Elapsed: 00:02:18.04
SQL> select count(*) from sales;COUNT(*)
----------334458852Elapsed: 00:02:21.23
SQL>  select count(*) from sales;COUNT(*)
----------334458852Elapsed: 00:02:19.30
SQL>  select count(*) from sales;COUNT(*)
----------334458852Elapsed: 00:02:18.65

去除压缩：

alter table sales move nocompress;

空间占用：

SQL>  select bytes, blocks from user_segments where segment_name = 'SALES';BYTES           BLOCKS
---------------- ----------------11,904,483,328        1,453,184

有个奇怪的问题，就是解压后的空间和最初未压缩时的空间不一致，要小些。

查询时间：

SQL> select count(*) from sales;COUNT(*)
----------334458852Elapsed: 00:02:15.14
SQL> select count(*) from sales;COUNT(*)
----------334458852Elapsed: 00:02:40.84
SQL> select count(*) from sales;COUNT(*)
----------------334,458,852Elapsed: 00:02:19.90
SQL> select count(*) from sales;COUNT(*)
----------------334,458,852Elapsed: 00:02:19.68
SQL> select count(*) from sales;COUNT(*)
----------------334,458,852Elapsed: 00:02:19.38

这篇关于10G的CSV倒入Oracle数据库会占用多少空间？的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---