sql 优化

Mille

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

SQL> col type for a10
SQL> show parameter optimizer_mode

NAME                                 TYPE       VALUE
------------------------------------ ---------- ------------------------------
optimizer_mode                       string     FIRST_ROWS

SQL> create table test_a as  select * from dba_objects;

Table created.

SQL> insert into test_a select * from dba_objects;

153134 rows created.

SQL> /

153134 rows created.

SQL> /

153134 rows created.

SQL> /

153134 rows created.

SQL> /

153134 rows created.

SQL> commit;

Commit complete.

SQL> select count(*) from test_a     ;

  COUNT(*)
----------
    918804

SQL> create index IDX_A on TEST_A (OBJECT_ID);

Index created.

SQL> create index IDX_B on TEST_A (CREATED);

Index created.
SQL>create index IDX_C on TEST_A (OBJECT_ID, CREATED);
Index created.
SQL> set autot traceonly
SQL> select *
  2    from test_a t
  3   where t.object_id = 800
  4     and t.created >= to_date('2005-9-7 ', 'yyyy-mm-dd')
  5     and t.created <= to_date('2005-10-7 ', 'yyyy-mm-dd')
  6   order by t.created;

6 rows selected.


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=7 Card=4 Bytes=7
          08)

   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEST_A' (Cost=7 Card=4 B
          ytes=708)

   2    1     INDEX (RANGE SCAN) OF 'IDX_B' (NON-UNIQUE) (Cost=3 Card=
          799)





Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       4300  consistent gets
        109  physical reads
          0  redo size
       1464  bytes sent via SQL*Net to client
        655  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          6  rows processed

生产库有很多这样的order by 语句，也不可避免，
这个执行计划走IDX_B
idx_a的选择性很高，直方图已经收集。
这样的语句生产库上很多。该怎么优化呢？

xifenfei

1.使用hint强制走idx_a，然后给出执行计划和Statistics信息
2.个人猜测：你这里有order by created，如果使用object_id index，查询出来数据还需要进行一次sort，可能会导致cost比较大，所以数据库选择created index

对于这样的单个表，查询简单，大多数情况下收集统计信息准确，oracle还是比较智能的为什么选择正确答案。