Home PC Games Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ Oracle 11.2.0.3 and MySQL5.6 DDL comparison     - JQuery implements the same content merge cells (Programming)

- Ubuntu under Spark development environment to build (Server)

- Killall five options you might not know (Linux)

- RHEL6.5 replace local YUM source (Linux)

- Linux - Common process the command (Linux)

- Linux Hard Disk Partition and file system management (Linux)

- Install Java JDK 8 in CentOS 7 / 6.5 / 6.4 (Linux)

- Large computer network security policy Experience (Linux)

- Cobbler remotely install CentOS system (Linux)

- Quick paging ROW_NUMBER conducted (Database)

- Python KNN algorithm of actual realization (Programming)

- Use Visual Studio Code Development TypeScript (Linux)

- Linux under DB2SQL1024N A database connection does not exist. SQLS (Database)

- After installing minimize RHEL / CentOS 7 we need to do (Linux)

- Oracle Character Set Summary (Database)

- Android development, may cause a memory leak problem (Programming)

- osprofiler use OpenStack Cinder Lane (Server)

- GoldenGate update is missing (Database)

- An Example of GoldenGate Extract Process Hang Problem Solving (Database)

- To assign multiple IP addresses NIC on the CentOS 7 (Linux)

 
         
  Oracle 11.2.0.3 and MySQL5.6 DDL comparison
     
  Add Date : 2018-11-21      
         
         
         
  1, CREATE INDEX, DROP INDEX
 2, ADD COLUMN, DROP COLUMN

After MySQL 5.6 features greatly enhanced ONLINE DDL, typically it is above two aspects, the first ADD COLUMN and DROP COLUMN not clog DML operations while indexing terms LOCK = NONE default mode without clogging DML, LOCK and 4 patterns default NONE, SHARED optional and exclusive and dEFAULT, LOCK = NONE mode in this case and Oracle CREATE INDEX ONLINE is very similar, even if the test on 5.6.19 CREATE INDEX LOCK = NONE when there is a thing not submitted or in progress, is not indexed, ORACLE is the same, in other words, ORACLE and MYSQL are indexed initial attempt or a X exclusive lock downgrade immediately once the acquisition, but MYSQL will plug this waiting process SELECT, we know that in any case there will not be blocked SELECT ORACLE's. Explained below;

1, CREATE INDEX (Online) things have not submitted in the case

ORACLE 11.2.0.3 Test CREATE INDEX ONLINE
  First insert a data table, do not submit
 insert into testti select * from testti where rownum < = 1;
  Then another to open a session
 create index test_in on testti (username) online;
  At this CREATE INDEX blocked view V $ LOCK
        SID TYPE LMODE REQUEST BLOCK
 ---------- ---- ---------- ---------- ----------
        48 TX 0 4 0
        48 TM 2 0 0
        48 TM 4 0 0
        48 TX 6 0 0
        53 TM 3 0 0
        53 TX 6 0 1
You can see the SID 53 blocked SID 48, SID 48 attempts to acquire the lock MODE 4 when they were blocked TX lock of a MODE 6
But other sessions SELECT statement is not blocked

MYSQL 5.6.19 same test CREATE INDEX LOCK = NONE
First, delete the data in a table, do not submit
 begin;
  mysql> delete from testno where i = 122;
 Query OK, 1 row affected (0.24 sec)
  Then another open session
mysql> create index test_ind on testno (j) lock = none;
  At this time blockage
 Then open a session
  select * from testno limit 1;
  In this case SELECT is blocked
  Last Seen INNODB STATUS to determine
  TRANSACTIONS
 ------------
 Trx id counter 462509
 Purge done for trx's n: o < 462509 undo n: o < 0 state: running but idle
 History list length 434
 LIST OF TRANSACTIONS FOR EACH SESSION:
 --- TRANSACTION 0, not started
 MySQL thread id 4, OS thread handle 0x40b4c940, query id 275 localhost root System lock
 show engine innodb status
 --- TRANSACTION 462459, not started
 MySQL thread id 3, OS thread handle 0x40b0b940, query id 274 localhost root Waiting for table metadata lock
 select * from testno limit 1
 --- TRANSACTION 462471, not started
 MySQL thread id 2, OS thread handle 0x40671940, query id 273 localhost root Waiting for table metadata lock
 create index test_ind on testno (j) lock = none
 --- TRANSACTION 462492, ACTIVE 100 sec inserting
 mysql tables in use 2, locked 2
 7016 lock struct (s), heap size 800296, 836672 row lock (s), undo log entries 322558
 MySQL thread id 1, OS thread handle 0x40430940, query id 272 localhost root Sending data
 insert into testno select * from testno

You can clearly see the locked 2

Thus we see that in the CREATE INDEX ORACLE and MYSQL If there are things in this table is going to be uncommitted blockage

 index (re) build online cleanup

 2, CREATE INDEX (online) in the case of the absence of things

ORACLE:
 Session 1 create index test_in on testti (username) online;
Session 2 can not issue any DML

