Comparison between Oracle8i and ms SQL SERVER2000

Editor's note: This article was written three years ago.

The following is my personal experience. Due to the limited level, it is estimated that there are some problems with understanding. Please refer to Oracle 8i for beginners, it is very likely that the concepts in ms SQL Server are often compared with those in Oracle. In my opinion, I do not need to make too many such comparisons, if you have not learned any other database management before, it may be easier to learn Oracle. now let me talk about the difference and connection between the two.

1. Database concepts
How can I find or create my own database?
The concept of Oracle Database is completely different from that of MS SQL

Oracle services and databases are equivalent to database services of MS SQL
Oracle services = background processes + related memory

Database = data file set
In addition, Oracle services can be completely separated from data files.
Where can I find the ms SQL database in Oracle,
Schema of Oracle, which is a set of all objects owned by a user.
The objects here include tables, views, instantiation views, sequences, processes, functions, packages, synonyms
(I will explain in detail some of these unfamiliar concepts below)

Therefore, we need to establish a database in Oracle that is similar to ms SQL: general process
· Create a data table space (equivalent to the data file of the ms SQL database)
· Create a temporary tablespace (equivalent to the transaction log file of the ms SQL database)
· Create a user, but save the tablespace and temporary tablespace as the newly created two tablespaces
· Grant permissions to users. The minimum role permission is the Connect role.
· Log in with this user and create your own tables, views, instantiation views, sequences, processes, functions, packages, synonyms, etc.

2. Enetrprise Manager for Oracle manager Server and MS SQL
It should be said that there is a big difference between the two. DBA Studio provides the same functions as ms SQL's Enetrprise manager,
The DBA Studio tool is sufficient for general use.
However, it is necessary to explain to the Oracle Manager Server
· It is a service for Oracle to manage distributed databases. Note that it is a service.
· It saves the trouble of Installation
· It requires you to manage the databases you need and create users in a database
· Identity authentication is required for its record. Pay attention to the identity authentication here, and whether it is a concept for managing the users in the required database,
It is not the Sys and system users in the database, but saves the user as oemtemp.
· Its functions include:
· To use it, you must start the Manager Server service, the intelligent proxy service (OracleAgent), and the managed database Server on the Management end.
To start the intelligent proxy service (OracleAgent)

3. Comparison between Oracle Sys and System users and ms SQL master
The master database of ms SQL stores some configuration information of the current database service, such
Database device (which has been weakened in ms SQL), Character Set, data file, login account, database owned, configuration of the entire service parameter, and other information

Oracle's Sys stores information about Oracle services or instances and data dictionary information of all users.
Unlike ms SQL, ms SQL each database has its own object's data dictionary Information
System users have data dictionaries which are view information. With these views, it is especially convenient for us to query database information.
However, the system user has DBA system role permissions, while sys not only has DBA permissions but also SysDBA permissions.

What are the differences between DBA and SysDBA system roles?

Before explaining this, I need to talk about the Oracle service creation process.
· Create an instance
· Start an instance
· Create a database (system tablespace is required)
Startup Process
· Instance startup
· Load databases
· Open a database

SysDBA manages Oracle instances and does not rely on the full startup of the entire database,
As long as the instance is started, it already exists. log in as SysDBA, load the database, and open the database
The DBA role has a foundation only when the database is opened or the entire database is fully started!

4. New database objects in Oracle: instantiated views, snapshots, sequences, packages, synonyms, and abstract data types

· Instantiate a view, also known as a display chart, indicates that it has its own storage space. View: indicates that its data comes from other table data.
The data in the instantiation view is set to update data at intervals. The update mode can be defined as full update and incremental update.
· Snapshots are basically the same as instantiation views, but the data sources are different. snapshot data is intended for remote databases, while instantiation views are derived from local data tables.
· Sequence, which is equivalent to the identity column in ms SQL. It is a numerical sequence list.
· Package: a set of processes, functions, and global variables. It encapsulates private variables, private processes, and private functions.
Example: dbms_out package
· Synonyms: aliases of objects in the database. synonyms can be global or private (belonging to a user)
Such as Tab and col.
· Abstract data type, similar to the struct or Pascal record type in C
There is another question about the type: A Tab % RowType, which is A special abstract data type. The component of this type is the TAB field.
B Tab. TName % Type, which defines a variable of the same data Type as the TNAME field of the tab. Think about its advantages.

