[rt-devel] Behaviour correction for SB-0.88 (improves Oracle support)
brooksch at mac.com
Thu Jul 17 08:20:44 EDT 2003
On Thursday, July 17, 2003, at 07:35 PM, Paul Wagland wrote:
> Figured I would chime in... I can't say whether the change is correct
> not, will look into testing this next Monday... off to Frankfurt for
> the REM
> concert in the meantime ;-)
The issue isn't whether the change is correct or not - its whether the
SQL that is generated gives the same result set as previous
implementations (or a result set the same as the MySQL or PostgreSQL
implementations - which wasn't the case).
This change to the distinct code does not change the result set
returned - but does improve the SQL generated.
This also made it easier for me to make the next change to SB-0.88 - in
that changing the order of the building of the SQL removed the syntax
problems with the SQL generated for Oracle.
The only issue I could see creeping up is the ($key IS NULL or $key =
'') change - as that may not be portable between other databases. This
is due to the differences between CLOB and VARCHAR2 in Oracle (this has
been detailed in a previous post of mine).
> But with postgres, this statement appears to be true:
This SQL isn't generated for PostgreSQL. Only Oracle uses the nested
SELECT DISTINCT to avoid the problem of being unable to perform a
DISTINCT on a table with a CLOB column type.
I believe that the previous code would have generated an excessive
amount of SELECT DISTINCT queries when they are unnecessary.
> Here is the explain texts for these. (We have 18 queues, 1 of which is
> disabled). Mind you, this probably won't make a huge difference in the
> interface... but every little bit helps I guess...
Since this improvement is present in all queries - there would be an
improvement by not needing a DISTINCT. Remember that this change would
only modify the SQL for PostgreSQL/MySQL from:
SELECT DISTINCT main.* FROM Queues main WHERE (main.Disabled = '0')
SELECT main.* FROM Queues main WHERE (main.Disabled = '0')
which should result in very little cost saving - but a much bigger
improvement for Oracle users. Not that there are any Oracle users (or
at least not many - because support is not complete).
BTW: There are many other SQL queries that were generated using an
unnecessary DISTINCT - so there will be savings by removing this when
it isn't necessary - especially on large datasets most useful for
installations which have large numbers of users.
Additional Oracle support is still on the way!
More information about the Rt-devel