51)Will Oracle make use of a Hint if it is specified in the Query?
Not necessarily use the hint, if the optimizer sees a better optimized path than the one specified by means of the hint.
52)Can we specify more than one Hint in a Query?
yes.Multiple hints are separated with a space. Specifying multiple hints that conflict with each other causes the query to use none of the hints that are conflicting.
53)What are the different Hints that you have come across?
Using the FIRST_ROWS hint to generally force the use of indexes
Using the ALL_ROWS hint to generally force a full table scan
Using the FULL hint to force a full table scan
Using the INDEX hint to force the use of an index
Using the NO_INDEX hint to disallow a specified index from being used
Using the INDEX_JOIN hint to allow the merging of indexes on a single table
Using the INDEX_ASC hint to use an index ordered in ascending order
Using the INDEX_DESC hint to use an index ordered in descending order
Using the AND_EQUAL hint to access multiple b-tree indexes
Using the INDEX_COMBINE hint to access multiple bitmap indexes
Forcing fast full scans with the INDEX_FFS hint
Using the ORDERED hint to specify the driving order of tables
Using the LEADING hint to specify just the first driving table
Using the NO_EXPAND hint to eliminate OR expansion
Queries involving multiple locations and the DRIVING_SITE hint
Using the USE_MERGE hint to change how tables are joined internally
Forcing the subquery to process earlier with PUSH_SUBQ
Using the parallel query option and using PARALLEL and NO_PARALLEL
Using APPEND and NOAPPEND with parallel options
Caching and pinning a table into memory with the CACHE hint
Forcing clustering with the CLUSTER hint
Forcing cluster hashing with the HASH hint
Overriding the CURSOR_SHARING setting with the CURSOR_SHARING_EXACT hint
54)What are the different types of Joins available in Oracle?
Inner join
Outer Join
Cartesian join
Full join
55)Explain where each type of Join can be used w.r.t. Different Sceanarios?
NESTED LOOPS joins are ideal when the driving row source (the records you are looking for) is small and the joined columns of the inner row source are uniquely indexed or have a highly selective non-unique index. NESTED LOOPS joins have an advantage over other join methods in that they can quickly retrieve the first few rows of the result set without having to wait for the entire result set to be determined. This situation is ideal for query screens where an end user can read the first few records retrieved while the rest are being fetched.
If the driving row source (the records retrieved from the driving table or the outer table) is quite large, other join methods may be more efficient.
HASH joins can be effective when the lack of a useful index renders NESTED LOOPS joins inefficient. The HASH join might be faster than a SORT-MERGE join, in this case, because only one row source needs to be sorted, and it could possibly be faster than a NESTED LOOPS join because probing a hash table in memory can be faster than traversing a b-tree index. As with SORT-MERGE joins and CLUSTER joins, HASH joins work only on equijoins.
SORT-MERGE Join - Requires a sort on both tables. It is built for best optimal throughput and does not return the first row until all rows are found.
HASH Join - Can require a large amount of memory for the hash table to be built. Does not return the first rows quickly. Can be extremely slow if it must do the operation on disk. Better than NESTED LOOPS when an index is missing or the search criteria are not very selective. It is usually faster than a SORT-MERGE.
56)What is meant by Reverse Key Indexes?
It is as simple as that -- instead of all inserts hitting one side of the index, producing mass contention on one side of the index -- a reverse key index will tend to distribute the inserts throughout the entire structure more evenly.
It has been suggested that using reverse-key indexes will speed-up Oracle INSERT statements, especially with an increasing key, like an index on an Oracle sequence (which is used for the primary key of the target table). For large batch inserts, Oracle reverse key indexes will greatly speed-up data loads because the high-order index key has been reversed.
Reverse key indexes void the ability of an index range scan.They only work with exact equality.
With a Reverse Key Index, Oracle will reverse the bytes and insert the values sorted by the reverse bytes into the index
57)What and where do you make use of a Reverse Key Index?
For large batch inserts, Oracle reverse key indexes will greatly speed-up data loads because the high-order index key has been reversed.
58)Will a Reverse Key Index increase the performance of a Query?
Reverse key indexes void the ability of an index range scan.They only work with exact equality.Also RBO doesn't make use of a Reverse Key Index. So the Large Batch Inserts making use of Sequences benefit from the use of a Reverse Key Index
59)How do you read an Explain Plan?
The Explain plan shows the following
The row source tree is the core of the execution plan. It shows the following information:
* An ordering of the tables referenced by the statement
* An access method for each table mentioned in the statement
* A join method for tables affected by join operations in the statement
* Data operations like filter, sort, or aggregation
In addition to the row source tree, the plan table contains information about the following:
* Optimization, such as the cost and cardinality of each operation
* Partitioning, such as the set of accessed partitions
* Parallel execution, such as the distribution method of join inputs
Examining an explain plan lets you look for throw-away in cases such as the following:
* Full scans
* Unselective range scans
* Late predicate filters
* Wrong join order
* Late filter operations
60)What are the different types of Joins used by the optimizer?
The following are the joins used by the optimizer
NESTED LOOP JOIN
SORT MERGE JOIN
HASH JOIN
ANTI JOIN and
SEMI JOIN
Not necessarily use the hint, if the optimizer sees a better optimized path than the one specified by means of the hint.
52)Can we specify more than one Hint in a Query?
yes.Multiple hints are separated with a space. Specifying multiple hints that conflict with each other causes the query to use none of the hints that are conflicting.
53)What are the different Hints that you have come across?
Using the FIRST_ROWS hint to generally force the use of indexes
Using the ALL_ROWS hint to generally force a full table scan
Using the FULL hint to force a full table scan
Using the INDEX hint to force the use of an index
Using the NO_INDEX hint to disallow a specified index from being used
Using the INDEX_JOIN hint to allow the merging of indexes on a single table
Using the INDEX_ASC hint to use an index ordered in ascending order
Using the INDEX_DESC hint to use an index ordered in descending order
Using the AND_EQUAL hint to access multiple b-tree indexes
Using the INDEX_COMBINE hint to access multiple bitmap indexes
Forcing fast full scans with the INDEX_FFS hint
Using the ORDERED hint to specify the driving order of tables
Using the LEADING hint to specify just the first driving table
Using the NO_EXPAND hint to eliminate OR expansion
Queries involving multiple locations and the DRIVING_SITE hint
Using the USE_MERGE hint to change how tables are joined internally
Forcing the subquery to process earlier with PUSH_SUBQ
Using the parallel query option and using PARALLEL and NO_PARALLEL
Using APPEND and NOAPPEND with parallel options
Caching and pinning a table into memory with the CACHE hint
Forcing clustering with the CLUSTER hint
Forcing cluster hashing with the HASH hint
Overriding the CURSOR_SHARING setting with the CURSOR_SHARING_EXACT hint
54)What are the different types of Joins available in Oracle?
Inner join
Outer Join
Cartesian join
Full join
55)Explain where each type of Join can be used w.r.t. Different Sceanarios?
NESTED LOOPS joins are ideal when the driving row source (the records you are looking for) is small and the joined columns of the inner row source are uniquely indexed or have a highly selective non-unique index. NESTED LOOPS joins have an advantage over other join methods in that they can quickly retrieve the first few rows of the result set without having to wait for the entire result set to be determined. This situation is ideal for query screens where an end user can read the first few records retrieved while the rest are being fetched.
If the driving row source (the records retrieved from the driving table or the outer table) is quite large, other join methods may be more efficient.
HASH joins can be effective when the lack of a useful index renders NESTED LOOPS joins inefficient. The HASH join might be faster than a SORT-MERGE join, in this case, because only one row source needs to be sorted, and it could possibly be faster than a NESTED LOOPS join because probing a hash table in memory can be faster than traversing a b-tree index. As with SORT-MERGE joins and CLUSTER joins, HASH joins work only on equijoins.
SORT-MERGE Join - Requires a sort on both tables. It is built for best optimal throughput and does not return the first row until all rows are found.
HASH Join - Can require a large amount of memory for the hash table to be built. Does not return the first rows quickly. Can be extremely slow if it must do the operation on disk. Better than NESTED LOOPS when an index is missing or the search criteria are not very selective. It is usually faster than a SORT-MERGE.
56)What is meant by Reverse Key Indexes?
It is as simple as that -- instead of all inserts hitting one side of the index, producing mass contention on one side of the index -- a reverse key index will tend to distribute the inserts throughout the entire structure more evenly.
It has been suggested that using reverse-key indexes will speed-up Oracle INSERT statements, especially with an increasing key, like an index on an Oracle sequence (which is used for the primary key of the target table). For large batch inserts, Oracle reverse key indexes will greatly speed-up data loads because the high-order index key has been reversed.
Reverse key indexes void the ability of an index range scan.They only work with exact equality.
With a Reverse Key Index, Oracle will reverse the bytes and insert the values sorted by the reverse bytes into the index
57)What and where do you make use of a Reverse Key Index?
For large batch inserts, Oracle reverse key indexes will greatly speed-up data loads because the high-order index key has been reversed.
58)Will a Reverse Key Index increase the performance of a Query?
Reverse key indexes void the ability of an index range scan.They only work with exact equality.Also RBO doesn't make use of a Reverse Key Index. So the Large Batch Inserts making use of Sequences benefit from the use of a Reverse Key Index
59)How do you read an Explain Plan?
The Explain plan shows the following
The row source tree is the core of the execution plan. It shows the following information:
* An ordering of the tables referenced by the statement
* An access method for each table mentioned in the statement
* A join method for tables affected by join operations in the statement
* Data operations like filter, sort, or aggregation
In addition to the row source tree, the plan table contains information about the following:
* Optimization, such as the cost and cardinality of each operation
* Partitioning, such as the set of accessed partitions
* Parallel execution, such as the distribution method of join inputs
Examining an explain plan lets you look for throw-away in cases such as the following:
* Full scans
* Unselective range scans
* Late predicate filters
* Wrong join order
* Late filter operations
60)What are the different types of Joins used by the optimizer?
The following are the joins used by the optimizer
NESTED LOOP JOIN
SORT MERGE JOIN
HASH JOIN
ANTI JOIN and
SEMI JOIN
No comments:
Post a Comment