Oracle Face Test ____oracle

The difference between Oracle and MySQL
1. is a large database and MySQL is a small and medium-sized database, Oracle market share of 40%,mysql only about 20%, while MySQL is open source and Oracle price is very high Oracle.
2. Oracle supports large concurrency, large traffic, and is the best tool for OLTP. (oltp[online transaction processing]) (olap[Data Warehouse processing has a large base of data based on data analysis market positioning])
3. The space used for installation is also very large, MySQL installed only after 152M and Oracle has 3G around, and the use of Oracle occupies a particularly large memory space and other machine performance.
4.Oracle also MySQL operation on some of the differences between "developer Contact"
① PRIMARY Key
MySQL generally uses the automatic growth type, when the table is created as long as the primary key of the specified table is auto increment, when the record is inserted, there is no need to specify the primary key value of the record, MySQL will automatically grow; Oracle does not have an automatic growth type, a sequence used by the primary key, The next value of the serial number is paid to the field when the record is inserted; The ORM framework is simply the native primary key generation policy.
Processing of ② single quotes
MySQL can be used in double quotes wrap strings, Oracle can only use single quotes wrap strings. A single quotation mark must be replaced before inserting and modifying a string: Replace all occurrences of one single quote with two single quotes.
Processing of SQL statements for ③ paging
MySQL processing pages of the SQL statement is relatively simple, with limit start position, the number of records; Oracle handles paging The SQL statement is more cumbersome. Each result set has only one rownum field that indicates its location, and can only be used rownum<100, not rownum>80
Processing of ④ long strings
Processing of long strings Oracle also has its own special place. The maximum operable string length for insert and update is less than 4,000 single-byte, and if you want to insert a longer string, consider using the CLOB type for the field to borrow the Dbms_lob package from Oracle. Be sure to do non-null and length judgments before inserting a change record, and you should warn about field values that are not empty and values that exceed the length field, and return to the last action.
Processing of ⑤ NULL characters
MySQL's Non-empty field also has empty content, Oracle defined a NON-EMPTY field does not allow empty content. The Oracle table structure is defined according to the NOT null of MySQL, and errors are generated when data is directed. Therefore, to guide the data to judge the null character, if null or empty characters, you need to change it to a space string.
Fuzzy comparison of ⑥ strings
MySQL in the field name like '% string% ', Oracle can also use the field name "%"% string "but this method can not use the index, the speed is not fast." Index cannot be used at the beginning of "like '%" without the use of an index
⑦oracle implements most of the functions in Ansii SQL, such as the isolation level of the transaction, propagation characteristics, etc. and MySQL is still relatively weak in this respect.
Two The difference between stored procedures and functions "emphasis"
1. You can understand that a function is a stored procedure, is a precompiled "block statement every run will compile the stored procedure block compile multiple times more efficient"
Plsql Block statement
Begin
End
Stored Procedure block
Create Procedure Prg_add ()
As
Begin
End;
2. A function can have no parameters, but must need a return value, stored procedures can have no parameters, do not need to return the value
3. Function return returns the value does not return the parameter pattern, the stored procedure returns the value through the out parameter, and if more than one argument needs to be returned, the stored procedure "function Oracle can use in and out MySQL cannot use out"
4. Only functions can be called in SQL data manipulation (DML) statements and no stored procedures can be invoked
Three Oracle Import and Export methods
Using the Oracle Tools EXP/IMP use PLSQL related tools plsql Developer Text Import Export
Method 1. Import/export is binary data, 2.plsql Import/export is a text file of SQL statements
ODBC Import Export System related
Four. What kinds of documents are in Oracle?
Data file (General suffix is. dbf or. ora), log file (suffix name. log), control file (suffix named. ctl)
General \ORADATA\ORCL under the installation directory
Five There are several ways to optimize Oracle databases (emphasis)
Database performance is the most critical factor in Io, because the operation of memory is fast, but read and write disk is slow, the most critical problem is to reduce disk IO, personal understanding should be divided into physical and logical optimization, physical refers to the Oracle product itself, some optimization, Logical optimization refers to application-level optimization
Some principles of physical optimization:
1. Oracle's operating Environment (network, hardware, etc.)
2). Using the appropriate optimizer
3). Reasonable configuration of Oracle instance parameters
4. Establish the appropriate index (reduce IO)
5. Separate the index data from the table data on different table spaces (reduce IO conflicts)
6. Create table partitions, store data on separate partitions (in exchange for time and less IO)


