|GDB-1||Dated: Aug 06, 15|
Please Note that GDB of CS614 will be launched on August 12, 2015(Wednesday) and will last for 48 hours only.
Consider the following table:
Customer_Order (order_id, customer_id, order_date, Discount, Order_status)
Suppose a customer can place many orders and we need to fetch the records of customers and their orders. By keeping one thing in mind that, we are interested in quickly retrieving the orders. For this, we have to apply appropriate indexing technique on above table. You have studied different indexing technique in this course. Identify the indexing technique, which you think is suitable in this case. Justify your answer with valid reasons.
Note: Try to provide precise, to the point answer and avoid irrelevant details.
Please Discuss here about this GDB.Thanks
Our main purpose here discussion not just Solution
We are here with you hands in hands to facilitate your learning and do not appreciate the idea of copying or replicating solutions.
i think we will use cluster indexing technique because it is ten time faster then others
The big advantage of a cluster index is that all the rows with the same cluster index value
will be placed into adjacent locations in a small number of data blocks.
In this example, all accounts for a specific customer will be clustered into adjacent
locations to increase locality of reference by customer_id.
Cluster indexing allows significant reduction in I/Os when accessing base table via index
because rows with the same index value will be stored into the same blocks. This is a big
win for indexed access for query execution against a single table as well as nested loop
joins using indexed access. Cluster indexing has the effect of transforming a random I/O
workload into a sequential I/O workload when accessing through the cluster index.
To decide that what kind of indexing should apply; following points will help you:
Cardinality data: The cardinality data of a column is the number of distinct values in the column. It is better to know that the cardinality of an indexed column is low or high since an indexing technique may work efficiently only with either low cardinality or high cardinality.
Distribution: The distribution of a column is the occurrence frequency of each distinct value of the column. The column distribution guides us to determine which index type we should take.
Value range: The range of values of and indexed column guides us to select an appropriate index type. For example, if the range of a high cardinality column is small, an indexing technique based on bitmap should be used. Without knowing this information, we might use a B-Tree resulting in a degradation of system performance
make some change in this and submit as your GDB CS614
If you wish to quickly retrieve all orders of one particular customer, you may wish to create a clustered index on the "CustomerID" column of the Order table. This way the records with the same CustomerID will be physically stored close to each other on disk (clustered) which speeds up their retrieval.
P.S. The index on CustomerID will obviously be not unique, so you either need to add a second field to "uniquify" the index or let the database handle that for you but that's another story.
Regarding multiple indexes. You can have only one clustered index per table because this defines how the data is physically arranged. If you wish an analogy, imagine a big room with many tables in it. You can either put these tables to form several rows or pull them all together to form a big conference table, but not both ways at the same time. A table can have other indexes, they will then point to the entries in the clustered index which in its turn will finally say where to find the actual data.
VERY VERY THANKS BRO.
Just for idea: