Oracle-相关笔记

-- 此条SQL可以非常直观的看到数据库中各个表空间的实时使用率情况
selecta.tablespace_name,                    -- 表空间名称total,                                -- 总大小（MB）free,                                 -- 空闲大小（MB）total - free as used,                 -- 已用大小（MB）ROUND(free / total * 100, 2) AS "FREE%",ROUND((total - free) / total * 100, 2) AS "USED%"from (select tablespace_name, sum(bytes) / 1024 / 1024 as totalfrom dba_data_filesgroup by tablespace_name) a,(select tablespace_name, sum(bytes) / 1024 / 1024 as freefrom dba_free_spacegroup by tablespace_name) b
where a.tablespace_name = b.tablespace_name
order by a.tablespace_name;

SELECTfile_id,file_name,tablespace_name,autoextensible,bytes / 1024 / 1024 AS size_mb,maxbytes / 1024 / 1024 AS max_size_mb
FROMdba_data_files
ORDER BYtablespace_name, file_id;

优化器

本章讨论SQL处理、优化方法以及查询优化器（通常称为优化器）如何选择特定的计划来执行SQL。

Oracle优化器就是用来确定如何执行SQL才是最优的。

优化器是内置软件，可确定执行 SQL 语句的最有效方法。

优化器操作

数据库可以通过多种方式执行SQL语句，例如全表扫描、索引扫描、嵌套循环和哈希连接。

优化器在确定执行计时会考虑与查询中的对象和条件相关的许多因素。此确定是SQL处理中的重要步骤，并且会极大地影响执行时间。

优化器在不同版本的Oracle数据库之间做出的决策可能有所不同。在较新的版本中，优化器可能会做出不同的决策，因为可以获得更准确的信息。

当用户提交SQL语句执行时，优化器执行以下步骤：

1、优化器根据可用的访问路径和提示为 SQL 语句生成一组潜在计划。

2、优化器根据数据字典中的统计信息估算每个计划的成本。统计信息包括语句访问的表、索引和分区的数据分布和存储特性信息。

3、这cost是一个预估值，与使用特定计划执行语句所需的预期资源使用量成比例。优化器会根据预估的计算机资源（包括 I/O、CPU 和内存）来计算访问路径和连接顺序的成本。

成本较高的串行计划比成本较低的串行计划执行时间更长。使用并行计划时，资源使用量与运行时间没有直接关系。

优化器比较各个计划并选择成本最低的计划。

优化器的输出是一个执行计划，它描述了最佳的执行方法。该计划展示了 Oracle 数据库执行 SQL 语句所使用的步骤组合。每个步骤要么从数据库中物理检索行，要么为发出语句的用户准备行。

全表扫描

这种类型的扫描会读取表中的所有行，并过滤掉那些不符合选择条件的行。在全表扫描期间，表中所有位于扫描高水位线。高水位线表示已用空间量，或已格式化为接收数据的空间量。检查每一行以确定其是否满足语句的WHERE子句。

当 Oracle 数据库执行全表扫描时，会按顺序读取数据块。由于数据块相邻，数据库可以进行大于单个数据块的 I/O 调用，以加快读取速度。读取调用的大小范围从一个数据块到初始化参数指定的数据块数。DB_FILE_MULTIBLOCK_READ_COUNT使用多块读取，数据库可以非常高效地执行全表扫描。数据库每个块仅读取一次。

1、为什么全表扫描在访问大量数据时速度更快
当访问表中的大部分数据块时，全表扫描比索引范围扫描的开销更小。全表扫描可能会使用较大的 I/O 调用，而较少的大型 I/O 调用比多次较小的 I/O 调用开销更小。

2、当优化器使用全表扫描时
在以下任一情况下，优化器都会使用全表扫描：

2.1、缺少索引
如果查询无法使用现有索引，也就是索引失效。则使用全表扫描。例如，如果查询中索引列上使用了函数，则优化器无法使用索引，而是使用全表扫描。

