Why the Order by clause avoid index scan and taking long time in Oracle 11g

I am facing problem on query when i run with ORDER BY clause it perform TABLE ACCESS and at the same time if i comment the ORDER BY clause then the query start using index .

Anybody answer me why the optimizer avoid index when i use ORDER BY clause. And also help me to use index with ORDER BY clause which gives resultset in 1 sec??

With ORDER BY clause query takes 18 secs and Without ORDER BY clause query takes 1 sec

Table Structure :

SQL> desc eum
Name Null? Type
----------------------------------------------------- -------- ----------------
--------------------
APPLICATION_NAME NOT NULL VARCHAR2(128)
GROUPNAME NOT NULL VARCHAR2(128)
PAGETYPE NOT NULL VARCHAR2(16)
DEVICE NOT NULL VARCHAR2(16)
OS NOT NULL VARCHAR2(64)
BROWSER NOT NULL VARCHAR2(64)
COUNTRY NOT NULL VARCHAR2(128)
REGION NOT NULL VARCHAR2(128)
CITY NOT NULL VARCHAR2(128)
REQUEST_DATE NOT NULL DATE
GUID NOT NULL VARCHAR2(128)
MSMT_TIME NOT NULL DATE
RUM_USER_EXP NOT NULL VARCHAR2(16)
REQUEST_TIME_ZONE NOT NULL VARCHAR2(16)
URL NOT NULL VARCHAR2(256)
REFERRER NOT NULL VARCHAR2(256)
SESSIONID NOT NULL VARCHAR2(128)
PAGE_LOAD_TIME NOT NULL NUMBER(16)
FRONT_END_TIME NOT NULL NUMBER(16)
PAGE_RENDER_TIME NOT NULL NUMBER(16)
DOCUMENT_READY_TIME NOT NULL NUMBER(16)
DOCUMENT_DOWNLOAD_TIME NOT NULL NUMBER(16)
DOCUMENT_PROCESSING_TIME NOT NULL NUMBER(16)
FIRST_BYTE_TIME NOT NULL NUMBER(16)
RESPONSE_AVAILABLE_TIME NOT NULL NUMBER(16)
SERVER_CONNECTION_TIME NOT NULL NUMBER(16)
DNSLOOKUPTIME NOT NULL NUMBER(16)
TCPCONNECTTIME NOT NULL NUMBER(16)
IP NOT NULL VARCHAR2(16)
BROWSER_VERSION NOT NULL VARCHAR2(16)
COUNTRY_CODE NOT NULL VARCHAR2(16)
LATITUDE NOT NULL VARCHAR2(16)
LONGITUDE NOT NULL VARCHAR2(16)
USER_AGENT NOT NULL VARCHAR2(512)
SLOW_URL_THRESHOLD NOT NULL VARCHAR2(16)
ISSLOWREQUEST NOT NULL NUMBER(16)
IS_ERROR NOT NULL NUMBER(16)
IS_SATISFIED NOT NULL NUMBER(16)
IS_TOLERATING NOT NULL NUMBER(16)
IS_FRUSTRATED NOT NULL NUMBER(16)
ERROR_MESSAGE NOT NULL VARCHAR2(2000)
);

I am having the index as below.

================================
ALTER TABLE EUM ADD constraint PK_EUM PRIMARY KEY (GUID, REQUEST_DATE,PAGE_LOAD_TIME );

CREATE INDEX IDX_EUM ON EUM(APPLICATION_NAME,ISSLOWREQUEST,IS_ERROR,IS_SATISFIED,IS_TOLERATING,IS_FRUSTR
ATED,MSMT_TIME);

CREATE INDEX IDX1_EUM ON EUM(APPLICATION_NAME,DEVICE,MSMT_TIME);

CREATE INDEX IDX2_EUM ON EUM(APPLICATION_NAME,GROUPNAME,MSMT_TIME);

CREATE INDEX IDX3_EUM ON EUM(APPLICATION_NAME,BROWSER,MSMT_TIME);

CREATE INDEX IDX4_EUM ON EUM(APPLICATION_NAME,PAGETYPE,MSMT_TIME);

CREATE INDEX IDX5_EUM ON EUM(APPLICATION_NAME,COUNTRY,REGION,CITY,MSMT_TIME);

Query with ORDER BY clause

