Original address :http://blog.sina.com.cn/s/blog_5014663501007n40.html

When designing a database , Choose the right data type , Many problems can be avoided , Understand the type of database correctly , For storage space planning , Application performance tuning can be very helpful , Here is my personal summary :
 
1、 Char
Fixed length format string , When storing in the database, there are not enough digits to fill in the space , Not recommended , It will cause unnecessary trouble
a、  When comparing strings , If you don't pay attention to (char Fill in the blanks ) It can lead to mistakes
b、  When comparing strings , If you use trim function , So the index on the field is invalid ( Sometimes it can cause serious performance problems )
c、  Waste storage space
 
2、 Varchar2/Varchar
Variable length format string , about 4000 Strings within bytes , This type is recommended
a、  It's said on the Internet char Than varchar2 Good performance , But if you're interested in testing , Will find no difference ( If line migration occurs , Can pass pctfree To adjust )
b、  Make the most of storage space
 
3、 Long/long raw
Oracle Has been abandoned , It's just reserved for downward compatibility , It should all be upgraded to lob
Long There are many restrictions on types
a、  There can only be one column in the table long type
b、  Long Type does not support distributed transactions
c、  Too many queries cannot be found in long It has been used.
 
4、 Number
Definition Number Methods :Number(p,s)
among p,s It's all optional :
a、  p Represents precision , The default is 38
b、  s For decimal places , Value range -84~127, The default value depends on whether it is specified p, If there is p, Default s by 0, If not specified p, The default value is the maximum .
A few examples :
a、  Number(5,0)=Number(5) Value range 99999~-99999
b、  Number(5,2) Value range 999.99~-999.99
Be careful : The number of integers is just 3 position , The number of decimal places is 2 position , Calculate as follows :
     
Number of integers <=p-s
     
Decimal digit <=s
If you insert 123.555 Stored in a database becomes 123.56
( Round the third decimal place ), If you insert 999.999, The database is about to throw a mistake .
c、  Number(5,-2) Value range 9999900~-9999900
( Number of integers <=p-s, There are no decimal places )
If you insert 9999949 Stored in a database becomes 9999900( Round the second digit of an integer ), If you insert 9999950, The database is about to throw a mistake .
The other numerical types are number Derived from , The bottom is number, such as integer/int It's completely mapped to number(38)
Performance related :number It's a type of soft implementation , If necessary number Doing complex operations , It is recommended to use first. cast Built in function transformation number Is a floating point number type
Another thing to be aware of :number It's the variable length type , When calculating table storage space, remember
 
5、 Date
Date The type is a 7 Fixed length data type of bytes , There's nothing to say , An example : performance a>b>c

a、Where
date_colum>=to_date(’01-jan-2007’,’dd-mon-yyyy’)

and date_colum<
DIV>

b、Where
trunc(date_colum,’y’)=to_date(’01-jan-2007’,’dd-mon-yyyy’)
c、Where to_char(date_colum,’yyyy’)=’2007’
 
6、 Timestamp/timestamp with time zone/timestamp with
local time zone
and date similar , But it also supports fractional seconds and time zones . grammar Timestamp(n),n Specify the number of decimal places for the second , Value range 0~9. Optional .
 
7、 Lob
Clob/blob The implementation is more complex , Here are just a few performance related points , Of course not lob As far as possible need not :
a、  One lob Fields including lobindex and lobsegment
b、  Lob By default, it can be stored in the table ( Table field ), On the condition that :
    
1. It's smaller than 4kb
    
2. And it's not used when it's defined (disable storage inrow) Words and expressions ( The default is enable)
    
When lob Greater than 4kb It will be stored in lobsegment in
c、 
When lob When it's in the table , It can be cached , For it, the operation efficiency is much higher than that stored in lobsegment Medium lob( no need lobindex)
d、 
Stored in lobsegment Medium lob The default is not in the buffer cache , about lob It's all about physics IO, The cost is very high , So for greater than 4kb Of lob Don't update fields frequently , Very inefficient
e、 
Stored in lobsegment Medium lob You can specify the use of cache( The default is nocache), This is for medium-sized lob( How about k~ Dozens of k) Very effective , Less Physics IO

