表结构如下:
-- Create table
create table T_GG_MOBILENO_SENDABLE
(
sendable_id NUMBER(10) not null,
mobileno_id NUMBER(10),
mobileno NUMBER(11),
mobileno_kind NUMBER(4),
mobileno_age NUMBER(4),
mobileno_money NUMBER(8),
mobileno_area NVARCHAR2(6),
mobileno_sex NUMBER(1),
mobileno_double NUMBER(1),
mobileno_treble NUMBER(1)
)
tablespace GXT
pctfree 10
pctused 40
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
-- Create/Recreate indexes
create index INDEX_T_GG_MOBILENO_AGE on T_GG_MOBILENO_SENDABLE (MOBILENO_AGE)
tablespace GXT
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
create index INDEX_T_GG_MOBILENO_AREA on T_GG_MOBILENO_SENDABLE (MOBILENO_AREA)
tablespace GXT
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
create index INDEX_T_GG_MOBILENO_DOUBLE on T_GG_MOBILENO_SENDABLE (MOBILENO_DOUBLE)
tablespace GXT
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
create index INDEX_T_GG_MOBILENO_KIND on T_GG_MOBILENO_SENDABLE (MOBILENO_KIND)
tablespace GXT
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
create index INDEX_T_GG_MOBILENO_MONEY on T_GG_MOBILENO_SENDABLE (MOBILENO_MONEY)
tablespace GXT
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
create index INDEX_T_GG_MOBILENO_SENDABLE on T_GG_MOBILENO_SENDABLE (MOBILENO)
tablespace GXT
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
create index INDEX_T_GG_MOBILENO_SEX on T_GG_MOBILENO_SENDABLE (MOBILENO_SEX)
tablespace GXT
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
create index INDEX_T_GG_MOBILENO_TREBLE on T_GG_MOBILENO_SENDABLE (MOBILENO_TREBLE)
tablespace GXT
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
-- Create/Recreate primary, unique and foreign key constraints
alter table T_GG_MOBILENO_SENDABLE
add constraint PK_T_GG_MOBILENO_SENDABLE primary key (SENDABLE_ID)
using index
tablespace GXT
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);SQL语句:
select count( distinct( MOBILENO))
from t_gg_mobileno_sendable a
WHERE (MOBILENO_KIND = 1
and MOBILENO_AGE >= 0 and MOBILENO_AGE <= 99 and
MOBILENO_MONEY between 0 and 999)
Where后面的条件是动态的 所以有的索引暂时没有用到这是为什么捏?如果对count加强制索引 怎么加?
-- Create table
create table T_GG_MOBILENO_SENDABLE
(
sendable_id NUMBER(10) not null,
mobileno_id NUMBER(10),
mobileno NUMBER(11),
mobileno_kind NUMBER(4),
mobileno_age NUMBER(4),
mobileno_money NUMBER(8),
mobileno_area NVARCHAR2(6),
mobileno_sex NUMBER(1),
mobileno_double NUMBER(1),
mobileno_treble NUMBER(1)
)
tablespace GXT
pctfree 10
pctused 40
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
-- Create/Recreate indexes
create index INDEX_T_GG_MOBILENO_AGE on T_GG_MOBILENO_SENDABLE (MOBILENO_AGE)
tablespace GXT
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
create index INDEX_T_GG_MOBILENO_AREA on T_GG_MOBILENO_SENDABLE (MOBILENO_AREA)
tablespace GXT
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
create index INDEX_T_GG_MOBILENO_DOUBLE on T_GG_MOBILENO_SENDABLE (MOBILENO_DOUBLE)
tablespace GXT
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
create index INDEX_T_GG_MOBILENO_KIND on T_GG_MOBILENO_SENDABLE (MOBILENO_KIND)
tablespace GXT
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
create index INDEX_T_GG_MOBILENO_MONEY on T_GG_MOBILENO_SENDABLE (MOBILENO_MONEY)
tablespace GXT
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
create index INDEX_T_GG_MOBILENO_SENDABLE on T_GG_MOBILENO_SENDABLE (MOBILENO)
tablespace GXT
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
create index INDEX_T_GG_MOBILENO_SEX on T_GG_MOBILENO_SENDABLE (MOBILENO_SEX)
tablespace GXT
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
create index INDEX_T_GG_MOBILENO_TREBLE on T_GG_MOBILENO_SENDABLE (MOBILENO_TREBLE)
tablespace GXT
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
-- Create/Recreate primary, unique and foreign key constraints
alter table T_GG_MOBILENO_SENDABLE
add constraint PK_T_GG_MOBILENO_SENDABLE primary key (SENDABLE_ID)
using index
tablespace GXT
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);SQL语句:
select count( distinct( MOBILENO))
from t_gg_mobileno_sendable a
WHERE (MOBILENO_KIND = 1
and MOBILENO_AGE >= 0 and MOBILENO_AGE <= 99 and
MOBILENO_MONEY between 0 and 999)
Where后面的条件是动态的 所以有的索引暂时没有用到这是为什么捏?如果对count加强制索引 怎么加?
MOBILENO_MONEY 的查询条件都要+ >0 的条件么?
Execution Plan
----------------------------------------------------------
Plan hash value: 1954617196--------------------------------------------------------------------------------
------------------------| Id | Operation | Name | Rows | Bytes |
Cost (%CPU)| Time |--------------------------------------------------------------------------------
------------------------| 0 | SELECT STATEMENT | | 1 | 19 |
0 (0)| 00:00:01 || 1 | SORT GROUP BY | | 1 | 19 |
| ||* 2 | TABLE ACCESS BY INDEX ROWID| T_GG_MOBILENO_SENDABLE | 3 | 57 |
0 (0)| 00:00:01 ||* 3 | INDEX RANGE SCAN | INDEX_T_GG_MOBILENO_AGE | 1 | |
0 (0)| 00:00:01 |--------------------------------------------------------------------------------
------------------------
Predicate Information (identified by operation id):
--------------------------------------------------- 2 - filter("MOBILENO_MONEY"<=999 AND "MOBILENO_KIND"=1 AND "MOBILENO_MONEY">=
0) 3 - access("MOBILENO_AGE">=0 AND "MOBILENO_AGE"<=99)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2204994 consistent gets
99408 physical reads
0 redo size
429 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
----------------------------------------------------------
Plan hash value: 1954617196--------------------------------------------------------------------------------
------------------------| Id | Operation | Name | Rows | Bytes |
Cost (%CPU)| Time |--------------------------------------------------------------------------------
------------------------| 0 | SELECT STATEMENT | | 1 | 19 |
0 (0)| 00:00:01 || 1 | SORT GROUP BY | | 1 | 19 |
| ||* 2 | TABLE ACCESS BY INDEX ROWID| T_GG_MOBILENO_SENDABLE | 3 | 57 |
0 (0)| 00:00:01 ||* 3 | INDEX RANGE SCAN | INDEX_T_GG_MOBILENO_AGE | 1 | |
0 (0)| 00:00:01 |--------------------------------------------------------------------------------
------------------------
Predicate Information (identified by operation id):
--------------------------------------------------- 2 - filter("MOBILENO_MONEY"<=999 AND "MOBILENO_KIND"=1 AND "MOBILENO_MONEY">=
0) 3 - access("MOBILENO_AGE">=0 AND "MOBILENO_AGE"<=99)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2204994 consistent gets
99408 physical reads
0 redo size
429 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
SELECT COUNT(MOBILENO)
FROM (SELECT A.MOBILENO
FROM T_GG_MOBILENO_SENDABLE A
WHERE A.MOBILENO_KIND = 1
AND MOBILENO_AGE >= 0
AND A.MOBILENO_AGE <= 99
AND A.MOBILENO_MONEY BETWEEN 0 AND 999
GROUP BY A.MOBILENO)
SELECT A.MOBILENO
FROM T_GG_MOBILENO_SENDABLE A
WHERE A.MOBILENO_KIND = 1
AND MOBILENO_AGE >= 0
AND A.MOBILENO_AGE <= 99
AND A.MOBILENO_MONEY BETWEEN 0 AND 999或者走MOBILENO_KIND的索引试试:
SELECT COUNT(MOBILENO)
FROM (SELECT A.MOBILENO
FROM T_GG_MOBILENO_SENDABLE A
WHERE A.MOBILENO_KIND = 1
AND MOBILENO_AGE+0 >= 0
AND A.MOBILENO_AGE+0 <= 99
AND A.MOBILENO_MONEY+0 BETWEEN 0 AND 999
GROUP BY A.MOBILENO)
加group by 就不要distinct。
那就需要改变原先的执行计划, 改成MOBILENO_AGE+0这样的写法就会使MOBILENO_AGE这个字段的索引失效,强制Oracle使用MOBILENO_KIND字段的索引.我的想法就是这样来的,见笑了.
当然要让Oracle走什么索引也可以用hint.