5. Oracle database connection and ms SQL remote connection
Both of them are intended for Distributed Database Operations.
Both can implement distributed transactions. For more information, see the online help of ms SQL.

6. Database Transaction log files for Oracle rollback segments and MS SQL
The rollback segment provides the images before data changes are required for an event rollback. These images are stored by entries,
If there are too few of these entries, the chance that one transaction will wait for another transaction to increase will affect the database performance.
During installation, a System rollback segment is provided in the system tablespace. To improve the performance, the System tablespace
Do not store information other than data dictionary information.

Ms SQL database transaction log file function similar to the rollback segment, but he is closely related to the specific database

7. Manage data tables
· Management of super large data tables
A data table, especially the kind of sequential account tables, expands sharply after several years and affects query performance.
Oracle and ms SQL both provide a method to store data files and their indexes in a specific
In data files or tablespaces, but this still cannot solve the problem. What should I do? developers can only add
Time Mark: such as CWSJ2000 (financial data 2000), CWSJ2001, CWSJ2002,
It can indeed improve the query performance, but it brings a lot of trouble to the development (this is what inspur finance did ),
And when you don't know which table the data is in, you have to join this table for query!
Oracle provides a good way to solve this problem: Table and index partition storage. Field Value Range
Partition storage. The specific practices are not described.

· Oracle index organization table and ms SQL Cluster Index Table
Both data are stored in the order of index values.

· A table without the concept of transaction rollback. Operations on such tables are not performed in transactions.
I remember that MySQL did not provide transaction rollback (I do not know whether it has changed)
Oracle provides the table creation parameter nologging, so that operations on the table are not involved in transaction rollback.

· Index
Oracle provides index types not available for multiple ms SQL statements
· Bitmap index,
For example, gender: only men and women
1st records: Male
2nd records: Male
3rd records: Female
4th records: Male
5th records: Female
6th records: Male
.........
Then its index: 110101 ............

This index saves much space. It applies to one of the several known field values.

· Function-based or expression-based indexing, Which is powerful

8. External Connection
· Ms SQL SERVER supports two forms of table connection
① The form inherited from Sybase:
Field 1 * = Field 2 (left join)
Field 1 = * Field 2 (Right join)
No full outer join syntax in this form
② Standard outer join syntax
Left [outer] join on logical expression
Right [outer] join on logical expression
Full [outer] join (all outer join) on logical expression
The logical expression can be A complex expression, for example, A. ID = B. ID AND (A. Parebt_ID = 1 OR A. Parent_ID = 2)

Please note that the query statement you wrote has reported such errors.
Joined tables cannot be specified in a query containing outer join operators. Joined tables cannot be specified in a query containing outer join operators. View or function 'dbo. VU_CAF_BILLS 'contains joined tables
This statement tells you that the view or subquery referenced by the query statement also uses an external connection, but the reference view or subquery outer connection syntax is not consistent with your outer connection syntax.
Example: select A. [ZONE], A. FLAG, A. FlagDesc, A. CAF_NO
From dbo. VU_CAF_BILLS A, TU_Flag
Where A. CAF_NO * = TU_Flag.ObjNo
View the external connection Syntax of dbo. VU_CAF_BILLS is a standard SQL syntax, while the external connection syntax in this statement is a Sybase-style external connection syntax.

· Oracle does not support standard external connection syntax or full external connection, which is a defect
Field 1 = Field 2 (+) (left join)
Field 1 (+) = Field 2 (Right join)

· Use of external connection statements
① You do not want to lose data rows in the primary table because of table connection.
① If A record exists in Table A but does not exist in Table B, use the not in (select query clause) syntax as A general practice,
The biggest disadvantage of using not in is slow because each data row is executed: select query clause
Use the following syntax:
Select TU_COMPANY .*
From TU_COMPANY left join TU_Comp_Agent on TU_COMPANY.ID = TU_Comp_Agent.CompCode
Where TU_Comp_Agent.Id is null

9. triggers
· From what I learned, ms SQL SERVER, only table triggers, and the trigger time is not rich enough
For example, when an insert task is triggered on a subaccount, the single or multiple inserts are not distinguished, and the pre-insertion or post-insertion triggers are not distinguished.
When multiple data entries are inserted, you need to use a cursor to process each inserted data.

