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 的大作中提到】
: 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 的大作中提到】
: a b?
|
b*****d
发帖数: 15 | 4 可以解释一下吗？
谢谢！
【在 B*****g 的大作中提到】
: ab
: ---
: 没事请勿往下看
: 憋了半天，还是抬个杠，desc是reserved word的，所以create table那个script会
: fail
|
B*****g
发帖数: 34098 | 5 解释ab 还是 杠？
【在 b*****d 的大作中提到】
: 可以解释一下吗？
: 谢谢！
|
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 的大作中提到】
: 解释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 的大作中提到】
: 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%'
|
