Previous Next JavaScript must be enabled to correctly display this content

## Examples of queries using index

You can write simple queries to understand how an index is used.

Query 1:

Fetch the bag details of passengers for ticket numbers satisfying 2 range of values.

    SELECT fullname, ticketNo,bag.bagInfo[].tagNum,
    bag.bagInfo[].routing
    FROM BaggageInfo bag WHERE 1762340000000 < ticketNo
    AND ticketNo < 1762352000000

In the above example, the query contains 2 index predicates. The primary key index is used as `ticketNo` is the primary key here. For the primary key index, 1762340000000 < ticketNo is a start predicate and ticketNo < 1762352000000 is a stop predicate.

A portion of the query plan is shown below. You can see the primary index being used.

    "iterator kind" : "TABLE",
    "target table" : "BaggageInfo",
    "row variable" : "$$bag",
    "index used" : "primary index",
    "covering index" : false,
    "index scans" :
    [
      {
        "equality conditions" : {},
        "range conditions" : { "ticketNo" : { "start value" : 1762340000000,
           "start inclusive" : false,
           "end value" : 1762352000000,
           "end inclusive" : false } }
      }
    ]

For more information on how a query is executed, see Query execution plan.

Query 2:

Fetch the bag details of passengers for ticket numbers satisfying one of the two ranges of values.

    SELECT fullname, ticketNo,bag.bagInfo[].tagNum,
    bag.bagInfo[].routing
    FROM BaggageInfo bag
    WHERE ticketNo > 1762340000000 OR
    ticketNo < 1762352000000

In the above example, the query contains 1 index predicate, which is the whole WHERE expression. The primary key index is used as `ticketNo` is the primary key here. The predicate is a filtering predicate.

A portion of the query plan is shown below. You can see the primary index and the index filtering predicates being used.

    "iterator kind" : "TABLE",
    "target table" : "BaggageInfo",
    "row variable" : "$$bag",
    "index used" : "primary index",
    "covering index" : false,
    "index scans" :
    [
      {
        "equality conditions" : {},
        "range conditions" : {}
      }
    ],
    "index filtering predicate" :
    {
      "iterator kind" : "OR",
      "input iterators" :
      [
        {
          "iterator kind" : "GREATER_THAN",
          "left operand" :
            {
              ---
            },
          "right operand" :
            {
              ---
            }
        },
        {
          "iterator kind" : "LESS_THAN",
          "left operand" :
            {
              ---
            },
          "right operand" :
            {
              ---
            }
        }
      ]
    }

For more information on how a query is executed, see Query execution plan.

Query 3:

Fetch the bag details for a particular reservation code.

    SELECT fullName,bag.ticketNo, bag.confNo,
    bag.bagInfo[].tagNum,bag.bagInfo[].routing
    FROM BaggageInfo bag WHERE bag.confNo="FH7G1W"

In the above example, two indexes are applicable `compindex_tckNoconfNo` and `fixedschema_conf` .

A portion of the query plan is shown below. The `fixedschema_conf` is used as that is a single index on `ticketNo`. An index scan is performed with the equality condition.

    "iterator kind" : "TABLE",
    "target table" : "BaggageInfo",
    "row variable" : "$$bag",
    "index used" : "fixedschema_conf",
    "covering index" : false,
    "index scans" :
    [
      {
        "equality conditions" : {"confNo":"FH7G1W"},
        "range conditions" : {}
      }
    ]

For more information on how a query is executed, see Query execution plan.

Query 4:

Fetch the name and routing details of all male passengers.

    SELECT fullname,bag.bagInfo[].routing FROM BaggageInfo bag
    WHERE gender!="F"

In the above example, there is no index predicate, because no index has information about gender.

A portion of the query plan is shown below. As there are no available indexes to be used, only the primary key index is used.

    "iterator kind" : "TABLE",
    "target table" : "BaggageInfo",
    "row variable" : "$$bag",
    "index used" : "primary index",
    "covering index" : false,
    "index scans" :
    [
      {
        "equality conditions" : {},
        "range conditions" : {}
      }
    ]

For more information on how a query is executed, see Query execution plan.

Query 5:

Fetch the name and phone number for all passengers.

    SELECT bag.contactPhone, bag.fullName FROM BaggageInfo bag
    ORDER BY bag.fullName

In the above example, only the index `compindex_namephone` is applicable. The sort (for the order by clause) will be index-based because the order-by expression matches the 1st field of the index used by the query. In this case, the full name and contact phone information needed in the SELECT clause is available in the index. As a result, the whole query can be answered from the index only, with no access to the table. So the index `compindex_namephone` is a covering index in this example. The query processor will apply this optimization.

A portion of the query plan is shown below. You can see the index `compindex_namephone` is used and it is a covering index.

    "iterator kind" : "TABLE",
    "target table" : "BaggageInfo",
    "row variable" : "$$bag",
    "index used" : "compindex_namephone",
    "covering index" : true,
    "index row variable" : "$$bag_idx",
    "index scans" :
    [
      {
        "equality conditions" : {},
        "range conditions" : {}
      }
    ]

For more information on how a query is executed, see Query execution plan.

