如何从有几万条记录的数据表中快速查找到自己需要的数据 在Oracle(或者其他数据库管理系统)中,如何从数万条记录中,快速地找到自己需要的记录? 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 一看就是菜鸟,几万行记录还多么?select *where ...-- 加where条件呗,你想要什么样的记录,就加什么样的where条件! 对一楼的回复:似乎不是你所表达的这么简单,似乎有一些更加高效的查找技术,假如有十万条记录,必须保证在3秒内得到查询结果,在不考虑机器硬件配置的情况下,有什么其他技术能达到这种要求吗?(要找到自己想要的记录,肯定需要where子句,但是where只能保证实现这个功能,好像并不能保证效率特别高)对二楼的回复:除了加索引,还有其他方法吗? 需求不明确怎么说呢,索引是最有效的,ROWID最快,适合你么? 优化一下就行了,尽量把能过滤条数比较大的检索条件放到最下面,并且加索引,用执行计划分析SQL,并且调整 回复4楼:你所说的“ROWID”是不是像下面的例子一样?[SCOTT@orcl] SQL>select rowid,t.empno,t.ename from emp t;ROWID EMPNO ENAME------------------ ---------- ----------AAAMgzAAEAAAAAgAAA 7369 SMITHAAAMgzAAEAAAAAgAAB 7499 ALLENAAAMgzAAEAAAAAgAAC 7521 WARDAAAMgzAAEAAAAAgAAD 7566 JONESAAAMgzAAEAAAAAgAAE 7654 MARTINAAAMgzAAEAAAAAgAAF 7698 BLAKEAAAMgzAAEAAAAAgAAG 7782 CLARKAAAMgzAAEAAAAAgAAH 7788 SCOTTAAAMgzAAEAAAAAgAAI 7839 KINGAAAMgzAAEAAAAAgAAJ 7844 TURNERAAAMgzAAEAAAAAgAAK 7876 ADAMSAAAMgzAAEAAAAAgAAL 7900 JAMESAAAMgzAAEAAAAAgAAM 7902 FORDAAAMgzAAEAAAAAgAAN 7934 MILLER --你可以查询[SCOTT@orcl] SQL>select empno,ename,job from emp where rowid = 'AAAMgzAAEAAAAAgAAN'; EMPNO ENAME JOB---------- ---------- --------- 7934 MILLER CLERK 看了这个例子,我对ROWID有了一点了解了,我想问的是,还有比这个方法更快的其他方法吗? select tt.* from (select rownum rn,t.* from Table t where rownum<20) tt where rn>10 LZ还是先看看oracle基本原理和结构吧。这样的问题实在不具备讨论性。特别是这个话说得很有意思:“用什么方法才能让查询时间极短?比一般的查询快很多很多,比加上索引还快,也就是说,用了这种方法之后,查询速度提升得非常显著”有点像这个梦想:“用什么方法才能让我明天买彩票中个两个亿” 使用rowid的时候,一定要注意oracle数据库的版本,不是使用rowid就能提高sql的性能,下面举个例子SQL> select * from emp;14 rows selected.Execution Plan----------------------------------------------------------Plan hash value: 3956160932--------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 14 | 518 | 3 (0)| 00:00:01 || 1 | TABLE ACCESS FULL| EMP | 14 | 518 | 3 (0)| 00:00:01 |--------------------------------------------------------------------------Statistics---------------------------------------------------------- 0 recursive calls 0 db block gets 4 consistent gets 0 physical reads 0 redo size 1534 bytes sent via SQL*Net to client 492 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 14 rows processedSQL> select * from emp where rowid in ('AAAM7oAAGAAAADMAAA','AAAM7oAAGAAAADMAAB','AAAM7oAAGAAAADMAAC','AAAM7oAAGAAAADMAAD','AAAM7oAAGAAAADMAAE');Execution Plan----------------------------------------------------------Plan hash value: 1106538681------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 37 | 1 (0)| 00:00:01 || 1 | INLIST ITERATOR | | | | | || 2 | TABLE ACCESS BY USER ROWID| EMP | 1 | 37 | 1 (0)| 00:00:01 |------------------------------------------------------------------------------------Statistics---------------------------------------------------------- 1 recursive calls 0 db block gets 5 consistent gets 0 physical reads 0 redo size 1194 bytes sent via SQL*Net to client 492 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 5 rows processed第一个sql语句查询emp所有的数据记录需要4次consistent gets,全表14条记录,而第二条sql使用rowid做过滤条件,查询4条记录,却需要5 consistent gets,这是因为在oracle10G版本里面,有多少rowid做查询条件,就会有多少次consistent gets Oracle SQL连接问题 关于在磁盘组中删除其中一块磁盘 请教Oracle两个数据表信息同步(复制)问题 异构表之间数据互导问题 关于oracle的一个最简单问题 oracle 用loop遍历游标,每次总会多走一遍的问题。。。什么情况- -@ 在使用sql*loader时,中文乱码导致的一个错误,请教大家是否遇到过同样的问题 出现报错:<多步操作产生错误,请检查每一步的状态值> 我个问题,请指教。 新手入门,疑问求解!!!(基础) 模拟远程登录Oracle oracle学习请教
select *
where ...-- 加where条件呗,你想要什么样的记录,就加什么样的where条件!
[SCOTT@orcl] SQL>select rowid,t.empno,t.ename from emp t;
ROWID EMPNO ENAME
------------------ ---------- ----------
AAAMgzAAEAAAAAgAAA 7369 SMITH
AAAMgzAAEAAAAAgAAB 7499 ALLEN
AAAMgzAAEAAAAAgAAC 7521 WARD
AAAMgzAAEAAAAAgAAD 7566 JONES
AAAMgzAAEAAAAAgAAE 7654 MARTIN
AAAMgzAAEAAAAAgAAF 7698 BLAKE
AAAMgzAAEAAAAAgAAG 7782 CLARK
AAAMgzAAEAAAAAgAAH 7788 SCOTT
AAAMgzAAEAAAAAgAAI 7839 KING
AAAMgzAAEAAAAAgAAJ 7844 TURNER
AAAMgzAAEAAAAAgAAK 7876 ADAMS
AAAMgzAAEAAAAAgAAL 7900 JAMES
AAAMgzAAEAAAAAgAAM 7902 FORD
AAAMgzAAEAAAAAgAAN 7934 MILLER --你可以查询
[SCOTT@orcl] SQL>select empno,ename,job from emp where rowid = 'AAAMgzAAEAAAAAgAAN'; EMPNO ENAME JOB
---------- ---------- ---------
7934 MILLER CLERK 看了这个例子,我对ROWID有了一点了解了,我想问的是,还有比这个方法更快的其他方法吗?
“用什么方法才能让查询时间极短?比一般的查询快很多很多,比加上索引还快,也就是说,用了这种方法之后,查询速度提升得非常显著”有点像这个梦想:“用什么方法才能让我明天买彩票中个两个亿”
SQL> select * from emp;14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 14 | 518 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| EMP | 14 | 518 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
1534 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
14 rows processed
SQL> select * from emp where rowid in ('AAAM7oAAGAAAADMAAA','AAAM7oAAGAAAADMAAB','AAAM7oAAGAAAADMAAC','AAAM7oAAGAAAADMAAD','AAAM7oAAGAAAADMAAE');
Execution Plan
----------------------------------------------------------
Plan hash value: 1106538681------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 37 | 1 (0)| 00:00:01 |
| 1 | INLIST ITERATOR | | | | | |
| 2 | TABLE ACCESS BY USER ROWID| EMP | 1 | 37 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
1194 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
5 rows processed
第一个sql语句查询emp所有的数据记录需要4次consistent gets,全表14条记录,而第二条sql使用rowid做过滤条件,查询4条记录,却需要5 consistent gets,这是因为在oracle10G版本里面,有多少rowid做查询条件,就会有多少次consistent gets