The slq genreated creates queries that cause a full table scan for pagination. Is there any way to fix this?
I am using
ODP.NET ODTwithODAC1120320_32bit
ASP.NET 4.5
EF 5
Oracle 11gR2
This table has 2 million records. The further into the records you page the longer it takes.
LINQ
var cnt = (from errorLog in ctx.ERRORLOGANDSERVICELOG_VIEW
select errorLog).Count();
var query = (from errorLog in ctx.ERRORLOGANDSERVICELOG_VIEW
orderby errorLog.ERR_LOG_ID
select errorLog).Skip(cnt-10).Take(10).ToList();
Here is the query & execution plans.
SELECT *
FROM (SELECT "Extent1"."ERR_LOG_ID" AS "ERR_LOG_ID",
"Extent1"."SRV_LOG_ID" AS "SRV_LOG_ID",
"Extent1"."TS" AS "TS",
"Extent1"."MSG" AS "MSG",
"Extent1"."STACK_TRACE" AS "STACK_TRACE",
"Extent1"."MTD_NM" AS "MTD_NM",
"Extent1"."PRM" AS "PRM",
"Extent1"."INSN_ID" AS "INSN_ID",
"Extent1"."TS_1" AS "TS_1",
"Extent1"."LOG_ETRY" AS "LOG_ETRY"
FROM (SELECT "Extent1"."ERR_LOG_ID" AS "ERR_LOG_ID",
"Extent1"."SRV_LOG_ID" AS "SRV_LOG_ID",
"Extent1"."TS" AS "TS",
"Extent1"."MSG" AS "MSG",
"Extent1"."STACK_TRACE" AS "STACK_TRACE",
"Extent1"."MTD_NM" AS "MTD_NM",
"Extent1"."PRM" AS "PRM",
"Extent1"."INSN_ID" AS "INSN_ID",
"Extent1"."TS_1" AS "TS_1",
"Extent1"."LOG_ETRY" AS "LOG_ETRY",
row_number() OVER (ORDER BY "Extent1"."ERR_LOG_ID" ASC) AS "row_number"
FROM (SELECT "ERRORLOGANDSERVICELOG_VIEW"."ERR_LOG_ID" AS "ERR_LOG_ID",
"ERRORLOGANDSERVICELOG_VIEW"."SRV_LOG_ID" AS "SRV_LOG_ID",
"ERRORLOGANDSERVICELOG_VIEW"."TS" AS "TS",
"ERRORLOGANDSERVICELOG_VIEW"."MSG" AS "MSG",
"ERRORLOGANDSERVICELOG_VIEW"."STACK_TRACE" AS "STACK_TRACE",
"ERRORLOGANDSERVICELOG_VIEW"."MTD_NM" AS "MTD_NM",
"ERRORLOGANDSERVICELOG_VIEW"."PRM" AS "PRM",
"ERRORLOGANDSERVICELOG_VIEW"."INSN_ID" AS "INSN_ID",
"ERRORLOGANDSERVICELOG_VIEW"."TS_1" AS "TS_1",
"ERRORLOGANDSERVICELOG_VIEW"."LOG_ETRY" AS "LOG_ETRY"
FROM "IDS_CORE"."ERRORLOGANDSERVICELOG_VIEW" "ERRORLOGANDSERVICELOG_VIEW") "Extent1") "Extent1"
WHERE ("Extent1"."row_number" > 1933849)
ORDER BY "Extent1"."ERR_LOG_ID" ASC)
WHERE (ROWNUM <= (10))
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 31750 | | 821K (1)| 02:44:15 |
|* 1 | COUNT STOPKEY | | | | | | |
| 2 | VIEW | | 1561K| 4728M| | 821K (1)| 02:44:15 |
|* 3 | VIEW | | 1561K| 4748M| | 821K (1)| 02:44:15 |
| 4 | WINDOW SORT | | 1561K| 3154M| 4066M| 821K (1)| 02:44:15 |
|* 5 | HASH JOIN OUTER | | 1561K| 3154M| | 130K (1)| 00:26:09 |
| 6 | TABLE ACCESS FULL| IDS_SERVICES_LOG | 1047 | 52350 | | 5 (0)| 00:00:01 |
| 7 | TABLE ACCESS FULL| IDS_SERVICES_ERROR_LOG | 1561K| 3080M| | 130K (1)| 00:26:08 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=10)
3 - filter("Extent1"."row_number">1933849)
5 - access("T1"."SRV_LOG_ID"(+)="T2"."SRV_LOG_ID")