Hi there - I think this question/idea is still relevant. I made these changes:
https://github.com/nigelbayliss/django/commit/83b6b339ad58b0c1ac9283e49d7df9c9ea79b8d9 https://github.com/nigelbayliss/django/commit/7c3513cf97dc8ebee124cd3ddd154ca3d24d192c What's the right thing to do next? I don't expect this to be anywhere near the best solution, but I think it's an idea that's worth some consideration. Should I - for example - create a pull request? Regards, Nigel On Tuesday, 27 April 2021 at 18:56:34 UTC+1 NPB wrote: > Hi there - > > Should I create a ticket? > > Thanks and regards, > Nigel. > > On Wednesday, 14 April 2021 at 16:19:54 UTC+1 NPB wrote: > >> Hi - >> >> Here is something I tried out: >> >> https://github.com/nigelbayliss/django >> >> I am no Python expert, and having never done this before I'm not sure if >> it would be worthy of a pull request. Let me know. >> >> I added a test to *compiler.py *to detect GROUP BYs with a combined >> expression. This test is a little less specific than I'd like because it >> would be nice to target combined expressions that will include a named >> parameter placeholder. I don't know how to implement this, though. >> Nevertheless, it looks like the *execute_with_val_placeholder_in_gb* >> method added to handle GROUP BYs with expressions (in Oracle's base.py) is >> called only once (via *aggregation_regress*). >> >> No unit test issues were seen for Oracle or SQLLite (I ran all enabled >> tests). >> >> The implementation idea avoids having to wrap the GROUP BY query in an >> outer query block and makes use of the existing *unify by values * >> solution. >> >> Regards, >> Nigel >> >> On Tuesday, 13 April 2021 at 14:23:15 UTC+1 NPB wrote: >> >>> P.S. >>> >>> I think I will have a stab at creating a solution. I'll run it through >>> the queries, aggregation, and aggregation_regress tests. >>> >>> Thanks, >>> Nigel >>> >>> On Tuesday, 13 April 2021 at 13:34:30 UTC+1 NPB wrote: >>> >>>> 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/496e7daf-03db-4046-bc70-13d3ba18e31fn%40googlegroups.com.
