y********o
发帖数: 2565 | 1 比如说：
select last_name from employees where upper(last_name) like 'JO%';
有没有创建一个index在last_name上，我们肉眼根本看不出区别的吧，是不是？ |
t*****g
发帖数: 1275 | 2 select * from all_indexes where table_name = EMPLOYEES |
y********o
发帖数: 2565 | 3 I don't know what you are suggesting, though.
I did find the index I created in all_indexes:
OWNER INDEX_NAME INDEX_TYPE TABLE_OWNER TABLE_NAME |
q**1
发帖数: 193 | 4 MySQL一般用explain
explain select last_name from employees where ******;
会输出一个表，可以看出你的 SQL 命令到底有没有用到indexes
【在 y********o 的大作中提到】
: 比如说：
: select last_name from employees where upper(last_name) like 'JO%';
: 有没有创建一个index在last_name上，我们肉眼根本看不出区别的吧，是不是？
|
y********o
发帖数: 2565 | 5 噢，这个我知道，在Oracle里面做：
set autotrace on explain
我可以看到oracle 确实用了lastnameindex: |
y********o
发帖数: 2565 | 6 其实我不太懂oracle的这个 execution plan.
(
2
2
【在 y********o 的大作中提到】
: 噢，这个我知道，在Oracle里面做：
: set autotrace on explain
: 我可以看到oracle 确实用了lastnameindex:
|
s**o
发帖数: 584 | 7 You actually don't have any benefit of index by using 'like'.
If you use select * from employees where upper(last_name) like 'JO%';
It will be gaurantee a full table access.
In your statement,
select last_name from employees where upper(last_name) like 'JO%';
Most likely, if you have an index built on last_name, you will have full
index scan which doesn't do any better performance.
avoid to use 'like' unless you absolutely have to or the table is small and
it's definitely not scalable in the fut
【在 y********o 的大作中提到】
: 比如说：
: select last_name from employees where upper(last_name) like 'JO%';
: 有没有创建一个index在last_name上，我们肉眼根本看不出区别的吧，是不是？
|
y********o
发帖数: 2565 | 8 So, we will only benefit from index if we use the equal operator (=), right?
and
【在 s**o 的大作中提到】
: You actually don't have any benefit of index by using 'like'.
: If you use select * from employees where upper(last_name) like 'JO%';
: It will be gaurantee a full table access.
: In your statement,
: select last_name from employees where upper(last_name) like 'JO%';
: Most likely, if you have an index built on last_name, you will have full
: index scan which doesn't do any better performance.
: avoid to use 'like' unless you absolutely have to or the table is small and
: it's definitely not scalable in the fut
|
q**1
发帖数: 193 | 9 I think the main problem is the upper() function, since you indexed
the value of "last_name" instead of the value of upper(last_name).
"like" clause should be OK. coz you use "A%" instead of "%A" or
"%A%", you can use index anyway.
my 2 cents,
right?
【在 y********o 的大作中提到】
: So, we will only benefit from index if we use the equal operator (=), right?
:
: and
|
y********o
发帖数: 2565 | 10 Saho, your 2 cents are in order.
【在 q**1 的大作中提到】
: I think the main problem is the upper() function, since you indexed
: the value of "last_name" instead of the value of upper(last_name).
: "like" clause should be OK. coz you use "A%" instead of "%A" or
: "%A%", you can use index anyway.
: my 2 cents,
:
: right?
|
q**1
发帖数: 193 | 11 that is for MySQL, I dont know Oracle..They may have very
different indexing strategies.
【在 y********o 的大作中提到】
: Saho, your 2 cents are in order.
|
s**o
发帖数: 584 | 12 you are right on that. 'A%' is a little bit special, you will get a index
range scan. The statement give him an index full scan due to "Select last_
name from ...". It will give a index range scan if the upper() function
index is built or the upper() clause is removed.
【在 q**1 的大作中提到】
: I think the main problem is the upper() function, since you indexed
: the value of "last_name" instead of the value of upper(last_name).
: "like" clause should be OK. coz you use "A%" instead of "%A" or
: "%A%", you can use index anyway.
: my 2 cents,
:
: right?
|
