(Q) What is a Partition-Wise Join? How can SQL statements benefit from Full Partition-Wise Joins?
- A P-W Join divides a large join into smaller joins between a pair of partitions from the two joined tables.
- To use P-W Joins:
- (a) equipartition BOTH tables on their JOIN keys or
- (b) use Reference partitioning
- P-W Joins reduce query response time by minimizing the amount of data exchanged among parallel execution servers when joins execute in parallel
- Full P-W Joins: Divide a large join into smaller joins between a pair of partitions from the two joined tables
- Tables MUST be equipartitioned on their join keys, or use reference partitioning.
(Q) What are the two most common types of indexes in Data Warehouse environments?
- B-tree and bitmap indexes
- Both can be created as local indexes on a partitioned table, inheriting the table's partitining strategy
- Local nonprefixed indexes can improve performance because many index partitions can be scanned in parallel by range queries on the index key.
SQL> create index cust_last_name_ix ON customers(last_name) LOCAL PARALLEL NOLOGGING;
(Q) How can bitmap index improve performance in a Data Warehouse environment?
- Bitmap indexes use a very efficient storage mechanism for low cardinality columns.
- Fact table foreign key column are ideal candidates for bitmap indexes, since generally there are few distinct values relative to the total number of rows in the fact table. (Because dimension tables should not have many rows)
(Q) What is Partition Exchange Load (PEL)? When is it used? What benefits does it provides?
- Oracle strongly recommends that the larger tables or fact tables in a data warehouse are partitioned.
- with a partitioned table, you can load data quickly and easily with minimal impact on business users by using the exchange partition command.
- With the exchange partition command you can swap the data in a non-partitioned table into a particular partition in your partitioned table.
- NO physical data movement occurs with the exchange partition command.
- Instead the data dictionary is updated to exchange a pointer from the partition to the table and vice versa.
- Because there is no physical movement of data, an exchange does not generate redo and undo, making it a VERY fast operation.
- You can convert by exchanging their data segments:
- (a) a partition (or subpartition) into a nonpartitioned table and
- (b) a nonpartitioned table into a partition (or subpartition) of a partitioned table
- In DWH environments, exchanging partitions facilitates high-speed data loading of new, incremental data into an existing partitioned table.
- OLTP and DWH also can benefit from exchanging old data partitions out of a partitioned table.
- In the latter case, the data is purged from the partitioned table without being deleted and can be archived separately afterwards.
(1) create a separate table that looks exactly like a single partition in the existing table (including indexes, constraints and partition schema)
(2) Load the data in the new table, build indexes, enable constraints. This will not impact current users of the existing table
(3) Exchange the existing table partition with the newly created table
i.e. (a) create new partition on existing table
SQL> Alter table sales add partition p_sales_jun_2007
values less than (to_date('01-JUL-2007', 'DD-MON-YYYY');
(b) create temp table that looks like existing partition
SQL> create table sales_jun_2007 COMPRESS FOR OLTP as select * from sales where 1=0;
(c) Populate table and create indexes and constraints
SQL> create bitmap index time_id_jun_2007_bix ON sales_jun_2007(time_id) nologging;
SQL> create bitmap index cust_id_jun_2007_bix ON sales_jun_2007(cust_id) nologging;
SQL> create bitmap index prod_id_jun_2007_bix ON sales_jun_2007(prod_id) nologging;
SQL> create bitmap index promo_id_jun_2007_bix ON sales_jun_2007(promo_id) nologging;
SQL> create bitmap index channel_id_jun_2007_bix ON sales_jun_2007(channel_id) nologging;
SQL> ALTER TABLE sales_jun_2007 ADD CONSTRAINT prod_id_fk
FOREIGN KEY (prod_id) REFERENCES products(prod_id);
SQL> ALTER TABLE sales_jun_2007 ADD CONSTRAINT cust_id_fk
FOREIGN KEY (cust_id) REFERENCES customers(cust_id);
SQL> ALTER TABLE sales_jun_2007 ADD CONSTRAINT promo_id_fk
FOREIGN KEY (promo_id) REFERENCES promotions(promo_id);
SQL> ALTER TABLE sales_jun_2007 ADD CONSTRAINT time_id_fk
FOREIGN KEY (time_id) REFERENCES times(time_id);
SQL> ALTER TABLE sales_jun_2007 ADD CONSTRAINT channel_id_fk
FOREIGN KEY (channel_id) REFERENCES channels(channel_id);
(d) Exchange partition
SQL> Alter table sales EXCHANGE PARTITION p_sales_jun_2007 WITH TABLE sales_jun_2007
including indexes;
Partition Exchange Load (PEL)
