SQL标准规定使用like将不会用索引,所以速度会很慢的!
解决方案 »
- oracle安装在linux下比windows下好吗?(性能、安全)
- 数据库莫名宕机问题
- 求助!Exp成功无报错信息,imp却报奇怪错误
- 关于重复记录的的更改
- 为什么这个存储过程总提示编译错误?请大家帮帮忙
- 高分求助,把这个sqlserver存储过程转换为oracle存储过程!多谢!
- 请问在PL/SQL中如何创建一个二维数组?
- Oracle 怎样建立 Bool字段?
- 请教!!!
- 在sql查询中如果where条件in中的参数超过一千条甚至更多一般有什么优化方案?如 in(1,2,...........99999)
- 为什么不能设置autotrace?
- 我在oracle中程序中调用dbms_job.submit(),为什么它不正常执行?
select ... from ... where ... and num like '%abc%' --将不使用索引
select ... from ... where ... and num like 'abc%' --将使用索引
当然是在不行,该用的时候还是一定要用的。
The following statements creates a function-based index on the emp table based on an uppercase evaluation of the ename column: CREATE INDEX emp_i ON emp (UPPER(ename));
To ensure that Oracle will use the index rather than performing a full table scan, be sure that the value of the function is not null in subsequent queries. For example, the statement SELECT * FROM emp WHERE UPPER(ename) IS NOT NULL
ORDER BY UPPER(ename);
is guaranteed to use the index, but without the WHERE clause, Oracle may perform a full table scan. In the next statements showing index creation and subsequent query, Oracle will use index emp_fi even though the columns are in reverse order in the query: CREATE INDEX emp_fi ON emp(cola + colb);SELECT * FROM emp WHERE colb + cola > 500;Function-based Index on Type Method Example
This example entails an object type rectangle containing two number attributes: length and width. The area() method computes the area of the rectangle. CREATE TYPE rectangle AS OBJECT
( length NUMBER,
width NUMBER,
MEMBER FUNCTION area RETURN NUMBER DETERMINISTIC
);
CREATE OR REPLACE TYPE BODY rectangle AS
MEMBER FUNCTION area RETURN NUMBER IS
BEGIN
RETURN (length*width);
END;
END;
Now, if you create a table rectab of type rectangle, you can create a function-based index on the area() method as follows: CREATE TABLE recttab OF rectangle;
CREATE INDEX area_idx ON recttab x (x.area());
You can use this index efficiently to evaluate a query of the form: SELECT * FROM recttab x WHERE x.area() > 100;
excute dbms_stats.gather_schema_stats('数据库名')执行这句后,查询速度大大加快。效果明显,我试的喔!