After the lab you can go back online and test your data warehouse creations. The users are given random results and depending on the degree of those users the data is sorted. Then the sorted results are shown in a matrix. The top row is the users with the highest score, in this case the score is 7. It means that there is one user per department with the highest score, then another row for the second highest score and so on until the department with the lowest score is displayed.
When the screen that shows all users with the highest score enters the second screen it will give information about the users who have a score less than or equal to the one you have. These are the users who have been created as a new table. If you want to create an index on that table then you can click on “create index” and a pop up window will appear. But if you do not want to create an index then you can just click on “no”.
You may wonder why there is a need to create an index on a table, because after all we only create them once. There is a simple answer to this, when you update a table there are chances that you make any changes on the database and that will affect the structure of the table. That will also affect the indexes on that specific table.
The structure of a data warehouse consists of a central index and twelve secondary indexes. The central index is linked to nearly all the rows and columns in the database and contains the most recent data. While the secondary indexes are linked to various rows and columns, but these contain old data.
A data warehouse index is important for several reasons. First of all it makes the queries over long ranges of rows much easier, because now we can use the columns of the central index. Secondly, the indexes on the secondary indexes help to avoid joins between tables. When we create the joins in earlier versions of the database we have to use the primary key for the joins, and this caused many performance problems with the application.
This problem was solved by Oracle 8i and now we don’t have to use the primary key for joins anymore. However, when we work with the data warehouse, we still need to be aware of the join details, because they can sometimes make the data warehouse even smaller. To make the data warehouse small, we often create small tables, and although this makes the database very manageable, it makes the queries over the whole database rather long. In case you only have a few hundred rows, you should be able to reduce your work with an ordinary Odbc server.
Well that’s it for today’s article. Hope you learned something new. If you want to do more practice, you can check out my blog from the start, where I will explain more about the exams and help you prepare. Cheers! psst!
