Architecture

ORACLE's file architecture is

Data file DBF (real data)

Log file RDO

Control file CTL

Parameter file ORA

SQL SERVER's file architecture is

MDF (data dictionary)

NDF (data file)

LDF (log file)

ORACLE storage structure

In ORACLE there are two block parameters, PCTFREE (padding factor) and PCTUSED (multiplexing factor), that control the block to determine when the block itself has, and does not have, enough space to accept new information. New information (a mechanism for analyzing block storage)

This reduces the likelihood of row joins and row migration Block size can be set (for OLTP and DSS blocks)

In ORACLE, contiguous blocks are grouped into zones Dynamically allocated zones (which can be either equal or self-growing) reduce the number of space allocations

In ORACLEl, tables are grouped into zones, which are either equal or self-growing, reducing the number of space allocations

In ORACLEl, tables can be grouped together into zones. ORACLEl tables can be divided into segments Segments consist of multiple zones Each segment can be assigned to a tablespace (segments are categorized as Data Segments Index Segments Rollback Segments Temporary Segments CASH Segments) ORACLEl also allows for partitioning of tables Data can be physically separated from the disk according to user-defined business rules, conditions, or specifications

This greatly reduces the likelihood of disk contention

ORACLEl has the ability to partition tables into segments, which can be used to create a single block. >

ORACLE has seven basic tablespaces

- SYSTEM tablespace (which holds the data dictionary and information needed to manage the data itself)

- RBS rollback tablespace

- TEMP temporary tablespace

- TOOLS interactive tablespace

- USERS user-default tablespace

-INDX index tablespace

-DBSYS database tablespace

Different data is placed in different tablespace (data dictionary is stored separately from the real data) In ORACLE, the base table (which stores the system parameter information) is encrypted and stored in an encrypted manner, which is inaccessible to anyone, and can only be viewed by the user's visual view

SQL SERVER Storage Structure

SQL SERVER Storage Structure

Minimum allocation is in pages, each page is K (uncontrollable) Lack of mechanism to analyze storage of pages

Successive pages can be grouped into a single extension to further reduce the resources used for allocation (lack of flexibility in allocation) In SQL SERVER the data is stored in the form of tables, which are stored in the database

SQL SERVER is stored as a table, which is stored in the database

SQL SERVER is stored as a table, which is stored in the database.

SQL SERVER has five basic databases

-master (the data dictionary)

-mode (which holds the sample version)

-tempdb (the temporary database)

-msdb (which holds scheduling and logging information)

-pubs (the sample database)

The real data is stored with the data dictionary. Real data is stored with the data dictionary No security for system parameter information

ORACLE login management

-SYSTEM/MANAGER (initial account)

-SYS/CHANGE_ON_NSTALL

-INSTALL/ORACLE(installation account)

-SCOTT/TIGER (sample database for testing)

There are only three system users in ORACLE by default, and ORACLE logs them in as users

SQL SERVER login management

-SQL SERVER authentication

-WINDOWS authentication

In SQL SERVER, there are only three system users by default. > In SQL SERVER, you connect to the database through a WINDOWS user account or by using SQL SERVER authentication

SQL is not a language, it is a way of transferring commands to the ORACLE database

NULL in SQL is neither a character nor a number, it is the default data ORACLE provides NVL functions to solve the problem

SQL is not a language, it is a way of transferring commands to the database

SQL is a language. to solve this problem

String concatenation in ORACLE is string ‖ string +string in SQL

Collection operations There is only UNION (concatenation) in SQL and MINUS (difference) in ORACLE

INTERECT UNION (concatenation)

The UNION (concatenation) operation is a way of transferring instructions from the ORACLE database to the SQL database. operations)

Indexes SQL indexes are divided into aggregated and unaggregated indexes, and also include full-text indexes

ORACLE indexes include B+tree indexes Bitmap bitmap indexes Function indexes Reverse indexes

