1 概述
2 详解
2.1 rownum
select t.empno,
t.ename,
t.hiredate,
rownum
from scott.emp t;
select t.empno,
t.ename,
t.hiredate,
rownum
from scott.emp t
order by t.hiredate;
查询截图:(rownum 不一定是连续的哦)
原因:sql 语句执行顺序:先 select 后 order by
解决:再嵌套查询一次,如:select rownum from (上述)
扩展:Oracle sql 语句执行顺序【面试题】
2.2 rowid
select rowid, -- base 64位 编码,18 个字符显示
-- 数据对象: dba_objects.object_id
dbms_rowid.rowid_object(rowid) object_id,
-- 相对文件: dba_data_files.relative_fno
dbms_rowid.rowid_relative_fno(rowid) relative_fno,
-- 数据块: dba_extents.(block_id + blocks - 1)
dbms_rowid.rowid_block_number(rowid) block_number,
-- 行: dba_tab_columns.column_id
dbms_rowid.rowid_row_number(rowid) row_number
from scott.emp;
查询截图:
解释说明:
base 64 编码
A-Z 0-25
a-z 26-51
0-9 52-61
+/ 62-63
计算公式:d*(b^p)
d: 字符对应的十进制编码值
b: 64
p: 从右至左,每个对象从 0 开始计数
AAAR3sAAEAAAACXAAA
数据对象id(6位):AAAR3s
0*64^5 + 0*64^4 + 0*64^3 + 17*64^2 + 54*64^1 + 44*64^0 = 73196
文件id(3位):AAE
0*64^2 + 0*64^1 + 4*64^0 = 4
块id(6位):AAAACX
2*64^1 + 23 = 151
行id(3位):AAA = 0
base 64 码表:
索引 | 字符 | 索引 | 字符 | 索引 | 字符 | 索引 | 字符 | |||
---|---|---|---|---|---|---|---|---|---|---|
0 | A | 26 | a | 52 | 1 | 62 | + | |||
1 | B | 27 | b | 53 | 2 | 63 | / | |||
2 | C | 28 | c | 54 | 3 | |||||
3 | D | 29 | d | 55 | 4 | |||||
4 | E | 30 | e | 56 | 5 | |||||
5 | F | 31 | f | 57 | 6 | |||||
6 | G | 32 | g | 58 | 7 | |||||
7 | H | 33 | h | 59 | 8 | |||||
8 | I | 34 | i | 60 | 9 | |||||
9 | J | 35 | j | |||||||
10 | K | 36 | k | |||||||
11 | L | 37 | l | |||||||
12 | M | 38 | m | |||||||
13 | N | 39 | n | |||||||
14 | O | 40 | o | |||||||
15 | P | 41 | p | |||||||
16 | Q | 42 | q | |||||||
17 | R | 43 | r | |||||||
18 | S | 44 | s | |||||||
19 | T | 45 | t | |||||||
20 | U | 46 | u | |||||||
21 | V | 47 | v | |||||||
22 | W | 48 | w | |||||||
23 | X | 49 | x | |||||||
24 | Y | 50 | y | |||||||
25 | Z | 51 | z |