问一个Oralce index的问题 - 未名空间MITBBS历史存档

国际科技财经博客移民网络热点娱乐民生时事公众号

Redian新闻

>未名空间

>Database - 数据库

问一个Oralce index的问题

问一个Oralce index的问题# Database - 数据库

y*o2007-05-18 07:05

1 楼

比如说：
select last_name from employees where upper(last_name) like 'JO%';
有没有创建一个index在last_name上，我们肉眼根本看不出区别的吧，是不是？

t*g2007-05-18 07:05

2 楼

select * from all_indexes where table_name = EMPLOYEES

y*o2007-05-18 07:05

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*12007-05-18 07:05

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*o2007-05-18 07:05

5 楼

噢，这个我知道，在Oracle里面做：
set autotrace on explain
我可以看到oracle 确实用了lastnameindex:

y*o2007-05-18 07:05

6 楼

其实我不太懂oracle的这个 execution plan.

(
2
2

【在 y********o 的大作中提到】

: 噢，这个我知道，在Oracle里面做：
: set autotrace on explain
: 我可以看到oracle 确实用了lastnameindex:

s*o2007-05-18 07:05

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*o2007-05-18 07:05

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*12007-05-18 07:05

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*o2007-05-18 07:05

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*12007-05-18 07:05

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*o2007-05-18 07:05

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 的大作中提到】