Hi Mariusz and Simon, Yes, agreed, it is true that Oracle doesn't support column aliases in GROUP BY. It is possible to use "GROUP BY <column-ordinal>", but only if a specific Oracle session parameter setting is used. IMO it would be undesirable to make this setting mandatory for Django, though.
Oracle definitely deals well with the example I gave, and the optimizer is very good at avoiding performance regressions when it is used (provided that optimizer transformations such as simple view merging are not disabled). It is, after all, an easy transformation to spot and transformations are applied even in very complex cases of course. IMHO, the effect on query performance is unlikely to be a significant concern. Tuning Oracle cursor sharing is something I've considered, but there is no solution in cases where bind variable names are used and are jumbled. Each version of the SQL statement will be hard parsed (rather than soft parsed) and a new cursor will be created. Sadly, the performance implications of this are almost always underestimated; probably because they don't become apparent until the application feels some heat. I don't want to over-state the issue too much though - when I say "some heat" I really mean "significant heat", but as a long-term Oracle user it is a no-brainer to empower cursor sharing to do its part in making sure that the database runs smoothly all the way up to machine/VM limits. Regards, Nigel. On Tuesday, 13 April 2021 at 06:31:51 UTC+1 Mariusz Felisiak wrote: > Hi Nigel, > > Creating a subquery only looks like a good solution for a limited > number of cases. As far as I'm aware it will also change an execution plan > and can cause a performance regression in complicated queries. We also > cannot group by column aliases on Oracle as proposed in PR14251 > <https://github.com/django/django/pull/14251>. You can try to tune cursor > sharing > <https://docs.oracle.com/en/database/oracle/oracle-database/19/tgsql/improving-rwp-cursor-sharing.html#GUID-971F4652-3950-4662-82DE-713DDEED317C> > > but I don't have any advice here. I would be happy to review a patch in > this area, so feel-free to prepare an optimization. > > Best, > Mariusz > -- You received this message because you are subscribed to the Google Groups "Django developers (Contributions to Django itself)" group. To unsubscribe from this group and stop receiving emails from it, send an email to django-developers+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/django-developers/eacf369c-5ac2-4832-8b39-b63452d6ee90n%40googlegroups.com.
