Making data fetching faster on VIEW

Question

I have created a rest service which fetches data from Oracle database and send it back to the application from which the rest service is called .

Below is the query to fetch data from VIEW ,this query supports pagination:

SELECT WORKFLOW_ID,COLLABRATION_KEY,WORKFLOWDATE,CURRENTSTEP,
STEPTIME,REQUESTOR FROM ( SELECT VIEWPAGE.*, rownum PAGEREQUEST 
FROM 
 (SELECT * FROM VIEW1 WHERE CDATE 
 BETWEEN TO_TIMESTAMP('2016-01-06 00:00:00.0', 'YYYY-MM-DD HH24:MI:SS,FF1') 
 AND TO_TIMESTAMP('2016-01-14 23:59:59.009', 'YYYY-MM-DD HH24:MI:SS,FF3') 
 ORDER BY CDATE DESC )VIEWPAGE 
 WHERE rownum < ((pageNum*pageSize) + 1 )) 
WHERE PAGEREQUEST >= (((pageNum-) *pageSize) + 1)

The above query works fine when the no of records is very few ,but this query takes more than a minutes if no of records is in thousands .

We have already indexed most of the columns but still it is very slow .

Any tips to make it faster would be highly appreciated.

Thanks .

UPDATE :

Here is the VIEW query as requested .

create view VIEW1 as
select TAB4.workflow_id , TAB4.collabration_key ,TAB4.WORKFLOWDATE ,TAB4.CURRENT_STEP_NAME,TAB4.CURRENTSTEP ,TAB4.STEPTIME,TAB4.JOB_STATUS,workflow_request.REQUESTOR from
(select workflow_id,collabration_key,TAB3.START_TIME as WORKFLOWDATE,batch_step_execution.STEP_NAME AS CURRENT_STEP_NAME , batch_step_execution.EXIT_CODE AS CURRENTSTEP ,batch_step_execution.start_time as STEPTIME ,TAB3.EXIT_CODE as JOB_STATUS
from batch_step_execution
inner join
(select * from app_id_mapping
inner join
(select batch_job_execution.job_execution_id,batch_job_execution.job_instance_id ,batch_job_execution.START_TIME ,batch_job_execution.EXIT_CODE
from batch_job_execution where start_time in (select max(start_time) 
FROM batch_job_execution where job_instance_id in (select job_id from app_id_mapping) group by job_instance_id)) TAB2
on app_id_mapping.job_id = TAB2.job_instance_id) TAB3
on batch_step_execution.job_execution_id = TAB3.job_execution_id
where batch_step_execution.start_time = (select max(start_time) 
from batch_step_execution where batch_step_execution.job_execution_id = TAB3.job_execution_id)) TAB4
inner join workflow_request 
on TAB4.WORKFLOW_ID=WORKFLOW_REQUEST.WORKFLOW_ID

UDPATE :-

Here is the execution plan .

------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 202 | 480 (2)| 00:00:06 |
|* 1 | FILTER | | | | | |
| 2 | NESTED LOOPS | | | | | |
| 3 | NESTED LOOPS | | 1 | 202 | 390 (2)| 00:00:05 |
|* 4 | HASH JOIN | | 1 | 158 | 389 (2)| 00:00:05 |
|* 5 | HASH JOIN | | 1 | 97 | 380 (2)| 00:00:05 |
|* 6 | HASH JOIN | | 4329 | 232K| 231 (2)| 00:00:03 |
| 7 | VIEW | VW_SQ_1 | 4329 | 109K| 150 (2)| 00:00:02 |
| 8 | HASH GROUP BY | | 4329 | 90909 | 150 (2)| 00:00:02 |
| 9 | TABLE ACCESS FULL | BATCH_STEP_EXECUTION | 17405 | 356K| 149 (1)| 00:00:02 |
| 10 | TABLE ACCESS FULL | BATCH_JOB_EXECUTION | 4329 | 122K| 80 (0)| 00:00:01 |
| 11 | TABLE ACCESS FULL | BATCH_STEP_EXECUTION | 17405 | 713K| 149 (1)| 00:00:02 |
| 12 | TABLE ACCESS FULL | APP_ID_MAPPING | 2565 | 152K| 8 (0)| 00:00:01 |
|* 13 | INDEX UNIQUE SCAN | SYS_C00434473 | 1 | | 0 (0)| 00:00:01 |
| 14 | TABLE ACCESS BY INDEX ROWID| WORKFLOW_REQUEST | 1 | 44 | 1 (0)| 00:00:01 |
|* 15 | FILTER | | | | | |
| 16 | HASH GROUP BY | | 25 | 475 | 90 (3)| 00:00:02 |
|* 17 | HASH JOIN | | 4208 | 79952 | 89 (2)| 00:00:02 |
| 18 | TABLE ACCESS FULL | APP_ID_MAPPING | 2565 | 10260 | 8 (0)| 00:00:01 |
| 19 | TABLE ACCESS FULL | BATCH_JOB_EXECUTION | 4329 | 64935 | 80 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
 1 - filter( EXISTS (SELECT 0 FROM APP_USER."BATCH_JOB_EXECUTION" 
 "BATCH_JOB_EXECUTION",APP_USER."APP_ID_MAPPING" "APP_ID_MAPPING" WHERE 
 "JOB_INSTANCE_ID"="JOB_ID" GROUP BY "JOB_INSTANCE_ID" HAVING MAX("START_TIME")=:B1))
 4 - access("APP_ID_MAPPING"."JOB_ID"="BATCH_JOB_EXECUTION"."JOB_INSTANCE_ID")
 5 - access("BATCH_STEP_EXECUTION"."START_TIME"="MAX(START_TIME)" AND 
 "BATCH_STEP_EXECUTION"."JOB_EXECUTION_ID"="BATCH_JOB_EXECUTION"."JOB_EXECUTION_ID")
 6 - access("ITEM_1"="BATCH_JOB_EXECUTION"."JOB_EXECUTION_ID")
 13 - access("APP_ID_MAPPING"."WORKFLOW_ID"="WORKFLOW_REQUEST"."WORKFLOW_ID")
 15 - filter(MAX("START_TIME")=:B1)
 17 - access("JOB_INSTANCE_ID"="JOB_ID")

UPDATE :- Is their something wrong with the pagination logic ? I believe VIEW performance is fine .

• zgguy: You should post the DDL for your view and underlying tables.
• user1822: Please add the execution plan (formatted text please, no screen shots)
• DevG: a_horse_with_no_name This is not screen shots ,Can you please tell me what do you mean by execution plan?
• Philᵀᴹ: @DevG Query plans. oracle-base.com/articles/9i/dbms_xplan
• DevG: a_horse_with_no_name updated with execution plan.

Answer 1

You can use MATERIALIZED VIEW to get faster response then the simple view in oracle.

here is the syntax for the same. try this in your query.

CREATE MATERIALIZED VIEW hr.employees AS SELECT * FROM hr.employees;

• DevG: Hi Tejas , We always need updated data , i think MATERIALIZED VIEW is not a good option ,what do you think ?
• Tejas Patel: I think Materialized works perfec. atleast you can try it once to check the response.
• Vérace: This is very confusing. It should be CREATE MATERIALIZED VIEW employee_mv AS SELECT * FROM hr.employees. Calling it the view the same name as its own schema won't work (probably - don't have Oracle to test). Even if it does, it would not be a sensible thing to do.
• Tejas Patel: This is only example that i have to show you, you can try this query at your own end to check response of your data only.
• Vérace: Still doesn't get away from the fact that giving database objects the same names is a bad (bad...) idea even if the syntax will work. I don't have Oracle handy to test.