Logically optimized (development-related):
1. The table can be logically divided, such as the China Mobile User table, can be divided into 10 tables according to the phone mantissa, which will have a certain effect on performance
2. SQL statements use placeholder statements, and the development time must be written in accordance with the provisions of SQL statements (such as all uppercase, all lowercase, etc.) Oracle parsing statements will be placed in the shared pool
such as: SELECT * from EMP where name=? This statement will only have one in the shared pool, and if it is a string, there are different statements based on different names, so the placeholder is more efficient
3. The database is not only a place to store data, but also a programming place, some time-consuming operations, through the stored procedures, and so on in less user circumstances, and thus stagger the peak time used by the system, improve database performance
4. Try not to use *, such as SELECT * from EMP, because to be converted to a specific column name is to look up the data dictionary, more time-consuming
5). Select a valid table name
For a multiple-table join query, it is possible that Oracle's optimizer will not be optimized to this extent, and the multiple table queries in Oracle are based on the from right to left data, so the best table on the right (that is, the underlying table) selects less data tables, so the sorting is faster, If there is a link table (many-to-many multiple intermediate tables), the link table will be placed at the far right as the underlying table, and Oracle will automatically optimize by default, but if the optimizer is configured, it may not be automatically optimized, so it's usually best to write SQL in this way
6). Where-clause rule
When the WHERE clause in Oracle is handled from right to left, the connection between tables is written before other conditions, the conditions that can filter out very much data are placed at the end of the where, and the columns of the!= symbol comparison do not use the index, and the columns are computed (such as uppercase, etc.) without using the index (need to establish a function), The optimizer does not use the index for IS null, is not NULL
7). Use Exits not Exits instead of in, "Do not use index"
8. Reasonable use of transactions, the rational establishment of transaction isolation
Database data operations compared to consume database resources, try to use batch processing to reduce the number of transaction operations




Six How Oracle is paginated.
Oracle uses rownum for paging, which is the most efficient paging method, Hibernate is also used rownum for Oralce paging
SELECT * FROM
(select RowNum r,a from TabName where rownum <= 20)
where r > 10
Seven Oralce how to store files and which files to store.


Oracle can store Clob, NCLOB, blobs, bfile
Clob variable-length character data, which is the text-type data type mentioned in other databases
Nclob data of a mutable character type, but it stores character data for the Unicode character set
Blob variable-length binary data
Bfile variable binary data stored outside the database


Eight Oralce explains the differences between cold and hot backups, as well as their respective advantages.
Cold backup occurs when a database has been shut down properly, copying a key file to a different location
Hot backup is the way to back up data in an archived way while the database is running
Advantages and disadvantages of Lengbei:
1). is a very fast backup method (just copy the file)
2). Easy to archive (simple copy)
3). Easy to recover to a point in time (just copy the file back)
4). Can be combined with the archiving method to restore the "latest state" of the database.
5). Low maintenance, high safety.
Insufficient Cold backup:
1). When used alone, it can only provide a recovery at a point in time.
2). In the process of implementing a backup, the database must be backed up without any other work. In other words, the database must be closed during a cold backup.
3). If disk space is limited, it can only be copied to other external storage devices such as tape, which can be slow.
4). You cannot restore by table or by user.


Advantages and disadvantages of hot standby
1). Can be backed up at the table space or data file level for short backup time.
2). The database is still available for backup.
3). Can reach a second level recovery (revert to a point in time).
4). Almost all database entities can be recovered.
5). Recovery is quick and, in most cases, restored when the database is still working.
The shortage of hot backup is:
1). There is no mistake, otherwise the consequence is serious.
2). If a hot backup is unsuccessful, the resulting results are not available for point-in-time recovery.
3). Because it is difficult to maintain, so be particularly careful, do not allow "failure to end".


