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

This article mainly introduces what you should pay attention to when using the create table...as select * sentence in the database. What is introduced in this article is very detailed and has a certain reference value. Interested friends must finish reading it!

1. When doing some data migration, many people will use create table as select * from table where id=-1 to create the same table in the coming year, but this has a big drawback that the default value in the original table cannot be migrated together.

2 、 Using the CREATE TABLE... AS SELECT... Command: This command will copy acrooss to the new table all the data,but the constraints triggers, and so on will not be transferred to the new table.

Those are not null constraints, other constraints and trigger can not be brought over, strictly speaking, not null is also a kind of constraints, but it is excluded from the textbook.

Be careful with create table as select, be sure to pay attention to the problem of default value

Oraclecreate talbe as select database migration

When doing some data migration, many people will use create table as select * from table where id=-1 to create the same table in the coming year, but this has a big disadvantage. The default value in the original table cannot be migrated with it. See the following example:

First, create a new table

-- Create table

Create table table01

(

Id number (16)

Add_date date default sysdate

Status number (1)

Entp_code varchar2 (200)

)

Second, use create table table02 as

Select * From table01 where id=-1

Third, if you look at the structure of the two tables, you will find that the defaule value of the second table is gone. As shown in the following 2 figures, it is obvious that the default value of the add_date of table 02 is worth missing sysdate.

Table structure of table01

Table structure of table02

Brief introduction to Create table as select performance:

The fastest way to move data in a database is to move it from one table to another, rather than going into the operating system to move data from one table to another. There are some common ways to improve the performance of data migration:

1. Adjust the structure of the table (delete indexes and triggers)

2. Disable constraints during data migration

3. Use tips and options to improve transaction performance

The first technique is to adjust the structure of the table, which involves disabling any triggers or indexes on the target table. For example, if there is a row-level trigger on the target table, each row inserted into the table executes the trigger. If possible, it is best to disable the trigger for the target table before the data is inserted; if the trigger should be executed for each inserted row, you can create the trigger after the bulk insert is complete. instead of executing the trigger repeatedly with each piece of data inserted during the bulk insert.

In addition to disabling triggers, you should delete the index on the target table before starting data loading, and then recreate the index after the data has been loaded. If the index remains on the table, ORACLE will dynamically manage the index as each row is inserted.

Note: deleting indexes and disabling triggers can solve the performance problems encountered in data migration between most numbers and large tables.

In addition to disabling the index, take into account the constraints on the table. If the source data is already in a table in the database, you can examine the data to understand its related constraints (such as foreign keys or check constraints) before loading the data into the target table, and once the loading of the data is complete, you can re-enable these constraints.

If the above options do not provide appropriate performance, you should investigate the options introduced by Oracle for data migration tuning. These options include the following:

1. The APPPEND prompt for the insert command is similar to the Direct Path Loader,APPENED prompt to load the data block into the table, starting with the table's high water mark. Use the APPENED hint to increase space utilization.

2.Nologging option if you are executing the create table as select command, use the nologing option to avoid writing the redo log file during the operation.

3. Parallel options parallel queries use multiple processes to complete a task. For the create table as select command, the create table part and the query part can be parallelized. If you use the parallel option, you should also use the nologing option, otherwise parallel operations will have to wait as serialization writes to the online redo log file.

Before using these advanced options, you should first investigate the structure of the target table to make sure that some of the common problems mentioned earlier have been avoided.

The above is all the contents of the article "what to pay attention to when using create table...as select * sentences in the database". Thank you for your reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

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.