TOP をテンプレートにして作成
ホーム
バックアップ
一覧
検索
最終更新
ヘルプ
ログイン
開始行
Indexing & Optimizations
Table of Contents
Optimizations in V.2.1
Improved PLAN Clause
Optimizer Improvements
Enhancements to Indexing
Optimizations in V.2.1
Optimization improvements in v.2.1 include:
(v.2.1) Economising on Indexed Reads for MIN() and MAX()
Indexed MIN/MAX aggregates would produce three indexed re...
SELECT MIN(COL) FROM TAB
should be completely equivalent, to
SELECT FIRST 1 COL FROM TAB
ORDER BY 1 ASC
with both performing a single record read. However, forme...
The same optimization applies to the MAX() function when ...
Improved PLAN Clause
D. Yemanov
(V.2.0.x) A PLAN clause optionally allows you to provide ...
PLAN (A ORDER IDX1 INDEX (IDX2, IDX3))
For more details, please refer to the topic Query Plans I...
Optimizer Improvements
For All Databases
For ODS 11 Databases only
This section represents a collection of changes done in F...
For All Databases
The first group of changes affect all databases, includin...
Some General Improvements
O. Loa, D. Yemanov
Much faster algorithms to process the dirty pages tree
Firebird 2 offers a more efficient processing of the list...
This change also improves the overall performance of data...
Increased maximum page cache size to 128K pages (2GB for ...
Faster Evaluation of IN() and OR
O. Loa
Constant IN predicate or multiple OR booleans are now eva...
Sparse bitmap operations were optimized to handle multipl...
Improved UNIQUE Retrieval
A. Brinkman
The optimizer will now use a more realistic cost value fo...
More Optimization of NOT Conditions
D. Yemanov
NOT conditions are simplified and optimized via an index ...
Example
(NOT NOT A = 0) -> (A = 0)
(NOT A > 0) -> (A <= 0)
Distribute HAVING Conjunctions to the WHERE Clause
If a HAVING clause or any outer-level select refers to a ...
Examples
select rdb$relation_id, count(*)
from rdb$relations
group by rdb$relation_id
having rdb$relation_id > 10
select * from (
select rdb$relation_id, count(*)
from rdb$relations
group by rdb$relation_id
) as grp (id, cnt)
where grp.id > 10
In both cases, an index scan is performed instead of a fu...
Distribute UNION Conjunctions to the Inner Streams
Distribute UNION conjunctions to the inner streams when p...
Improved Handling of CROSS JOIN and Merge/SORT
Improved cross join and merge/sort handling
Better Choice of Join Order for Mixed Inner/Outer Joins
reasonable join order for intermixed inner and outer joins
Equality Comparison on Expressions
MERGE PLAN may now be generated for joins using equality ...
For ODS 11 Databases only
This group of optimizations affects databases that were c...
Segment-level Selectivities are Used
See Selectivity Maintenance per Segment.
Better Support for IS NULL and STARTING WITH
Previously, IS NULL and STARTING WITH predicates were opt...
Matching of Both OR and AND Nodes to Indexes
Complex boolean expressions consisting of many AND/OR pre...
Better JOIN Orders
Cost estimations have been improved in order to improve J...
Indexed Order Enabled for Outer Joins
It is now possible for indexed order to be utilised for o...
Enhancements to Indexing
252-byte index length limit is gone
Expression Indexes
Changes to Null keys handling
Improved Index Compression
Selectivity Maintenance per Segment
252-byte index length limit is gone
A. Brinkman
New and reworked index code is very fast and tolerant of ...
A 40-bit record number is included on “non leaf-level pag...
Expression Indexes
O. Loa, D. Yemanov, A. Karyakin
Arbitrary expressions applied to values in a row in dynam...
Syntax Pattern
CREATE [UNIQUE] [ASC[ENDING] | DESC[ENDING]] INDEX <index...
ON <table name>
COMPUTED BY ( <value expression> )
Examples
1.
CREATE INDEX IDX1 ON T1
COMPUTED BY ( UPPER(COL1 COLLATE PXW_CYRL) );
COMMIT;
/* */
SELECT * FROM T1
WHERE UPPER(COL1 COLLATE PXW_CYRL) = 'ÔÛÂÀ'
-- PLAN (T1 INDEX (IDX1))
2.
CREATE INDEX IDX2 ON T2
COMPUTED BY ( EXTRACT(YEAR FROM COL2) || EXTRACT(MONTH ...
COMMIT;
/* */
SELECT * FROM T2
ORDER BY EXTRACT(YEAR FROM COL2) || EXTRACT(MONTH FROM ...
-- PLAN (T2 ORDER IDX2)
Note
The expression used in the predicate must match exactly t...
Expression indices have exactly the same features and lim...
Changes to Null keys handling
V. Khorsun, A. Brinkman
Null keys are now bypassed for uniqueness checks. (V. Kho...
If a new key is inserted into a unique index, the engine ...
NULLs are ignored during the index scan, when it makes se...
Prevously, NULL keys were always scanned for all predicat...
Note
The predicates IS NULL and IS NOT DISTINCT FROM still req...
Improved Index Compression
A. Brinkman
A full reworking of the index compression algorithm has m...
Selectivity Maintenance per Segment
D. Yemanov, A. Brinkman
Index selectivities are now stored on a per-segment basis...
This opens more opportunities to the optimizer for clever...
The per-segment selectivity values are stored in the colu...
最終行:
Indexing & Optimizations
Table of Contents
Optimizations in V.2.1
Improved PLAN Clause
Optimizer Improvements
Enhancements to Indexing
Optimizations in V.2.1
Optimization improvements in v.2.1 include:
(v.2.1) Economising on Indexed Reads for MIN() and MAX()
Indexed MIN/MAX aggregates would produce three indexed re...
SELECT MIN(COL) FROM TAB
should be completely equivalent, to
SELECT FIRST 1 COL FROM TAB
ORDER BY 1 ASC
with both performing a single record read. However, forme...
The same optimization applies to the MAX() function when ...
Improved PLAN Clause
D. Yemanov
(V.2.0.x) A PLAN clause optionally allows you to provide ...
PLAN (A ORDER IDX1 INDEX (IDX2, IDX3))
For more details, please refer to the topic Query Plans I...
Optimizer Improvements
For All Databases
For ODS 11 Databases only
This section represents a collection of changes done in F...
For All Databases
The first group of changes affect all databases, includin...
Some General Improvements
O. Loa, D. Yemanov
Much faster algorithms to process the dirty pages tree
Firebird 2 offers a more efficient processing of the list...
This change also improves the overall performance of data...
Increased maximum page cache size to 128K pages (2GB for ...
Faster Evaluation of IN() and OR
O. Loa
Constant IN predicate or multiple OR booleans are now eva...
Sparse bitmap operations were optimized to handle multipl...
Improved UNIQUE Retrieval
A. Brinkman
The optimizer will now use a more realistic cost value fo...
More Optimization of NOT Conditions
D. Yemanov
NOT conditions are simplified and optimized via an index ...
Example
(NOT NOT A = 0) -> (A = 0)
(NOT A > 0) -> (A <= 0)
Distribute HAVING Conjunctions to the WHERE Clause
If a HAVING clause or any outer-level select refers to a ...
Examples
select rdb$relation_id, count(*)
from rdb$relations
group by rdb$relation_id
having rdb$relation_id > 10
select * from (
select rdb$relation_id, count(*)
from rdb$relations
group by rdb$relation_id
) as grp (id, cnt)
where grp.id > 10
In both cases, an index scan is performed instead of a fu...
Distribute UNION Conjunctions to the Inner Streams
Distribute UNION conjunctions to the inner streams when p...
Improved Handling of CROSS JOIN and Merge/SORT
Improved cross join and merge/sort handling
Better Choice of Join Order for Mixed Inner/Outer Joins
reasonable join order for intermixed inner and outer joins
Equality Comparison on Expressions
MERGE PLAN may now be generated for joins using equality ...
For ODS 11 Databases only
This group of optimizations affects databases that were c...
Segment-level Selectivities are Used
See Selectivity Maintenance per Segment.
Better Support for IS NULL and STARTING WITH
Previously, IS NULL and STARTING WITH predicates were opt...
Matching of Both OR and AND Nodes to Indexes
Complex boolean expressions consisting of many AND/OR pre...
Better JOIN Orders
Cost estimations have been improved in order to improve J...
Indexed Order Enabled for Outer Joins
It is now possible for indexed order to be utilised for o...
Enhancements to Indexing
252-byte index length limit is gone
Expression Indexes
Changes to Null keys handling
Improved Index Compression
Selectivity Maintenance per Segment
252-byte index length limit is gone
A. Brinkman
New and reworked index code is very fast and tolerant of ...
A 40-bit record number is included on “non leaf-level pag...
Expression Indexes
O. Loa, D. Yemanov, A. Karyakin
Arbitrary expressions applied to values in a row in dynam...
Syntax Pattern
CREATE [UNIQUE] [ASC[ENDING] | DESC[ENDING]] INDEX <index...
ON <table name>
COMPUTED BY ( <value expression> )
Examples
1.
CREATE INDEX IDX1 ON T1
COMPUTED BY ( UPPER(COL1 COLLATE PXW_CYRL) );
COMMIT;
/* */
SELECT * FROM T1
WHERE UPPER(COL1 COLLATE PXW_CYRL) = 'ÔÛÂÀ'
-- PLAN (T1 INDEX (IDX1))
2.
CREATE INDEX IDX2 ON T2
COMPUTED BY ( EXTRACT(YEAR FROM COL2) || EXTRACT(MONTH ...
COMMIT;
/* */
SELECT * FROM T2
ORDER BY EXTRACT(YEAR FROM COL2) || EXTRACT(MONTH FROM ...
-- PLAN (T2 ORDER IDX2)
Note
The expression used in the predicate must match exactly t...
Expression indices have exactly the same features and lim...
Changes to Null keys handling
V. Khorsun, A. Brinkman
Null keys are now bypassed for uniqueness checks. (V. Kho...
If a new key is inserted into a unique index, the engine ...
NULLs are ignored during the index scan, when it makes se...
Prevously, NULL keys were always scanned for all predicat...
Note
The predicates IS NULL and IS NOT DISTINCT FROM still req...
Improved Index Compression
A. Brinkman
A full reworking of the index compression algorithm has m...
Selectivity Maintenance per Segment
D. Yemanov, A. Brinkman
Index selectivities are now stored on a per-segment basis...
This opens more opportunities to the optimizer for clever...
The per-segment selectivity values are stored in the colu...
ページ名:
新規
名前変更
ホーム
一覧
検索
最終更新
バックアップ
ヘルプ
最終更新のRSS