指导学习oracle

### Oracle 数据库学习指南 #### 一、通过索引提高查询效率 在Oracle数据库中，索引是一种重要的数据结构，用于提高数据检索的性能。它类似于书籍中的索引，可以帮助快速定位到特定的数据行。 ##### 创建表与批量插入数据 ```sql CREATE TABLE dumpy ( id NUMBER(10), name VARCHAR2(10), rand NUMBER(10, 2) ); DECLARE recordCount INTEGER := 1000000; -- 100万条记录 BEGIN FOR i IN 1 .. recordCount LOOP INSERT INTO dumpy (id, name, rand) VALUES (i, dbms_random.string('x', 8), abs(dbms_random.value)/100.0); IF MOD(i, 1000) = 0 THEN COMMIT; -- 每插入1000条提交 END IF; END LOOP; END; / ``` 在这段代码中，我们首先创建了一个名为 `dumpy` 的表，其中包含三个字段：`id`（主键）、`name` 和 `rand`。接着，使用一个PL/SQL块来插入100万条随机数据，为了提高性能，每插入1000条数据就进行一次提交。 ##### 测试查询性能 为了测试查询效率，可以运行以下SQL命令： ```sql SET TIMING ON SELECT * FROM dumpy WHERE rand BETWEEN 1000 AND 2000; ``` 首次执行时查询可能较慢，这是因为Oracle需要构建执行计划并执行查询。随着缓存的建立，后续相同的查询将更快。 ##### 创建索引 创建索引后，上述查询的性能将得到显著提升： ```sql CREATE INDEX ind_dumpy_rand ON dumpy (rand); ``` 再次执行相同的查询，可以看到执行时间显著减少，通常效率至少提高了一倍。 #### 二、Oracle中删除重复的行 在Oracle数据库中处理重复记录是常见的需求之一。 ##### 查找重复记录 使用以下SQL语句可以查找具有重复 `id` 的记录： ```sql SELECT id, COUNT(*) FROM dumpy GROUP BY id HAVING COUNT(*) > 1; ``` 或者更简洁地： ```sql SELECT id FROM dumpy GROUP BY id HAVING COUNT(*) > 1; ``` 这两种方式都能有效地找出重复的 `id`。 ##### 删除重复记录 有多种方法可以用来删除重复的记录： 1. **使用ROWID** ```sql DELETE FROM dumpy WHERE rowid IN ( SELECT a.rowid FROM dumpy a, dumpy b WHERE a.id = b.id AND a.rowid > b.rowid ); ``` 2. **使用子查询** ```sql DELETE FROM dumpy d WHERE d.rowid > ( SELECT MIN(x.rowid) FROM dumpy x WHERE d.id = x.id ); ``` **方法1** 的效率通常更高，因为它不依赖于额外的索引。**方法2** 在建立了适当的索引时效率较高。 #### 三、Oracle实现分页提取数据 在Oracle中实现数据分页可以采用不同的方法： 1. **使用ROWNUM** ```sql SELECT * FROM ( SELECT ROWNUM rn, d.* FROM dumpy d WHERE ROWNUM <= 20 ) WHERE rn >= 11; ``` 2. **使用ROW_NUMBER()** ```sql SELECT * FROM ( SELECT ROW_NUMBER() OVER (ORDER BY id) AS rn, d.* FROM dumpy d ) WHERE rn BETWEEN 11 AND 20; ``` 3. **使用MINUS操作符** ```sql SELECT * FROM dumpy WHERE ROWNUM <= 20 MINUS SELECT * FROM dumpy WHERE ROWNUM < 11; ``` **方法1** 在查询前几页时速度较快，但在数据量很大时查询最后几页的速度较慢。**方法2** 查询效率更为稳定。**方法3** 只适用于查询结果在200行以内的场景，数据量大时可能会导致Oracle错误。 在Oracle数据库的学习过程中，理解如何利用索引来优化查询性能、如何处理重复数据以及如何实现高效的数据分页是非常重要的。这些技巧不仅有助于提高系统的整体性能，也能在面试和技术交流中展示出扎实的技术功底。