如果您需要使用索引进行不区分大小写的搜索，则要么不允许搜索列中出现大小写混合的数据，要么在搜索列上创建基于函数的索引，例如UPPER( )。请参阅“使用基于函数的索引来提高性能”。last_name

2.2、大量数据
如果优化器认为查询需要表中的大多数块，那么它会使用全表扫描，即使索引可用。

2.3、小表
如果表中包含的DB_FILE_MULTIBLOCK_READ_COUNT高水位线以下的块少于数据库可以在单个 I/O 调用中读取的块，则全表扫描可能比索引范围扫描更便宜，无论访问的表或存在的索引的比例如何。

2.4、高度并行
如果表的并行度较高，优化器会倾向于进行全表扫描而非范围扫描。请检查表DEGREE中的列以确定并行度。ALL_TABLES

执行计划

EXPLAIN PLAN FOR
SELECT *
FROM INIT_ADSB_PART
WHERE nowtim BETWEEN TO_DATE('2025-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')AND TO_DATE('2025-01-01 00:01:10', 'YYYY-MM-DD HH24:MI:SS');SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

执行顺序说明（从最底层往上看）：

1. Step 3 - INDEX RANGE SCAN（IDX_NOWTIM_LOCAL）

   • 使用本地索引 IDX_NOWTIM_LOCAL 扫描符合时间范围的记录。

   • 索引范围条件是：

     NOWTIM >= TO_DATE('2025-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') AND NOWTIM <= TO_DATE('2025-01-01 00:01:10', 'YYYY-MM-DD HH24:MI:SS')

   • 命中数据行数预估：2543

2. Step 2 - TABLE ACCESS BY LOCAL INDEX ROWID

   • 使用从索引中获取的 ROWID 回到表中获取完整数据行。

   • 表是分区表，每个分区都有独立的 ROWID 空间（因此叫 LOCAL）。

   • 命中数据行：2465（比索引少可能是过滤了无效数据）

3. Step 1 - PARTITION RANGE SINGLE

   • 表示只访问了第一个分区（Pstart=1, Pstop=1）。

   • 分区裁剪已成功。

   • 意味着 Oracle 根本没去看其它分区。

4. Step 0 - SELECT STATEMENT

   • 汇总所有结果。

分区

文章：

Oracle分区表详解（Oracle Partitioned Tables）_oracle 分区表-CSDN博客

分区允许将表、索引或索引组织表细分为更小的部分，其中每个数据库对象的部分称为一个分区。每个分区都有自己的名称，并且可以选择具有自己的存储特性。

Oracle 中的分区（Partitioning）是一种将大型表或索引划分为多个更小、更易于管理的部分的技术。它是优化大数据量表性能、可维护性和可扩展性的强大手段。

分区表：逻辑上是一个表，物理上被拆分成多个段（segment），称为分区（partition）。

每个分区可以存放一部分数据，Oracle 在访问数据时可以通过分区“跳过不相关的分区”，这叫分区裁剪（partition pruning），可以显著提高查询效率。

何时考虑分区 

当单表数据量随着时间变的越来越大时，会给数据的管理和查询带来不便。我们可以考虑对表进行分区，利用分区表特性将数据分成小块存储，可以大幅提升查询性能，管理便捷性及数据的可用性。

• 数据量大于 几百万行时可以考虑分区。

• 大于 2 GB 的表。

• 包含历史数据的表，其中新数据被添加到最新的分区中。一个典型的例子是历史表，其中只有当前月份的数据可更新，而其他 11 个月的数据是只读的。

• 查询条件中涉及时间、地域、ID 等字段时，可考虑使用分区提升性能。

• 分区字段应该常作为查询条件（WHERE），否则难以裁剪。

• 表中有大量的历史数据，数据存在明显的时间顺序

一般是定时任务+存储过程来创建未来的分区，这个月定时任务触发存储过程创建下个月的分区。

分区的操作

