| Careerhunt - Fast track your career as a ninja

Query optimization



  • https://blog.acolyer.org/2016/01/04/access-path-selection/

    The four phases of (SQL) statement processing in System R are:

    Parsing
    Optimization
    Code Generation
    Execution
    We’re concerned here with the optimization phase, and in particular with access path selection. An access path is a way of accessing the tuples of a relation – there is always the possibility of a full (data) segment scan, but there may also be one or more indices.



  • If the subquery returns at least one row, the result of EXISTS is true. In case the subquery returns no row, the result is of EXISTS is false.

    EXISTS is often used with the correlated subquery.

    The result of EXISTS depends on whether any row returned by the subquery, and not on the content of the rows. Therefore, the columns that appear on the SELECT clause of the subquery are not important.

    For this reason, the common coding convention is to write EXISTS in the following form:

    SELECT
    column_1
    FROM
    table_1
    WHERE
    EXISTS( SELECT
    1
    FROM
    table_2
    WHERE
    column_2 = table_1.column_1);

    Note that if the subquery returns NULL, the result of EXISTS is true.



  • The difference between a semi-join and a conventional join is that rows in the first table will be returned at most once. Even if the second table contains two matches for a row in the first table, only one copy of the row will be returned. Semi-joins are written using the EXISTS or IN constructs.


Log in to reply
 

Looks like your connection to CareerHunt was lost, please wait while we try to reconnect.