Nine. Explain the difference between data block, extent and segment.
Data block blocks, which are the smallest logical units of Oracle, and typically Oracle reads and writes from disk to block
The extent area is made up of several adjacent blocks.
The segment section is composed of a set of areas
Tablespace table space, where data is logically stored in a database, a tablespace can contain multiple data files
10. Compare truncate and delete commands.
1. Truncate and delete all can delete the data entity, Truncate operation is not logged to the rollback log, so the operation is faster, but at the same time this data can not be restored
2). Delete operation does not make space for tablespace
3). Truncate cannot be deleted on view
4. Truncate is the data definition language (DDL), and delete is the data Manipulation language (DML)
Eleven. Explains what a deadlock is and how to resolve a deadlock in Oracle.
In short, there is a lock without unlocking, may be using the lock did not commit or rollback TRANSACTION, if it is a table-level lock can not operate the table, the client is in the state, if it is row-level lock can not operate locked line
Solution:
1). Find out which table is locked
Select B.owner,b.object_name,a.session_id,a.locked_mode
From V$locked_object a,dba_objects b
where b.object_id = a.object_id;

Select B.username,b.sid,b.serial#,logon_time
From V$locked_object a,v$session b
where a.session_id = B.sid order by B.logon_time;
2. Kill the session in the process
Alter system kill session "sid,serial#";
Twelve. Briefly describe the use of DML, DDL, DCL in Oracle
DML data manipulation languages, such as SELECT, Update, Delete,insert
DDL data definition language, such as CREATE table, drop table, and so on
DCL Data Control language, such as commit, rollback, grant, invoke, etc.
13. Talk about the frequently used functions in Oracle
length, lower lowercase, upper uppercase, to_date conversion date, To_char conversion character
Ltrim go to the left space, RTrim to the right space, substr take strings, add_month add or subtract the month, To_number converted to numbers
14. How do I create a stored procedure, and how does a cursor work in a stored procedure?
Attached: General format of stored procedures, cursor use reference questions
1. You can use cursors to perform multiple unrelated operations. If you want a result set to be generated, do a variety of unrelated data operations on the data in the result set
2. Use cursors to provide readability of scripts
3. Using cursors, you can create command strings, use cursors to transfer table names, or transfer variables to parameters in order to establish a command string that can be executed.
But the individual thinks the cursor operation efficiency is not very high, and uses must be specially careful, after using, must close in time
Stored procedure Advantages and disadvantages:
Advantages:
1. Stored procedures enhance the functionality and flexibility of the SQL language. Stored procedures can be written with flow control statements, with strong flexibility to complete complex judgments and complex operations.
2. Ensures data security and integrity.
3. Stored procedures enable users without permission to access the database indirectly under control, thereby ensuring data security.
Stored procedures allow related actions to occur together to maintain the integrity of the database.
3. Before running the stored procedure, the database has been analyzed by syntax and syntax, and the optimal execution scheme is given. This compiled process can greatly improve the performance of SQL statements. Since most of the work on executing the SQL statement has been completed, the stored procedure can be executed at a very fast rate.
4. Can reduce network traffic, do not need to pass the network to send a lot of SQL statements to the database server
5. Put the operational procedures that embody enterprise rules into the database server for centralized control
Change the stored procedure in the server when the enterprise rule changes, without modifying any applications. The characteristic of enterprise rule is to change frequently, if the operation that embodies enterprise rule is put into application, then when enterprise rule changes, need to modify the application workload very much (modify, publish and install application). If you put an operation that embodies an enterprise rule into a stored procedure, you can modify the stored procedure as long as the enterprise rules change, and the application needs no change.
Disadvantages:
1. Poor portability
2. Occupy the server side of the resources, the server caused by a lot of pressure
3. Readability and maintainability are not good


