b*****d 发帖数: 15 | 1 We have the following schema of a table:
create table Test (id number, name varchar2(32), desc varchar2(400));
create index index_test on Test (name);
Which of the following statements will invoke an index scan by oracle
execution plan
a) select * from test where name='name';
b) select * from test where name like 'name%';
c) select * from test where name like '%name';
d) select * from test where name like '%name%' |
a9 发帖数: 21638 | 2 a b?
【在 b*****d 的大作中提到】![](/moin_static193/solenoid/img/up.png) : We have the following schema of a table: : create table Test (id number, name varchar2(32), desc varchar2(400)); : create index index_test on Test (name); : Which of the following statements will invoke an index scan by oracle : execution plan : a) select * from test where name='name'; : b) select * from test where name like 'name%'; : c) select * from test where name like '%name'; : d) select * from test where name like '%name%'
|
B*****g 发帖数: 34098 | 3 ab
---
没事请勿往下看
憋了半天,还是抬个杠,desc是reserved word的,所以create table那个script会
fail
【在 a9 的大作中提到】![](/moin_static193/solenoid/img/up.png) : a b?
|
b*****d 发帖数: 15 | 4 可以解释一下吗?
谢谢!
【在 B*****g 的大作中提到】![](/moin_static193/solenoid/img/up.png) : ab : --- : 没事请勿往下看 : 憋了半天,还是抬个杠,desc是reserved word的,所以create table那个script会 : fail
|
B*****g 发帖数: 34098 | 5 解释ab 还是 杠?
【在 b*****d 的大作中提到】![](/moin_static193/solenoid/img/up.png) : 可以解释一下吗? : 谢谢!
|
B*****g 发帖数: 34098 | 6 其实想一想B-tree的工作原理就明白了
http://docs.oracle.com/cd/E11882_01/server.112/e16638/optimops.
1
11.2.3.3.1 When the Optimizer Uses Index Range Scans
The optimizer uses a range scan when it finds one or more leading columns of
an index specified in conditions, such as the following:
col1 = :b1
col1 < :b1
col1 > :b1
AND combination of the preceding conditions for leading columns in the index
col1 like 'ASD%' wild-card searches should not be in a leading position othe
rwise the condition col1 like '%ASD' does not result in a range scan
Range scans can use unique or non-unique indexes. Range scans avoid sorting
when index columns constitute the ORDER BY/GROUP BY clause.
script会
【在 B*****g 的大作中提到】![](/moin_static193/solenoid/img/up.png) : 解释ab 还是 杠?
|
v***e 发帖数: 2108 | 7 For a and b, you will see a plan like this. Note the index range scan,
----------------------------------------------------------------------------
----------------------
| Id | Operation | Name | Rows | Bytes |
Cost (%CPU)| Time |
----------------------------------------------------------------------------
----------------------
| 0 | SELECT STATEMENT | | 1 | 233 |
1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| TEST | 1 | 233 |
1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INDEX_TEST | 1 | |
1 (0)| 00:00:01 |
----------------------------------------------------------------------------
----------------------
for c and d, it is usually a full table scan,
【在 b*****d 的大作中提到】![](/moin_static193/solenoid/img/up.png) : We have the following schema of a table: : create table Test (id number, name varchar2(32), desc varchar2(400)); : create index index_test on Test (name); : Which of the following statements will invoke an index scan by oracle : execution plan : a) select * from test where name='name'; : b) select * from test where name like 'name%'; : c) select * from test where name like '%name'; : d) select * from test where name like '%name%'
|