-- 增加分区
ALTER TABLE INIT_ADSB_PART
ADD PARTITION p_202505 VALUES LESS THAN (TO_DATE('2025-06-01', 'YYYY-MM-DD'));-- 删除分区 该操作会删除 p_202501 分区中的所有数据，谨慎操作。
ALTER TABLE INIT_ADSB_PART
DROP PARTITION p_202501;-- 查看分区信息
SELECT TABLE_NAME, PARTITION_NAME, HIGH_VALUE
FROM USER_TAB_PARTITIONS
WHERE TABLE_NAME = 'INIT_ADSB_PART';

已有数据分区

由于表已经有数据，不能直接给普通表加分区，所以我们需要 新建分区表 + 数据迁移。

-- 创建一个新的分区表（例：按月分区）
CREATE TABLE INIT_ADSB_PART (ID NUMBER,NOWTIM DATE,OTHER_COLS VARCHAR2(100),...
)
PARTITION BY RANGE (NOWTIM) (PARTITION p_202501 VALUES LESS THAN (TO_DATE('2025-02-01', 'YYYY-MM-DD')),PARTITION p_202502 VALUES LESS THAN (TO_DATE('2025-03-01', 'YYYY-MM-DD')),PARTITION p_202503 VALUES LESS THAN (TO_DATE('2025-04-01', 'YYYY-MM-DD')),PARTITION p_max     VALUES LESS THAN (MAXVALUE)
);-- 把原表数据迁移过去
INSERT /*+ APPEND */ INTO INIT_ADSB_PART
SELECT * FROM INIT_ADSB;-- 可选：校验数据是否一致
SELECT COUNT(*) FROM INIT_ADSB;
SELECT COUNT(*) FROM INIT_ADSB_PART;

每个 PARTITION 表示一个数据分区，Oracle 会根据 NOWTIM 值将数据自动插入到对应分区：

分区名	包含的数据范围（NOWTIM）
p_202501	< 2025-02-01
p_202502	≥ 2025-02-01 且 < 2025-03-01
p_202503	≥ 2025-03-01 且 < 2025-04-01
p_max	≥ 2025-04-01

ALTER TABLE INIT_ADSB_PART
ADD PARTITION p_202505 VALUES LESS THAN (TO_DATE('2025-06-01', 'YYYY-MM-DD'));

分区的优势

分区裁剪：指的是 Oracle 优化器在执行 SQL 查询时，根据 WHERE 条件，只扫描符合条件的分区，而跳过其他无关分区，从而大幅提升性能。

分区修剪通常可以将查询性能提高几个数量级。例如，假设某个应用程序包含一个Orders包含订单历史记录的表，并且该表已按周分区。请求一周订单的查询只会访问该Orders表的一个分区。如果该Orders表包含两年的历史数据，那么该查询只需访问一个分区，而不是 104 个分区。仅仅因为分区修剪，这个查询的执行速度就有可能提高 100 倍。

索引

Oracle 的 索引（Index） 是一种提高查询性能的数据结构，它类似于书的目录，可以加快数据检索的速度，但也会占用额外空间，并在插入、更新、删除时增加维护成本。

索引的作用

• 加快 SELECT 查询，特别是 WHERE 条件、JOIN、ORDER BY。

• 避免全表扫描（Full Table Scan）。

• 用于约束（如主键、唯一键）实现。

索引类型

1、分区索引分为本地索引和全局索引

本地索引是对单个分区的，每个分区索引只指向一个表分区，全局索引则不然，一个分区所引能指向n个表分区，同时，一个表分区，也可能指向n个索引分区，对分区表中的某个分区TRUNCATE或者MOVE、SHRINK等，可能会影响到n个全局索引分区，正因为这一点，本地索引具有更高的可用性。

在 Oracle 分区表中，本地索引（Local Index）是一种每个分区都有自己对应子索引段的索引。它的索引结构是按照表的分区方式同步划分的，也就是说：一个分区对应一个索引分区。