xv. ROWID, definition of rownum
1. rowID and rownum are all virtual columns
2. rowID is the physical address used to locate the physical storage location of specific data in Oracle
3. RowNum is the SQL output order, from the following examples can see the difference.
16. Oracle stored procedures, cursors and functions differ
Cursors are similar to pointers, and cursors can perform multiple unrelated operations. If you want a result set to be generated, do a variety of unrelated data operations on the data in the result set
A function can understand that a function is a stored procedure; a function can have no parameters, but it must need a return value, the stored procedure can have no parameters, no return value, both can return a value through the out parameter, and it is recommended that you use a stored procedure if you need to return more than one parameter ; only functions can be called in SQL data manipulation statements and no stored procedures can be invoked
17. To delete duplicate records in a table using Oracle Pseudo-columns:
Delete table T where t.rowid!= (select Max (t1.rowid) from table1 t1 where t1.name=t.name)


17. How to design a database
Using the Powerdisine tool, it is generally possible to meet the third normal form. Logical import and export of exp and IMP database


What are the advantages and disadvantages of indexing?
Index Benefits
Edit
1. Significantly speed up the retrieval of data;
2. Create a unique index to ensure the uniqueness of each row of data in a database table;
3. Accelerate the connection between table and table;
4. When you use grouping and sorting clauses for data retrieval, you can significantly reduce the time to group and sort in a query.
Index disadvantage
Edit
1. The index needs to occupy the physical space.
2. When the data in the table to add, delete and modify the time, the index will also be dynamic maintenance, reduce the data maintenance speed.
Before you create an index, you must determine which columns to use and the type of index you want to create. For more information, see:
* Indexed Columns
* Index Type
Index type
Edit
Depending on the functionality of your database, you can create four indexes in the Database Designer: Normal index, unique index, primary key index, and clustered index. For more information about the indexing capabilities supported by the database, see your database documentation.
Normal index
This is the most basic type of index, and it has no uniqueness, such as restrictions. Normal indexes can be created in several ways:
Create indexes, such as the name of the CREATE INDEX < index > on tablename (a list of columns);
Modify a table, such as ALTER TABLE TableName ADD index [name of index] (list of columns);
Specify the index when creating the table, such as CREATE TABLE TableName ([...], index [first name] (List of columns));
Unique index
A unique index is one that does not allow any two rows to have the same index value.
Most databases do not allow a newly created unique index to be saved with a table when duplicate key values exist in existing data. The database may also prevent the addition of new data that will create duplicate key values in the table. For example, if a unique index is created on the employee's last name (lname) in the employee table, no two employees will have the same surname.
Several ways to create unique indexes:
Create an index, such as the name of the Create UNIQUE Index < index > on tablename (a list of columns);
Modify a table, such as ALTER TABLE TableName ADD UNIQUE [name of index] (list of columns); ；
Specify the index when creating the table, such as CREATE TABLE TableName ([...], UNIQUE [name of index] (List of columns));
Primary key Index
Database tables often have a column or combination of columns whose values uniquely identify each row in the table. This column is called the primary key of the table.
Defining a primary key for a table in a database diagram automatically creates a primary key index, which is a specific type of unique index. The index requires that each value in the primary key is unique. When a primary key index is used in a query, it also allows for fast access to the data. For more information about primary keys, see Defining primary keys.
Tip Although a unique index helps locate information, it is recommended that you use a primary key index instead for the best performance results. For more information about these constraints, see PRIMARY KEY constraints and unique constraints.
Clustered index (also called clustered index):
In a clustered index, the physical order of the rows in the table is the same as the logical (indexed) Order of the key values. A table can contain only one clustered index. If an index is not a clustered index, the physical order of the rows in the table does not match the logical order of the key values. Clustered indexes typically provide faster data access than nonclustered indexes.
28 talking about Oracle's connection (proprietary and shared)
Shares


18 What is the three paradigm of database design
19 What is the isolation level of data to solve the problem?
20 What is ER drawing An example describes ER
21 What is the difference between a normal view and a materialized view
22 Common functions of Oracle
23 The difference between NVL and NVL2 and decode
---23


24 Design the following table
Students participate in activities each activity has an organizer, after each activity completes some alumni to publish the article
1 Check out the students who participated in the most activities this year
2 Query all organizers of today's published articles
3 The ER diagram of the design of the table
25 Common commands for Oracle operations
26 Say what is Cartesian product, inner connection, left outer join, right outer connection, full connection and example
27 Write a stored procedure to pass in a time to delete articles published by all students who are less than the incoming time