Lab5 n01530481
Lab5 n01530481
Lab5 n01530481
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.
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.