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

What is the relevant foundation of the database? in view of this problem, this article introduces the corresponding analysis and solutions in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

Database related foundation

0.1 Database Foundation--

What are the shortcomings of this code?

Try {

Connection conn =...

Statement stmt =...

ResultSet rs = stmt.executeQuery ("select * from table1")

While (rs.next ()) {}

} catch (Exception ex) {}

Answer: there is no finally statement to close individual objects, and after using finally, put the definition of variables outside the try statement block so that the variables can still be accessed in the finally block outside the try statement block.

0.1.1 how can I view the implementation plan of sql without the help of third-party tools?

Set autot on

Explain plan set statement_id = & item_id for & sql

Select * from table (dbms_xplan.display)

0.2.1 paging solution under a large amount of data.

A: the best way is to use SQL statements for paging, so that the result set of each query contains only the data content of a page. In the case that the sql statement is unable to achieve paging, you can consider getting the data of a page by cursor positioning for a large result set.

Sql statements are paged. Paging schemes vary from database to database. Here are the paging sql of three mainstream databases:

Sql server:

String sql =

"select top" pageSize + "* from students where id not in" +

(select top "+ pageSize * (pageNumber-1) +" id from students order by id) "+

"order by id"

Mysql:

String sql =

"select * from students order by id limit" + pageSize* (pageNumber-1) + "," + pageSize

Oracle:

String sql =

"select * from" +

(select *, rownum rid from (select * from students order by postime desc) where rid "+ pageSize* (pageNumber-1)

0.2.2 oracle index?

1.index requires storage space and Istroke O operation.

The purpose of 2.index is to accelerate the speed of select.

3. Insert oracle update and delete data will adjust the index accordingly, so it will increase some consumption.

4. Is it necessary to speed up select by using index? No, index can affect the speed of select when the data is small and huge, so don't build index if the query speed can be satisfied.

5.Index is not valid for null.

Tell me about the composition of the index?

Index column, rowid

0.2.3 Database performance optimization is mainly in the following aspects:?

1. Whether the execution plan of the sql statement is normal

2. Reduce the number of interactions between the application and the database and the execution of the same sql statement

3. Defragmentation of database entities (especially insert and delete actions are often performed on some tables, especially note that the index fields are series fields, self-growing fields and time fields. For systems with frequent business, it is best to rebuild once a month)

4. Reduce the association between tables, especially for batch data processing, query the data in a single table as far as possible, unify the logical processing in memory, and reduce the pressure on the database (it is not advisable for java to deal with batch data, try to use c or C++ to deal with it, the efficiency is greatly improved)

5. Make full use of database cache and application cache for frequently accessed data

6. if the amount of data is relatively large, in the design process, in order to reduce the association of other tables, add some redundant fields and improve the query performance.

0.2.4 how long did Oracle take? what's the difference between char and varchar2?

Char is a fixed-length string and varchar2 is a variable-length string.

For example, if you insert a test string into char (10) and varchar2 (10), the length of the char (10) type is 10, the deficiency is filled with spaces, and the length of the varchar2 (10) type is 4.

0.2.5 how do I track the SQL of a session?

Exec dbms_system.set_sql_trace_in_session (sid,serial#,&sql_trace)

Select sid,serial# from v$session where sid = (select sid from v$mystat where rownum = 1)

0.3 Database architecture and extensions-

0.3.1 conceptual interpretation of ACID, BASE and CAP?

CAP theory in distributed domain

Consistency (consistency), data is updated consistently, all data changes are synchronized, and the final consistency

Partition tolerance (Tolerance of network Partition Partition Fault tolerance) Network Partition tolerance (it can be understood that the system can still work properly in case of partial node failure or connection failure between nodes)

Theorem: any distributed system can only satisfy two points at the same time, not all three.

Advice: instead of wasting energy on how to design a perfect distributed system that meets all three, architects should make choices.

What is ACID?

DBMS emphasizes ACID: Atomicity, Consistency, Isolation, Durability. The consistency emphasizes that when the transaction defined by the programmer completes, the database is in a consistent state, for example, for a transfer, the transaction must be completed with more money and less money as much as An is less.

What is BASE?

BASE:Basically Availble-basic available; Soft-state -; Eventual Consistency-final consistency

0.3.2 what is the working mechanism of data connection pool?

When the J2EE server starts, it establishes a certain number of pooled connections and maintains no less than this number of pooled connections. When the client program needs a connection, the pool driver returns an unused pool connection and records it as busy. If there are currently no idle connections, the pool driver creates a certain number of new connections, and the number of new connections is determined by configuration parameters. When the used pool connection call is complete, the pool driver records the connection table as idle, and other calls can use the connection.

In implementation, the returned Connection is the proxy of the original Connection, and the close method of the proxy Connection does not really close the connection, but returns the Connection object it proxies to the connection pool.

0.3.3 what is the difference between DELETE and TRUNCATE?

Answer: 1. TRUNCATE is very fast on all kinds of tables, whether it is big or small. If there is a ROLLBACK command, DELETE will be undone, but TRUNCATE will not be undone.

2. TRUNCATE is a DDL language, and like all other DDL languages, it will be implicitly submitted and cannot use the ROLLBACK command on TRUNCATE.

3. TRUNCATE will reset the high level and all indexes. When fully browsing the entire table and index, the table after TRUNCATE operation is much faster than the table after DELETE operation.

4. TRUNCATE cannot trigger any DELETE triggers.

5. No one can be granted the right to empty other people's tables.

6. When the table is emptied, the table and the index of the table will be reset to the initial size, while delete cannot.

7. Cannot empty the parent table.

Compare truncate and delete commands?

Answer: both can be used to delete all records in the table. The difference is:

Truncate is a DDL operation that moves HWK and does not require rollback segment. Delete is a DML operation, which requires rollback segment and takes a long time.

0.3.4 the concept of index reconstruction? What is the significance of index reconstruction?

When we create an index, oracle creates an index tree for the index, and tables and index trees locate data through rowid (pseudo columns). When the data in the table is updated, oracle automatically maintains the index tree. However, there are no update operations in the index tree, only delete and insert operations.

For example, to create an index on a table id column, a table id column has a value of "101101101.When I update" 101101, oracle will update the index tree at the same time, but oracle will first mark "101101in the index tree as deleted (actually not deleted, just marked it), and then write" 10110" to the index tree.

If the table is updated frequently, more and more tags will be deleted in the index, and the query efficiency of the index will inevitably decrease, so we should rebuild the index regularly. To remove these delete tags from the index.

Generally, you do not choose to delete the index and then recreate the index, but the rebuild index. During rebuild, users can also use the original index, and rebuild the new index will also use the original index information, so rebuilding the index will be more block.

0.3.5 how do I view the SQL performance status of the production environment database server?

The TKPROF SQL trace tool collects performance status data for the executing SQL and records it in a trace file. This trace file provides a lot of useful information, such as the number of parses. Execution times, CPU usage time, etc. This data can be used to optimize your system.

Set SQL TRACE at the session level:

Effective

ALTER SESSION SET SQL_TRACE TRUE

To set the validity of SQL TRACE in the entire database, you must set the SQL_TRACE parameter to TRUE in init.ora. The USER_DUMP_DEST parameter indicates the directory where the trace file is generated.

The answer to the question about the foundation of the database is shared here. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel to learn more about it.

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.