1.Query transformer:
1)View merging - represent the view to the containing query block(table).
2)Predicate pushing - push the relevant predicates from the containing query block into the view.
3)Subquery unnesting - transform a subquery into an equivalent join.
4)Query rewrite - look for materialized views to replace the table.
2.Estimation:
1)Selectivity - a fraction from row set. 0 is more selective for b* tree index, 1 is less selective for bitmap index. 1/count(distinct values)
2)Cardinality - the number of rwos from row set.
3)Cost - CPU,Memory,I/O as unit of work.
3.Bind variable peeking - can determine the selectivity estimate.
4.adaptive Cursor Sharing - different plans for different bind values,then a single statement use multiple plans.
5.I/O by block, not row.
6 Index culstering factor - low,the same indexed column values in the same block(good), high,conversely(bad)
7. Index skip scans - only good when the leading column is big(such as 0.5),It first split the index, then union all the query.
8. Fast full index scans - one column of index has the NOT NULL constraint, and run in parallel.
9. Nested loop join - small table; Hash join: one small tale, one large table.Sort Merge join: sorted tables; Cartesian join:no join condition.
10.Gathering statistics without sampling requires full table scans and sorts of entire tables.
11.Distributed query optimization uses cost-based optimization to find or generate SQL expressions that extract only the necessary data from remote tables, process that data at a remote site or sometimes at the local site, and send the results to the local site for final processing.
12.Involving a composite index only when the statement contains constructs that use a leading portion of the index.This is no longer the case with index skip scans.
13.T＝S/V.T is time. S is the memory(consistent gets). V is CPU,IO(physical read)
1)View merging - represent the view to the containing query block(table).
2)Predicate pushing - push the relevant predicates from the containing query block into the view.
3)Subquery unnesting - transform a subquery into an equivalent join.
4)Query rewrite - look for materialized views to replace the table.
2.Estimation:
1)Selectivity - a fraction from row set. 0 is more selective for b* tree index, 1 is less selective for bitmap index. 1/count(distinct values)
2)Cardinality - the number of rwos from row set.
3)Cost - CPU,Memory,I/O as unit of work.
3.Bind variable peeking - can determine the selectivity estimate.
4.adaptive Cursor Sharing - different plans for different bind values,then a single statement use multiple plans.
5.I/O by block, not row.
6 Index culstering factor - low,the same indexed column values in the same block(good), high,conversely(bad)
7. Index skip scans - only good when the leading column is big(such as 0.5),It first split the index, then union all the query.
8. Fast full index scans - one column of index has the NOT NULL constraint, and run in parallel.
9. Nested loop join - small table; Hash join: one small tale, one large table.Sort Merge join: sorted tables; Cartesian join:no join condition.
10.Gathering statistics without sampling requires full table scans and sorts of entire tables.
11.Distributed query optimization uses cost-based optimization to find or generate SQL expressions that extract only the necessary data from remote tables, process that data at a remote site or sometimes at the local site, and send the results to the local site for final processing.
12.Involving a composite index only when the statement contains constructs that use a leading portion of the index.This is no longer the case with index skip scans.
13.T＝S/V.T is time. S is the memory(consistent gets). V is CPU,IO(physical read)
1242
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
