ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 组合索引顺序

组合索引顺序

原创 Linux操作系统 作者:zergduan 时间:2009-03-09 22:05:28 0 删除 编辑

C:\Documents and Settings\RequieM>sqlplus mydb

SQL*Plus: Release 9.2.0.8.0 - Production on Mon Mar 9 20:39:33 2009

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Enter password:

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

SQL> select count(*) from test_abc where object_id=10;

  COUNT(*)
----------
     24976

Elapsed: 00:00:00.00
SQL> update test_abc set object_id=20 where rownum<16000;

15999 rows updated.

Elapsed: 00:00:00.02
SQL> update test_abc set object_id=20 where rownum<8000;

7999 rows updated.

Elapsed: 00:00:00.00SQL> select object_id,count(*) from test_abc group by object_id;

 OBJECT_ID   COUNT(*)
---------- ----------
        10       8977
        20       8000
        30       7999

SQL> create index ind_test_abc on test_abc(data_object_id,object_id);

Index created.

Elapsed: 00:00:00.00

SQL> select count(*)from test_abc where object_id=20 and data_object_id between 20 and 10000;

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE
   1    0   SORT (AGGREGATE)
   2    1     INDEX (RANGE SCAN) OF 'IND_TEST_ABC' (NON-UNIQUE)

 


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         12  consistent gets
          0  physical reads
          0  redo size
        407  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> explain plan for
  2  select count(*)from test_abc where object_id=20 and data_object_id between 20 and 10000;

Explained.

Elapsed: 00:00:00.00
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------


----------------------------------------------------------------------
| Id  | Operation            |  Name         | Rows  | Bytes | Cost  |
----------------------------------------------------------------------
|   0 | SELECT STATEMENT     |               |       |       |       |
|   1 |  SORT AGGREGATE      |               |       |       |       |
|*  2 |   INDEX RANGE SCAN   | IND_TEST_ABC  |       |       |       |
----------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("TEST_ABC"."DATA_OBJECT_ID">=20 AND
              "TEST_ABC"."OBJECT_ID"=20 AND "TEST_ABC"."DATA_OBJECT_ID"<=10000)
       filter("TEST_ABC"."OBJECT_ID"=20)

Note: rule based optimization

17 rows selected.

Elapsed: 00:00:00.00

 

SQL> create index ind_test_abc on test_abc (object_id,data_object_id);

Index created.

Elapsed: 00:00:00.02
SQL> set autotrace traceonly
SQL> select count(*)from test_abc where object_id=20 and data_object_id between 20 and 10000;

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE
   1    0   SORT (AGGREGATE)
   2    1     INDEX (RANGE SCAN) OF 'IND_TEST_ABC' (NON-UNIQUE)

 


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          5  consistent gets
          4  physical reads
          0  redo size
        407  bytes sent via SQL*Net to client
        503  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> explain plan for
  2  select count(*)from test_abc where object_id=20 and data_object_id between 20 and 10000;

Explained.

Elapsed: 00:00:00.00
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------


----------------------------------------------------------------------
| Id  | Operation            |  Name         | Rows  | Bytes | Cost  |
----------------------------------------------------------------------
|   0 | SELECT STATEMENT     |               |       |       |       |
|   1 |  SORT AGGREGATE      |               |       |       |       |
|*  2 |   INDEX RANGE SCAN   | IND_TEST_ABC  |       |       |       |
----------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("TEST_ABC"."OBJECT_ID"=20 AND
              "TEST_ABC"."DATA_OBJECT_ID">=20 AND
              "TEST_ABC"."DATA_OBJECT_ID"<=10000)

Note: rule based optimization

17 rows selected.

Elapsed: 00:00:00.00

 

组合索引顺序,范围查询的谓词放在后面

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/386081/viewspace-566473/,如需转载,请注明出处,否则将追究法律责任。

上一篇: Oracle10g的trace
请登录后发表评论 登录
全部评论

注册时间:2008-03-12

  • 博文量
    77
  • 访问量
    750963