2.Oracle gives dba more flexibility, and can adjust the parameters according to the actual situation to make your application performance the best, which brings a disadvantage that it is difficult to get started; On the other hand, Sql server is easy to use.
architecture
Oracle's file architecture is:
Data file. Real data
Log file. remote data objects
Control file. Cytotoxic T lymphocyte (short for cytotoxic lymphocyte)
Parameter file. eyrir
The file architecture of sql server is:
. data dictionary
. Data file
. Log file
www.2cto.com
Oracle storage structure:
In oracle, there are two block parameters, pctfree (fill factor) and pctused (reuse factor), which can control the block to decide when there is a block itself and when there is not enough space to accept new information (the analysis mechanism of the storage situation of the block).
This can reduce the possibility of data row connection and row migration. You can set the block size (oltp block and dss block).
In oracle, continuous blocks are grouped into partitions, which can be dynamically allocated (the allocation of partitions can be equal or self-growing), thus reducing the number of space allocations.
In oraclel, a table can be divided into multiple segments and composed of multiple regions, and each segment can specify which tablespace to allocate (the types of segments are: data segment, index segment, rollback segment, temporary segment and cash segment). Oracle can also partition tables according to user-defined business rules, conditions or specifications, and physically separate the data on the disk.
This greatly reduces the possibility of disk contention.
Oracle has seven basic tablespaces:
System table space (storing information needed by data dictionary and data management itself)
Rbs rollback tablespace
Temp temporary tablespace
Tool interactive tablespace
User User Default Tablespace
Indx index tablespace
Dbsys fu data table space
Different data are placed in different tablespaces (data dictionaries are stored separately from actual data). In oracle, the base table (which stores system parameter information) is encrypted and no one can access it. Can only be viewed through the user's visual view.
Sql server storage structure
Taking pages as the minimum allocation unit, each page is 8k (uncontrollable, lacking the analysis mechanism of page storage), and eight consecutive pages can be combined into an' extension' to further reduce the resources consumed during allocation. (allocation is not flexible). In sql server, data is stored in tables, and tables are stored in databases.
Sql server has five basic databases: www.2cto.com.
Master (data dictionary)
Schema (storage template)
Temporary database
Msdb (storing scheduling information and log information)
Pubs (sample database)
Real data is stored in a data dictionary. There is no security mechanism for system parameter information.
Oracle login management:
System/Manager (Initial Account)
System/Change Open Installation
Install/oracle (installation account)
Scott/tiger (sample database for testing)
In oracle, there are only three system users by default, and oracle logs in through users.
Sql server login management:
Sql server authentication
Windows authentication
In sql server, the database is connected through windows user account or sql server authentication.
Sql is not a language, but a way to transmit instructions to oracle database.
Null in sql is neither a character nor a number, it is the default data. Oracle provides nvl function to solve this problem.
String connection in oracle is string 1‖string2, and string 1+string2 in sql.
Set operation: Only union exists in sql, and minus (difference operation), interect (intersection operation) and union (joint operation) are included in oracle.
Index: sql index is divided into clustered index and nonclustered index, including full-text index;
Oracle's indexes include: b+ tree index, bitmap bitmap index, function index, inverted index,
Primary key index, hash index, local index. www.2cto.com
Oracle's data types are complex, including basic data type, derived type, column object type, table object type and structure type.
The data in sql is relatively simple, and only some simple basic data types cannot provide transaction operations.
In sql, if a transaction is wrong from beginning to end, it will be rolled back to the beginning;
Fixed-point rollback is adopted in oracle, that is, transactions are functionalized and errors are accurately located, savepoints are used to mark savepoints, and rollback is used to mark savepoints of rollback errors.
There is only one loop in sql, which is not flexible to use. In oracle, there are many loops (while loop, loop loop).
The use of cursors in sql is complicated, so it is impossible to open multiple cursors at the same time, because it has only one global variable @ @ @ fast _ statues, and it is troublesome to declare cursors, and the memory is not cleared when closing cursors; Cursors in oracle are all unified single cursors, which have improved speed, can allow multi-users to read asynchronously, and the declaration is relatively simple, only one statement of cursor name select is needed.
Fault-tolerant mechanism: the error mechanism in sql is complex and does not provide error description; There are three types of fault tolerance in oracle, one is predefined error, the other is non-predefined error and the other is user-defined error. Among the custom errors, there are two that are not found in sql, namely, sqlcode error number and sqlerrm error description.
File schema: sql contains. Master data file. Ndf (extension file, which can have multiple files),
. Ldf (There can be multiple log files, which are stored in online redo log data. One disadvantage of the log file here is that if the log file is small and exquisite, sql will automatically stop running and wait for human intervention, so it is necessary to monitor the log frequently to ensure the stable operation of the system. Oracle Bone Inscriptions includes. Master data file. Rdo (log file for catastrophic data backup). Ctl (control file, which maps the physical file of the database to the logical table space in the data dictionary and the redo log file on the computer to ensure the consistency of data) and. Ora (parameter file).
Sql can only back up the local database, but it can't be online. The backup compression rate is very low and it takes up a lot of space. Oracle provides seven backup mechanisms, with online backup function and dedicated backup machine.
There are three logs in oracle by default. First, write LogNo. 1, and then fill the No.2 log, and then fill the No.3 log. When the log is full, oracle will automatically prepare the contents of the log number. 1, and then empty and write new log information. Moreover, oracle's logs have multiplexing function. We can create multiple mirrors for the log and write the same log information into multiple log files at the same time, which can prevent disk damage.
Sql server can only allocate one log file for each database. And when the log is full, it will stop working and wait for manual intervention, so it cannot be reused automatically. www.2cto.com
The control file of oracle records the physical location and validity of the database file. Every time the oracle system is opened, it will automatically check the data to check its validity and prevent data loss, which reflects the rigor of oracle data management.
Sql server has no such security mechanism, and only after using the information of the database will the system find out whether the data exists.
Oracle's parameter file init.ora is adjustable, that is, we can adjust oracle's use of system resources according to the size of the database to achieve the most reasonable resource allocation and the best performance of Oracle.
The configuration parameters of sql server are fixed and cannot be adjusted, and its allocation of system resources is also fixed, so it cannot handle the needs of a large number of users at the same time, which limits its use as a small and medium-sized database.
Oracle uses blocks as the minimum storage unit and allocates space by region. Users can define the block size according to their own needs, and the areas can be allocated in an equal or incremental way, which can greatly reduce the i/o operation of the system and improve the performance of the database.
In sql server, by using the extension type to allocate space in units of pages or in units of 8 pages, manual intervention is impossible. In the case of frequent storage or big data storage, the system will frequently perform i/o operations, resulting in low work efficiency.
Sql statements in oracle are standardized and provide complete functions and data types. Oracle provides a perfect error mechanism, and users can easily get the location and reason of the error and modify it. Users can also define their own error types to check logical errors. Data type conversion and cursor definition in oracle are also very convenient. Therefore, it is much more convenient for us to write and maintain code than sql server.
Sql server uses C-like language, so it is difficult to maintain and view the code. Sql server contains too few data types to define structures, arrays and objects. Sql server cannot define the process mechanism, and type conversion is very inconvenient. The definition of cursor in sql server is very complicated. When multiple user-defined cursors are opened at the same time, the system has only one cursor variable, which is a serious defect of sql server.
Users in oracle can define a fill factor (percentage of free space) and a reuse factor when creating a block according to data requirements (only when the space occupied by the stored data of the block is lower than this percentage will the block be re-marked as available). Users can set appropriate fill factor and idle factor according to the frequency of recording changes and the size of data volume.
Sql server has only one fill factor, and its definition is just the opposite of that of oracle, which defines the percentage of remaining free space. However, there is no reuse factor in sql server. When the data in the page is lower than the fill factor, but it is impossible to insert new data in the remaining space, the page is still marked as available. When the system wants to write new data, visiting this page will waste time. This kind of page is called a waste page. When the system inserts new data, it will waste a lot of time to find available pages.
When oracle creates tables, users can accurately define the table space for storing data, and even store a table in multiple table spaces, so that tables with huge data can be stored separately according to certain fields, which will bring high efficiency to the query.
www.2cto.com
With the rapid development of information technology, data processing requires not only more and more quantity, but also higher and higher quality.
The stability of the operating system is very important for the database. On the operating platform of database, oracle can run on all mainstream platforms. Oracle database adopts open strategic objectives, enabling customers to choose the solution that best suits their specific needs. Customers can use a variety of third-party applications and tools. It is a great support for developers. But sql server can only run on windows, which is monotonous. However, the performance of sql sever on the window platform, the overall integration with windows operating system, the ease of use, and the integration with Microsoft development platform are much better than oracle. But the stability and reliability of windows operating system are obvious to all. Moreover, Microsoft's strategic goal is to lock customers in the environment of windows platform. Only by improving the performance of windows can sql server be further improved. From the operating platform, oracle is completely superior to sql server.
As can be seen from the data, oracle's security certification has obtained the iso standard certification with the highest certification level, while sql server has not obtained any security certification. This proves that the security of oracle is higher than that of sql server.
When buying a product, the first consideration is of course the performance of the product. When considering the performance, of course, how much this product will cost is also a key issue. Establish and run a database system. It includes not only the upfront cost of purchasing software and hardware, but also the cost of training and post-maintenance. The price of orcale database is much higher than that of sql server database. Why is the oracle database so much higher than the sql server database? On the one hand, the initial cost of oracle is relatively high, especially when considering tool software, many tools of oracle need to be purchased separately. Compared with the free sql server tool software provided by Microsoft, oracle is more expensive. However, because sql server can only run under windows, it is generally considered that the reliability of sql server database is relatively poor. The performance advantage of Oracle lies in its multi-users, while the performance advantage of sql server is not enough for multi-users.
The operation of sql server is obviously simpler than orcale. If you have used java and dotnet development platforms, the basic difference is that oracle and sql server are different. The interfaces of oracle are basically based on java, and most tools are dos interfaces, even sqlplus. Sql server, like vb, has a complete graphical interface and rarely sees dos windows. Enterprise Manager in sql server provides users with an integrated management console, which has a complete graphical interface to centrally manage multiple servers. Oracle also has its own enterprise manager, which even surpasses sql server in some aspects, but it is difficult to install.
Which is faster, carale database or sql server database? In fact, this is a difficult question to answer, because there are many uncertain factors, including processing type, data distribution and hardware infrastructure.
Sql server 2000 is a database product that fully supports the web. It provides the core support for Extensible Markup Language and the ability to query on the internet and outside the firewall. It has the characteristics of scalability, flexibility, expansibility and low price, while oracle has reliable security, which is hundreds of times faster than sql server, but the price is hundreds of times higher than sql server in implementation.
In fact, the choice of database by various industries is determined by the characteristics of database and the comprehensive consideration of their own business needs and infrastructure.