Primary key indexes Hash indexes Local indexes

ORACLE's data types are more complex There are the basic data types Derivative Columns Objects Table Objects Structural Objects Table Objects Structures Data types in SQL are simple, and there are only a few simple, basic data types that don't provide for transactions

In SQL, if a transaction makes a mistake from the beginning to the end of its execution, it rolls back to the beginning

In ORACLE, it uses fixed-point rollback, which is where the transaction is functionalized and the exact location of the error is marked with a savepoint to save the point of the transaction. savepoint to mark the savepoint and Rollback to mark the savepoint where the error was rolled back

In SQL, there is only one loop, WHILE, which is not very flexible, but there are many kinds of loops in ORACLE (LOOP loops, WHILE loops, FOR loops)

The use of cursors in SQL is more complicated because there is only one global variable, WHILE, and FOR. Because it has only one global variable @@Fast_statues and the declaration of the cursor is also troublesome Close the cursor does not clear the memory of the cursor in ORACLE cursor is to improve the speed of all the unified single cursor can allow multiple users to asynchronous reading and declaration is relatively simple on a declare cursor cursor name is select statement

Fault Tolerance The error mechanism in SQL is complex and does not provide error descriptions. There are three types of fault tolerance in ORACLE: a predefined error, a non-predefined error, and a user-defined error, and among the custom errors, there are two that do not exist in SQL, namely, the SQLCODE error number and the SQLERRM error description.

File Architecture There are three types of file architecture in SQL: the MDF (Main Data File), the MDF (Primary Data File), and the MDF (Primary Data File). MDF (Main Data File) NDF (Extension File can have more than one)

LDF (Log File can have more than one) The log file here has a disadvantage is that if the log file has been filled with small and exquisite, then the SQL will automatically stop running and wait for human intervention, so we must always monitor the log situation to ensure the stability of the system). ORACLE DBF (main data file) RDO (log file used to do catastrophic data backup) CTL (control file) maps the physical database files to the logical tablespace in the data dictionary and redo log files to ensure data consistency) ORA (parameter file)

SQL can only be local backup of the local database can not be backed up on-line backups, and the backup compression is very low, taking up a lot of space ORACLE SQL can only be used for local backups. We can create multiple mirrors of the log and write the same log information to multiple log files at the same time to prevent data loss due to disk corruption

SQL Server can only allocate one log file per database, and when the log is full, the log stops working and waits for manual intervention, but cannot be automatically reused

Oracle's control file records the physical location and validity of database files. Every time you open Oracle, the system automatically verifies the validity of the data and prevents data loss, which demonstrates Oracle's rigorous data management

SQL Server does not have such a security mechanism, and only uses it to find out whether the data exists after it has been used to access the database's information

SQL Server's configuration parameters are built-in and not adjustable, and its allocation of system resources is fixed and not subject to control by the user. This limits its use to small and medium-sized databases

Oracle uses blocks as the smallest unit of storage and allocates space in zones, where users can define their own block sizes and zones can be allocated in equal or incremental increments. This greatly reduces I/O operations and improves the performance of the database

SQL Server allocates space in pages or, using extended types, in pages. SQL Server allocates space on a page-by-page basis, or on a page-by-page basis using extended types, without human intervention.

SQL statements in Oracle are written in a standardized way, and Oracle provides a complete set of functions and data types. Users can easily get the location of the error and the cause of the error and modify it. Users can also define the type of error required to check for logical errors. SQL Server contains too few data types to define structures, arrays, and objects. SQL Server cannot define flow mechanisms, and type conversions are inconvenient. The definition of cursors in SQL Server is very complex, and when user-defined cursors are opened at the same time, there is only one cursor variable in the system, which is a serious shortcoming in SQL Server.

In ORACLE, you can define the fill factor when creating a block according to your data needs. In ORACLE, you can define the fill factor (the percentage of free space) and reuse factor (when the space occupied by the stored data of a block falls below this percentage, the block is marked as available again) at the time of creating a block according to the data needs. You can set the appropriate fill factor and free factor according to the frequency of record changes and the size of the data volume

