Lab5 n01530481

Download as docx, pdf, or txt
Download as docx, pdf, or txt
You are on page 1of 5

ITE 5323 Data Warehouse Fundamentals

Lab Exercise 5 Database Sizing


Question 1
You can calculate the approximate size of storage required for a database. Not done as much today since
storage is much less expensive now compared to years gone by
To calculate the size of required space for the database you will use the following formula.
1. Take the row size of the fact table. What columns exist and the sizes of each data type.
2. Multiply the number of rows in each of the dimension tables together.
3. Multiply this product by the size of the fact table.

There is another factor called sparsity. This will typically reduce the size since all cells in the fact table will not
contain a value. If the sparsity factor is 15%, you will determine what 15% of the total size is that you
calculated, then subtract that amount for the total you determined previously.
So, fact table row size (rows in dimension table A * B * C *D)
Take that total size then determine 15% of that size.
Using the following data determine the database size assumption.
Total size – sparsity size = the size needed
Use this data:
FACT Table – SALES row size is 64
DIMENSION Tables – Number of entries at the finest level.
LOCATIONS - 45
INVENTORY - 1,250
CUSTOMERS - 750
TIME - 250 Weeks
Sparsity is low adjust the total by 15%.
What is the estimated database size?

Answer 1:

(64*45*1,250*750*250) = 675,000,000,000
Sparsity size is 15% of total size.
15 % 675,000,000,000 = 101,250,000,000
Therefore,
Total size – sparsity size = the size needed
675,000,000,000 - 101,250,000,000 = 573,750,000,000
573.750 GB is the estimated DB size.
The following questions are to show that various objects you create do consume disk space. They will not
be very large due to the small size of what we are working with.
Question 2
Using the formula above calculate the approximate size required for out AV schema on you VM. You will need
to determine the number of rows in each table, and the size of the row in the fact table. For NUMBER data
types use a value of 9 for each one.
Show your work. Show any SQL commands you used to assist with this.
Question 3
Create B-Tree indexes on the following columns. In the GEOGRAPHY table, on the REGION_NAME,
COUNTRY_NAME, and STATE_PROVINCE_NAME columns. One index for each column. Use the syntax
below to create the indexes. Show the SQL command and output in SQL Developer.
CREATE INDEX name_of_index ON table(column_name);
Question 4
Create B-Tree indexes on the following columns. In the PRODUCTS table, on the CATEGORY_NAME, and
DEPARTMENT_NAME columns. One index for each column. Use the syntax below to create the indexes.
Show the SQL command and output in SQL Developer.
CREATE INDEX name_of_index ON table(column_name);

Question 5
Create the following MATERIALIZED VIEW.
Before you do this, you will need to connect as the SYSTEM user to give the AV user permission to create
materialized views.

Connect as the AV user. Issue the following command.


Question 6
As the AV user issue the following command. This command will show the space occupied by the objects you
have created, TABLES, INDEXES, and the MATERIALIZED VIEW. Show the command and the output in
SQL Developer.
What did you observe? Please explain what you have seen in this display.

The above query retrieves the information from the user_Segments view and then calculates the size of each
segment in mb (As we mentioned ‘sizeinmb’). By group by segment_name, the query provides the size in MB
for each unique segment_name view.

You might also like