Truncate and delete

Truncate:
1. Moves the high water mark down to beginning.
2. Doesnt need commit. It is a DDL command.
3. No condition needed. Removes all rows.
4. Extents are deallocated. (If you specify the reuse storage clause, then the extents are not deallocated)
5. No rollback.

Delete:
1. Doesnt change the high water mark.
2. Needs commit. It’s a DML command.
3. Can specify a condition and remove specific rows.
4. Extents are not deallocated.
5. Changes can be rolled back.

Why index doesnt access null values?

Oracle indexes do not contain entries that point to rows with null values on all the indexed columns.

Therefore any query that has a column IS NULL or IS NOT NULL will not use the index.

The maximum size of a fact table.

Well, this is just a workout. It may or may not make much sense!

Here I have detailed how we can calculate the maximum size of a fact table.

Assume our datamart has 5 dimensions: Time_dim, customer_dim, product_dim, product_warehouse_dim, supplier_dim. And our fact table has 5 foreign keys, 15 measures, 2 degenerate columns

Time_dim has 20 years of rows on daily grain: so 365 X 20 = 7300 rows
There are 4000 customers in customer_dim.
200 products.
10 warehouses.
12 suppliers.

So the maximum possible fact table rows is
7300 x 4000 x 200 x 10 x 12 = 7008000000000 (7008000 million or 7008 billion)

Now this is the maximum no of rows possible in the fact table.(Assuming that every customer brought every product from every store supplied by every supplier on every day…..etc… it goes on … permutations and combinations)

Assuming that the 22 columns have an average size of 5 bytes, we need 110 bytes per row.

So now, 110 x 7008 billion = 71794 GB

The maximum possible size for this fact table (assuming that dimension count stays same) is 71794 GB.

The maximum possible increase per year could be 3590 GB.

Index usage

What do you think? Will an index be used in these cases?

1. Table returns 2,500,000 (2.5 million) records from a table having 10,000,000 rows (10 million).

2. You use a value !=’45′ condition in your where clause. ( Value column is indexed)

3. You say Value=’%ABC’ condition in where clause (value column is indexed)

4. You say Value=’ABC%’ condition in where clause (value column is indexed)

5. Where Value IS NULL (value column is indexed)

6. Where Value IS NOT NULL (value column is indexed)

In all these cases, do you think the index will be used or not?

Read the rest of this entry »