-- 假设你的表 INIT_ADSB_PART 分为 3 个分区：
INIT_ADSB_PART（分区表）
├── p_202501
├── p_202502
└── p_max-- 如果你创建了一个本地索引：
CREATE INDEX IDX_ADSB_NOWTIM_LOCAL 
ON INIT_ADSB_PART(NOWTIM)
LOCAL;-- 它在物理上等价于创建了 3 个索引段：
IDX_ADSB_NOWTIM_LOCAL（本地索引）
├── IDX_ADSB_NOWTIM_LOCAL_p_202501
├── IDX_ADSB_NOWTIM_LOCAL_p_202502
└── IDX_ADSB_NOWTIM_LOCAL_p_max每个分区索引 只维护自己分区的数据，这样当你查询涉及一个时间段时，Oracle 能：直接跳过不相关的分区（分区裁剪）；只扫描对应分区内的索引段，提高性能。

索引的操作

-- 创建普通 B 树索引
CREATE INDEX idx_emp_name ON emp(name);-- 创建组合索引
CREATE INDEX idx_emp_dept_sal ON emp(deptno, sal);-- 创建函数索引
CREATE INDEX idx_upper_name ON emp(UPPER(name));-- 创建唯一索引
CREATE UNIQUE INDEX idx_emp_id ON emp(empno);

-- 删除索引
DROP INDEX idx_nowtim;-- 查询表的索引
SELECT index_name, table_name, uniqueness, status
FROM user_indexes
WHERE table_name = 'INIT_ADSB';SELECT i.index_name,i.table_name,i.uniqueness,i.status,c.column_name
FROM user_indexes iJOIN user_ind_columns c ON i.index_name = c.index_name
WHERE i.table_name = 'INIT_ADSB_PART';uniqueness
说明：表示索引的唯一性。该字段可以有两种值：
UNIQUE：表示该索引是唯一索引，保证索引列中的值唯一。
NONUNIQUE：表示该索引是非唯一索引，允许索引列中的值重复。

索引失效

查看索引是否生效

• EXPLAIN PLAN FOR ... 用来生成 SQL 查询的执行计划（存入 PLAN_TABLE 中）。

• SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY); 是用来查看刚才那条 SQL 的执行计划。

