I also think it should not be directly in the SQL. I like the
not-really-a-comment syntax. Another option might be a few PRAGMAs,
something like
PRAGMA hint("table1.col1 IN (1,2,5)", 0.05);
PRAGMA hint("table1.col2 LIKE '%bach%'". 0.4);
these would add the hints to an internal table. When preparing a query, the
planner would check the hints table to see if any hints match the
table/column/condition triplet, and if so optionally use the hint. Removing
a hint and removing all hints would also be a couple of PRAGMAs.
On Wed, Sep 11, 2013 at 3:53 AM, kyan <alfasud...@gmail.com> wrote:
> Hello Dr Hipp,
>
> First of all, I apologize for this rather off-topic suggestion knowing that
> you may have already implemented the syntax you describe, but there is an
> IMHO good reason for it, read ahead.
>
> On Tue, Sep 10, 2013 at 10:26 PM, Richard Hipp <d...@sqlite.org> wrote:
>
> > SELECT DISTINCT aname
> > FROM album, composer, track
> > WHERE unlikely(cname LIKE '%bach%')
> > AND composer.cid=track.cid
> > AND album.aid=track.aid;
> >
>
> I would prefer that the planner hint is not interleaved inside normal SQL
> syntax. Instead I propose a special comment-like syntax instead, as
> Oracle's /*+ */ or --+, but replacing "+" with another symbol, e.g. ">":
>
> SELECT DISTINCT aname
> > FROM album, composer, track
> > WHERE cname LIKE '%bach%'
> > /*> unlikely */
> > AND composer.cid=track.cid AND album.aid=track.aid;
> >
>
> or:
>
> SELECT DISTINCT aname
> > FROM album, composer, track
> > WHERE cname LIKE '%bach%'
> > --> unlikely
> > AND composer.cid=track.cid
> > AND album.aid=track.aid;
>
>
> If the hint is to be applied to an expression that combines many column
> predicates with AND (I am not sure if this actually makes sense):
>
> SELECT DISTINCT aname
> > FROM album, composer, track
> > WHERE unlikely(cname LIKE '%bach%'
> > AND composer.cid=track.cid)
> > AND album.aid=track.aid;
> >
>
> then a -normally redundant- pair of parentheses can be used to specify the
> scope of the hint:
>
> SELECT DISTINCT aname
> > FROM album, composer, track
> > WHERE (cname LIKE '%bach%' AND composer.cid=track.cid) /*> unlikely */
> > AND album.aid=track.aid;
> >
>
> The SQLite SQL parser will have to look for exactly "/*>" or "-->" without
> whitespace between the characters, so it can easily tell a planner hint
> from a plain comment with a single character read-ahead. Also, the fact
> that hints are "transparent" to the SQL syntax will allow the query parser
> to handle them in an "orthogonal" way (e.g. a small separate parser for
> hints) to normal SQL parsing, IMO making handling of any future hints
> easier to add.
>
> The main reason for this proposal is that the planner hint will be ignored
> by default by other SQL parsers without the need to modify them, which in
> some cases may not even be possible. For instance it will allow someone to
> write SQL that is valid in databases of alternative DB vendors and still
> provide planner hints when the DB vendor is SQLite (that is why I replaced
> "+" with ">", to avoid conflicts with a hypothetical alternate Oracle query
> optimizer) without having to modify the SQL in the application code to
> remove the hints. This is a property of the Oracle optimizer hint syntax I
> have always appreciated when writing SQL that is to be executed in
> databases of alternative DB vendors with the same schema, for applications
> where the user chooses the database vendor from a list of supported ones.
>
> For more on Oracle optimizer hints see
> http://docs.oracle.com/cd/B19306_01/server.102/b14211/hintsref.htm.
>
> As for the name of the hint itself I would propose:
>
> --> PROBABLY(True) -- the current default
> --> PROBABLY(False)
> --> PROBABLY(False, 0.7)
> --> PROBABLY(False, 0.6, 0.3) --re "pedantic detail", the second value if
> for True, the remainder for NULL.
>
> Kind regards,
>
> Constantine Yannakopoulos
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
--
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
