oracle order by 求帮优化

select A.aId, (select count(1) from B where B.aId = a.aId) c from A
order by c

4个回答

select A.aId, NVL(B.c,0) from A LEFT JOIN (select aId,count(1) c from B group by aId) B ON A.AID=B.AID
order by B.c

SELECT aId, 1 FROM
select DISTINCT(A.aId, A.c) from A， B where B.aId = a.aId D
ored by c

Coursera SELECT aId, 1 FROM select DISTINCT(A.aId, B.c) from A， B where B.aId = a.aId D ored by c

lnkToKing 回复lnkToKing: (select count(1) from B where B.aId = a.aId) c

lnkToKing A表没有c字段，c字段是b表统计出来的

c 是计算字段，不宜用于排序

select *
from
(select A.aId, (select count(1) from B where B.aId = a.aId) c from A) T
order by c

lnkToKing 这写法试过了，效率没差多少

select a.aId,
sum(case when B.aId is not null then 1 else 0 end) tot
from A left join B
on B.aId = a.aId
group by a.aId;

oracle order by 查询要10分钟 求帮优化
select A.aId, (select count(1) from B where B.aId = a.aId) c from Arnorder by crn表A有30W+数据rn表B有18W+数据rn用上面sql查要10分钟，如果去掉order by则在2秒内，求高手指导怎么优化
oracle order by 优化

select (a+b+c*d-e) from t order by (a+b+c*d-e)rn(a+b+c*d-e)内容不定rn

[code=SQL]rnrnUSE [HJGINFODB]rnGOrn/****** 对象: Table [dbo].[TB_IP_DUAN] 脚本日期: 09/28/2010 09:56:38 ******/rnSET ANSI_NULLS ONrnGOrnSET QUOTED_IDENTIFIER ONrnGOrnCREATE TABLE [dbo].[TB_IP_DUAN](rn [id] [int] IDENTITY(1,1) NOT NULL,rn [CITY_ID] [int] NOT NULL,rn [StartIP] [bigint] NOT NULL,rn [EndIP] [bigint] NOT NULL,rn CONSTRAINT [PK_TB_IP_DUAN] PRIMARY KEY CLUSTERED rn(rn [id] ASCrn)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]rn) ON [PRIMARY]rnrnrnrnselect top 1 (StartIP-124115011056) as tt,CITY_ID from TB_IP_DUAN rnwhere (StartIP-124115011056)>0rnorder by tt ascrnrn[/code]rnrn请问，这个表的索引改如何设计，现在这个sql语句消耗性能比较高rnrn测试通过给满分rn

[code=sql]rnselect a.id,rn a.name,rn sum(case when n.voltagelevelid = 35 then e.q else 0 end) as sumq500,rn sum(case when n.voltagelevelid = 33 then e.q else 0 end) as sumq220,rn sum(case when n.voltagelevelid = 32 then e.q else 0 end) as sumq110,rn sum(case when n.voltagelevelid = 25 then e.q else 0 end) as sumq35rn from hissubstationmeasure ern left join tblsubstation nrn on e.id = n.idrn left join tblsubcontrolarea arn on n.subcontrolareaid = a.idrn where n.used = 1rn and e.dtime betweenrn to_date('2013-12-31 00:00:00', 'yyyy-MM-dd hh24:mi:ss') andrn to_date('2013-12-31 23:59:59', 'yyyy-MM-dd hh24:mi:ss')rn group by a.id, a.name;rn[/code]rn[code=sql]rnSELECT STATEMENT, GOAL = ALL_ROWS 8488 40 4440rn HASH GROUP BY 8488 40 4440rn TABLE ACCESS BY GLOBAL INDEX ROWID SDWGDY HISSUBSTATIONMEASURE 43 258 19092rn NESTED LOOPS 8474 100243 11126973rn NESTED LOOPS OUTER 7 388 14356rn TABLE ACCESS FULL SDWGDY TBLSUBSTATION 5 388 9700rn TABLE ACCESS BY INDEX ROWID SDWGDY TBLSUBCONTROLAREA 1 1 12rn INDEX UNIQUE SCAN SDWGDY PK_TBLSUBCONTROLAREA 0 1 rn INDEX RANGE SCAN SDWGDY INDEX_2202 2 83 rn[/code]rnselect count(*) from hissubstationmeasure ;rn7311213rnselect count(*) from tblsubstation;rn938rnselect count(*) from tblsubcontrolarea;rn9rn这样的查询需要30秒rn其中对表 建立 ID,DTIME,AREAID 索引rn对tblsubstation 建立唯一索引IDrn对tblsubcontrolarea 建立唯一索引ID

