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博客 ” ，链接：https://blog.itpub.net/386081/viewspace-566473/ ，如需转载，请注明出处，否则将追究法律责任。
