Instructions
Write 1 thread of at least 400 words and 2 replies of at least 200 words each. For the thread, you must support your assertions with at least 4 citations in current APA format. Each reply must incorporate at least 2 citations. Acceptable sources include peer-reviewed journal articles, books, the course textbook, and the Bible. Note that you may use the article that is included with your assigned reading as 1 of your sources.
Thread Topic (400 words)
Thread: Select 1 topic from among the 6 listed below, and in at least 400 words, write a post that explains your chosen topic.
Additional resources are listed in the Reading & Study folder of this Module/Week.
1. Query Optimizer Overview
Query Optimization Steps
*Be sure to address the question “What are the algorithm categories?”
2. Algorithms Introduction
External Sort/Merge
*Be sure to explain how the merge/sort algorithm works.
3. Select Algorithms – Simple
*Be sure to discuss Algorithms S1-S7.
4. Select Algorithms – Complex
*Be sure to discuss Algorithms S8-S11.
5. Join Operation Algorithms
*Be sure to explain Algorithms J1-J5
6. Optimization Techniques
*Be sure to discuss Cost vs. Heuristic based optimization
Replies (200 words each)
Thread Reply 1
There are many different aspects to database organization, function, and optimization that make each database unique. Algorithm complexity and style can defer depending on a few factors. Complexity, quantity, and requirements all can play a pivotal role and what algorithm, or system could be used. Understanding algorithms at an introductory level is an essential aspect of understanding and executing database functions. “Techniques for performing an external merge sort are often used to sort data managed by a distributed processing device that processes large-scale data” (External, 2015). This method is also most when the data is constantly increasing, but the system only has a certain capacity of memory to process that large amount of data. Furthermore, a merge sort algorithm is one of the most common. The merge sort function is just a general sorting algorithm that arranges certain elements of the database in a predetermined manner. Furthermore, merge sort breaks down aspects of the information into subcategories and characteristics. According to an article published by Runestone Academy, “Merge sort is a recursive algorithm that continually splits a list in half…. If the list has more than one item, we split the list and recursively invoke a merge sort on both halves” (Merge). The merge sort method is also simply known as the “divide and conquer” method. The algorithm “divides by finding the midpoint and conquers by recursively sorting subarrays” (Khan). Also, the worst case of the merge sort is O(n*log(n)), whereas the best case is O(n) (Sorting). The merge sort algorithm function is to split the data into smaller groups by halving the groups each time. Then the groups will continue this process until each group only has one element or no elements, and the result is uniquely sorted groups (Sorting). Lastly, the groups are merged back together in an order that places their elements in proper arrangements. The merge sort algorithm works efficiently and effectively for most users needs.
Thread Reply 2
According to oracle.com query optimization is the overall process of choosing the most efficient means of executing a SQL statement. In the article it also gave us an example on when one would be using this optimizer. They gave us an instance when looking for information regarding employees in a database. If there was a query that would ask for employees that are managers, and the statistics say that 80 percent of the employees are managers, then a full table scan would be most effective in that scenario. However, when the statistics are saying that very few employees are managers, then reading an index with a table access by the row ID would be much more efficient than a full scan of the entire table. The query optimizer does these things to make it more efficient. With this, there are multiple algorithm categories for a query optimizer. According to emory.edu there are four steps in query optimization. The first step is to convert the SQL query into a query parse tree. This is essentially an outline of what the user’s query would be, statements like SELECT, FROM, and WHERE. The second step is to make that parse tree into an initial logical plan to see what query the user would like to initiate. The third step is to transform that initial query plan into an optimal query plan which makes what the user has searched to be more specific towards what the user is trying to find. Finally, the last step is to select the algorithm for each operator in the query plan that will output what the user has asked for. There are two main algorithm categories according to emory.edu. The two categories are the logical query plan and the physical query plan. The logical query plan is optimal for relational algebra operations, while the physical query plan is optimal for relational algebra algorithms. The difference between these two are that logical operators are used to describe the relational operation to process a statement while physical operators implement the operation given by the logical operators. And by definition as stated earlier an algorithm is a set of instructions and an operation is more of a particular method. This is just a very basic explanation of what a query optimizer is and what are it’s functions.
