SQL Performance Tuning
Optimizer Hints
The query optimizer tries to execute the fastest execution plan. However, you can know about the data design, application design or data distribution in your cluster better. SQL hints can help the optimizer to make optimizations more rationally or build execution plan faster.
Hints format
SQL hints are defined by a special comment /*+ HINT */, referred to as a hint block. Spaces before and after the hint name are required. The hint block must be placed right after the operator. Several hints for one relation operator are not supported.
Example:
SELECT /*+ NO_INDEX */ T1.* FROM TBL1 where T1.V1=? and T1.V2=?Hint parameters
Hint parameters, if required, are placed in brackets after the hint name and separated by commas.
The hint parameter can be quoted. Quoted parameter is case-sensitive. The quoted and unquoted parameters cannot be defined for the same hint.
Example:
SELECT /*+ FORCE_INDEX(TBL1_IDX2,TBL2_IDX1) */ T1.V1, T2.V1 FROM TBL1 T1, TBL2 T2 WHERE T1.V1 = T2.V1 AND T1.V2 > ? AND T2.V2 > ?;
SELECT /*+ FORCE_INDEX('TBL2_idx1') */ T1.V1, T2.V1 FROM TBL1 T1, TBL2 T2 WHERE T1.V1 = T2.V1 AND T1.V2 > ? AND T2.V2 > ?;Hints errors
The optimizer tries to apply every hint and its parameters, if possible. But it skips the hint or hint parameter if:
-
The hint is not supported.
-
Required hint parameters are not passed.
-
The hint parameters have been passed, but the hint does not support any parameter.
-
The hint parameter is incorrect or refers to a nonexistent object, such as a nonexistent index or table.
-
The current hints or current parameters are incompatible with the previous ones, such as forcing the use and disabling of the same index.
Supported hints
FORCE_INDEX / NO_INDEX
Forces or disables index scan.
Parameters:
-
Empty. To force an index scan for every underlying table. Optimizer will choose any available index. Or to disable all indexes.
-
Single index name to use or skip exactly this index.
-
Several index names. They can relate to different tables. The optimizer will choose indexes for scanning or skip them all.
Examples:
SELECT /*+ FORCE_INDEX */ T1.* FROM TBL1 T1 WHERE T1.V1 = T2.V1 AND T1.V2 > ?;
SELECT /*+ FORCE_INDEX(TBL1_IDX2, TBL2_IDX1) */ T1.V1, T2.V1 FROM TBL1 T1, TBL2 T2 WHERE T1.V1 = T2.V1 AND T1.V2 > ? AND T2.V2 > ?;
SELECT /*+ NO_INDEX */ T1.* FROM TBL1 T1 WHERE T1.V1 = T2.V1 AND T1.V2 > ?;
SELECT /*+ NO_INDEX(TBL1_IDX2, TBL2_IDX1) */ T1.V1, T2.V1 FROM TBL1 T1, TBL2 T2 WHERE T1.V1 = T2.V1 AND T1.V2 > ? AND T2.V2 > ?;Using the EXPLAIN PLAN FOR Statement
GridGain supports the EXPLAIN PLAN FOR statement that can be used to read the execution plan of a query.
Use this command to analyse your queries for possible optimization, for example:
EXPLAIN PLAN FOR SELECT name FROM Person WHERE age = 26;Here is how the results of the explanation may look like:
Exchange(distribution=[single]): rowcount = 1500.0, cumulative cost = IgniteCost [rowCount=11500.0, cpu=41500.0, memory=0.0, io=40000.0, network=6000.0], id = 47
TableScan(table=[[PUBLIC, PERSON]], tableId=[9], filters=[=($t1, 26)], projects=[[$t0]], requiredColumns=[{2, 3}]): rowcount = 1500.0, cumulative cost = IgniteCost [rowCount=10000.0, cpu=40000.0, memory=0.0, io=40000.0, network=0.0], id = 46
© 2024 GridGain Systems, Inc. All Rights Reserved. Privacy Policy | Legal Notices. GridGain® is a registered trademark of GridGain Systems, Inc.
Apache, Apache Ignite, the Apache feather and the Apache Ignite logo are either registered trademarks or trademarks of The Apache Software Foundation.