Oracle sql性能优化定理 1、选择最有效率的表名顺序：ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名，FROM子句中写在最后的表(基础表 driving table)将被最先处理，在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表。测试通过~2、 WHERE子句中的连接顺序性能优化oraclesql 解决方案 » 免费领取超大流量手机卡，每月29元包185G流量+100分钟通话, 中国电信官方发货 2、 WHERE子句中的连接顺序越有效筛选数据的条件放在越右边，连接表条件放在最左边 前面我说的可能不太准确，我刚才测试了一下，11g下from 后表名的顺便不会影响查询的速度贴出执行计划如下：select count(1) from a;已用时间: 00: 00: 00.09执行计划---------------------------------------------------------- Plan hash value: 3918351354 ------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | ------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 3 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS FULL| A | 4 | 3 (0)| 00:00:01 | ------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement 统计信息---------------------------------------------------------- 4 recursive calls 0 db block gets 15 consistent gets 0 physical reads 0 redo size 419 bytes sent via SQL*Net to client 416 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> select count(1) from temp_a;已用时间: 00: 00: 00.01执行计划---------------------------------------------------------- Plan hash value: 2773624539 --------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | --------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 9 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | | | | 2 | TABLE ACCESS FULL| TEMP_A | 16388 | 9 (0)| 00:00:01 | --------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement 统计信息---------------------------------------------------------- 4 recursive calls 0 db block gets 63 consistent gets 0 physical reads 0 redo size 421 bytes sent via SQL*Net to client 416 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> select * from a,temp_a;已选择65552行。已用时间: 00: 00: 01.15执行计划---------------------------------------------------------- Plan hash value: 3066928336 ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 65552 | 3968K| 36 (3)| 00:00:01 | | 1 | MERGE JOIN CARTESIAN| | 65552 | 3968K| 36 (3)| 00:00:01 | | 2 | TABLE ACCESS FULL | A | 4 | 124 | 3 (0)| 00:00:01 | | 3 | BUFFER SORT | | 16388 | 496K| 33 (4)| 00:00:01 | | 4 | TABLE ACCESS FULL | TEMP_A | 16388 | 496K| 8 (0)| 00:00:01 | ------------------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement 统计信息---------------------------------------------------------- 7 recursive calls 0 db block gets 81 consistent gets 0 physical reads 0 redo size 1232628 bytes sent via SQL*Net to client 48486 bytes received via SQL*Net from client 4372 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 65552 rows processed SQL> select * from temp_a,a;已选择65552行。已用时间: 00: 00: 00.90执行计划---------------------------------------------------------- Plan hash value: 3066928336 ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 65552 | 3968K| 36 (3)| 00:00:01 | | 1 | MERGE JOIN CARTESIAN| | 65552 | 3968K| 36 (3)| 00:00:01 | | 2 | TABLE ACCESS FULL | A | 4 | 124 | 3 (0)| 00:00:01 | | 3 | BUFFER SORT | | 16388 | 496K| 33 (4)| 00:00:01 | | 4 | TABLE ACCESS FULL | TEMP_A | 16388 | 496K| 8 (0)| 00:00:01 | ------------------------------------------------------------------------------- Note ----- - dynamic sampling used for this statement 统计信息---------------------------------------------------------- 7 recursive calls 0 db block gets 81 consistent gets 0 physical reads 0 redo size 1232628 bytes sent via SQL*Net to client 48486 bytes received via SQL*Net from client 4372 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 65552 rows processed 楼上测试时有没有清除sql缓存？ 删除数据问题 求清除数据库某张表中冗余数据的sql语句 oracle访问问题 ORACLE 存储过程同步的问题 Pro *C编程，多线程处理 求教一个触发器~~~~~~~每过一天更新表中记录，在线等~~~ 连接远程Oracle服务器出现如下错误 - ORA-12541: TNS:no listensr, 如何解决？ 有关动态语句 紧急求救 请ORACLE的技术支持回答 奇怪SSH用户等效性配置问题 急啊，Oracle监听程序启动不了
越有效筛选数据的条件放在越右边，连接表条件放在最左边
前面我说的可能不太准确，我刚才测试了一下，11g下from 后表名的顺便不会影响查询的速度
贴出执行计划如下：select count(1) from a;已用时间: 00: 00: 00.09执行计划
----------------------------------------------------------
Plan hash value: 3918351354
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| A | 4 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
4 recursive calls
0 db block gets
15 consistent gets
0 physical reads
0 redo size
419 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed SQL> select count(1) from temp_a;已用时间: 00: 00: 00.01执行计划
----------------------------------------------------------
Plan hash value: 2773624539
---------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| TEMP_A | 16388 | 9 (0)| 00:00:01 |
---------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
4 recursive calls
0 db block gets
63 consistent gets
0 physical reads
0 redo size
421 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed SQL> select * from a,temp_a;已选择65552行。已用时间: 00: 00: 01.15执行计划
----------------------------------------------------------
Plan hash value: 3066928336
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 65552 | 3968K| 36 (3)| 00:00:01 |
| 1 | MERGE JOIN CARTESIAN| | 65552 | 3968K| 36 (3)| 00:00:01 |
| 2 | TABLE ACCESS FULL | A | 4 | 124 | 3 (0)| 00:00:01 |
| 3 | BUFFER SORT | | 16388 | 496K| 33 (4)| 00:00:01 |
| 4 | TABLE ACCESS FULL | TEMP_A | 16388 | 496K| 8 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
7 recursive calls
0 db block gets
81 consistent gets
0 physical reads
0 redo size
1232628 bytes sent via SQL*Net to client
48486 bytes received via SQL*Net from client
4372 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
65552 rows processed SQL> select * from temp_a,a;已选择65552行。已用时间: 00: 00: 00.90执行计划
----------------------------------------------------------
Plan hash value: 3066928336
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 65552 | 3968K| 36 (3)| 00:00:01 |
| 1 | MERGE JOIN CARTESIAN| | 65552 | 3968K| 36 (3)| 00:00:01 |
| 2 | TABLE ACCESS FULL | A | 4 | 124 | 3 (0)| 00:00:01 |
| 3 | BUFFER SORT | | 16388 | 496K| 33 (4)| 00:00:01 |
| 4 | TABLE ACCESS FULL | TEMP_A | 16388 | 496K| 8 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
统计信息
----------------------------------------------------------
7 recursive calls
0 db block gets
81 consistent gets
0 physical reads
0 redo size
1232628 bytes sent via SQL*Net to client
48486 bytes received via SQL*Net from client
4372 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
65552 rows processed