请问什么是驱动表 驱动表是指的是在RBO(基于规则的优化器)模式下才有这一说的.其实只是一个表放在from后面的一个顺序而已. 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 你看一下相关sql tuning方面的知识.就能明白了. 转载biti的一段话:举例,表连接返回一条记录存在两个表,一个 10条记录 ,一个1000万条记录若2表都存在连接字段索引,若以小表为驱动表,则代价: 10* (通过索引在大表查询一条记录的代价) 若以大表为驱动表:1000万 * (通过索引在小表中查询一条记录的代价)通过索引获取一条记录,10rows的表,代价通常在 3 blocks 索引2块,表一块而如果是1000万的表,索引可能达到4块表一块这样一来参考上面的计算,你说哪个更好?很显然!小表查询参考SQL> create table test as select * from all_objects where rownum < 11;Table created.SQL> create index test_index on test(object_id);Index created.SQL> select object_id from test;OBJECT_ID----------1815977814841198912254917099177124287101071913510 rows selected.Execution Plan----------------------------------------------------------0 SELECT STATEMENT Optimizer=CHOOSE1 0 TABLE ACCESS (FULL) OF 'TEST'Statistics----------------------------------------------------------0 recursive calls12 db block gets6 consistent gets0 physical reads0 redo size736 bytes sent via SQL*Net to client425 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)10 rows processedSQL> select * from test where object_id = 4287;OWNER OBJECT_NAME------------------------------ ------------------------------SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE------------------------------ ---------- -------------- ------------------CREATED LAST_DDL_ TIMESTAMP STATUS T G S--------- --------- ------------------- ------- - - -SYS /1033c8a_SqlTypeWithMethods4287 JAVA CLASS14-NOV-00 03-JUL-03 2003-07-03:11:18:19 INVALID N N NExecution Plan----------------------------------------------------------0 SELECT STATEMENT Optimizer=CHOOSE1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST'2 1 INDEX (RANGE SCAN) OF 'TEST_INDEX' (NON-UNIQUE)Statistics----------------------------------------------------------0 recursive calls0 db block gets3 consistent gets0 physical reads0 redo size1157 bytes sent via SQL*Net to client425 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)1 rows processedSQL> 一般我提问的帖子,见者有分。。 oracle 怎么在同一个数据库里把一张表的数据插入另一张表 请教一个关于oracle数据库备份导出的问题 如何用C++开发oracle程序? Oracle存储过程,将“表”作为参数传入的问题 救救我吧,要崩溃了! ORACLE8.1.7监听出现的奇怪问题 8i里列出所有的表,怎么写? 在oracle里怎样实现auto_increment功能 关于JOB的概念与用法 ◆要选出最后的一条记录◆ 紧急求助,oracleorahome92tnslistener不能启动. 急
存在两个表,一个 10条记录 ,一个1000万条记录若2表都存在连接字段索引,若以小表为驱动表,则
代价:
10* (通过索引在大表查询一条记录的代价) 若以大表为驱动表:1000万 * (通过索引在小表中查询一条记录的代价)通过索引获取一条记录,10rows的表,代价通常在 3 blocks
索引2块,表一块而如果是1000万的表,索引可能达到4块表一块
这样一来参考上面的计算,你说哪个更好?很显然!小表查询参考SQL> create table test as select * from all_objects where rownum < 11;Table created.SQL> create index test_index on test(object_id);Index created.SQL> select object_id from test;OBJECT_ID
----------
18159
7781
4841
19891
22549
17099
17712
4287
10107
1913510 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (FULL) OF 'TEST'
Statistics
----------------------------------------------------------
0 recursive calls
12 db block gets
6 consistent gets
0 physical reads
0 redo size
736 bytes sent via SQL*Net to client
425 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processedSQL> select * from test where object_id = 4287;OWNER OBJECT_NAME
------------------------------ ------------------------------
SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
------------------------------ ---------- -------------- ------------------
CREATED LAST_DDL_ TIMESTAMP STATUS T G S
--------- --------- ------------------- ------- - - -
SYS /1033c8a_SqlTypeWithMethods
4287 JAVA CLASS
14-NOV-00 03-JUL-03 2003-07-03:11:18:19 INVALID N N NExecution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'TEST'
2 1 INDEX (RANGE SCAN) OF 'TEST_INDEX' (NON-UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
1157 bytes sent via SQL*Net to client
425 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processedSQL>