Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "what are the basic interview questions for Oracle". In the operation of actual cases, many people will encounter such a dilemma. Next, let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

First, talk about the function of Oracle table partition

The common partitions of Oracle can be divided into list partition, range partition, hash partition (hash partition), combined partition and interval partition.

1)。 Enhanced availability: if one partition of the table cannot be used due to a system failure, the rest of the good partitions of the table can still be used

2)。 Reduce shutdown time: if the system failure affects only part of the partition of the table, then only that part of the partition needs to be repaired, which may take less time than the entire large table to repair.

3)。 Easy to maintain: if tables need to be built, it is much easier to manage each public area than to manage a single large table

4)。 Balanced Ihop O: different partitions of the table can be allocated to different disks to balance Imax O to improve performance.

5)。 Improve performance: queries, additions, modifications and other operations on large tables can be decomposed into different partitions of the table to be executed in parallel, making it faster.

6)。 The partition is transparent to the user and the end user does not feel the existence of the partition.

2. Briefly describe the use of dml, ddl and dcl in oracle

Dml data manipulation languages such as select, update, delete,insert

Ddl data definition languages, such as create table, drop table, etc.

Dcl data control languages, such as commit, rollback, grant, invoke, etc.

Third, talk about the frequently used functions in oracle

Length length, lower lowercase, upper uppercase, to_date conversion date, to_char conversion character

Ltrim to the left space, rtrim to the right space, substr to the string, add_month to add or subtract months, to_number to numbers

What symbolic links are used for strings in the process of Oracle development?

Used in Oracle | | this symbolic connection string, such as' abc' | |'d'

5. How is Oracle paginated?

Rownum is used for paging in Oracle, which is the most efficient paging method. Hibernate also uses rownum for oralce paging.

Select * from

(select rownum RJ a from tabName where rownum 10

How does Oralce store files and which files can be stored?

Feng GE Oracle tutorial hints, Oracle can store clob, nclob, blob, bfile

Clob variable length character data, that is, text data types mentioned in other databases

Nclob variable character type data, but it stores the character data of the Unicode character set

Blob variable length binary data

Variable binary data stored outside the Bfile database

7. Index columns are used in Oracle. Where conditional queries, grouping, sorting, and aggregation functions are used for the columns. Which indexes are used?

Indexes are used, and it is worth noting that composite indexes (such as those established on columns An and B) may have different situations

Talk about the difference between truncate and delete commands

1)。 Both Truncate and delete can delete the data entity, and the operation of truncate is not recorded in the rollback log, so the operation speed is faster, but at the same time the data cannot be recovered.

2)。 The Delete operation does not make room for the table space.

3)。 Truncate cannot delete views, etc.

4)。 Truncate is a data definition language (DDL), while delete is a data manipulation language (DML).

Explain what deadlocks are and how to solve deadlocks in Oracle.

In short, the lock is added but not unlocked. It may be that the transaction is not committed or rolled back using the lock. If it is a table-level lock, the table cannot be manipulated, the client is in a waiting state, and if it is a row-level lock, the locked row cannot be manipulated. The solution:

1)。 Find the locked table

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 minute vanguard session b

Where a.session_id = b.sid order by b.logon_time

2)。 Kill a conversation in the process

Alter system kill session "sid,serial#"

There are several kinds of Oracle locks.

1. Row shared lock (ROW SHARE)

two。 Row exclusive lock (ROW EXCLUSIVE)

3. Shared lock (SHARE)

4. Shared row exclusive lock (SHARE ROW EXCLUSIVE)

5. Exclusive lock (EXCLUSIVE)

How to create an index, the principles of index use, and what are the advantages and disadvantages

Create a standard index:

CREATE INDEX index name ON table name (column name) TABLESPACE table space name

Create a unique index:

CREATE unique INDEX index name ON table name (column name) TABLESPACE table space name

Create a composite index:

CREATE INDEX index name ON table name (column name 1, column 2) TABLESPACE tablespace name

Create a reverse key index:

CREATE INDEX index name ON table name (column name) reverse TABLESPACE table space name

