sql如下:
select *
from (select * from cc_event_tsx where state = 'A' order by EVENT_ID)
where rownum < 1000
若建全局索引:
create index PK_CC_EVENT_TSX_NEW on CC_EVENT_TSX (
EVENT_ID ASC
)
tablespace IDX_CC;
则上面的sql能走索引,并且cost很小。
若建分区索引:
create index PK_CC_EVENT_TSX_NEW on CC_EVENT_TSX (
EVENT_ID ASC
)
local
tablespace IDX_CC;
则上面的sql不能走索引,并且cost非常大。
表结构:分区键为 PART_ID ,上面没有索引
SQL> desc cc_event_tsx;
Name Type Nullable Default Comments
--------------- -------------- -------- -------------------------------- ---------------
EVENT_ID NUMBER(12) EVENT_ID
EVENT_FORMAT_ID NUMBER(6) EVENT_FORMAT_ID
SUBS_ID NUMBER(9) Y SUBS_ID
PREFIX VARCHAR2(60) Y PREFIX
ACC_NBR VARCHAR2(60) Y ACC_NBR
CREATED_DATE DATE CREATED_DATE
STATE CHAR(1) STATE
STATE_DATE DATE STATE_DATE
EVENT_PARAM VARCHAR2(4000) Y EVENT_PARAM
COMMENTS VARCHAR2(4000) Y COMMENTS
PART_ID NUMBER(6) TO_NUMBER(TO_CHAR(SYSDATE,'DD'))