==================
SQL > SQL> SELECT *
2 FROM
3 (SELECT tmp.*,
4 rownum rn
5 FROM
6 (SELECT RUM_USER_EXP,
7 REQUEST_DATE,
8 REQUEST_TIME_ZONE,
9 URL,
10 PageType,
11 REFERRER,
12 SESSIONID,
13 PAGE_LOAD_TIME,
14 FRONT_END_TIME,
15 PAGE_RENDER_TIME,
16 DOCUMENT_READY_TIME,
17 DOCUMENT_DOWNLOAD_TIME,
18 DOCUMENT_PROCESSING_TIME,
19 FIRST_BYTE_TIME,
20 RESPONSE_AVAILABLE_TIME,
21 SERVER_CONNECTION_TIME,
22 DNSLOOKUPTIME,
23 TCPCONNECTTIME,
24 IP,
25 DEVICE,
26 BROWSER,
27 OS,
28 COUNTRY,
29 REGION,
30 CITY,
31 ERROR_MESSAGE,
32 TO_CHAR(msmt_time,'mm/dd/yyyy hh24:mi:ss') AS msmt_time
33 FROM EUM
34 WHERE APPLICATION_NAME='Demo'
35 AND ISSLOWREQUEST=1
36 AND msmt_time >= TO_DATE('02/01/2015 00:00:00','mm/dd/yyyy hh24:mi:ss'
)
37 AND msmt_time <= TO_DATE('02/02/2015 00:00:00','mm/dd/yyyy hh24:mi:ss')
38 ORDER BY PAGE_LOAD_TIME DESC, REQUEST_DATE DESC ) tmp
39 WHERE rownum <= 30)
40 WHERE rn > 10


20 rows selected.

Elapsed: 00:00:18.14

Execution Plan

--------------------------------------------------------------------------------
---------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| T
ime |
--------------------------------------------------------------------------------
---------
| 0 | SELECT STATEMENT | | 30 | 54600 | | 25797 (1)| 0
0:05:10 |
|* 1 | VIEW | | 30 | 54600 | | 25797 (1)| 0
0:05:10 |
|* 2 | COUNT STOPKEY | | | | | |
|
| 3 | VIEW | | 77173 | 132M| | 25797 (1)| 0
0:05:10 |
|* 4 | SORT ORDER BY STOPKEY| | 77173 | 20M| 25M| 25797 (1)| 0
0:05:10 |
|* 5 | TABLE ACCESS FULL | EUM | 77173 | 20M| | 21246 (1)| 0
0:04:15 |
--------------------------------------------------------------------------------
---------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("RN">10)
2 - filter(ROWNUM<=30)
4 - filter(ROWNUM<=30)
5 - filter("MSMT_TIME"<=TO_DATE(' 2015-02-02 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') AND "ISSLOWREQUEST"=1 AND "APPLICATION_NAME"='Demo' A
ND
"MSMT_TIME">=TO_DATE(' 2015-02-01 00:00:00', 'syyyy-mm-dd hh24:mi:
ss'))


Statistics
----------------------------------------------------------
267 recursive calls
34 db block gets
78658 consistent gets
78289 physical reads
592 redo size
4378 bytes sent via SQL*Net to client
535 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
11 sorts (memory)
0 sorts (disk)
20 rows processed

===============================================
The above same query without order by clause giving this execution plan
===============================================
Elapsed: 00:00:01.35

Execution Plan
----------------------------------------------------------
Plan hash value: 3775099455

--------------------------------------------------------------------------------
---------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| T
ime |
--------------------------------------------------------------------------------
---------
| 0 | SELECT STATEMENT | | 30 | 54600 | 23 (0)| 0
0:00:01 |
|* 1 | VIEW | | 30 | 54600 | 23 (0)| 0
0:00:01 |
|* 2 | COUNT STOPKEY | | | | |
|
| 3 | TABLE ACCESS BY INDEX ROWID| EUM | 31 | 8494 | 23 (0)| 0
0:00:01 |
|* 4 | INDEX RANGE SCAN | IDX_EUM | | | 4 (0)| 0
0:00:01 |
--------------------------------------------------------------------------------
---------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("RN">10)
2 - filter(ROWNUM<=30)
4 - access("APPLICATION_NAME"='Demo' AND "ISSLOWREQUEST"=1 AND
"MSMT_TIME">=TO_DATE(' 2015-02-01 00:00:00', 'syyyy-mm-dd hh24:mi:
ss') AND
"MSMT_TIME"<=TO_DATE(' 2015-02-02 00:00:00', 'syyyy-mm-dd hh24:mi:
ss'))
filter("MSMT_TIME"<=TO_DATE(' 2015-02-02 00:00:00', 'syyyy-mm-dd
hh24:mi:ss') AND "MSMT_TIME">=TO_DATE(' 2015-02-01 00:00:00', 'syy
yy-mm-dd
hh24:mi:ss'))


Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
13 consistent gets
11 physical reads
0 redo size
4378 bytes sent via SQL*Net to client
535 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
20 rows processed

Thanks Regards, Databases