[ turn ]oracle Design database should choose the correct data type for more related articles

  1. To better design the database , Rearrange sql server data type

    We're in the normal development process , When designing data , We often encounter the problem of data type selection , In order to be faster , Choose the right data type more appropriately , So here's a summary . classification sql server data type c# data type describe Application scenarios word ...

  2. WCF How to choose the right instance mode during development (InstanceMode)?

    WCF How to choose the right instance mode during development (InstanceMode)?   In the use of WCF Instance model , Have you ever thought about these questions : ”WCF How to correctly apply the instance pattern in ”? ” Use WCF What are the principles of the instance pattern in ...

  3. [ turn ] How to choose the right Hadoop edition

    Gartner: How to choose the right Hadoop edition The full name of this report is <How to Choose the Right Apache Hadoop Distribution>. This paper mainly introduces how to ...

  4. Oracle 11g Database creation

    Because it's self-study Oracle, If there are questions , Please point out , thank you ! Oracle Provides DBCA To create a database , For beginners, use DBCA Creating a database simplifies a lot of work and setup , All functions can be realized directly in the interactive interface . However, yes. ...

  5. Why? MySQL Database index selection use B+ Trees ?

    In the further analysis of why MySQL Database index selection use B+ Before the tree , I believe that many small partners are a little vague about the tree in the data structure , So let's explore the evolution of trees step by step , Step by step lead to B Trees and why MySQL Database index selection makes ...

  6. ( turn ) Authority supports : Choose the right one WebSphere Diagnostic tools

    Authority supports : Choose the right one WebSphere Diagnostic tools original text :https://www.ibm.com/developerworks/cn/websphere/techjournal/0807_supau ...

  7. Package Design 1: Select data type 、 Staging data and concurrency

    SSIS Design series : Package Design 1: Select data type . Staging data and concurrency Package Design 2: Incremental updating Package Design 3: Extraction and use of data sources One , Selection of data type about SSIS Data type of , Rong ...

  8. Oracle 10g Operation manual of database backup and recovery

    Oracle 10g Operation manual of database backup and recovery Catalog Oracle 10g Operation manual of database backup and recovery ... Preface ... Purpose of the document ... Document scope ... Target audience ... Other instructions ... Cold backup ... Hot backup ...

  9. Oracle 11g Database details

    Common abnormal : ORA-14025: Cannot specify... For materialized view or materialized view log PARTITION ORA-14026:PARTITION and CLUSTER Clauses are mutually exclusive ORA-14027: Only one... Can be specified PARTI ...

Random recommendation

  1. Codeforces 738D. Sea Battle simulation

    D. Sea Battle time limit per test: 1 second memory limit per test :256 megabytes input: standard inp ...

  2. ASP.NET frequently-used SqlDbHelper class

    Please quote using System.Data;using System.Data.SqlClient; Two namespaces . Can meet the common data sets , Read multiple data , And addition, deletion and modification Code : /// <sum ...

  3. iOS - Third party framework - AFN

    #5.AFNetworking 2.6 Usage method >2.6 edition Support iOS7 above , And support NSURLConnectionOperation >3.0 edition Support iOS7 above NSURLCo ...

  4. How to implement in browser web page java The function of the app

    We know ,java The use of language is object-oriented function , and c Different ,java Language for programmers , It's easier to use . Applets are different from applications , Applets can only work with Java Run in a compatible container , Can be embedded in HTML Webpage ...

  5. Spring Yes jdbc Support

    4. Spring Yes jdbc Support spring Yes jdbc Good support Embodied in : 1.Spring Yes C3P0 Connection pooling is well supported 2.Spring Yes jdbc Provides jdbcTemplate To simplify the jdbc ...

  6. C# JAVA Member access modifier comparison

    Among the object-oriented access modifiers commonly used are public ,private ,protected C# Access modifier : private < protected internal < internal/p ...

  7. Qt5 and VS2017 Build the development environment , New project cannot be found after installation Qt Options !!!

    Recently developed win Drive and Qt5 The test program , Need to establish Qt5 and VS2017 development environment --- about Qt5 and VS2017 There is no redundant description here . There are a lot of reference resources , The explanation was also good !! Let's get to the point : stay VS2017 Zhong An Zhuan Qt vs ...

  8. motor Use

    # -*- coding: utf-8 -*- # @Time : 2018/11/18 10:41 PM # @Author : cxa # @File : motordb.py # @Softwa ...

  9. memcached and redis Compare

    Belong to NOSQL Storage , There's a lot going on the Internet memcached What can be done is redis You can do it. , Why are both of them popular now , Because they have their own strengths . memcahed Internally, it adopts multi-core mode , It's fast on a single train .memcached Mining ...

  10. jmeter- How to be in JDBC Request Add multiple statements to execute

    1.JDBC Connection Configuration Middle configuration Database URL At the time URL Add later   ?allowMultiQueries=true 2.JDBC Request Add a statement to ...