Shulou(Shulou.com)06/01 Report--

The next level of the tablespace should be our data table, which is also our most commonly used object, the object that stores the associated fields of the fields.

Let's create the first table as test

Format

Create table test (id number (10) not null primary key,name varchar (20) not null,age char (2) not null)

explain

Create table is used to create the keyword used to create the table, followed by the table name test, column information (id number, name name) followed by the type in parentheses, and then not null cannot be empty, so it must be inserted, primary key primary key, globally unique identifier.

Format

Select table_name,tablespace_name from user_tables from table_name='test'

explain

The user_ tables table is used to view all user table information

Format

Describe test

explain

View the table structure, only describe the table structure of the current object, the keyword can be abbreviated desc.

Format

Alter table test rename to test1

explain

Modify the name keyword rename to of the data table

Format

Alter table test modify (id number (20))

explain

Modify the length of a type of a column in a data table

Format

Alter table test rename column id to user_id

explain

Modify the column name of the data table

Format

Alter table test add (sal int)

explain

Add a column of data (type) to the data table

Format

Alter table test drop column sal

explain

Delete a column in the database

Format

Drop table test cascade constraints

explain

Delete the entire table. Cascade constrints means that when the table is associated with or constrained by other tables, use keywords to force the table to be deleted

Well, there is also a kind of table in Oracle called temporary table, which is generally known by name to be temporary, but it is not.

What is a temporary table, so simply put, we use create table to create a table called permanent table or ordinary standard, temporary table is no different from ordinary standard, temporary table refers to the temporary data, that is to say, the table will always exist, but the data will be emptied automatically under some conditions in Oracle!

There are two conditions for emptying the temporary table: 1, the transaction is submitted or rolled back, 2, the end of the session

Then each database operation in Oracle is not updated immediately, but is modified and submitted to the database in terms of things. Each transaction meeting commit ends, from the previous to this commit, which may be one or more SQL statements, then it also means that the commit commit of things, transaction rollback rollback abolishes the current transaction operation, that is, the SQL statement just made does not count.

The session in Oracle is each process that interacts with the user, and the temporary table emptying data is also divided into two categories: 1, transaction-level temporary table 2, session-level temporary table

The transaction-level temporary table is when the data is emptied every time the transaction is committed, and the session level is when the session is disconnected, which means the end of the Oracle session user thread.

Session-level temporary table

Format

Create global temporary table tmp_user_session (user_id) on commit preserve rows

explain

Create a session temporary table create global temporary

Format

Commit

explain

Commit data, things are over, why don't we usually need to do this because we default that the auto-commit transaction function is turned on?

Transaction level

Format

Create global tempporary table. On commit delete rows

explain

To create transaction-level temporary tables that will be cleared automatically when we commit or roll back the data

Format

Select table_name,tablespace_name from user_tables where table_name='T_USERS_SESSION' or.

explain

Compare the tablespace information with the previous normal standard and find that the temporary tablespace is empty.

So we have said so much, what on earth is the use of it? let's talk about the use of his application.

1. Size table segmentation

Table indexing can greatly improve the efficiency of querying data, but for a large amount of data above 100 million, it takes a lot of time to create an index, and the effect of querying a large number of index tables is not much different from that of direct query, and there is no performance improvement. at this time, the commonly used method is to split the large table into several small temporary tables, operate on the temporary table, and finally comprehensively deal with the query results.

2. Parallel problem

Parallel based on multithreading, to put it simply, several clients operate on objects at the same time, which will lead to confusion. The most commonly used method is to add locks to files or tables to prevent multiple users from modifying objects together. Then, when operating frequently on the table, locking the table must have an impact on the performance of the whole database. At this time, using temporary tables, the data in the temporary table session is transparent, and there is no need to lock the table, which greatly improves the performance of the database.

3. Caching

Program segments may need to do a lot of complex operations, at this time to create a temporary table to store data in a temporary table, you can operate like a regular table. At this time, the temporary table serves as the storage space for temporary data.

There is also a special table in Oracle called dual, which is also a very meaningful table. To put it simply, it is a virtual table in Oracle, also known as a pseudo table. So what is it for? we know that select * from must specify a table to output the results. Then there are some application scenarios where there is no table storage, such as system calls, and you need to specify a table to output. At this time, the role of dual is displayed, that is, dual is not used as data storage, but as the source table of from.

Format

Select sysdate from dual; or selec 3 / 2 / 5 as result from dual

explain

Time is real-time change, system call time, when the output needs to point to a source table dual function can be known, and can be used as the output table of the operation.

Whether modifying the dual data will have an impact, then we find that after the query, the dual has only one column and only one value Y. We randomly insert the data and then do the above calculation and find that the result is the same, and the numerical operation of the table structure in which we delete it is also the same. Then the value mentioned that the insertion and deletion of dual before Oracle 10g will affect the result of the operation, so it has been greatly improved after oracle 10g. No matter what the operation of the internal data in dual will not affect the query results as a virtual table, a lot of work has been done in dual. No matter what the real data content is, it ensures that the effect is the same as that of a single record. Oracle 10g has made great improvements to the processing mechanism of the table.

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.