原来这个表(POI_DATA,2800w条数据)在oracle分区,之后查询还是慢,转到内存库timesten查询。返回时间在0.5s左右,但是还想提升查询速度,最好到0.05左右。
内存库timesten没有表分区概念,同时查询中带like不走索引,导致查询速度慢。请问如何操作,谢谢!
DDL:
create cache group VEASMS.POI_DATA_CACHE
from
VEASMS.POI_DATA (
"ID" VARCHAR2(20 BYTE) INLINE NOT NULL,
NM VARCHAR2(500 BYTE) NOT INLINE,
FL_FIR VARCHAR2(500 BYTE) NOT INLINE,
FL_FIRMC VARCHAR2(500 BYTE) NOT INLINE,
FL_SEC VARCHAR2(500 BYTE) NOT INLINE,
FL_SECMC VARCHAR2(500 BYTE) NOT INLINE,
FL_THI VARCHAR2(500 BYTE) NOT INLINE,
FL_THIMC VARCHAR2(500 BYTE) NOT INLINE,
ADDR VARCHAR2(1000 BYTE) NOT INLINE,
PHONE VARCHAR2(500 BYTE) NOT INLINE,
TEL VARCHAR2(500 BYTE) NOT INLINE,
LAT NUMBER(30,25),
LON NUMBER(30,25),
X NUMBER(7,3),
Y NUMBER(7,3),
SZQMC VARCHAR2(100 BYTE) INLINE,
CITYMC VARCHAR2(100 BYTE) INLINE,
SFMC VARCHAR2(100 BYTE) INLINE,
SF VARCHAR2(20 BYTE) INLINE,
CITY VARCHAR2(20 BYTE) INLINE,
SZQ VARCHAR2(20 BYTE) INLINE,
SF_SOURCE VARCHAR2(1 BYTE) INLINE,
CZ_USERID VARCHAR2(20 BYTE) INLINE,
CZ_DATETIME DATE,
primary key ("ID"));
create index VEASMS.IDX_POI_2014_CITY on VEASMS.POI_DATA (CITY);
create index VEASMS.IDX_POI_2014_NM on VEASMS.POI_DATA (NM);
QUERY SQL:
SELECT *
FROM (SELECT A.*, INSTR(A.NM, '中山') SXH
FROM POI_DATA_WH A
WHERE
A.NM LIKE '%中山%'
AND A.CITY = '10458'
ORDER BY SXH
)
WHERE ROWNUM < 51