Principles for the use of indexes:

NOT NULL constraint is recommended for index field.

Tables that are often connected to other tables should be indexed on the join field

Fields that often appear in the Where clause and are highly filtered, especially those with large tables, should be indexed.

Keywords with high selectivity should be indexed

Keywords with low selectivity, but when the value distribution of the data is very different, the index can still be used to improve efficiency when the selective data is relatively small.

The establishment of a composite index requires careful analysis; consider replacing it with a single-field index as far as possible:

A. correctly select the first field in the composite index, which is generally selective and commonly used in the where clause.

B. Several fields of the compound index often appear in the Where clause in the form of AND at the same time, otherwise the compound index can be established; otherwise, the single field index

C. If the fields contained in the composite index often appear separately in the Where clause, they are decomposed into multiple single-field indexes

D. if the composite index contains more than 3 fields, then carefully consider its necessity and consider reducing the number of composite fields

E. if you have both a single-field index and a composite index on these fields, you can generally delete the composite index

Do not build too many indexes for tables that are frequently DML

Do not use columns that change frequently as index columns

Advantages and disadvantages of indexes:

A little bit:

1. Create a uniqueness index to ensure the uniqueness of each row of data in the database table

two。 Greatly accelerate the speed of data retrieval, which is also the main reason for creating an index

3. The connection between the accelerometer and the table is particularly meaningful in achieving the referential integrity of the data.

4. When using grouping and sorting clauses for data retrieval, you can also significantly reduce the time of grouping and sorting in a query.

Disadvantages:

1. The index is created on the table, not on the view

two。 It takes time to create and maintain an index, which increases as the amount of data increases

3. The index needs to occupy the physical space, in addition to the data table occupies the data space, each index also takes up a certain amount of physical space, if you want to establish a clustered index, then the space required will be more.

4. When the data in the table is added, deleted and modified, the index should also be maintained dynamically, which reduces the maintenance speed of the data.

How to create a view, the benefits of the view, can the view control permissions?

Create view view name as select column name [alias]... From table [unio [all] select... ]]

Benefits:

1. You can simply understand the view as a sql query statement. The biggest advantage of the view is that it does not take up system space.

two。 Some systems with high security will not publish the table structure of the system, and may use views to overworry or rename some sensitive information and publish the structure.

3. Simplify query

If you can control the permissions, you need to give the grant of the view to the user when using it.

How to create a trigger, the definition of the trigger, how to define the cursor of the trigger

CREATE [OR REPLACE] TIGGER trigger name trigger time trigger event

ON table name

[FOR EACH ROW]

BEGIN

Pl/sql statement

CURSOR cursor name is SELECT * FROM table name (define cursor)

END

Feng GE Oracle tutorial tips, among which:

Trigger name: the name of the trigger object. Because the trigger is executed automatically by the database, the name is just a name and has no real use.

Trigger time: indicates when the trigger is executed. This value is desirable:

Before--- indicates that the trigger executes before the database action

After--- indicates that the issuer executes after the database action.

Trigger event: indicates which database actions trigger this trigger:

Insert: database insert triggers this trigger

The definition of rowid and rownum

1. Both rowid and rownum are virtual columns.

2. Rowid is a physical address that is used to locate the physical storage location of specific data in oracle.

3. Rownum is the sort of output of sql, and you can see the difference from the following example.

15. Use oracle pseudo column to delete duplicate records in the table:

Delete table t where t.rowidgets = (select max (t1.rowid) from table1 T1 where t1.name=t.name)

16. The difference between stored procedures, cursors and functions in oracle

Cursors are like pointers, and cursors can perform a number of unrelated operations. If you want to perform a variety of irrelevant data operations on the data in the result set after the result set is generated

Functions can understand that functions are a kind of stored procedures; functions can have no parameters, but must need a return value, and stored procedures can have no parameters and do not need to return values; both can return values through out parameters, and it is recommended to use stored procedures if multiple parameters need to be returned; only functions can be called in sql data manipulation statements, not stored procedures

This is the end of the content of "what are the basic interview questions for Oracle". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.