EXPLAIN PLAN FOR
SELECT * FROM INIT_ADSB WHERE nowtim BETWEEN TO_DATE('2025-04-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE('2025-04-01 00:00:10', 'YYYY-MM-DD HH24:MI:SS');SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

索引和分区

CREATE TABLE INIT_ADSB_PART (ID NUMBER,NOWTIM DATE,OTHER_COLS VARCHAR2(100)
)
PARTITION BY RANGE (NOWTIM) (PARTITION p202504 VALUES LESS THAN (TO_DATE('2025-05-01', 'YYYY-MM-DD')),PARTITION p202505 VALUES LESS THAN (TO_DATE('2025-06-01', 'YYYY-MM-DD')),PARTITION pmax    VALUES LESS THAN (MAXVALUE)
);CREATE INDEX idx_nowtim_local ON INIT_ADSB_PART (NOWTIM) LOCAL;

在 Oracle 中，当你同时使用了分区和索引（尤其是本地索引）后，查询的过程一般如下：

Oracle 执行流程如下：

1. 分区裁剪（Partition Pruning）：

   • Oracle 首先根据 WHERE 条件中的 NOWTIM 值判断哪些分区可能包含匹配数据；

   • 例如：NOWTIM BETWEEN '2025-04-01' AND '2025-04-02'，只会命中某个具体的分区，比如 p202504。

2. 访问目标分区（Partition Access）：

   • Oracle 只访问匹配的分区，而不是整个表（这就是分区带来的性能优势）；

   • 如果你使用了 LOCAL 索引，这些索引是按分区建立的子索引，只会读取目标分区的索引。

3. 索引查找（Index Access）：

   • Oracle 使用目标分区的本地索引（如果存在）进行范围扫描，如 INDEX RANGE SCAN；

   • 然后定位到具体的数据块读取行（即 TABLE ACCESS BY INDEX ROWID）。

4. 返回结果。

Oracle体系结构

Oracle体系结构（14）——Oracle 的数据文件（Data files）与表空间管理_oracle datafile-CSDN博客

Oarcle 数据库真正存放数据的是数据文件，表空间（tablespaces）实际上是一个逻辑的概念，在物理上并不存在。表空间具有如下特点：
（1）一个数据库可以包含多个表空间，一个表空间只能属于一个数据库；
（2）一个表空间包含多个数据文件，一个数据文件只能属于一个表空间。

批量操作

// 在Oracle中想要批量操作某个表的话，可以使用以下方式，
// 但是会报无效字符（the error occurred while setting parameters）
<foreach collection="equipinfos" item="e" separator=";" close=";">update local_equipinfo<set><if test="e.chuSelfCode != null">chu_self_code = #{e.chuSelfCode},</if><if test="e.selfCode != null">self_code = #{e.selfCode},</if></set>where ID = #{e.id}
</foreach>// 解决方式：使用begin end；将要执行批量操作的sql语句包装成sql语句块。// 但是要注意：在Oracle中，当使用PL/Sql语句块执行更新操作时（其他的还不知道），返回值为-1是正常的。begin
<foreach collection="equipinfos" item="e" separator=";" close=";">update local_equipinfo<set><if test="e.chuSelfCode != null">chu_self_code = #{e.chuSelfCode},</if><if test="e.selfCode != null">self_code = #{e.selfCode},</if></set>where ID = #{e.id}</foreach>
end;

批量插入 

<insert id="batchInsert" >begin<foreach collection="equipmentCountInfoList" item="eci" separator=";" close=";">insert into equipment_count_info<trim prefix="(" suffix=")" suffixOverrides=",">id,<if test="eci.equipmentCode != null and eci.equipmentCode != ''">equipment_code,</if><if test="eci.countPos != null">count_pos,</if><if test="eci.countTime != null">count_time,</if><if test="eci.countTaskId != null and eci.countTaskId != ''">count_task_id,</if><if test="eci.countUser != null">count_user,</if><if test="eci.countComm != null">count_comm,</if><if test="eci.scanCode != null">scan_code,</if><if test="eci.countCabiCode != null">count_cabi_code,</if><if test="eci.countCabiName != null">count_cabi_name,</if><if test="eci.countStatus != null">count_status,</if></trim><trim prefix="values (" suffix=")" suffixOverrides=",">equipment_count_info_seq.nextval,<if test="eci.equipmentCode != null and eci.equipmentCode != ''">#{eci.equipmentCode},</if><if test="eci.countPos != null">#{eci.countPos},</if><if test="eci.countTime != null">#{eci.countTime},</if><if test="eci.countTaskId != null and eci.countTaskId != ''">#{eci.countTaskId},</if><if test="eci.countUser != null">#{eci.countUser},</if><if test="eci.countComm != null">#{eci.countComm},</if><if test="eci.scanCode != null">#{eci.scanCode},</if><if test="eci.countCabiCode != null">#{eci.countCabiCode},</if><if test="eci.countCabiName != null">#{eci.countCabiName},</if><if test="eci.countStatus != null">#{eci.countStatus},</if></trim></foreach>end;</insert>

<insert id="insertBatch">INSERT INTO MATERIAL (ID, MATERIAL_NAME, MODEL, CLASS_ID, UNIT, NUM, UPDATE_TIME, UNIT_CODE, STORE_CODE, PRICE)SELECT MATERIAL_SEQUENCE.nextval ID,A.MATERIAL_NAME,A.MODEL,A.CLASS_ID,A.UNIT,A.NUM,A.UPDATE_TIME,A.UNIT_CODE,A.STORE_CODE,A.PRICEFROM (<foreach collection="materialDtoList" item="material" index="index" separator="UNION ALL">select#{material.materialName} MATERIAL_NAME,#{material.model} MODEL,#{material.classId} CLASS_ID,#{material.unit} UNIT,<choose><when test="material.num != null and material.num != ''">#{material.num} NUM,</when><otherwise>0 NUM,</otherwise></choose>sysdate UPDATE_TIME,#{material.unitCode} UNIT_CODE,#{material.storeCode} STORE_CODE,#{material.price} PRICEfrom dual</foreach>) A</insert>

private void myBatchInsertEquipCountInfo(List<EquipmentCountInfo> equipmentCountInfoList) {Connection connection = null;PreparedStatement preparedStatement = null;try {// 1. 连接到数据库connection = DriverManager.getConnection(dataUrl, dataUser, dataPwd);connection.setAutoCommit(false);// 2. 创建一个PreparedStatement，包含要执行的插入SQL语句String insertSQL = "INSERT INTO equipment_count_info " +"(ID, equipment_code, count_pos, count_time, count_task_id, " +"count_user, count_comm, scan_code, count_cabi_code, count_cabi_name, count_status) " +"VALUES " +"(equipment_count_info_seq.nextval, ?, ?, ?, ?, ?, ?, ?, ?, " +"?, ?)";preparedStatement = connection.prepareStatement(insertSQL);// 3. 创建要插入的数据列表// 假设你有一个List<YourEntity> entities 包含要插入的数据// 4. 遍历数据列表，将数据添加到批处理中for (EquipmentCountInfo entity : equipmentCountInfoList) {int num = 1;preparedStatement.setString(num++, getValueOrNullString(entity.getEquipmentCode()));preparedStatement.setString(num++, getValueOrNullString(entity.getCountPos()));preparedStatement.setDate(num++, (java.sql.Date) entity.getCountTime());preparedStatement.setString(num++, getValueOrNullString(entity.getCountTaskId()));preparedStatement.setString(num++, getValueOrNullString(entity.getCountUser()));preparedStatement.setString(num++, getValueOrNullString(entity.getCountComm()));preparedStatement.setString(num++, getValueOrNullString(entity.getScanCode()));preparedStatement.setString(num++, getValueOrNullString(entity.getCountCabiCode()));preparedStatement.setString(num++, getValueOrNullString(entity.getCountCabiName()));preparedStatement.setString(num++, getValueOrNullString(entity.getCountStatus()));preparedStatement.addBatch();}// 5. 执行批处理插入操作int[] batchResult = preparedStatement.executeBatch();// 6. 处理批处理的结果// batchResult 数组包含了每个插入语句的执行结果，通常返回1表示成功，返回-2表示成功但没有行数返回。// 7. 提交事务connection.commit();} catch (SQLException e) {// 处理异常e.printStackTrace();try {if (connection != null) {connection.rollback(); // 如果发生异常，回滚事务}} catch (SQLException rollbackException) {rollbackException.printStackTrace();}} finally {// 8. 关闭连接和PreparedStatementtry {if (preparedStatement != null) {preparedStatement.close();}if (connection != null) {connection.close();}} catch (SQLException closeException) {closeException.printStackTrace();}}}

一些SQL

查询 Oracle 中表 INIT_ADSB_PART 在 USERS 表空间上的 物理存储分配情况，包括每个 extent 的文件号、起始块号和大小（以字节为单位）。

SELECT segment_name, tablespace_name, file_id, block_id, bytes
FROM dba_extents
WHERE segment_name = 'INIT_ADSB_PART' AND tablespace_name = 'USERS';

这些表示该表在数据文件中使用的物理空间情况。 

字段名	含义说明
SEGMENT_NAME	对象名称（如表、索引名）。这里是 'INIT_ADSB_PART' 表
TABLESPACE_NAME	表所在的表空间名。这里是 'USERS'
FILE_ID	表空间中数据文件的 ID（对应数据文件 *.dbf 文件）
BLOCK_ID	该 extent 的起始块号（在该文件中的起始逻辑块编号）
BYTES	此 extent 的大小，单位是 字节

一些问题

cx_Oracle.DatabaseError: ORA-01688: 表 LIN.INIT_ADSB_DAY_PART 分区 SYS_P48 无法通过 1024 (在表空间 USERS 中) 扩展 

表示 表 LIN.INIT_ADSB_DAY_PART 的某个分区（SYS_P48）尝试扩展（写入更多数据）时失败了，因为所在的表空间 USERS 没有足够的空间可用（无法分配 1024 个 Oracle 块大小的空间）。

表空间对应的数据文件达到了最大大小，无法继续扩展写入了。