[Info-vax] Oracle on VMS

[Jan-Erik Soderholm]

Den 2016-11-20 kl. 15:14, skrev Arne Vajhøj:
> On 11/20/2016 1:01 AM, IanD wrote:
>> Yes, modern DB's do cache execution plans, RDB I believe was the
>> first to come up with the idea of a query optimizer and unlike MySQL
>> (or how it used to be) with RDB you can access the query optimiser
>> for free and it's cincluded in the price and even make changes to it
>> to force your own behavior if you believe the DB got it wrong on it's
>> strategy (usually happens when it gets the number of tuples wrong on
>> complex joins etc)
>
> Query optimizer is generally included in the price today (also for
> MySQL @ $0).
>
> But I am not aware of any database where you can change the plan.

Rdb.

See chapter "5.9 Ensuring Query Stability, Controllability, and
Performance with Query Outlines" in the manual "Oracle Rdb Guide
to Database Performance and Tuning, Volume 1 and Volume 2"

SQL commands CREATE OUTLINE, SHOW OUTLINE, DROP OUTLINE and so on.

>
>> I wonder with the push for dynamic languages how much optimisation is
>> worth it at compile time versus having a smarter optimiser at run
>> time? I guess some information could be supplied to the DB in advance
>> because RDB creates a strategy at run time and formulates it's
>> strategy then - being forewarned wth inside knowledge from an EXE
>> might help?
>
> With the nature of SQL then I would expect the database to be
> better than the application to optimize.
>

As the manual above say, the main reason is not to be "better then
the computer", but to ensure equal performance over Rdb releases.

In some rare cases, a new Rdb release can have minor changes to the
optimizer that causes specific SQL statements to perform worse than
before. In that case, an OUTLINE from the previouse release can help
keep the performance the same until the cause has been found and fixed.

So it is not that much for actually *changing* the plan, but it can
be, and has been, used for that also.

> Fundamentally the SQL just defines the data needed. If we
> assume that the SQL does not request data that is not actually
> needed, then there should be little that the application
> can tell the database.

Right. The QUERY OUTLINE is usualy created by first executing the
SQL in an environment (Rdb release) where it executes OK, then
storing that OUTLINE into the database to ensure performance efter
an Rdb upgrade.

This feature come with Rdb V6.0 (must be over 10 years ago now)
and is probably not used as much today as it was.


>
> Arne