[img=https://forum.csdn.net/PointForum/ui/scripts/csdn/Plugin/003/onion/30.gif][/img]求大神们帮小弟优化下sql，现在查询一条要7s，简直要了我的老命。上代码。rnrn[code=sql]rnselect su.id as poolid, su.su_name as cnName, sum(c.count) as countrn FROM (select distinct jet.office, jet.appname, companyidrn from ir biguserrn join ip apprn on biguser.user_id = app.user_idrn and app.invalid = 0rn and biguser.invalid = 0rn join it jetrn on app.jet_id = jet.jet_idrn and jet.ilid = 0) usersrn join absu surn on su.id || '' = users.companyidrn join IBL prn on upper(p.enname) = upper(users.appname)rn and upper(p.office) = upper(users.office)rn join (select * from IT where cmd <> 'Exception' and logdate between to_date(20140101, 'yyyymmdd') andrn to_date(20140910, 'yyyymmdd')) crn on c.poolid = p.poolidrn join IE cmdrn on cmd.cmd_id = RTRIM(c.cmd || '_' || c.subcmd1, '_')rn group by su.id, su.su_namern order by count descrnrn[/code]

SQL表连接、 order by 优化
select * from (select t2.* from (select rownum r, t1.* from (Select * From (Select * From crm_Vd健康档案 Where 1 = 1 And 有效状态b = '1') T order by 显示顺序, 系统序号) t1 where rownum &amp;lt;= 100) t2 where t2.r &amp;gt; 0...
mysql order by 优化
1.一些简单sql调优n 2.复杂sql的调优
mysql 关于order by的优化
explain SELECT m.*,u.username nickname,u.vip,u.appid FROM wwj_mingxi m left join wwj_user u on u.id=m.uid WHERE u.appid = 0 ORDER BY m.id desc LIMIT 0,20 花费时间4s 可以看出主要原因是使用了临时表，文件排序。也就是说order

[code=SQL]rnrnSELECT ip, COUNT(ip) ip_count FROM ip_log GROUP BY ip ORDER BY ip_count descrnrnrnSELECT ip, COUNT(ip) FROM ip_log GROUP BY ip ORDER BY COUNT(ip) descrnrn[/code]rnrn请问一下 这两条sql的效率相差大么？？rnrn有多大？rnrn还有可以优化的余地了么？rnrn谢谢大家
order by语句怎么优化
mysql order by/group by 优化
explain select im.item_id from property_value p left join item i on p.id = i.value left join rnitem_message im on i.item_id = im.item_id where ( p.value like '%餐饮%' or p.value like '%拉%' ) and im.class = 1 and im.status = 0 and p.`property_id` in (SELECT id FROM rn`property` WHERE `key_p` = 1) LIMIT 24 , 8rnrnrnid select_type table type possible_keys key key_len ref rows Extra rn1 PRIMARY im ref class,item_id class 4 const 9096 Using where rn1 PRIMARY i ref value,item_id item_id 4 fenlei.im.item_id 8 Using where rn1 PRIMARY p eq_ref PRIMARY PRIMARY 4 fenlei.i.value 1 Using where rn2 DEPENDENT SUBQUERY property unique_subquery PRIMARY PRIMARY 4 func 1 Using where rnrnrnexplain select count(im.item_id), im.item_id from property_value p left join item i on p.id = i.value left join rnitem_message im on i.item_id = im.item_id where ( p.value like '%餐饮%' or p.value like '%拉%' ) and im.class = 1 and im.status = 0 and p.`property_id` in (SELECT id FROM rn`property` WHERE `key_p` = 1) group by im.item_id LIMIT 20 , 8rnrnid select_type table type possible_keys key key_len ref rows Extra rn1 PRIMARY im index class,item_id item_id 4 NULL 3 Using where rn1 PRIMARY i ref value,item_id item_id 4 fenlei.im.item_id 8 Using where rn1 PRIMARY p eq_ref PRIMARY PRIMARY 4 fenlei.i.value 1 Using where rn2 DEPENDENT SUBQUERY property unique_subquery PRIMARY PRIMARY 4 func 1 Using where rnrnexplain select count(im.item_id), im.item_id from property_value p left join item i on p.id = i.value left join rnitem_message im on i.item_id = im.item_id where ( p.value like '%餐饮%' or p.value like '%拉%') and im.class = 1 and im.status = 0 and p.`property_id` in (SELECT id FROM rn`property` WHERE `key_p` = 1) group by im.item_id order by count(im.item_id) LIMIT 20 , 8rnrnid select_type table type possible_keys key key_len ref rows Extra rn1 PRIMARY im index class,item_id item_id 4 NULL 3 Using where; Using temporary; Using filesort rn1 PRIMARY i ref value,item_id item_id 4 fenlei.im.item_id 8 Using where rn1 PRIMARY p eq_ref PRIMARY PRIMARY 4 fenlei.i.value 1 Using where rn2 DEPENDENT SUBQUERY property unique_subquery PRIMARY PRIMARY 4 func 1 Using where rnrn我要用得第3条sql，我要order by的是count(im.item_id)这个数，请问如何优化，附索引，谢谢rnrnSHOW INDEX FROM item_message rnrnTable Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment rnitem_message 0 PRIMARY 1 id A 11994 NULL NULL BTREE rnitem_message 1 class 1 class A 3 NULL NULL BTREE rnitem_message 1 regional 1 regional A 3 NULL NULL YES BTREE rnitem_message 1 city 1 city A 11 NULL NULL YES BTREE rnitem_message 1 regional_city 1 regional A 3 NULL NULL YES BTREE rnitem_message 1 regional_city 2 city A 11 NULL NULL YES BTREE rnitem_message 1 create_date 1 create_date A 3 NULL NULL YES BTREE rnitem_message 1 item_id 1 item_id A 11994 NULL NULL BTREE rnrnSHOW INDEX FROM item rnrnTable Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment rnitem 0 PRIMARY 1 id A 1148679 NULL NULL BTREE rnitem 0 value 1 value A 1148679 NULL NULL BTREE rnitem 1 item_id 1 item_id A 143584 NULL NULL BTREE rnitem 1 class_id 1 class_id A 3 NULL NULL BTREE rnrnSHOW INDEX FROM property_value rnrnTable Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment rnproperty_value 0 PRIMARY 1 id A 1149757 NULL NULL BTREE rnproperty_value 1 property_id 1 property_id A 23 NULL NULL BTREE rnproperty_value 1 property_id 2 value A 6495 255 NULL BTREE rnproperty_value 1 value 1 value NULL 4892 NULL NULL FULLTEXT rnrnrn
mysql order by 原理和优化

MySQL之order by 优化（1）

MySQL Query 的优化— ORDER BY 的实现与优化
mysql、优化、order by
MySQL中队order by的优化（优化2）
MySQL有两种排序方式：通过所以扫描直接返回有序的数据，先返回无序数据，后对数据进行排序（filesort）。 第一种方式：前提条件索引必须是有序的，在查询时，where条件和order by 使用相同的索引，而且order by和索引的顺序一致。否则就会filesort，在SQL中使用索引的情况有： (1),select *from table_name order by key, (2),s...
oracle order by 降序不了

oracle order by 排序问题~~~

Oracle之分析函数 之 order by子句
/* 有ORDER BY的存在将添加一个默认的开窗子句！意味着从第一行到当前行； 没有ORDER BY时,默认的窗口是全部的分区 ； 在Order by 子句后可以添加nulls last,如:order by comm desc nulls last   表示排序时忽略comm列为空的行.     不写between AND  ,在有order BY 的情况下，就是分组第一行
oracle order by问题求助

oracle order by问题
select t.name as 名称,rn nvl(t.face, 0) as 面值rnfrom st_order t group t by t.name,t.facernorder by casern when v_orderby = 1 thenrn 名称rn when v_orderby = 2 thenrn 面值rnendrnrnrn面值是number类型,名称是char ,这个面值排序有问题,to_char后排出来的不对,谁能帮解决一下
oracle的order by排序规则

oracle自表关联求优化

ORACLE删除重复记录求优化
DELETErn FROM "table"rn WHERE "RowID" NOT IN (SELECT MAX("RowID")rn FROM "table"rn GROUP BYrn "aaa",rn "bbb",rn "ccc"rn HAVING COUNT(*) >= 1);rnrn很普通的一条去重复语句，但是当数据量大时（两三万条记录），执行时间要几个小时，但这时执行括号内的查询很快rn求优化方案

update oracle 不能order by?

【oracle】中文的order by排序规则

oracle OTM order management
oracle transportation management, order management functionality
oracle 子查询 order by 问题
select * from A where id in (select aid from B where rownum<2 order by create_time desc)rn我想根据某个字段排序后的取第一条数据，怎么报错呢，报：缺少右括号；rn本人对oracle不是太精通，请各位大侠们帮帮忙！！急啊，
SQL语句优化过程策略，帮助您优化Oracle查询语句
SQL语句优化过程优化策略 21. /*+ORDERED*/ 根据表出现在FROM中的顺序,ORDERED使ORACLE依此顺序对其连接. 例如: SELECT /*+ORDERED*/ A.COL1,B.COL2,C.COL3 FROM TABLE1 A,TABLE2 B,TABLE3 C WHERE A.COL1=B.COL1 AND B.COL1=C.COL1; 22. /*+USE_NL(TABLE)*/ 将指定表与嵌套的连接的行源进行连接,并把指定表作为内部表. 例如: SELECT /*+ORDERED USE_NL(BSEMPMS)*/ BSDPTMS.DPT_NO,BSEMPMS.EMP_NO,BSEMPMS.EMP_NAM FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO; 23. /*+USE_MERGE(TABLE)*/
Mysql随机取样——ORDER BY RAND()优化
The palest ink is better than best memory——好记性不如烂笔头。2013补记 一、关键词：随机取样、order by rand()二、业务场景：一款新产品上线后，刚开始用户比较少，不够活跃。但，出于运营需要，比如社交产品首页Feed流随机出老动态，系统修改发布时间显示~电商产品的商品列表随机显示商品，不至于每次用户看见的商品都一样——空城计——一种活跃的假象。
MySQL数据库SQL优化之order by 语句

【mysql】order by 优化与索引的应用

今天一个用户反馈SQL执行比较慢记录下分析过程：  1.用户的SQL      SELECT * FROM ie_article_25 WHERE status = 3  AND addtime &amp;lt; 1284078469 order by addtime desc LIMIT 0, 1 \G    2. 查看执行计划    mysql&amp;gt; explain SELECT ...
mysql 带条件的 order by 优化
1.一些简单sql调优n 2.复杂sql的调优
mysql中order by实现与优化