free code 128 barcode font for crystal reports Descending Indexes in Objective-C

Make Data Matrix ECC200 in Objective-C Descending Indexes

Descending indexes were introduced in Oracle8i to extend the functionality of a B*Tree index. They allow for a column to be stored sorted in descending order (from big to small) in the index instead of ascending order (from small to big). Prior releases of Oracle (pre-Oracle8i) always supported the DESC (descending) keyword syntactically, but basically ignored it it had no effect on how the data was stored or used in the index. In Oracle8i and above, however, the DESC keyword changes the way the index is created and used. Oracle has had the ability to read an index backward for quite a while, so you may be wondering why this feature is relevant. For example, if we use a table T: ops$tkyte%ORA11GR2> create table t 2 as 3 select * 4 from all_objects 5 / Table created. ops$tkyte%ORA11GR2> create index t_idx 2 on t(owner,object_type,object_name); Index created. ops$tkyte%ORA11GR2> begin 2 dbms_stats.gather_table_stats 3 ( user, 'T', method_opt=>'for all indexed columns' ); 4 end; 5 / PL/SQL procedure successfully completed. and query it as follows ops$tkyte%ORA11GR2> set autotrace traceonly explain ops$tkyte%ORA11GR2> 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 DESC;

Execution Plan ---------------------------------------------------------Plan hash value: 2685572958 ------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | ------------------------------------------------------------| 0 | SELECT STATEMENT | | 784 | 11760 | |* 1 | INDEX RANGE SCAN DESCENDING| T_IDX | 784 | 11760 | ------------------------------------------------------------Oracle will just read the index backward. There is no final sort step in this plan; the data is sorted. Where this descending index feature comes into play, however, is when you have a mixture of columns, and some are sorted ASC (ascending) and some DESC (descending), for example: ops$tkyte%ORA11GR2> 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: 2813023843 --------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 784 | 11760 | 9 (12)| 00:00:01 | | 1 | SORT ORDER BY | | 784 | 11760 | 9 (12)| 00:00:01 | |* 2 | INDEX RANGE SCAN| T_IDX | 784 | 11760 | 8 (0)| 00:00:01 | --------------------------------------------------------------------------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: ops$tkyte%ORA11GR2> create index desc_t_idx on t(owner desc,object_type asc); Index created. ops$tkyte%ORA11GR2> 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 | | 889 | 13335 | 3 (0)| 00:00:01 | |* 1 | INDEX RANGE SCAN| DESC_T_IDX | 889 | 13335 | 3 (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. It should be noted that unless your compatible init.ora parameter is set to 8.1.0 or higher, the DESC option on the CREATE INDEX will be silently ignored no warning or error will be produced, as this was the default behavior in prior releases.