oracle 按照时间倒序_急求oracle倒序索引的解决方法

原帖由 zzj-lucky 于 2008-5-26 10:46 发表

这个不起用!

[php]

huiyi@HUIYI(192.168.1.100)> create table tt01

2  as select * from all_objects;

Table created.

Elapsed: 00:00:05.20

huiyi@HUIYI(192.168.1.100)> alter table tt01 modify object_id null;

Table altered.

Elapsed: 00:00:00.09

huiyi@HUIYI(192.168.1.100)> create index tt01_01 on tt01(object_id);

Index created.

Elapsed: 00:00:00.71

huiyi@HUIYI(192.168.1.100)> analyze table tt01

2  compute statistics

3  for table

4  for all indexed columns

5  for all indexes

6  /

Table analyzed.

Elapsed: 00:00:00.70

huiyi@HUIYI(192.168.1.100)> set autot trace

huiyi@HUIYI(192.168.1.100)> select * from

2  ( select * from tt01 order by object_id) where rownum<2;

Elapsed: 00:00:00.09

Execution Plan

----------------------------------------------------------

Plan hash value: 2287737346

----------------------------------------------------------------------------------------

| Id  | Operation               | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |

----------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT        |      |     1 |   128 |       |  1277   (1)| 00:00:16 |

|*  1 |  COUNT STOPKEY          |      |       |       |       |            |          |

|   2 |   VIEW                  |      | 50107 |  6263K|       |  1277   (1)| 00:00:16 |

|*  3 |    SORT ORDER BY STOPKEY|      | 50107 |  4746K|    12M|  1277   (1)| 00:00:16 |

|   4 |     TABLE ACCESS FULL   | TT01 | 50107 |  4746K|       |   160   (2)| 00:00:02 |

----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter(ROWNUM<2)

3 - filter(ROWNUM<2)

Statistics

----------------------------------------------------------

1  recursive calls

0  db block gets

694  consistent gets

0  physical reads

0  redo size

1200  bytes sent via SQL*Net to client

400  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

1  sorts (memory)

0  sorts (disk)

1  rows processed

huiyi@HUIYI(192.168.1.100)> alter table tt01 modify object_id not null;

Table altered.

Elapsed: 00:00:00.09

huiyi@HUIYI(192.168.1.100)> select * from

2  (select * from tt01 order by object_id) where rownum<2;

Elapsed: 00:00:00.05

Execution Plan

----------------------------------------------------------

Plan hash value: 2275813590

-----------------------------------------------------------------------------------------

| Id  | Operation                     | Name    | Rows  | Bytes | Cost (%CPU)| Time     |

-----------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT              |         |     1 |   128 |     3   (0)| 00:00:01 |

|*  1 |  COUNT STOPKEY                |         |       |       |            |          |

|   2 |   VIEW                        |         |     1 |   128 |     3   (0)| 00:00:01 |

|   3 |    TABLE ACCESS BY INDEX ROWID| TT01    | 50107 |  4746K|     3   (0)| 00:00:01 |

|   4 |     INDEX FULL SCAN           | TT01_01 |     1 |       |     2   (0)| 00:00:01 |

-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

1 - filter(ROWNUM<2)

Statistics

----------------------------------------------------------

237  recursive calls

0  db block gets

53  consistent gets

0  physical reads

0  redo size

1200  bytes sent via SQL*Net to client

400  bytes received via SQL*Net from client

2  SQL*Net roundtrips to/from client

6  sorts (memory)

0  sorts (disk)

1  rows processed

--

[/php]

[本帖最后由 HuiYiSky 于 2008-5-26 14:29 编辑]