But things ORACLE be affected during the CREATE INDEX ONLINE, while not affecting the DML, but after creating things must be submitted during the whole creation process to complete.

MYSQL:
 Session 1 create index test_ind on testno (j) lock = none;
Session 2 can not issue any DML

3, DROP INDEX

About DROP INDEX If there are things that are accessing this table ORACLE and MYSQL using the same basic way is to let you delete
ORACLE error
drop index test_in
 ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
 MYSQL is waiting METADATA

If things are not accessed, delete indexes two kinds of databases are basically the same reason very fast is that he simply deleted
 Data dictionary information, then mark the space as available data is not really deleted.
mysql> drop index test_ind on testno;
 Query OK, 0 rows affected (0.05 sec)
 Records: 0 Duplicates: 0 Warnings: 0

 SQL> drop index test_in;
 Index dropped
 0.17 seconds

4, ADD COLUMN

In the current situation there are things,
 First of all
 insert into testti select * from testti where rownum < = 1;
Also open session
 alter table testti add test varchar2 (20);

 ORACLE 11G things waiting things out, wait for the event:
 enq: TX - row lock contention
  Note 11G has parameters ddl_lock_timeout, but this operation is not affected by the impact parameter
 But this operation is reported in 10G
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

but if
alter table testti add test varchar2 (20) default 'test1'
 10G 11G are being given
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

 The same mechanism MYSQL
 There are things in the case of ADD COL INNODB STATUS can not be completed as follows:
Trx id counter 462664
 Purge done for trx's n: o < 462661 undo n: o < 0 state: running but idle
 History list length 474
 LIST OF TRANSACTIONS FOR EACH SESSION:
 --- TRANSACTION 0, not started
 MySQL thread id 13, OS thread handle 0x40b4c940, query id 446 localhost root System lock
 show engine innodb status
 --- TRANSACTION 462658, not started
 MySQL thread id 2, OS thread handle 0x40671940, query id 445 localhost root Waiting for table metadata lock
 alter table testno add m int (10)
 --- TRANSACTION 462663, ACTIVE 45 sec
 1 lock struct (s), heap size 360, 0 row lock (s), undo log entries 1
 MySQL thread id 1, OS thread handle 0x40430940, query id 443 localhost root cleaning up

Under no circumstances things:

ORACLE If you do not specify a default value, add a field very fast, opportunity instantaneous, so there is no clogging.
 if
alter table testti add test varchar2 (20) default 'test1';
Then another open session
insert into testti select * from testti where rownum < = 1;

 ORACLE wait time:
library cache lock

 MYSQL looks better in this regard, ADD COLUMN not plug any DDL, during the operation he would put
innodb_online_alter_log_max_size, after completion of the synchronization wait to come back, in fact, ORACLE and MYSQL
In the case of CREATE INDEX ONLINE is the use of this mechanism are modified into a temporary
 After the completion of the synchronization region to complete consistency, ORACLE is a temporary recording to the table inside SYS_JOURNAL _ ****
ORACLE only in the case of ADD COL should not be used, because if there is no default value for ORACLE
 COL increase is very fast, but MYSQL whether the default values are the same.
 Session 1:
mysql> alter table testno add m int (10) default 0;
 Query OK, 0 rows affected (43.97 sec)
 Records: 0 Duplicates: 0 Warnings: 0

Session 2:
mysql> insert into testno values (122, 'test1');
 Query OK, 1 row affected (0.00 sec)
Insert unaffected.

5, drop col

 Way drop col ORACLE and MYSQL and ADD COL both with default values is not much difference, ORACLE in case things do not
 However, either wait for the session will be blocked
enq: TM - contention
 MYSQL is not, but may need to reorganize the table MYSQL ORACLE similar to the MOVE TABLE, research needed
     
         
         
         
  More:      
 
- How to import JNI resulting .so libraries in Android Studio (Programming)
- Setting Derby as Linux / Windows running as a service from the start (Server)
- Open remote MySQL database connection managed under CentOS (Database)
- Apache Kafka: the next generation of distributed messaging system (Server)
- How to Debian Linux the default Python version switch to alternative version (Linux)
- Linux 64-bit porting (Programming)
- Linux, ls command to achieve (Linux)
- ORA-00600 error solve one case (Database)
- KVM virtualization nested configuration (Server)
- Qt signals and slots mechanism (Programming)
- Linux tmux tcpdump summary (Linux)
- Linux in order to make NMAP hide and seek with the firewall (Linux)
- Firewall Configuration Red Hat Enterprise Linux 4 (Linux)
- Schema snapshot rollback (Database)
- Debian (Wheezy) Install Java environment / replace OpenJDK as the SUN JDK (Linux)
- Android application simulates the phone keypad (Programming)
- Go build the locale under Windows (Linux)
- Depth Java Singleton (Programming)
- Android Get App version number and version name (Programming)
- Construction LVM-based raw device Oracle10g Database on Oracle Linux 5.11 (Database)
     
           
     
  CopyRight 2002-2022 newfreesoft.com, All Rights Reserved.