· The triggers provided by Oracle include not only table-based triggers, but also other types, such as database-level triggers: Database startup and database Shutdown
For Table-level triggers, You can differentiate whether single insert or multiple insert operations are triggered before or after insert operations.
 
10. Table Data Replication
· Data replication in the database
· Ms SQL Server
Insert into copy table name select statement (copy table already exists)
Select field list into copy table name from table (copy table does not exist)
· Oracle
Insert into copy table name select statement (copy table already exists)
Create table copy table name as select statement (the copy table does not exist)
 
· Batch processing of text file transfer and transfer
· Ms SQL Server
BCP command line program
· Oracle
SQLLDR command line program
11. Update and delete multiple tables
An update statement cannot update multiple tables. Unless the trigger is used for implicit update, I mean to update the table to be updated based on the data of other tables.
General format:
· Ms SQL Server
Update
SET field 1 = B Table field expression,
Field 2 = B Table field expression
From B
WHERE logical expression
· Oracle
Update
SET field 1 = (select field expression from B WHERE ...),
Field 2 = (select field expression from B WHERE ...)
WHERE logical expression
 
From the above, I feel that oracle does not have A good ms SQL, mainly because: If A requires multiple field updates, the MS_ SQL statement is more concise

Do you know how the people who just learned the database do the above? They use cursors to process one by one

12. Both of the temporary tables used in stored procedures or functions provide this function.
Temporary tables, the main advantage is that the operation does not leave any trace, do not generate logs,
So fast
· MS SQL SERVER
Create table # TABLE Name (...) or SELECT field expression list INTO # TABLE name FROM
Add # before the table name. These temporary tables are only valid during a database connection session.

· Oracle
Create [Global] Temporary Table, plus [Global] is a Global Temporary Table (all database connection sessions are visible ),
Not private (valid during a database connection session)

13. Both of them provide their own desktop databases.
· Ms SQL SERVER Needless to say
Basically, the functions provided by the database of the desktop edition are not much different from those provided by the Server Edition.
Missing full-text search service features
· Oracle
Oracle Lite 8, I have seen that Oracle lite 8 has been used as a customs declaration system.
Supplement: Sybase provides the desktop version of Sybase SQL anywhere.
14. dynamically Execute SQL statements
· Ms SQL server 7.0 does not seem to have this function, ms SQL SERVER 2000 has this function.
Do you want to pass a table name in the parameters of the stored procedure or dynamically
It is difficult to generate an SQL statement. I have read the following example: you have solved all your previous problems.
Declare @ count int
Declare @ SQL nvarchar (200)
Set @ SQL = n' select count (*) from sysobjects'
Exec sp_executesql @ SQL, n' @ I int output', @ count output
 
 
· Oracle provides two methods to implement this function.
① Package DBMS_ SQL:
Open the cursor (open_cursor, this process is not available for non-query statements)
Analysis Statement (Parse)
Bind_variable)
Execute)
Close the cursor (close_cursor, this process is not available for non-query statements)
② Execute immediate ls_ SQL

15. database backup and recovery
Both provide strong database backup and recovery capabilities, and Oracle provides more means,
Orace claims that her database cannot be destroyed, and it is not a blow.
· MS SQL SERVER
① Database Import and export the DTS tool. If the data source and target are ms SQL SERVER, the structure of the database can be completely copied.
(Including tables, views, indexes, triggers, rules, defaults, stored procedures, user-defined functions, and table data)
② Database backup and recovery commands
Backup database
Restore database
The inverted data file can be compressed, which is different from Sybase.
In addition, this database backup and recovery method can be incremental and complete.
 
③ Attach the database
As long as the original data and log files of the database are not damaged
Command: SP_ATTACH_DB
In this case, there may be problems: for example, if you press the Logon account, instead of using the default Logon account sa,
Because the login account information is stored in the master database, you cannot log on using the original account,
So do some post-completion work

· Oracle
① Inverted import tool exp and imp Tool
How many inverted parameters are there?
② Cold backup, or offline backup, indicates that the database is stopped during Backup.
Back up all data files, log files, and control files
③ Hot backup or online backup
The database is in ARCHIVELOG mode. Note that ARCHIVELOG is used by default.
You can select full recovery, time point recovery, SCN point recovery, and CANCEL recovery with user intervention.
It can be said that his Hot Backup recovery function is very powerful
④ RMAN recovery manager. I am studying it.