Home Database Mysql Tutorial Oracle中表添加带有默认值列的讨论(包括11G新特性)

Oracle中表添加带有默认值列的讨论(包括11G新特性)

Jun 07, 2016 pm 04:47 PM

这个问题为什么要讨论呢,是有历史根源的,我们从Oracle 10G开始说起。在11G以前,一个大表,如果要添加一个新列,且新列有默认值

这个问题为什么要讨论呢,是有历史根源的,我们从Oracle 10G开始说起。在11G以前,一个大表,如果要添加一个新列,且新列有默认值,如果使用alter table xx add xx_1 number default 1000; 这类语句设置,那将非常杯具!

它的动作其实就是,添加一个列,然后对这一列进行更新,如果这个表非常大,那么将会产生一个非常大的事务。

而且因为它是ddl操作,它甚至会导致关于该表的select语句都被堵塞!后果非常可怕。

我们在10G中,遇到这种需求怎么办呢?

简单来说,就是先添加一列(不设置默认值)

之后通过使用PL/SQL 对这一列进行批量提交更新(比如1000行 一提交)

好处是,不会锁太多的行,而且每个事务都不大,出现故障之类的,不会产生死事务

或者可以使用在线重定义的方式。 ,参考我的这篇博客

到了11G中,我们对于表添加一列 带有默认值,有了新特性

我们来测试一下,创建表t,100多万数据。

SQL> select count(*) from t;

  COUNT(*)
----------
  1207317

对表加了一列,并设置not null 加默认值

SQL> alter table t add MrDai number default 10000 not null;

Table altered.

Elapsed: 00:00:00.34

速度飞快

这里其实只是在数据字典中做了一个标记,并不会对块中行的实际数据进行修改,所以速度飞快

我们查询的时候,会对新列做一个类似nvl()之类的操作,如果是null,则返回默认的数值。

在设置完成以后,如果是新插入的行,在默认值列,会将数据真正的插入到行

总结一下,这个特性类似于 给表设了一个断点, 断点之前的,如果该列是null,则对其转换为 默认值,而断点之后的,,该列会插入真正的值

这篇文章  对该特性有dump过程,可以参考阅读。

如果想让新列,之前的都为null,而之后的数据则有默认值,也可以,分两步

alter table t add MrDai number ;

alter table t modify MrDai number default 10000;

linux

Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
3 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
R.E.P.O. Best Graphic Settings
3 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
R.E.P.O. How to Fix Audio if You Can't Hear Anyone
3 weeks ago By 尊渡假赌尊渡假赌尊渡假赌

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

How do you alter a table in MySQL using the ALTER TABLE statement? How do you alter a table in MySQL using the ALTER TABLE statement? Mar 19, 2025 pm 03:51 PM

The article discusses using MySQL's ALTER TABLE statement to modify tables, including adding/dropping columns, renaming tables/columns, and changing column data types.

How do I configure SSL/TLS encryption for MySQL connections? How do I configure SSL/TLS encryption for MySQL connections? Mar 18, 2025 pm 12:01 PM

Article discusses configuring SSL/TLS encryption for MySQL, including certificate generation and verification. Main issue is using self-signed certificates' security implications.[Character count: 159]

How do you handle large datasets in MySQL? How do you handle large datasets in MySQL? Mar 21, 2025 pm 12:15 PM

Article discusses strategies for handling large datasets in MySQL, including partitioning, sharding, indexing, and query optimization.

What are some popular MySQL GUI tools (e.g., MySQL Workbench, phpMyAdmin)? What are some popular MySQL GUI tools (e.g., MySQL Workbench, phpMyAdmin)? Mar 21, 2025 pm 06:28 PM

Article discusses popular MySQL GUI tools like MySQL Workbench and phpMyAdmin, comparing their features and suitability for beginners and advanced users.[159 characters]

How do you drop a table in MySQL using the DROP TABLE statement? How do you drop a table in MySQL using the DROP TABLE statement? Mar 19, 2025 pm 03:52 PM

The article discusses dropping tables in MySQL using the DROP TABLE statement, emphasizing precautions and risks. It highlights that the action is irreversible without backups, detailing recovery methods and potential production environment hazards.

How do you represent relationships using foreign keys? How do you represent relationships using foreign keys? Mar 19, 2025 pm 03:48 PM

Article discusses using foreign keys to represent relationships in databases, focusing on best practices, data integrity, and common pitfalls to avoid.

How do you create indexes on JSON columns? How do you create indexes on JSON columns? Mar 21, 2025 pm 12:13 PM

The article discusses creating indexes on JSON columns in various databases like PostgreSQL, MySQL, and MongoDB to enhance query performance. It explains the syntax and benefits of indexing specific JSON paths, and lists supported database systems.

How do I secure MySQL against common vulnerabilities (SQL injection, brute-force attacks)? How do I secure MySQL against common vulnerabilities (SQL injection, brute-force attacks)? Mar 18, 2025 pm 12:00 PM

Article discusses securing MySQL against SQL injection and brute-force attacks using prepared statements, input validation, and strong password policies.(159 characters)

See all articles