select tbadmuser0_.*
from TB_ADM_USER tbadmuser0_
inner join TB_DEPT tbdept1_ on tbadmuser0_.DEPT_ID =
tbdept1_.DEPT_ID
inner join BAS_DISTRICT basdistric2_ on tbadmuser0_.DISTRICT_ID =
basdistric2_.DISTRICT_ID
where
tbadmuser0_.DEL_FLAG = 0
and tbadmuser0_.LOGIN_NAME != 'oppoadmin'
and (basdistric2_.SELFLEV_CODE like
'40288188119c102f01119cadc42d01d0,4028818811a15abe0111a1a517480004%')
order by tbadmuser0_.CREATE_DATE desc;
以上是我的sql,为什么这个sql没使用上我的索引呢?太家帮我分析分析。以下是索引的创建:
create index INDEX_USER_DEL on TB_ADM_USER (CREATE_DATE, DEPT_ID, DISTRICT_ID, DEL_FLAG, LOGIN_NAME);
但是我的:select count(tbadmuser0_.*)
from TB_ADM_USER tbadmuser0_
inner join TB_DEPT tbdept1_ on tbadmuser0_.DEPT_ID =
tbdept1_.DEPT_ID
inner join BAS_DISTRICT basdistric2_ on tbadmuser0_.DISTRICT_ID =
basdistric2_.DISTRICT_ID
where
tbadmuser0_.DEL_FLAG = 0
and tbadmuser0_.LOGIN_NAME != 'oppoadmin'
and (basdistric2_.SELFLEV_CODE like
'40288188119c102f01119cadc42d01d0,4028818811a15abe0111a1a517480004%')是能使用上这个索引。
求太家帮帮忙。这个花了好几天没整明白。
from TB_ADM_USER tbadmuser0_
inner join TB_DEPT tbdept1_ on tbadmuser0_.DEPT_ID =
tbdept1_.DEPT_ID
inner join BAS_DISTRICT basdistric2_ on tbadmuser0_.DISTRICT_ID =
basdistric2_.DISTRICT_ID
where
tbadmuser0_.DEL_FLAG = 0
and tbadmuser0_.LOGIN_NAME != 'oppoadmin'
and (basdistric2_.SELFLEV_CODE like
'40288188119c102f01119cadc42d01d0,4028818811a15abe0111a1a517480004%')
order by tbadmuser0_.CREATE_DATE desc;
以上是我的sql,为什么这个sql没使用上我的索引呢?太家帮我分析分析。以下是索引的创建:
create index INDEX_USER_DEL on TB_ADM_USER (CREATE_DATE, DEPT_ID, DISTRICT_ID, DEL_FLAG, LOGIN_NAME);
但是我的:select count(tbadmuser0_.*)
from TB_ADM_USER tbadmuser0_
inner join TB_DEPT tbdept1_ on tbadmuser0_.DEPT_ID =
tbdept1_.DEPT_ID
inner join BAS_DISTRICT basdistric2_ on tbadmuser0_.DISTRICT_ID =
basdistric2_.DISTRICT_ID
where
tbadmuser0_.DEL_FLAG = 0
and tbadmuser0_.LOGIN_NAME != 'oppoadmin'
and (basdistric2_.SELFLEV_CODE like
'40288188119c102f01119cadc42d01d0,4028818811a15abe0111a1a517480004%')是能使用上这个索引。
求太家帮帮忙。这个花了好几天没整明白。
select stateemnt,goal=choose
sort order by
netsted loops
hash join
index fasx full scan selfcode
table access full tb_adm_user
index unique scan pf_tb_dept 主要是 tb_adm_user全扫描,
create table TB_ADM_USER
(
USER_ID VARCHAR2(32) not null,
LOGIN_NAME VARCHAR2(50) not null,
USER_PASSWORD VARCHAR2(50),
USER_CODE VARCHAR2(20),
USER_NAME VARCHAR2(50),
EMAIL VARCHAR2(100),
GROUP_ID VARCHAR2(32),
DEPT_ID VARCHAR2(32),
STATE INTEGER,
REMARK VARCHAR2(200),
DEL_FLAG INTEGER,
DISTRICT_ID VARCHAR2(32),
MOBILE VARCHAR2(50),
ZYZ_ID VARCHAR2(32),
SEX VARCHAR2(2),
PHONE VARCHAR2(50),
USER_WORK VARCHAR2(200),
CREATE_DATE DATE
);
create table TB_DEPT
(
DEPT_ID VARCHAR2(32) not null,
DEPT_CODE VARCHAR2(50) not null,
DEPT_NAME VARCHAR2(100) not null,
DEPT_TEL VARCHAR2(50),
DEPT_FAX VARCHAR2(50),
DEPT_MANAGER VARCHAR2(50),
DEPT_OFFICE VARCHAR2(200),
UP_LEVEL VARCHAR2(32),
SORT INTEGER,
HAVE_CHILD INTEGER,
DEPT_DESCRIPTION VARCHAR2(2000),
CREATOR VARCHAR2(32),
SELFLEV_CODE VARCHAR2(1024),
SYS_FLAG INTEGER default 0,
DEL_FLAG INTEGER default 0,
DEPT_LEVEL INTEGER
);
create table BAS_DISTRICT
(
DISTRICT_ID VARCHAR2(32) not null,
DISTRICT_LEVEL INTEGER,
DISTRICT_NAME VARCHAR2(100),
DISTRICT_CODE VARCHAR2(20),
DISTRICT_PID VARCHAR2(32),
SORT INTEGER,
HAVE_CHILD INTEGER,
REMARK VARCHAR2(200),
SELFLEV_CODE VARCHAR2(1024),
DEL_FLAG INTEGER default 0 not null
)