Oracle backend: slicing is using row_number() instead of rownum
|Reported by:||Owned by:||nobody|
|Component:||Database layer (models, ORM)||Version:||1.0|
|Has patch:||yes||Needs documentation:||no|
|Needs tests:||no||Patch needs improvement:||no|
Doing Entity.objects.all()[:10] is generating a query like:
SELECT * FROM (SELECT (ROW_NUMBER() OVER (ORDER BY "ENTITY"."ID")) AS "_RN", "ENTITY"."ID", "ENTITY"."NAME" FROM "ENTITY") WHERE "_RN" > 0 AND "_RN" <= 10
This is suboptimal as a full table scan is forced (at least in 10g).
Using rownum instead of row_number() is faster (it can use indexes), and doesn't require an order by clause when not required. The query would look like:
SELECT "ENTITY"."ID", "ENTITY"."NAME" FROM "ENTITY" WHERE ROWNUM > 0 AND ROWNUM <= 10 ORDER BY "ENTITY"."ID"
It won't make a noticeable difference with tables with a couple of rows; but when you are reaching the million rows, you go from minutes to milliseconds.
Change History (10)
comment:1 Changed 8 years ago by
|Patch needs improvement:||unset|