SQL SERVER only has a fill factor, and its definition is the opposite of that of ORACLE. SQL SERVER has only fill factor and it is defined as the percentage of free space left, whereas there is no reuse factor in SQL SERVER. When the data in a page is below the fill factor, but there is no space left for inserting new data, but the page is still marked as free, the system wastes time by accessing the page when it wants to write new data. This type of page is called a wasted page, which will waste a lot of time in searching for a free page when it wants to insert new data. p> ORACLE allows the user to define exactly which tablespace the data will be stored in when creating a table, and can even separate a table into multiple tablespaces. This allows tables with large amounts of data to be separated by certain fields, which can make queries extremely efficient

========================================== ===============================================

With the rapid development of information technology, data processing is not only more and more demanding in terms of quantity, but also in terms of quality

The stability of the operating system is very important for the database. Oracle can run on all major platforms Oracle Database open strategy goal It allows customers to choose a solution that best suits their specific needs Customers can take advantage of a wide range of third-party applications Tools for developers is a great support while SQL Server can only run on Windows This would seem to be more monotonous But SQL Sever on the Window platform, but SQL Server on the Window platform is not the only one, but it is the only one that can run on Windows. However, the performance of SQL Server on the Windows platform, the overall integration with the Windows operating system, the ease of use, and the integration with Microsoft's development platforms is much better than that of Oracle, but the stability and reliability of the Windows operating system is well documented, and Microsoft's strategic goal is to lock in their customers to the Windows environment. Besides, Microsoft's strategic goal is to lock customers into the Windows environment, and only with the improvement of Windows performance can SQL Server be further improved.

From the data, we can see that Oracle's security certification has obtained the highest level of ISO standard certification, while SQL Server has not obtained any security certification. This proves that Oracle's security is higher than SQL Server's

The first thing to consider when buying a product is of course the product's performance When considering performance, of course, how much money you have to spend on the product is also a very critical issue To build and run a database system not only includes the initial purchase of software hardware costs but also training and maintenance costs. Including the cost of training and future maintenance Orcale database price is much higher than the SQL Server database Why Oracle database than the SQL Server database price is so much higher On the one hand, Oracle's initial cost is relatively high, especially when considering the tools and software Oracle many tools and software need to be purchased separately Compared with the SQL Server database, Microsoft provides free SQL Server software, which can be purchased separately. Compared with the free SQL Server tools provided by Microsoft, Oracle's price is even more expensive. However, because SQL Server can only run under Windows, it is generally believed that the reliability of SQL Server database is worse. Oracle's performance advantage is reflected in its multi-user, while SQL Server's performance advantage is not enough for multi-user.

Operation of SQL Server is significantly easier than Orcale If you have used Java and DOTNET development platforms, the difference between Oracle and SQL Server is basically that Oracle's interface is basically based on Java, and most of the tools are Dos interfaces. SQL Server is a full graphical interface like VB, with few DOS windows. The Enterprise Manager in SQL Server provides users with an integrated management console with a full graphical interface to centralize the management of multiple servers. Manager, but it is more difficult to install

Which is faster, an Orcale database or a SQL Server database? This is a difficult question to answer because there are a number of variables, including the type of processing, data distribution, and hardware infrastructure

SQL Server is a fully web-enabled database product that provides extensible markup language core support and the ability to query over the Internet and outside the firewall, and is scalable, flexible, expandable, and inexpensive, while Oracle is reliable, flexible, scalable, and inexpensive. Oracle, on the other hand, is reliable and secure, hundreds of times faster than SQL Server, but hundreds of times more expensive to implement than SQL Server

lishixinzhi/Article/program/Oracle/201311/18988

SQL Server is a fully web-enabled database product that provides extended markup language core support and the ability to query outside the firewall over the Internet.