Oracle公司从版本12c以后实际上引入了类似于SQL Server的以年份为版本号的版本风格。Oracle 12c以后的已release版本为18c,19c,21c，2020年由于全球疫情的问题未发布大版本号。
其大版本的释放周期大大缩短了，实际上大版本的释放频率已经覆盖了过去Patch Set补丁集的作用。
对于先进企业选择Oracle数据库版本而言，建议可以考虑对当前年份-2/3的算法。
即如果当前是2021年，则考虑使用(2021-2 or 3) 18c或19c；并打上最新的RU(Release Update)和RUR(Release Update Revision)。
DBI的Franck Pachot给出了一个更高效率的DBA_EXTENTS脚本，对于特别大的ORACLE数据库定位EXTENT时很有用：
column owner format a6
column segment_type format a20
column segment_name format a15
column partition_name format a15
set linesize 200
set timing on time on echo on autotrace on stat
WITH
l AS ( /* LMT extents indexed on ktfbuesegtsn,ktfbuesegfno,ktfbuesegbno */
SELECT ktfbuesegtsn segtsn,ktfbuesegfno segrfn,ktfbuesegbno segbid, ktfbuefno extrfn,
ktfbuebno fstbid,ktfbuebno + ktfbueblks - 1 lstbid,ktfbueblks extblks,ktfbueextno extno
FROM sys.x$ktfbue
),
d AS ( /* DMT extents ts#, segfile#, segblock# */
SELECT ts# segtsn,segfile# segrfn,segblock# segbid, file# extrfn,
block# fstbid,block# + length - 1 lstbid,length extblks, ext# extno
FROM sys.uet$
),
s AS ( /* segment information for the tablespace that contains afn file */
SELECT /*+ materialized */
f1.fenum afn,f1.ferfn rfn,s.ts# segtsn,s.FILE# segrfn,s.BLOCK# segbid ,s.TYPE# segtype,f2.fenum segafn,t.name tsname,blocksize
FROM sys.seg$ s, sys.ts$ t, sys.x$kccfe f1,sys.x$kccfe f2
WHERE s.ts#=t.ts# AND t.ts#=f1.fetsn AND s.FILE#=f2.ferfn AND s.ts#=f2.fetsn
),
m AS ( /* extent mapping for the tablespace that contains afn file */
SELECT /*+ use_nl(e) ordered */
s.afn,s.segtsn,s.segrfn,s.segbid,extrfn,fstbid,lstbid,extblks,extno, segtype,s.rfn, tsname,blocksize
FROM s,l e
WHERE e.segtsn=s.segtsn AND e.segrfn=s.segrfn AND e.segbid=s.segbid
UNION ALL
SELECT /*+ use_nl(e) ordered */
s.afn,s.segtsn,s.segrfn,s.segbid,extrfn,fstbid,lstbid,extblks,extno, segtype,s.rfn, tsname,blocksize
FROM s,d e
WHERE e.segtsn=s.segtsn AND e.segrfn=s.segrfn AND e.segbid=s.segbid
UNION ALL
SELECT /*+ use_nl(e) use_nl(t) ordered */
f.fenum afn,null segtsn,null segrfn,null segbid,f.ferfn extrfn,e.ktfbfebno fstbid,e.ktfbfebno+e.ktfbfeblks-1 lstbid,e.ktfbfeblks extblks,null extno, null segtype,f.ferfn rfn,name tsname,blocksize
FROM sys.x$kccfe f,sys.x$ktfbfe e,sys.ts$ t
WHERE t.ts#=f.fetsn and e.ktfbfetsn=f.fetsn and e.ktfbfefno=f.ferfn
UNION ALL
SELECT /*+ use_nl(e) use_nl(t) ordered */
f.fenum afn,null segtsn,null segrfn,null segbid,f.ferfn extrfn,e.block# fstbid,e.block#+e.length-1 lstbid,e.length extblks,null extno, null segtype,f.ferfn rfn,name tsname,blocksize
FROM sys.x$kccfe f,sys.fet$ e,sys.ts$ t
WHERE t.ts#=f.fetsn and e.ts#=f.fetsn and e.file#=f.ferfn
),
o AS (
SELECT s.tablespace_id segtsn,s.relative_fno segrfn,s.header_block segbid,s.segment_type,s.owner,s.segment_name,s.partition_name
FROM SYS_DBA_SEGS s
),
datafile_map as (
SELECT
afn file_id,fstbid block_id,extblks blocks,nvl(segment_type,decode(segtype,null,'free space','type='||segtype)) segment_type,
owner,segment_name,partition_name,extno extent_id,extblks*blocksize bytes,
tsname tablespace_name,rfn relative_fno,m.segtsn,m.segrfn,m.segbid
FROM m,o WHERE extrfn=rfn and m.segtsn=o.segtsn(+) AND m.segrfn=o.segrfn(+) AND m.segbid=o.segbid(+)
UNION ALL
SELECT
file_id+(select to_number(value) from v$parameter WHERE name='db_files') file_id,
1 block_id,blocks,'tempfile' segment_type,
'' owner,file_name segment_name,'' partition_name,0 extent_id,bytes,
tablespace_name,relative_fno,0 segtsn,0 segrfn,0 segbid
FROM dba_temp_files
)
select * from datafile_map where file_id=5495 and 11970455 between block_id and block_id+blocks
下载：
Dgg Oracle MySQL数据同步校准工具最新版：https://zcdn.askmac.cn/Dgg2107.zip
Dgg申请个人版注册码： http://askmac.cn:3333/acquire
教程：
Dgg Oracle/MySQL数据库复制同步校准软件
Dgg Rdbms Oracle MySQL Table/Schema Sync Compare Repair
最新版：
Dgg is a single application that allows you to tranfer data between Oracle and MySQL. It will also support Microsoft SQL Server in future.
It supports below directionals:
Oracle To Oracle
Oracle To MySQL
MySQL To MySQL
MySQL To Oracle
特性：
当目标数据库类型为MySQL时，需要对应数据库用户具备SUPER ON *.* 权限，
GRANT SUPER ON *.* to USER;
否则会出现报错：msg: Access denied; you need (at least one of) the SUPER privilege(s) for this operation
Oracle到Oracle的传输速度：
在有NVARCHAR、LOB(BLOB CLOB NCLOB)字段的情况下，DataGoGo的单线程传输速度约为4500条/秒。
在没有NVARCHAR、LOB(BLOB CLOB NCLOB)字段的情况下，DataGoGo的单线程传输速度约为30000条/秒。
MySQL到MySQL的传输速度
没有LOB/LONGTEXT的情况下，单线程速度约为20000条/秒。
在使用Dgg实现MYSQL跨库数据同步后，可以使用Dgg的数据校验功能来验证数据是否一致。
请首先参考《使用Dgg 全量同步/初始化Initial Load MySQL数据》 https://www.askmac.cn/archives/datagogo-initial-mysql.html
选中 树形图最上方的用户名，右击 Compare:
DataGoGo中存在三种 表数据的比较粒度：
测试 90万行 7列的表， 比较约耗时 10秒。
在使用Dgg实现Oracle跨库数据同步后，可以使用Dgg的数据校验功能来验证数据是否一致。
请首先参考《使用Dgg 全量同步/初始化Initial Load Oracle数据》 https://www.askmac.cn/archives/datagogo-initial-load-oracle-database.html
选中 树形图最上方的用户名，右击 Compare:
DataGoGo中存在三种 表数据的比较粒度：
测试 90万行 24列的表， 比较约耗时 2分钟。
比较结果如下：
Compare start date: 2021-06-07 14:58:54
Comparing SCOTT.T_XT_SYNCPERSONLOG and EAS.T_XT_SYNCPERSONLOG:
SCOTT.T_XT_SYNCPERSONLOG full table checksum: 235801620123
EAS.T_XT_SYNCPERSONLOG full table checksum: 235801620123
Full table checksum is the same.
End date: 2021-06-07 15:00:31
UDEV RELOAD是否会影响oracle ASM?
回答： 如果频繁 reload 显然会影响Oracle ASM ，甚至导致ASM Diskgroup OFFLINE。
如果只是一次很快速的reload，那么影响的概率较小。
参考链接：
‘udev’ rules continuously being reloaded resulted in Oracle ASM diskgroup outage
https://access.redhat.com/solutions/1465913
multipath.conf
# This is a basic configuration file with some examples, for device mapper
# multipath.
#
# For a complete list of the default configuration values, run either
# multipath -t
# or
# multipathd show config
#
# For a list of configuration options with descriptions, see the multipath.conf
# man page
## By default, devices with vendor = "IBM" and product = "S/390.*" are
## blacklisted. To enable mulitpathing on these devies, uncomment the
## following lines.
#blacklist_exceptions {
# device {
# vendor "IBM"
# product "S/390.*"
# }
#}
## Use user friendly names, instead of using WWIDs as names.
defaults {
user_friendly_names yes
find_multipaths yes
}
##
## Here is an example of how to configure some standard options.
##
#
#defaults {
# polling_interval 10
# path_selector "round-robin 0"
# path_grouping_policy multibus
# uid_attribute ID_SERIAL
# prio alua
# path_checker readsector0
# rr_min_io 100
# max_fds 8192
# rr_weight priorities
# failback immediate
# no_path_retry fail
# user_friendly_names yes
#}
##
## The wwid line in the following blacklist section is shown as an example
## of how to blacklist devices by wwid. The 2 devnode lines are the
## compiled in default blacklist. If you want to blacklist entire types
## of devices, such as all scsi devices, you should use a devnode line.
## However, if you want to blacklist specific devices, you should use
## a wwid line. Since there is no guarantee that a specific device will
## not change names on reboot (from /dev/sda to /dev/sdb for example)
## devnode lines are not recommended for blacklisting specific devices.
##
#blacklist {
# wwid 26353900f02796769
# devnode "^(ram|raw|loop|fd|md|dm-|sr|scd|st)[0-9]*"
# devnode "^hd[a-z]"
#}
#multipaths {
# {
# device {
# vendor "COMPAQ "
# product "HSV110 (C)COMPAQ"
# path_grouping_policy multibus
# path_checker readsector0
# path_selector "round-robin 0"
# hardware_handler "0"
# failback 15
# rr_weight priorities
# no_path_retry queue
# }
# device {
# vendor "COMPAQ "
# product "MSA1000 "
# path_grouping_policy multibus
# }
#}
# wwid 3600508b4000156d700012000000b0000
# alias yellow
# path_grouping_policy multibus
# path_selector "round-robin 0"
# failback manual
#devices {
# device {
# vendor "COMPAQ "
# product "HSV110 (C)COMPAQ"
# path_grouping_policy multibus
# path_checker readsector0
# path_selector "round-robin 0"
# hardware_handler "0"
# failback 15
# rr_weight priorities
# no_path_retry queue
# }
# device {
# vendor "COMPAQ "
# product "MSA1000 "
# path_grouping_policy multibus
# }
#}
# rr_weight priorities
# no_path_retry 5
# }
# multipath {
# wwid 1DEC_____321816758474
# alias red
# }
#
multipaths {
multipath {
wwid 360050764008103127000000000000067
alias disk30001
}
multipath {
wwid 360050764008103127000000000000068
alias disk30002
}
multipath {
wwid 360050764008103127000000000000069
alias disk30003
}
multipath {
wwid 360050764008103127000000000000064
alias disk31001
}
multipath {
wwid 360050764008103127000000000000065
alias disk31002
}
multipath {
wwid 36005076400810312700000000000005f
alias disk32001
}
multipath {
wwid 360050764008103127000000000000060
alias disk32002
}
multipath {
wwid 360050764008103127000000000000061
alias disk32003
}
multipath {
wwid 360050764008103127000000000000062
alias disk32004
}
multipath {
wwid 360050764008103127000000000000063
alias disk32005
}
multipath {
wwid 360050764008103127000000000000066
alias disk33001
}
}
12-dm-permissions.rules
ENV{DM_NAME}=="disk30001",NAME="asmdisk30001", OWNER:="grid", GROUP:="asmadmin", MODE:="660"
ENV{DM_NAME}=="disk30002",NAME="asmdisk30002", OWNER:="grid", GROUP:="asmadmin", MODE:="660"
ENV{DM_NAME}=="disk30003",NAME="asmdisk30003", OWNER:="grid", GROUP:="asmadmin", MODE:="660"
ENV{DM_NAME}=="disk31001",NAME="asmdisk31001", OWNER:="grid", GROUP:="asmadmin", MODE:="660"
ENV{DM_NAME}=="disk31002",NAME="asmdisk31002", OWNER:="grid", GROUP:="asmadmin", MODE:="660"
ENV{DM_NAME}=="disk32001",NAME="asmdisk32001", OWNER:="grid", GROUP:="asmadmin", MODE:="660"
ENV{DM_NAME}=="disk32002",NAME="asmdisk32002", OWNER:="grid", GROUP:="asmadmin", MODE:="660"
ENV{DM_NAME}=="disk32003",NAME="asmdisk32003", OWNER:="grid", GROUP:="asmadmin", MODE:="660"
ENV{DM_NAME}=="disk32004",NAME="asmdisk32004", OWNER:="grid", GROUP:="asmadmin", MODE:="660"
ENV{DM_NAME}=="disk32005",NAME="asmdisk32005", OWNER:="grid", GROUP:="asmadmin", MODE:="660"
ENV{DM_NAME}=="disk33001",NAME="asmdisk33001", OWNER:="grid", GROUP:="asmadmin", MODE:="660"
99-oracle-asmdevices.rules
KERNEL=="sd*", SUBSYSTEM=="block", ENV{DEVTYPE}=="disk", ENV{ID_SERIAL}=="360050764008103127000000000000067", OWNER="grid", GROUP="asmadmin", MODE="0660"
KERNEL=="sd*", SUBSYSTEM=="block", ENV{DEVTYPE}=="disk", ENV{ID_SERIAL}=="360050764008103127000000000000068", OWNER="grid", GROUP="asmadmin", MODE="0660"
KERNEL=="sd*", SUBSYSTEM=="block", ENV{DEVTYPE}=="disk", ENV{ID_SERIAL}=="360050764008103127000000000000069", OWNER="grid", GROUP="asmadmin", MODE="0660"
KERNEL=="sd*", SUBSYSTEM=="block", ENV{DEVTYPE}=="disk", ENV{ID_SERIAL}=="360050764008103127000000000000064", OWNER="grid", GROUP="asmadmin", MODE="0660"
KERNEL=="sd*", SUBSYSTEM=="block", ENV{DEVTYPE}=="disk", ENV{ID_SERIAL}=="360050764008103127000000000000065", OWNER="grid", GROUP="asmadmin", MODE="0660"
KERNEL=="sd*", SUBSYSTEM=="block", ENV{DEVTYPE}=="disk", ENV{ID_SERIAL}=="36005076400810312700000000000005f", OWNER="grid", GROUP="asmadmin", MODE="0660"
KERNEL=="sd*", SUBSYSTEM=="block", ENV{DEVTYPE}=="disk", ENV{ID_SERIAL}=="360050764008103127000000000000060", OWNER="grid", GROUP="asmadmin", MODE="0660"
KERNEL=="sd*", SUBSYSTEM=="block", ENV{DEVTYPE}=="disk", ENV{ID_SERIAL}=="360050764008103127000000000000061", OWNER="grid", GROUP="asmadmin", MODE="0660"
KERNEL=="sd*", SUBSYSTEM=="block", ENV{DEVTYPE}=="disk", ENV{ID_SERIAL}=="360050764008103127000000000000062", OWNER="grid", GROUP="asmadmin", MODE="0660"
KERNEL=="sd*", SUBSYSTEM=="block", ENV{DEVTYPE}=="disk", ENV{ID_SERIAL}=="360050764008103127000000000000063", OWNER="grid", GROUP="asmadmin", MODE="0660"
KERNEL=="sd*", SUBSYSTEM=="block", ENV{DEVTYPE}=="disk", ENV{ID_SERIAL}=="360050764008103127000000000000066", OWNER="grid", GROUP="asmadmin", MODE="0660"
UINT 是 UINT256的别名
INT是INT256的别名
uint and int are aliases for uint256 and int256, respectively.
Solidity 中整型有明确范围，例如 uint32 的范围是 0 ~ 2**32-1 , 若结果超过范围 会被truncate截断
address 类型 存放一个20字节的以太坊地址
address payable 类似于address ，且提供 transfer 和 send成员 ; 即 普通address默认不能传输ETH ， address payable 可以
pragma solidity 0.5.10;
contract Test1 {
address public notpaybleaddress;
address public payableaddress;
uint public balance1;
uint public balance2;
uint public balance3;
constructor() public payable {
notpaybleaddress = msg.sender;
payableaddress=address(0);
balance1= address(msg.sender).balance;
balance2= address(0xAb8483F64d9C6d1EcF9b849Ae677dD3315835cb2).balance;
balance3= address(this).balance;
}
function test1() public payable {
address payable x = address(0xAb8483F64d9C6d1EcF9b849Ae677dD3315835cb2);
x.transfer(1 ether);
balance1= address(msg.sender).balance;
balance2= address(0xAb8483F64d9C6d1EcF9b849Ae677dD3315835cb2).balance;
balance3= address(this).balance;
}
}
ABI 是 Application Binary Interface的简称，即2进制接口。是Ethereum生态中与合约交互的标准接口，包括了 链外和 合约对合约调用。其数据按照类型编码，由于不是自描述的，因此需要对应模式解码。
一个函数调用的调用数据的前4个字节，被用来指定要调用的函数。其是该函数的签名的Keccak-256 (SHA-3)哈希值的前4位。
bytes4 private constant SELECTOR = bytes4(keccak256(bytes('transfer(address,uint256)')));
(bool success, bytes memory data) = token.call(abi.encodeWithSelector(SELECTOR, to, value));
测试代码
pragma solidity 0.5.10;
contract Test2 {
address public owner;
mapping( address => uint ) public balances;
bytes4 private constant SELECTOR = bytes4(keccak256(bytes('transfer(address,uint256)')));
function transfer(address to,uint256 value) public {
balances[to]+= value;
balances[address(msg.sender)]-=value;
}
function _safeTransfer(address token, address to, uint value) private {
(bool success, bytes memory data) = token.call(abi.encodeWithSelector(SELECTOR, to, value));
require(success && (data.length == 0 || abi.decode(data, (bool))), 'TRANSFER_FAILED');
}
function testabitransfer() public {
_safeTransfer(address(this),address(0xAb8483F64d9C6d1EcF9b849Ae677dD3315835cb2),100);
}
constructor() public {
owner= msg.sender;
balances[msg.sender]=10000;
balances[address(this)]=10000;
}
}
type(C).creationCode
包含协定的创建字节码的内存字节数组。这可以在内联程序集中用于构建自定义创建例程，特别是通过使用 create2 操作码。此属性可以 not 在合同本身或任何派生合同中访问。它使字节码包含在调用站点的字节码中，因此不可能像这样循环引用。
bytes32 public constant INIT_CODE_PAIR_HASH = keccak256(abi.encodePacked(type(PancakePair).creationCode));
function createPair(address tokenA, address tokenB) external returns (address pair) {
require(tokenA != tokenB, 'Pancake: IDENTICAL_ADDRESSES');
(address token0, address token1) = tokenA < tokenB ? (tokenA, tokenB) : (tokenB, tokenA);
require(token0 != address(0), 'Pancake: ZERO_ADDRESS');
require(getPair[token0][token1] == address(0), 'Pancake: PAIR_EXISTS'); // single check is sufficient
bytes memory bytecode = type(PancakePair).creationCode;
bytes32 salt = keccak256(abi.encodePacked(token0, token1));
assembly {
pair := create2(0, add(bytecode, 32), mload(bytecode), salt)
}
IPancakePair(pair).initialize(token0, token1);
getPair[token0][token1] = pair;
getPair[token1][token0] = pair; // populate mapping in the reverse direction
allPairs.push(pair);
emit PairCreated(token0, token1, pair, allPairs.length);
}
create2 参考： https://hackernoon.com/using-ethereums-create2-nw2137q7
关于 memory 和 storage ， storage最后都会记录在blockchain上，默认函数里申明的变量 都是memory的， 一般 string 字符串需要 单独申明 memory。
例如 你在函数中特意申明 一个memory变量
uint memory var1=0;
编译器一般会警告：
TypeError: Storage location can only be given for array or struct types. uint memory var1=0; ^————–^
若在contract中的直接定义一个memory 变量 ，则：
uint memory var2=0;
ParserError: Expected identifier but got ‘memory’ uint memory var2=0; ^—-^
若在函数中 一个string 变量未指定为memory ,则会报错：
Warning: Variable is declared as a storage pointer. Use an explicit “storage” keyword to silence this warning. string name1 = name; ^————^
TypeError: Type string memory is not implicitly convertible to expected type string storage pointer. string name1 = name; ^——————-^
所以 一般只需要记录 在函数中的临时 string变量， 要指定要memory。其实也不用特意记住，因为不加memory会报错。
gas 评估 ，在remix 中
(async () => {
try {
let result = await web3.eth.estimateGas({
to: "0xc4abd0339eb8d57087278718986382264244252f",
data: "0xc6888fa10000000000000000000000000000000000000000000000000000000000000003"
});
console.log(result);
} catch (e) {
console.log(e.message)
}
})()
在nodejs中：
var Web3 = require('web3');
var web3 = new Web3(new Web3.providers.HttpProvider("https://ropsten.infura.io/v3/260d84d657264ecba48c8d6e95fc707d"));
var result = web3.eth.estimateGas({
to: "0xc4abd0339eb8d57087278718986382264244252f",
data: "0xc6888fa10000000000000000000000000000000000000000000000000000000000000003"
});
console.log(result);
pure 关键字的函数 效率更高 其暗示这个函数 既不读取 blockchain上的状态 也不修改 状态。
These functions cannot:
Read from state variables – read the block chain
Access balances
Call functions not marked pure
library SafeMath {
function add(uint x, uint y) internal pure returns (uint z) {
require((z = x + y) >= x, 'ds-math-add-overflow');
}
function sub(uint x, uint y) internal pure returns (uint z) {
require((z = x - y) <= x, 'ds-math-sub-underflow');
}
function mul(uint x, uint y) internal pure returns (uint z) {
require(y == 0 || (z = x * y) / y == x, 'ds-math-mul-overflow');
}
}
view 函数则按时该函数用以读取状态。 这些关键字可以让编译器优化的更好一点。
View functions are read only functions and do not modify the state of the block chain. In other words if you want to read data from the block chain one can use view. Getter method are by default view functions. These functions cannot:
Write to state variables – update the block chain
Emit events
Create contracts or use self destruct
Send ether
参考 https://cryptomarketpool.com/pure-and-view-in-solidity-smart-contracts/
现有2套MySQL服务器，需要将一台上的服务器上的数据同步到另一台。可以使用Dgg数据同步校准软件实现数据初始化同步的目的。
打开Dgg软件，点击开始 => 打开， 创建2个MySQL session:
选择源端 并点击下一步，选择目标端 并点击 连接：
可以在两侧的对象树上看到相关的表 , 点中用户 右键 => Transfer:
初始化同步后，若对表数据的一致性存在疑问，可以通过Compare功能比对，在左侧树形种点中你要比对的表，右键Compare:
校验分成几种：
其中行数对比校验 ，仅对比2个表的行数是否一致；即只比较count(1)
主键比对，只比对主键信息是否一致，支持联合主键
全表校验，比对全表数据信息（会排除TEXT LONGTEXT等长字段列）是否一致
对于小表而言（一般来说小于10万行），全表校验的速度也较快，其时间可接受。对于大表而言，除非是一致性要求极高的账目表，否则建议使用主键比对或行数比对。
Oracle 相比 mysql 的优势在哪里 原帖子在这里： https://v2ex.com/t/773654?p=1
目前的项目用的 mysql，支撑几十亿数据没问题（分库分表），偶尔慢 sql 也能优化索引解决，至于复杂查询通过搜索引擎实现，听说 Oracle 超级贵，那 Oracle 具体有什么优势呢，哪些场景下是 oracle only 的？
看完了回复，oracle 能单表支撑几十亿数据还是很强的，因为目前因为分表键导致很多需求无法实现，只能同步一个表用另一个分表键。
我再问下，如果是同时涉及几十个字段的复杂搜索，oracle 可以支持吗，目前用的搜索引擎实现也非常贵。
有一说一，单表支撑几十亿数据，听着有点玄乎，真的假的啊
我的回复：
oracle 的优化器 CBO optimizer 目前应该是所有 RDBMS 里最复杂的（不说是最先进的）。MYSQL 至少在优化器上还处于比较初级的阶段，虽然 MySQL 的目标可能并不希望实现非常复杂的优化器算法。其他一些东西 例如 undo 的实现等等 可能优势并不明显， 这里就不提了。
举一个例子， 都不使用索引的情况下，NO INDEX ! NO INDEX | NO INDEX !
以下数据量是一样的，机器是同一台。 Oracle 11.2.0.4 MySQL 8.0.14
都没有索引的情况下：oracle 使用 0.04 秒， MySQL 等了 10 分钟也没运行完
对于简单的 SQL 而言，差别不会有那么大。对于复杂的 SQL 而言， 能明显体现出优化器的优势。
对于拔高某个技术，没有兴趣。 所以 MYSQL 死忠请勿拍。
Oracle :
SQL> set timing on;
SQL> SELECT c.cust_city,
2 t.calendar_quarter_desc,
3 SUM(s.amount_sold) sales_amount
4 FROM sh.sales s, sh.times t, sh.customers c, sh.channels ch
5 WHERE s.time_id = t.time_id
6 AND s.cust_id = c.cust_id
7 AND s.channel_id = ch.channel_id
8 AND c.cust_state_province = 'FL'
9 AND ch.channel_desc = 'Direct Sales'
10 AND t.calendar_quarter_desc IN ('2000-01', '2000-02','1999-12')
11 GROUP BY c.cust_city, t.calendar_quarter_desc;
CUST_CITY CALENDA SALES_AMOUNT
------------------------------ ------- ------------
Cypress Gardens 2000-01 3545.82
Candler 2000-01 4166.32
Sanibel 2000-02 17908.67
Ocala 2000-02 7081.73
Molino 2000-01 18765.25
Ocala 2000-01 7146.73
Palmdale 2000-02 25829.24
Palmdale 2000-01 37793.44
Molino 2000-02 17144.7
Saint Marks 2000-01 55781.37
Noma 2000-01 33572.55
Evinston 2000-02 62657.21
Candler 2000-02 6493.94
Winter Springs 2000-02 20
Sugarloaf Key 2000-01 12027.66
Saint Marks 2000-02 48858.7
Blountstown 2000-02 38052.58
Sugarloaf Key 2000-02 9659.44
Cypress Gardens 2000-02 4928.93
Evinston 2000-01 53509.69
Blountstown 2000-01 27024.7
Sanibel 2000-01 15870.34
Winter Springs 2000-01 31.46
Noma 2000-02 23903.58
已选择 24 行。
已用时间: 00: 00: 00.04
执行计划
----------------------------------------------------------
Plan hash value: 1865285285
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 607 | 46132 | 955 (2)| 00:00:12 | | |
| 1 | HASH GROUP BY | | 607 | 46132 | 955 (2)| 00:00:12 | | |
|* 2 | HASH JOIN | | 2337 | 173K| 954 (2)| 00:00:12 | | |
| 3 | PART JOIN FILTER CREATE | :BF0000 | 274 | 4384 | 18 (0)| 00:00:01 | | |
|* 4 | TABLE ACCESS FULL | TIMES | 274 | 4384 | 18 (0)| 00:00:01 | | |
|* 5 | HASH JOIN | | 12456 | 729K| 936 (2)| 00:00:12 | | |
| 6 | MERGE JOIN CARTESIAN | | 383 | 14937 | 408 (1)| 00:00:05 | | |
|* 7 | TABLE ACCESS FULL | CHANNELS | 1 | 13 | 3 (0)| 00:00:01 | | |
| 8 | BUFFER SORT | | 383 | 9958 | 405 (1)| 00:00:05 | | |
|* 9 | TABLE ACCESS FULL | CUSTOMERS | 383 | 9958 | 405 (1)| 00:00:05 | | |
| 10 | PARTITION RANGE JOIN-FILTER| | 918K| 18M| 526 (2)| 00:00:07 |:BF0000|:BF0000|
| 11 | TABLE ACCESS FULL | SALES | 918K| 18M| 526 (2)| 00:00:07 |:BF0000|:BF0000|
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("S"."TIME_ID"="T"."TIME_ID")
4 - filter("T"."CALENDAR_QUARTER_DESC"='1999-12' OR "T"."CALENDAR_QUARTER_DESC"='2000-01' OR
"T"."CALENDAR_QUARTER_DESC"='2000-02')
5 - access("S"."CUST_ID"="C"."CUST_ID" AND "S"."CHANNEL_ID"="CH"."CHANNEL_ID")
7 - filter("CH"."CHANNEL_DESC"='Direct Sales')
9 - filter("C"."CUST_STATE_PROVINCE"='FL')
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
1726 consistent gets
0 physical reads
0 redo size
1495 bytes sent via SQL*Net to client
531 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
24 rows processed
SQL> select count(*) from sh.sales;
COUNT(*)
----------
918843
SQL> select * From v$VERSION;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for 64-bit Windows: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
MySQL :
mysql> SELECT c.cust_city,
-> t.calendar_quarter_desc,
-> SUM(s.amount_sold) sales_amount
-> FROM sh.sales s, sh.times t, sh.customers c, sh.channels ch
-> WHERE s.time_id = t.time_id
-> AND s.cust_id = c.cust_id
-> AND s.channel_id = ch.channel_id
-> AND c.cust_state_province = 'FL'
-> AND ch.channel_desc = 'Direct Sales'
-> AND t.calendar_quarter_desc IN ('2000-01', '2000-02','1999-12')
-> GROUP BY c.cust_city, t.calendar_quarter_desc;
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+
| 1 | SIMPLE | ch | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 20.00 | Using where; Using temporary |
| 1 | SIMPLE | t | NULL | ALL | NULL | NULL | NULL | NULL | 1804 | 30.00 | Using where; Using join buffer (Block Nested Loop) |
| 1 | SIMPLE | c | NULL | ALL | NULL | NULL | NULL | NULL | 55065 | 10.00 | Using where; Using join buffer (Block Nested Loop) |
| 1 | SIMPLE | s | NULL | ALL | NULL | NULL | NULL | NULL | 914584 | 0.10 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.14 |
+-----------+
1 row in set (0.00 sec)
mysql> select count(*) from sh.sales;
+----------+
| count(*) |
+----------+
| 918843 |
+----------+
1 row in set (0.96 sec)
