Tuning and Optimizing SQL queries

Query optimization is the overall process of choosing the most efficient means of executing a SQL statement.

You optimize a SQL query to get accurate and fast database results.

Using Indexes for query optimization

Indexing is a way to optimize the performance of a database by minimizing the number of disk accesses required when a query is processed.

In Oracle NoSQL Database, the query processor can identify which of the available indexes are beneficial for a query and rewrite the query to make use of such an index. "Using" an index means scanning a contiguous subrange of its entries, potentially applying further filtering conditions on the entries within this subrange, and using the primary keys stored in the surviving index entries to extract and return the associated table rows. The subrange of the index entries to scan is determined by the conditions appearing in the WHERE clause, some of which may be converted to search conditions for the index. Given that only a (hopefully small) subset of the index entries will satisfy the search conditions, the query can be evaluated without accessing each individual table row, thus saving a potentially large number of disk accesses.

Notice that in Oracle NoSQL Database, a primary-key index is always created by default. This index maps the primary key columns of a table to the physical location of the table rows. Furthermore, if no other index is available, the primary index will be used. In other words, there is no pure "table scan" mechanism; a table scan is equivalent to a scan via the primary-key index. When it comes to indexes and queries, the query processor must answer two questions:

Is an index applicable to a query? That is, will accessing the table via this index be more efficient than doing a full table scan (via the primary index).
Among the applicable indexes, which index or combination of indexes is the best to use?

There are no statistics on the number and distribution of values in a table column. As a result, the query processor has to rely on some simple heuristics in choosing among the applicable indexes. In addition, SQL for Oracle NoSQL Database allows for the inclusion of index hints in the queries. You can use index hints to force the use of a particular index in queries.

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.