Query 6:

Fetch the name, ticket number, and arrival date of passengers whose arrival date is greater than a given value.

    SELECT fullName, bag.ticketNo, bag.bagInfo[].bagArrivalDate
    FROM BaggageInfo bag WHERE EXISTS
    bag.bagInfo[$element.bagArrivalDate >="2019-01-01T00:00:00"]

In the above example, the EXISTS condition is actually converted to a filtering predicate. There is one filtering predicate which is the whole WHERE expression.

A portion of the query plan is shown below. The index `simpleindex_arrival` is used in this example.

    "iterator kind" : "TABLE",
    "target table" : "BaggageInfo",
    "row variable" : "$$bag",
    "index used" : "simpleindex_arrival",
    "covering index" : false,
    "index row variable" : "$$bag_idx",
    "index scans" :
    [
      {
        "equality conditions" : {},
        "range conditions" : {}
      }
    ],
    "index filtering predicate" :
    {
      "iterator kind" : "GREATER_OR_EQUAL",
      "left operand" :
        {
          ---
        },
      "right operand" :
        {
          ---
        }
    }

For more information on how a query is executed, see Query execution plan.

Query 7:

Fetch the reservation code and count of bags for all passengers.

    SELECT bag.confNo, count(bag.bagInfo) AS TOTAL_BAGS
    FROM BaggageInfo bag GROUP BY bag.confNo

In the above example, two indexes `fixedschema_conf` and `compindex_tckNoconfNo` are applicable.

A portion of the query plan is shown below. The index `fixedschema_conf` is used as that is a single index with only one column `confNo`. For this query, the group-by is index-based. As you need the entire bagInfo details to determine the number of bags using the aggregate count function, the index here is not covering.

    "iterator kind" : "TABLE",
    "target table" : "BaggageInfo",
    "row variable" : "$$bag",
    "index used" : "fixedschema_conf",
    "covering index" : false,
    "index scans" :
    [
      {
        "equality conditions" : {},
        "range conditions" : {}
      }
    ]

For more information on how a query is executed, see Query execution plan.

Query 8:

Fetch the full name and tag number of passengers who are in the given list of names.

    SELECT bagdet.fullName, bagdet.bagInfo[].tagNum
    FROM BaggageInfo bagdet
    WHERE bagdet.fullName IN
    ("Lucinda Beckman", "Adam Phillips",
    "Zina Christenson","Fallon Clements")

In the above example, only the index `compindex_namephone` is applicable.

A portion of the query plan is shown below. The index `compindex_namephone` is used. An index scan is performed on `compindex_namephone` evaluating four equality predicates.

    "iterator kind" : "TABLE",
    "target table" : "BaggageInfo",
    "row variable" : "$$bagdet",
    "index used" : "compindex_namephone",
    "covering index" : false,
    "index scans" :
    [
      {
        "equality conditions" : {"fullName":"Lucinda Beckman"},
        "range conditions" : {}
      },
      {
        "equality conditions" : {"fullName":"Adam Phillips"},
        "range conditions" : {}
      },
      {
        "equality conditions" : {"fullName":"Zina Christenson"},
        "range conditions" : {}
      },
      {
        "equality conditions" : {"fullName":"Fallon Clements"},
        "range conditions" : {}
      }
    ]

For more information on how a query is executed, see Query execution plan.

Query 9:

Select the ticket details( ticket number, reservation code, tag number, and routing) for a passenger with a specific ticket number and reservation code.

    SELECT fullName,bag.ticketNo, bag.confNo,
    bag.bagInfo[].tagNum,bag.bagInfo[].routing
    FROM BaggageInfo bag WHERE
    bag.ticketNo=1762311547917
    AND bag.confNo="FH7G1W"

In the above example, though the index `compindex_tckNoconfNo` is available, only the primary index (for `ticketNo`) gets used. An index scan is performed on the primary index and the WHERE expression is evaluated.

A portion of the query plan is shown below.

    "iterator kind" : "TABLE",
    "target table" : "BaggageInfo",
    "row variable" : "$$bag",
    "index used" : "primary index",
    "covering index" : false,
    "index scans" :
    [
      {
        "equality conditions" : {"ticketNo":1762311547917},
        "range conditions" : {}
      }
    ]

For more information on how a query is executed, see Query execution plan.

Query 10:

Fetch the source of passenger bags and the count of bags for all passengers and group the data by the source.

    SELECT $flt_src as SOURCE, count(*) as COUNT
    FROM BaggageInfo $bag,
    $bag.bagInfo.flightLegs[0].fltRouteSrc $flt_src
    GROUP BY $flt_src

In the above example, there is no index on the `fltRouteSrc` field. So the grouping is done in a generic way. An internal variable is created that iterates over the records produced by the SELECT statement.

A portion of the query plan is shown below. The primary index is being used.

    "iterator kind" : "TABLE",
    "target table" : "BaggageInfo",
    "row variable" : "$bag",
    "index used" : "primary index",
    "covering index" : false,
    "index scans" :
    [
      {
        "equality conditions" : {},
        "range conditions" : {}
      }
    ]

For more information on how a query is executed, see Query execution plan.