• How to make partition elimination/pruning to work

    If you have situation when partition elimination/pruning does not work, here are couple effective tricks to fix it. 1. If query derives partition key from sub-query, for example (query where sale_date is a partition key): select * from sales where sale_date=(select current_date from calendar_table where country_id=1) Replace sub-query with DETERMINISTIC function: create or replace function f_get_date (p_country_id in number) return date DETERMINISTIC is p_date date; begin select current_date into p_date from calendar_table where country_id=p_country_id; return p_date; end; / select * from sales where sale_date=f_get_date(1); In most cases it should not be differences between sub-query and function.
  • Index Clustering factor. One way how to avoid negative performance impact on joins which involves bad clustered indexes.

    High Index Clustering factor is popular problem in SQL world. The most known solution for it – rebuild table ordering by columns what are in the index. But in real world, with table size growing exponentially its not always practical or possible to do it. Here is one way to avoid index clustering problem by SQL optimization.. Consider classical example. We have tables: customer CREATE TABLE customer (customer_id NUMBER, name VARCHAR2(50), dob DATE, status VARCHAR2(20), .