Database Reference
In-Depth Information
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER">='T' AND "OWNER"<='Z')
filter("OBJECT_TYPE" IS NOT NULL)
Oracle isn't able to use the index we have in place on (
OWNER
,
OBJECT_TYPE
,
OBJECT_NAME
) anymore to
sort
the
data. It could have read it backward to get the data sorted by
OWNER DESC
, but it needs to read it “forward” to get
OBJECT_TYPE
sorted
ASC
. Instead, it collected together all of the rows and then sorted. Enter the
DESC
index:
EODA@ORA12CR1> create index desc_t_idx on t(owner desc,object_type asc);
Index created.
EODA@ORA12CR1> select owner, object_type
2 from t
3 where owner between 'T' and 'Z'
4 and object_type is not null
5 order by owner DESC, object_type ASC;
Execution Plan
----------------------------------------------------------
Plan hash value: 2494308350
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5008 | 50080 | 2 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN | DESC_T_IDX | 5008 | 50080 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access(SYS_OP_DESCEND("OWNER")>=HEXTORAW('A5FF') AND
SYS_OP_DESCEND("OWNER")<=HEXTORAW('ABFF'))
filter(SYS_OP_UNDESCEND(SYS_OP_DESCEND("OWNER"))>='T' AND
SYS_OP_UNDESCEND(SYS_OP_DESCEND("OWNER"))<='Z' AND "OBJECT_TYPE" IS NOT
NULL)
Once more, we are able to read the data sorted, and there is no extra sort step at the end of the plan.
■
do not be tempted to ever leave an
ORDER
BY
off a query. Just because your query plan includes an index does
not mean the data will be returned in “some order.” the
only way
to retrieve data from the database in some sorted order
is to include an
ORDER
BY
on your query. there is no substitute for
ORDER BY
.
Note
