AnySQL.net

    一条不能再简单的SQL引起了性能问题, Where条件中只有一个列, 并且有索引, 这是为什么呢? 请看下面的例子:

SQL> CREATE TABLE T_OBJECTS
  2  (
  3    COL1 NUMBER, COL2 VARCHAR2(20), COL3 VARCHAR2(30)
  4  );

Table created.

SQL> CREATE INDEX IDX_T_OBJECTS_1 ON T_OBJECTS(COL1);

Index created.

SQL> CREATE INDEX IDX_T_OBJECTS_2 ON T_OBJECTS(COL2);

Index created.

SQL> INSERT INTO T_OBJECTS SELECT
  2  OBJECT_ID, OBJECT_ID, OBJECT_NAME FROM DBA_OBJECTS;

9506 rows created.

SQL> COMMIT;

    在COL1上使用时可以正确地使用索引, 将1500用单引号括起来也行:

ASQL> EXPLAIN PLAN
    2   SELECT * FROM T_OBJECTS WHERE COL1=1500;

SQLPLAN                                                    
------------------------------------------------------------
  0     SELECT STATEMENT Optimizer=ALL_ROWS                
  1   0   TABLE ACCESS (BY INDEX ROWID) OF T_OBJECTS (TABLE)
  2   1     INDEX (RANGE SCAN) OF IDX_T_OBJECTS_1 (INDEX)  

    在COL2上使用时并不能使用索引, 这是因为Oracle将Where条件变成了"TO_NUMBER(COL2)=1500"来执行, 在9i上还有可能出现Index Full Scan的情况.

ASQL> EXPLAIN PLAN
    2   SELECT * FROM T_OBJECTS WHERE COL2=1500;

SQLPLAN                                            
---------------------------------------------------
  0     SELECT STATEMENT Optimizer=ALL_ROWS        
  1   0   TABLE ACCESS (FULL) OF T_OBJECTS (TABLE)  

ASQL> EXPLAIN PLAN
    2   SELECT /*+ INDEX_ASC(T_OBJECTS) */ * FROM T_OBJECTS
    3   WHERE COL2=1500;

SQLPLAN                                            
---------------------------------------------------
  0     SELECT STATEMENT Optimizer=ALL_ROWS        
  1   0   TABLE ACCESS (FULL) OF T_OBJECTS (TABLE)  

    使用绑定变量时, 虽然Plan会显示是Index Range Scan, 但其实Consistent Gets和Index Full Scan一样.