oracle11g order by 慢,oracle11g lead bug

with t0 as (select a.car_id,a.plate_no,a.plate_color

from vmc_car_info a,vmc_car_manager b

where a.car_id = b.car_id  and a.CAR_STATUS<>'99'

and a.enterprise_id=100000285

and b.user_id=200000319),

t1 as

(SELECT a.car_id,LOG_TIME,ONLINE_STATUS,

LEAD(LOG_TIME) OVER(partition by a.car_id ORDER BY LOG_TIME) AS next_time,

LEAD(ONLINE_STATUS) OVER(partition by a.car_id ORDER BY LOG_TIME) AS next_status,

lag(LOG_TIME) OVER(partition by a.car_id ORDER BY LOG_TIME) AS pre_time,

lag(ONLINE_STATUS) OVER(partition by a.car_id ORDER BY LOG_TIME) AS pre_status

FROM vmc_car_online_log a

where a.car_id in(select t0.car_id from t0 )

and log_time between

to_date('2013-11-26 12:29:03', 'yyyy-MM-dd hh24:mi:ss') and

to_date('2013-11-27 12:29:09', 'yyyy-MM-dd hh24:mi:ss')),

t2 as

(select car_id,

LOG_TIME online_time,

nvl(next_time,

to_date('2013-11-27 12:29:09', 'yyyy-MM-dd hh24:mi:ss')) offline_time

from t1

where t1.ONLINE_STATUS = 1),

t3 as

(select car_id,

nvl(pre_time,

to_date('2013-11-26 12:29:03', 'yyyy-MM-dd hh24:mi:ss')) online_time,

LOG_TIME offline_time

from t1

where t1.ONLINE_STATUS = 0),

t4 as (select * from t2  union  select * from t3),

t5 as (select CAR_ID,

round(SUM(OFFLINE_TIME - ONLINE_TIME) /

(to_date('2013-11-27 12:29:09',

'yyyy-MM-dd hh24:mi:ss') -

to_date('2013-11-26 12:36:03',

'yyyy-MM-dd hh24:mi:ss')) * 100,   6) online_ratio,

SUM(OFFLINE_TIME - ONLINE_TIME)*86400 online_time,count(*) online_cnt

from t4  GROUP BY CAR_ID)

select /*+ NOREWRITE */ a.car_id,a.plate_no,a.plate_color,to_char(nvl(b.online_ratio,0),'990D99') || '%' online_ratio,

online_time,online_cnt

from t0 a,t5 b

where a.car_id=b.car_id

order by a.car_id ;

CAR_ID PLATE_NO                                                     PLATE_COLOR ONLINE_RATIO ONLINE_TIME ONLINE_CNT

---------- ------------------------------------------------------------ ----------- ------------ ----------- ----------

600217878 粤BA705L                                                               1    7.76%            6674          1

600217937 粤B56G32                                                               1    2.45%            2106          1

600217938 粤B78WS6                                                               1    2.77%            2382          2

600217939 京G48811                                                               2    9.26%            7963          5

with t0 as (select a.car_id,a.plate_no,a.plate_color

from vmc_car_info a,vmc_car_manager b

where a.car_id = b.car_id  and a.CAR_STATUS<>'99'

and a.enterprise_id=100000285

and b.user_id=200000319),

t1 as

(SELECT a.car_id,LOG_TIME,ONLINE_STATUS,

LEAD(LOG_TIME) OVER(partition by a.car_id ORDER BY LOG_TIME) AS next_time,

LEAD(ONLINE_STATUS) OVER(partition by a.car_id ORDER BY LOG_TIME) AS next_status,

lag(LOG_TIME) OVER(partition by a.car_id ORDER BY LOG_TIME) AS pre_time,

lag(ONLINE_STATUS) OVER(partition by a.car_id ORDER BY LOG_TIME) AS pre_status

FROM vmc_car_online_log a,t0

where a.car_id=t0.car_id

and log_time between

to_date('2013-11-26 12:29:03', 'yyyy-MM-dd hh24:mi:ss') and

to_date('2013-11-27 12:29:09', 'yyyy-MM-dd hh24:mi:ss')),

t2 as

(select car_id,

LOG_TIME online_time,

nvl(next_time,

to_date('2013-11-27 12:29:09', 'yyyy-MM-dd hh24:mi:ss')) offline_time

from t1

where t1.ONLINE_STATUS = 1),

t3 as

(select car_id,

nvl(pre_time,

to_date('2013-11-26 12:29:03', 'yyyy-MM-dd hh24:mi:ss')) online_time,

LOG_TIME offline_time

from t1

where t1.ONLINE_STATUS = 0),

t4 as (select * from t2  union  select * from t3),

t5 as (select CAR_ID,

round(SUM(OFFLINE_TIME - ONLINE_TIME) /

(to_date('2013-11-27 12:29:09',

'yyyy-MM-dd hh24:mi:ss') -

to_date('2013-11-26 12:36:03',

'yyyy-MM-dd hh24:mi:ss')) * 100,   6) online_ratio,

SUM(OFFLINE_TIME - ONLINE_TIME)*86400 online_time,count(*) online_cnt

from t4  GROUP BY CAR_ID)

select /*+ NOREWRITE */ a.car_id,a.plate_no,a.plate_color,to_char(nvl(b.online_ratio,0),'990D99') || '%' online_ratio,

online_time,online_cnt

from t0 a,t5 b

where a.car_id=b.car_id

order by a.car_id

CAR_ID PLATE_NO                                                     PLATE_COLOR ONLINE_RATIO ONLINE_TIME ONLINE_CNT

---------- ------------------------------------------------------------ ----------- ------------ ----------- ----------

600217878 粤BA705L                                                               1    7.76%            6674          1

600217937 粤B56G32                                                               1    2.45%            2106          1

600217938 粤B78WS6                                                               1    2.77%            2382          2

600217939 京G48811                                                               2    3.49%            2999          6