SELECT
*
FROM
(
SELECT
SCORE ,
ROWNUM AS LINENUM ,
PHONE
FROM
(
SELECT
SCORE ,
PHONE
FROM
COM_NFXD_MZUSER
ORDER BY
SCORE
DESC ) )
WHERE
PHONE='02162090953'
对以SQL 语句进行优化下面是表结构-- Create table
create table COM_NFXD_EYE_MZUSER
(
PHONE VARCHAR2(21) not null,
CELL VARCHAR2(21),
DONE VARCHAR2(100),
SCORE NUMBER(10) default (0) not null,
ISZJ NUMBER(1) default (0),
GATENUM NUMBER(19) default (0) --此用户本日的闯关次数,
INPUTTIME DATE default (sysdate)
)
tablespace IVR_DATA
pctfree 10
pctused 40
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
-- Create/Recreate primary, unique and foreign key constraints
alter table COM_NFXD_EYE_MZUSER
add primary key (PHONE)
using index
tablespace IVR_DATA
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
*
FROM
(
SELECT
SCORE ,
ROWNUM AS LINENUM ,
PHONE
FROM
(
SELECT
SCORE ,
PHONE
FROM
COM_NFXD_MZUSER
ORDER BY
SCORE
DESC ) )
WHERE
PHONE='02162090953'
对以SQL 语句进行优化下面是表结构-- Create table
create table COM_NFXD_EYE_MZUSER
(
PHONE VARCHAR2(21) not null,
CELL VARCHAR2(21),
DONE VARCHAR2(100),
SCORE NUMBER(10) default (0) not null,
ISZJ NUMBER(1) default (0),
GATENUM NUMBER(19) default (0) --此用户本日的闯关次数,
INPUTTIME DATE default (sysdate)
)
tablespace IVR_DATA
pctfree 10
pctused 40
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
-- Create/Recreate primary, unique and foreign key constraints
alter table COM_NFXD_EYE_MZUSER
add primary key (PHONE)
using index
tablespace IVR_DATA
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
解决方案 »
- oracle9i在xp安装出错,提示error in writing to directory
- 100分,谁能给我写一个简单的Procedure或者Package
- 两个字段分组为条件查出表的全部内容
- 客户端和服务器连接后表中的数据若包含汉字,选取速度奇慢无比,如何解决?
- oracle字符集的问题啊!
- Oracle关于tunning的一个发现~ 顺便散分
- 有免费版本的oracle吗?怎么找不到?
- 字符集问题,我用ue把dmp文件的2,3字节改为03 54(原来为00 01) 然后IMP提示我无效的导出文件 ,why??
- oracle 8i在P4+WIN98 下安装的问题,旧的问题,!在线等候,
- 9i标准版做双机热备的问题
- 关于oracle job定时执行的问题?
- 查询24小时各时间段的记录
SCORE ,
ROWNUM AS LINENUM ,
PHONE
FROM
(
SELECT
SCORE ,
PHONE
FROM
COM_NFXD_MZUSER
WHERE PHONE='02162090953'
ORDER BY
SCORE
DESC )
SCORE,
LINENUM,
PHONE
FROM
(
SELECT
SCORE,
ROWNUM AS LINENUM,
PHONE
FROM
COM_NFXD_MZUSER
ORDER BY
SCORE
DESC
)
WHERE PHONE='02162090953'
SASACAT你的SQL 是很快可是LINENUM 总是一FENG2 你的LINENUM和我要的结果也不一样不过还是要谢谢2位了
SCORE,
ROWNUM AS LINENUM,
PHONE
FROM
(
SELECT
SCORE,
PHONE
FROM
COM_NFXD_MZUSER
ORDER BY
SCORE
DESC
)
WHERE PHONE='02162090953';
这次我自己先做了测试,呵呵。