Oracle单值字段数据类型
我有Status
列的表格,其中CheckOut,Stayover等值可能被存储。现在为了创建它的数据类型,我可以使用varchar
列按原样存储这些值,但是我担心的是性能以及应用程序中的值比较,因为我们可能必须使用trim
函数来移除值的空间。Oracle单值字段数据类型
我应该将它存储在数字1,2,3 ..还是以'S','C'等字符存储,然后在应用程序中比较这些数据?
让我知道什么样的数据类型将是更好地保存它:Number
VS Char
例如,是影响性能的,如果我们希望它是:
field='S'
or
field=1
NUMBER数据类型的Oracle存储为BCD(二进制编码的 十进制),它实际上是一个字符串类型。因此, 和varchar/char的比较基本相同。唯一的区别是 字符数据类型通过NLS层,因此需要更长一点的时间,但我发现差异可以忽略不计。
因此,我建议尽可能使用数字由于整数数据的操作比字符数据更快。
您应该阅读您链接到的所有AskTom主题。它根本不支持你的断言。 “一般来说,比较一个str到一个str,它和一个数字和一个数字相比一样快。” – APC
对于那些有兴趣在一些指标,这里有一系列的SQL语句来测试一个字符列的查询(VARCHAR2(1))和1位柱(NUMBER(1))。
测试设置 - 使用字符状态列和数字状态列创建100,000,000行表。运行一个简单的查询来使用字符状态过滤器对行进行计数,并将其时间与使用数字状态运行类似查询的时间进行比较。
执行摘要 - 差别不明显。
SQL> create table some_100_rows
2 as
3 select rownum as rnum
4 from dual
5 connect by level <= 100;
Table created.
SQL> create table some_1000000_rows
2 as
3 select ROWNUM as id
4 , cast(case when mod(rownum, 2) = 0 then 'S' else 'C' end as varchar2(1)) as varchar_status
5 , cast(case when mod(rownum, 2) = 0 then 1 else 2 end as number(1)) as num_status
6 from dual
7 connect by level <= 1000000
8 ;
Table created.
Elapsed: 00:00:01.46
(我保持数据和分布简单,所以字符检索和搜索数在做同样的事情,时间差异应该是由于单独的数据类型。)
SQL> create table test_varchar_vs_number -- a table of 100,000,000 rows
2 as
3 select t1.*
4 from some_1000000_rows t1
5 cross join
6 some_100_rows t2
7 ;
Table created.
Elapsed: 00:00:37.96
SQL> select count(*)
2 from test_varchar_vs_number
3 ;
COUNT(*)
----------
100000000
Elapsed: 00:00:10.54
请注意,只计算表格需要大约10秒。
这里的内容是什么样子:
SQL> select *
2 from test_varchar_vs_number
3 where rownum < 11;
ID VARCHAR_STATUS NUM_STATUS
---------- -------------- ----------
1 C 2
2 S 1
3 C 2
4 S 1
5 C 2
6 S 1
7 C 2
8 S 1
9 C 2
10 S 1
10 rows selected.
Elapsed: 00:00:00.04
运行选择带有“S”在VARCHAR_STATUS列数行数。重复几次以获得稳定的指标。
SQL> select count(*)
2 from test_varchar_vs_number
3 where varchar_status = 'S'
4 ;
COUNT(*)
----------
50000000
**Elapsed: 00:00:11.82**
SQL> select count(*)
2 from test_varchar_vs_number
3 where varchar_status = 'S'
4 ;
COUNT(*)
----------
50000000
**Elapsed: 00:00:11.05**
SQL> select count(*)
2 from test_varchar_vs_number
3 where varchar_status = 'S'
4 ;
COUNT(*)
----------
50000000
**Elapsed: 00:00:11.37**
因此只需要超过11秒来计算50,000,000个“S”行。
现在尝试与行同样的事情用一个1在NUMBER_STATUS列:
SQL> select count(*)
2 from test_varchar_vs_number
3 where num_status = 1;
COUNT(*)
----------
50000000
**Elapsed: 00:00:11.04**
SQL> select count(*)
2 from test_varchar_vs_number
3 where num_status = 1;
COUNT(*)
----------
50000000
**Elapsed: 00:00:10.79**
SQL> select count(*)
2 from test_varchar_vs_number
3 where num_status = 1;
COUNT(*)
----------
50000000
**Elapsed: 00:00:10.59**
所以,不同的是可以忽略不计。 (Min chacter搜索时间:11.05s与最小搜索时间10.59s。)
编辑: 对于那些对低级别细节感兴趣的人,这里是来自通过tkprof的10046踪迹的统计。这是从上面单独运行,所以不要指望时间完全匹配。 (请记住,查询的所有3次运行的总时间。)
select count(*)
from test_varchar_vs_number
where num_status = 1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 3 0.00 0.00 0 0 0 0
Execute 3 0.00 0.00 0 0 0 0
Fetch 6 11.85 34.30 621984 622005 0 3
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 12 11.85 34.30 621984 622005 0 3
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 110
Number of plan statistics captured: 3
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 SORT AGGREGATE (cr=207335 pr=207328 pw=0 time=11434679 us)
50000000 50000000 50000000 TABLE ACCESS FULL TEST_VARCHAR_VS_NUMBER (cr=207335 pr=207328 pw=0 time=10113986 us cost=56992 size=150000000 card=50000000)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 6 0.00 0.00
reliable message 1 0.00 0.00
enq: KO - fast object checkpoint 1 0.13 0.13
direct path read 4835 0.29 22.04
SQL*Net message from client 6 0.01 0.04
********************************************************************************
select count(*)
from test_varchar_vs_number
where varchar_status = 'S'
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 3 0.00 0.00 0 0 0 0
Execute 3 0.00 0.00 0 0 0 0
Fetch 6 11.20 33.43 621984 622005 0 3
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 12 11.20 33.43 621984 622005 0 3
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 110
Number of plan statistics captured: 3
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 SORT AGGREGATE (cr=207335 pr=207328 pw=0 time=11146155 us)
50000000 50000000 50000000 TABLE ACCESS FULL TEST_VARCHAR_VS_NUMBER (cr=207335 pr=207328 pw=0 time=9700296 us cost=56940 size=100000000 card=50000000)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 6 0.00 0.00
reliable message 1 0.00 0.00
enq: KO - fast object checkpoint 1 0.21 0.21
direct path read 4873 0.25 22.12
SQL*Net message from client 6 0.03 0.05
********************************************************************************
谢谢,它证明了我的回答 –
挂钟时间是臭名昭着的毫无意义的。没有索引,执行路径始终是全表扫描; WHERE子句过滤是总流逝时间中可以忽略的一部分。很抱歉,如果你付出了很大的努力,那么会很失望。 – APC
@APC没有索引;以上的SQL都是有的。我确实用位图索引进行了一次运行,对于数字和字符搜索,时间下降到大约0.01秒。为了完整性,我将包含执行计划和TKPROF信息(包括等待事件)。 –
您将一次检索多少条记录并检查/比较它们的状态? 1千,10万,100万,1亿? – krokodilko
不知道,可能超过这个 – Ash
我的建议是一个简短的varchar2列,存储一个固定长度的alpha代码,另一个两列表将代码关联到描述性名称。对代码进行一些真正的思考,以提出符合当前需求的标准,但也可以在未来将其添加到角落的情况下进行添加。这实际上并不特定于任何数据库产品。这是基本的数据设计。 – EdStevens