SELECT * FROM (
Select T.*,rownum from table1 t where condition)
where Rownum> (pangeNow-1) *pagesize and rownum<= (Pagenow) *pagesize
Answer: A transaction is a mechanism that ensures that multiple SQL statements are treated as a single unit of work. A transaction has the following effects:
consistency : Simultaneous queries and updates do not conflict with each other, and other users will not see data that has changed but not yet committed.
recoverability: Once the system fails, the database automatically recovers the unfinished transaction completely.
Answer: A trigger is a procedure that can be started by an event that exists in the database server. His role: to achieve more complex integrity requirements by implementing complex constraints that cannot be accomplished by general constraints. There is no strict qualification for using triggers, and triggers can be used as long as the user wants to ensure the integrity of the database by completing constraints that the general definition constraint cannot accomplish without human involvement.
Since triggers are primarily used to guarantee the integrity of the database, to create a trigger, first make sure that the trigger belongs to the one (Dml,instead Of,system) because they each have a purpose, and the second is to determine what data the trigger will design later. Commit cannot be used in triggers.
Answer: The difference between a relational database system file system is:
first , the overall data of a relational database is structured and described by a relational data model, which is fundamentally different from the file system. (data models include: Data structures, operations, and integrity constraints)
secondly , the relational database system has high sharing, the redundancy is low can face the whole system, and the file system has the limitation of application scope, it is not easy to expand.
thirdly , the relational database system adopts the two-level mapping mechanism to ensure the high independence of data, so that the program writing and data are highly independent. This is a file system that cannot be achieved, it can only be targeted to a specific application. (Level Two mapping: External mode/pattern mapping to ensure logical independence and internal mode/pattern mapping to ensure physical independence.) External mode: User mode, is a description of the logical structure characteristics of the local data of the database user. Schema: A description of the logical structure characteristics of the database's entire data. Internal mode: The description of the final physical storage structure of the data. ）
Fourth , the relational database system is managed by a unified DBMS to provide data such as security protection, concurrency control, integrity checking, and database recovery services.
Answer: A trigger: A process stored in a database that is implicitly fired when the table is modified (added, deleted, changed). Stored procedures: is a collection of database language SQL, also stored in the database, but he is started by other applications to run or can also run directly.
006 What is transactional consistency? Select a familiar database to implement a transaction, such as a credit card withdrawal. Answer:Transactional Consistency : Is the embodiment of the atomicity of the transaction, the corresponding database operation of the firm either succeeds or fails without a third case. A transaction cannot affect the consistency state of the database data, whether the commit succeeds or not.
Transactions : A user-defined sequence of database operations that either completes successfully or does not all, is an indivisible whole. The SQL statement that defines the transaction is: BEGIN transaction,commit,rollback.
atomicity of a transaction: the database operations that the office contains are either done or not.
isolation of transactions: Transaction operations on data cannot be affected by other transactions.
The continuity of a transaction: that is, the effect of the transaction on the data is permanent.
For credit card Withdrawals this is a matter of ensuring that the ' withdraw to cash ' and ' Card account balance ' changes are successful or unsuccessful at the same time.
BEGIN TRANSACTION
Read A's account balance balance
Balance=balance-amount Transfer Amount
IF (balance<0)
Then ROLLBACK
ELSE BEGIN
Write back the new balance of a
Read B's account balance BALANCEB
Balanceb=balanceb+amount Transfer Amount
Write the New Balance of B back
COMMIT
END IF
END
Answer: There are two ways to implement an index : to create specific indexes on some fields of a table, such as on the Oracle:create index name on the table name (field name), to establish a primary key constraint or a unique constraint for a field when the table is created, and the system will automatically index it.
Index principle : Based on the indexed field to establish the Index table, the field value and the corresponding record of the physical address, in order to search the index table according to the field values to the physical address of the direct access to the record.
Although the index increases the query speed, it occupies a certain amount of system storage capacity and system processing time, which needs to be analyzed according to the actual situation.
The types of (Oracle) indexes are:B-Tree index , bitmap index , function index , and so on.
Answer: View is a multi-angled perspective of table-level data and is suitable for environments where query security and flexibility are required.
009 What are the advantages of accessing a database using stored procedures rather than directly using SQL statements?Answer: Stored procedures are precompiled, do not need to compile, execute faster, the stored procedure encapsulates a batch of SQL statements, easy to maintain the integrity and consistency of the data, you can implement code reuse.
010 The concept of rollback in Oracle? What is the effect of the rollback segment. rollback is the database data state before a transaction is committed before the database data is restored to the transaction modification.
The rollback section is the basis for rollback, which records the data before the transaction operation database or the operation that corresponds to the previous operation, depending on the previous operation. For example, if the previous transaction operation is update then the rollback segment stores the previous data of the update, and if the transaction is a delete operation then the corresponding insert operation statement is stored, whereas if the transaction operation is insert then the record corresponding is the delete operation.
Logical Architecture: block, Zone, segment, Tablespace
Physical Architecture: Table space, three large files
Software Architecture: SGA, background process.
A: The system global Zone includes: shared pool , redo log buffer , data cache area , large pool ,Java pool .
013 Brief description of how many steps Oracle starts and shuts down?Start: Launches the instance, mounts the database data, and opens the database. Close: Close the database, unload the database data, and close the instance.
What types of files are available in the 014 Oracle database?Data files, control files, log files, parameter files
015 Where do you put the tablespace information you created in Oracle?stored in a data dictionary, the data dictionary content corresponds to the system table space of the systems tablespace.
016 What are the basic data types for Oracle?Char () stores the fixed-length character, which can be defined without specifying a length for him, but if inserting a value into it, it will be wrong; VARCHAR2 () must specify a length when storing the variable-length character definition, date-stored time-of-day; number () numeric type, including integer, float, etc. clob () Large-capacity string; blob () binary object
017 Several physical files of the database?1) Data file
2) Control file
3) Log file
The control file contains instance information (instance name creation time, etc.), data file and log file information, System change code (SCN) recorded during system runtime, checkpoint information and current status information of the archive, etc. When the database is loaded, the data files and log files are loaded and the database is opened after the control file is first read to obtain the physical structure information about the database.
What is the difference between 019 truncate and delete?1. Truncate on various tables, large and small, are very fast. If there is a rollback command delete will be revoked, and truncate will not be revoked.
2. Truncate is a DDL language and delete is a DML statement, and as with all other DDL languages, he will be implicitly committed and cannot use the rollback command with truncate.
3. Truncate will reset the high-level line and all indexes. When a full table and index are fully browsed, the table after the truncate operation is much faster than the table after the delete operation.
4. Truncate cannot trigger trigger, delete triggers trigger.
5. You cannot grant anyone permission to empty another's table.
6. When the table is emptied, the index of the table and table is reset to the initial size, and delete does not.
7. Cannot empty the parent table.
Two ways to expand:
1. Add data file Alter tablespace tablespace_name add datafile ' XXMB
2. Extended data file size ALTER DATABASE datafile ' Resize NEWMB
1. Dictionary Management mode extent management dictionary; default mode
2. Local Management method Extent management Local[autoallocate/uniform xxmb]< recommended >;
1. A partitioned table has one or more partitions, each of which is partitioned by using a range partition, a hash partition, or a combination of rows in a partitioned partition
2. Each partition in a partitioned table is a segment that can be in a different table space
3. Very useful for large table partitions that can be queried or manipulated using several processes at the same time
An index is a list of keywords for several rows of data that, when queried, can quickly locate the data block of the record being accessed by the keywords in the index, thus significantly reducing the number of I/O to the data block being read, thus significantly improving performance.
024 How does the application of stored procedures have both input and output?Create procedure pro_name (xxxx in/out type; yyyy in/out/inout type;)
Is/as zzzz type;
Begin
Sqlpro;
Exception exceptionxxxxx;
Commit
End
There are three types of exceptions in Oracle. Predefined exception non-predefined exception user-defined exception the second non-predefined exception is associated with a specific Oracle error.
026 Oracle's optimization strategy (same for MySQL)Optimization strategies generally include: memory optimization, operating system optimization, data storage optimization, network optimization and other methods
Specific to different databases involves adjusting different database configuration files, different operating system parameters, network parameters, and so on, different databases.
In general, querying data through an index is faster than a full table scan. But we must also pay attention to its cost. Indexes require space to store, and they need to be maintained regularly, and the index itself is modified whenever a record is added to a table or the index column is modified. This means that the insert,delete,update of each record will pay 4, 5 disk I/Os for this. Because indexes require additional storage space and processing, those unnecessary indexes can slow query response time. Using index queries does not necessarily improve query performance, index range queries ( INDEX RANGE SCAN is available in two situations:
Based on a range of searches, the general query returns a result set less than 30% of the number of records in the table should be adopted;
Index based on non-unique index is to improve the performance of the query exists, if the index in the query does not improve performance, can only be said to use the wrong index, or the occasion is different
A bound variable is a variable that is used in an SQL statement, changing the value of a variable to change the execution result of the SQL statement.
Advantage : By using bound variables, you can reduce the parsing of SQL statements and reduce the resources that the database engine consumes on SQL statement parsing. Improved programming efficiency and reliability. Reducing the number of times a database is accessed can actually reduce the workload of Oracle.
disadvantage : often need to use dynamic SQL notation, due to different parameters, SQL may be executed in different efficiency;
Binding variables are relative text variables, the so-called text variables refers to the SQL directly write query conditions, such SQL in different conditions need to be resolved repeatedly, binding variable refers to the use of variables instead of direct writing conditions, query bind value is passed at run time, and then binding execution.
The Advantage is to reduce hard parsing, reduce CPU contention, and save Shared_pool
disadvantage is that it is difficult to use Histogram,sql optimization
tablespace: A database is divided into one or more logical units, which becomes a tablespace; each table space may contain one or more Segment;
Segments: segment refers to the space allocated for a particular logical storage structure in tablespace. Each segment is comprised of one or more extent. Includes data segments, index segments, rollback segments, and temporary segments.
extents: A extent consists of a series of contiguous Oracle blocks. Oracle allocates space for segment through extent.
Data Blocks: The smallest I/O storage unit of the Oracle database, one data block corresponding to one or more operating system blocks assigned to the data file.
When table is created, a data segment is created by default, and each data segment contains the number of extents specified by min extents, and the storage parameters for each extent data table space are assigned a certain number of blocks
The rollback segment is used to hold the image before the data modification, which is used to generate read-consistent database information, to be used in database recovery and rollback. A transaction can use only one rollback segment.
transaction rollback : When a transaction modifies data in a table, the value before the data modification (that is, the former image) is stored in the rollback segment, and when the user rolls back the transaction (ROLLBACK), Oracle uses the pre-data image in the rollback segment to restore the modified data to its original value.
Transaction Recovery : When the transaction is being processed, the routine fails and the rollback segment information is saved in the Undo Tablespace, and Oracle will use rollback to recover uncommitted data the next time the database is opened.
Read Consistency : When a session is modifying data, other sessions will not see changes that were not committed by that session. When a statement is executing, the statement does not see uncommitted modifications from the beginning of the statement (statement-level read consistency) when Oracle executes a SELECT statement, Oracle follows the current system change number (NUMBER-SCN) To ensure that any uncommitted changes prior to the current SCN are not handled by the statement. As you can imagine: When a long query is executing, if another session changes a block of data that the query is querying, Oracle constructs a read-consistent view using the pre-data image of the rollback segment
log file records all modifications to database data, primarily to protect the database against failures, and to use when recovering data. Its characteristics are as follows:
1. Each database consists of at least two log filegroups. Each log file group contains at least two log file members.
2. The log file group is written in a circular manner.
3. Each log file member corresponds to a physical file.
Record database transactions to maximize data consistency and security
Redo log Files : Contains a record of the changes made to the database so that if a failure can enable data recovery, a database requires at least two redo log files
archived log Files : An offline copy of the redo log files that may be necessary to recover from media failure.
system Global Area (SGA): A set of shared buffer stores that Oracle assigns to an instance to hold database data and control information for the management and operation of database data. The SGA mainly includes:
1. Shared pool : The data used to store the most recently executed SQL statement and the most recently used data dictionary.
2. data buffers (database buffer cache): used to store data that was recently read and written from a data file.
3. Redo the log buffers (redo log buffer): Used to record the operation of the service or background process against the database. In addition, there are two optional memory structures in the SGA:
4. java pool: used to store Java code.
5. Large pool: Used to store large memory structures that are not directly related to SQL. Backup, recovery use.
Data Write process (DBWR): Responsible for writing changed data from the database buffer cache to the data file
Log Write process (LGWR): Writes changes in the redo log buffer to the online redo log file
system Monitoring (Smon): Check database consistency If necessary also initiates recovery of the database when the database is opened
process Monitoring (Pmon): Responsible for cleaning up resources when an Oracle process fails
checkpoint process (CKPT): Updates the database state information in the control files and data files whenever changes in the buffer cache are permanently recorded in the database.
Archive Process (ARCH): Backs up or archives a full log group at each log switch
recovery Process (RECO): To ensure the consistency of distributed transactions, in a distributed transaction, either commit at the same time, or simultaneously rollback
logical Backup: Exp/imp The logical backup physical backup of the specified table:
Hot backup : Alter tablespace begin/end backup;
Cold backup : Offline backup (Database shutdown)
Rman Backup
Full Backup/incremental Backup (cumulative/differential) physical backup
Physical backup is the most important way to backup. Used to ensure that the database is restored with minimal database loss or no data loss.
Cold Physics
Cold physical Backup provides the simplest and most straightforward way to protect a database from physical damage. Recommended for use in the following scenarios.
For a database that already has a large maximum data size, the database can be closed at night, and a cold physical backup is applied at this time. For the database server to be upgraded, such as replacing the hard disk, you need to back up the database information, and restore the data information on the new hard disk, we recommend the use of cold physical backup.
Thermal Physics
The main point is that the backup process occurs when the database is open and the user can use it. The situations in which you need to perform a hot physical backup are:
Because the nature of the database requires uninterrupted work, only hot physical backups can be used at this time. Because the backup requirements are too long, and the database is only closed for a short period of time.
logical Backup (EXP/IMP)
Logical backups are used to implement recovery of database objects. But not based on a point-in-time, fully recoverable backup strategy. Can only be used as a supplement to online and offline backups.
Full Logical backup
A full logical backup is a format file that exports an entire database to a database that can be ported between different database versions, operating systems, and hardware platforms.
To specify a logical backup of a table
The Backup utility allows you to back up the specified database tables, which avoids the time and financial waste of a full logical backup.
A transaction is a series of operations performed as a logical unit, and a logical work cell must have four properties, called ACID (atomicity, consistency, isolation, and persistence) properties, in order to be a transaction:
atomicity : A transaction must be an atomic unit of work; for its data to be modified, it is either all executed or not executed.
consistency : When a transaction is complete, it must keep all data in a consistent state. In a related database, all rules must be applied to transaction modifications to maintain the integrity of all data. At the end of the transaction, all internal data structures, such as B-tree indexes or doubly linked lists, must be correct.
Isolation : Modifications made by concurrent transactions must be isolated from modifications made by any other concurrent transaction. The state in which the data is located when the transaction is viewing the data, either when another concurrent transaction modifies its state or after another transaction modifies it, and the transaction does not view the data in the middle state. This is called serializable because it is able to reload the starting data and replay a series of transactions so that the state at the end of the data is the same state as the original transaction execution.
Persistence : After a transaction completes, its effect on the system is permanent. This modification will persist even if a system failure occurs.
conditions for deadlocks to occur :
1, resources can not be shared, need to be used only by one process or thread
2. Request and hold, the resource that has been locked has been kept from being released
3, do not deprive, self-sufficiency application to the resources can not be deprived by others
4. Cyclic waiting
If you want to prevent deadlocks, destroy one of the four conditions above.
The way to prevent deadlocks is to avoid situations where the deadlock condition is met, and for this reason the user should follow these guidelines.
(1) Try to avoid concurrent execution of statements that involve modifying data.
(2) Each transaction is required to lock all data to be used at once, otherwise it will not be executed.
(3) Pre-defined a blockade order, all transactions must be in this order to the data execution blockade. As with different procedures, the order in which updates are performed on objects within a transaction should be kept as consistent as possible.
(4) The execution time of each transaction is not too long, and it is possible to consider splitting the transaction into several small transactions if the business allows it to be executed. "For example, to scatter complex multi-table queries into multiple single-table queries"
(5) Data storage space discretization method. The data storage space discretization method refers to the use of various means to spread the data logically in a table into a number of discrete spaces, in order to improve the access performance of the table. Mainly by the large table by the row or column decomposition into a number of small tables, or according to different user group decomposition two methods of implementation. This approach is similar to dispersing "data hotspots", but it is true that deadlocks do not occur too often if the data is not accessed too often.
(6) or similar (1), such as having an UPDATE statement that modifies hundreds of records, we can modify each of the 10 update statements, or simply one UPDATE statement per record.
(7) separate frequently updated databases and query databases
When the Oracle server starts, each file that starts the instance and then reads the database, of course, also includes the control file. That is, read at the second step of the database server startup.
038 The concepts of unique indexes and bitmap indexes, which are categorized by Oracle indexes.The Oracle index has a B-tree index, a bitmap index, a function index, a cluster index , and so on.
A unique index is also a type of B-tree index that requires the indexed field values not to be duplicated. Created using the B-tree algorithm at the time of creation.
Instead of storing the index segment as a unique index (indexed field value, record rowID), the bitmap index creates a bitmap for each unique field value, using bits in the bitmap to correspond to the rowid of a record. The bits to ROWID are mapped by the to.
A table space can contain one or more data files. Tablespaces augment the table space by increasing or extending the data file, and the size of the table space is the size of the data file that makes up the tablespace. A datafile can only belong to one table space; A tablespace can have one or more datafile, each datafile only in one tablespace, the data in the table, through the hash algorithm distributed in Tablespace, Tablespace is a logical concept, and datafile physically stores objects of the database.
"Interview", "Oracle FAQ Summary" "01"
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
