Using EF6 and ODP.NET 12.2 I have the following query:
db.SAMPLES.OrderBy(p => p.NAME).ThenBy(p => p.DESCRIPTION).Skip(0).Take(10).ToList();
I'm wondering why this can be translated to:
SELECT *
FROM (
SELECT
"Extent1"."ID" AS "ID",
"Extent1"."NUMBER" AS "NUMBER",
"Extent1"."NAME" AS "NAME",
"Extent1"."DESCRIPTION" AS "DESCRIPTION"
FROM ( SELECT "Extent1"."ID" AS "ID", "Extent1"."NUMBER" AS "NUMBER", "Extent1"."NAME" AS "NAME", "Extent1"."DESCRIPTION" AS "DESCRIPTION", row_number() OVER (ORDER BY "Extent1"."NAME" ASC, "Extent1"."DESCRIPTION" ASC) AS "row_number"
FROM "ZENKI"."SAMPLEs" "Extent1"
) "Extent1"
WHERE ("Extent1"."row_number" > 0)
ORDER BY "Extent1"."NAME" ASC, "Extent1"."DESCRIPTION" ASC
)
WHERE (ROWNUM <= (10) )
It seems to me that the line in bold is not needed, is already sorted in the inner FROM, but instead is killing the query performance. ¿Why?