Q&As: Data Warehosing and Partitioning (I)




(Q) In which ways partitions facilitate administrative operations?

  • Partitioning enable administrative operations to work on subsets of data.
  • You can add, organize or drop a partition with minimal or zero interruption to read-only applications
  • Adding a partition: you can defer segment creation, which is useful when the segment is very large
  • Changing partition granularity: You can split or merge partitions.
  • Swapping partitions: you can easily add, remove, or swap a large amount of data quickly
  • Partitioning enhances data access and improve overall application performance:
    • More users can query the system, since each SQL is likely to access smaller amounts of data
    • Complex queries run faster. Less IO access


(Q) List three advantages of using partitioned objects

(1) Backup and recovery can be performed on a lower level of granularity (at the partition level)
(2) Part of the database objects can be placed in compressed storage, while others remain uncompressed
(3) You can transparently store data on different storage tiers to lower costs


(Q) How can the database take advantage of partitioning when executing an SQL?

  • Using parallel executions to speed up queries, DML and DDL.
  • Individual parallel execution servers can work on their own data sets, identified by partition boundaries


(Q) what is Partition pruning?

  • When executing a SQL statement, the Optimizer analyzes FROM and WHERE clauses to eliminate unneeded partitions from the execution plan's access list.
  • In the example below, the database scans only four partitions when executing the query. In an non-partitioned table, all data would have to be scanned.


(Q) What is the difference between static pruning and dynamic pruning?

  • Static pruning - occurs at compile-time.
    • example: SQL stmt with WHERE clause with a constant literal on the partition key column
  • Dynamic pruning - occurs at run-time, when the exact partitions to be accessed are not known before hand
    • example: SQL stmt with operator or function in the WHERE condition


(Q) How can you identify when static pruning is used in an execution plan?

  • In the execution plan, the OPERATION column shows PARTITION RANGE SINGLE, indicating that only a single (or range of) partition(s) is being accessed.
  • If OPERATION = PARTITION RANGE ALL, then ALL partitions are being accessed
select * from sales where time_id = to_date('01-jan-2001', 'dd-mon-yyyy');
... Returns 766 rows

explain plan for select * from sales where time_id = to_date('01-jan-2001', 'dd-mon-yyyy');
plan FOR succeeded

select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------
Plan hash value: 3733446669

---------------------------------------------------------------------------------------------------------------------  
| Id | Operation                          | Name           | Rows | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------- 
|  0 | SELECT STATEMENT                   |                |  766 | 22980 |    27   (0)| 00:00:01 |       |       |
|  1 |  PARTITION RANGE SINGLE            |                |  766 | 22980 |    27   (0)| 00:00:01 |    17 |    17 |
|  2 |   TABLE ACCESS BY LOCAL INDEX ROWID| SALES          |  766 | 22980 |    27   (0)| 00:00:01 |    17 |    17 |
|  3 |    BITMAP CONVERSION TO ROWIDS     |                |      |       |            |          |       |       |
|* 4 |     BITMAP INDEX SINGLE VALUE      | SALES_TIME_BIX |      |       |            |          |    17 |    17 |
--------------------------------------------------------------------------------------------------------------------- 

Predicate Information (identified by operation id):                
----------------------------------------
   4 - access("TIME_ID"=TO_DATE(' 2001-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))      

 16 rows selected 


(Q) What types of SQL statements are candidates for partition pruning?

  • On Range or List partitioned columns: When you use range, LIKE, equality, and IN-list predicates
  • On Hash Partitioned columns: When you use equlity and IN-list predicates


(Q) How can datatype conversions affect the type of partition pruning that may be used?

  • Datatype conversion typically lead to dynamic, when static pruning would have otherwise been possible
  • Static pruning provides better performance than dynamic pruning
  • example: Proper use of TO_DATE function guarantees that the database can uniquely determine the date value and thus the partition that should be accessed
Compare the following queries:
SELECT SUM(amount_sold) total_revenue FROM sh.sales
(clause 1)   WHERE time_id between '01-JAN-00' and '31-DEC-00' ;

(clause 2)   WHERE time_id between '01-JAN-2000' and '31-DEC-2000' ;

(clause 3)   WHERE time_id between to_date('01-JAN-2000', 'dd-MON-yyyy') and 
                                   to_date('31-DEC-2000', 'dd-MON-yyyy');

(Clause 1:) 
Leads to dynamic pruning, because date needs to be implicitly converted and does not match nls_date_format

SQL> explain plan for 
 SELECT SUM(amount_sold) total_revenue 
 FROM sales
 WHERE time_id between '01-JAN-00' and '31-DEC-00';

plan FOR succeeded.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------
Plan hash value: 3772476237   

---------------------------------------------------------------------------------------------------- 
| Id  | Operation                  | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop | 
---------------------------------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT           |       |     1 |    13 |   592  (19)| 00:00:08 |       |       | 
|   1 |  SORT AGGREGATE            |       |     1 |    13 |            |          |       |       | 
|*  2 |   FILTER                   |       |       |       |            |          |       |       | 
|   3 |    PARTITION RANGE ITERATOR|       |   230K|  2932K|   592  (19)| 00:00:08 |   KEY |   KEY | 
|*  4 |     TABLE ACCESS FULL      | SALES |   230K|  2932K|   592  (19)| 00:00:08 |   KEY |   KEY | 
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
                                                  
   2 - filter(TO_DATE('01-JAN-00')<=TO_DATE('31-DEC-00'))
   4 - filter("TIME_ID">='01-JAN-00' AND "TIME_ID"<='31-DEC-00')

 17 rows selected 


(Clause 2:) 
Uses STATIC pruning, because although date needs to be implicitly converted is DOES match nls_date_format

SQL> explain plan for 
 SELECT SUM(amount_sold) total_revenue 
 FROM sales
 WHERE time_id between '01-JAN-2000' and '31-DEC-2000';

plan FOR succeeded.

SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT 
-------------------------------------------------------------------------------------------------
Plan hash value: 1500327972 

--------------------------------------------------------------------------------------------------- 
| Id  | Operation                 | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop | 
--------------------------------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT          |       |     1 |    13 |   136  (11)| 00:00:02 |       |       | 
|   1 |  SORT AGGREGATE           |       |     1 |    13 |            |          |       |       | 
|   2 |   PARTITION RANGE ITERATOR|       |   230K|  2932K|   136  (11)| 00:00:02 |    13 |    16 | 
|*  3 |    TABLE ACCESS FULL      | SALES |   230K|  2932K|   136  (11)| 00:00:02 |    13 |    16 | 
--------------------------------------------------------------------------------------------------- 

Predicate Information (identified by operation id):  
---------------------------------------------------  

   3 - filter("TIME_ID"<=TO_DATE(' 2000-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

 15 rows selected 

(Clause 3:) 
Uses STATIC pruning, because although date is explicitly converted

SQL> Explain plan for
SELECT SUM(amount_sold) total_revenue 
FROM sales
   WHERE time_id between to_date('01-JAN-2000', 'dd-MON-yyyy') and 
                         to_date('31-DEC-2000', 'dd-MON-yyyy');

plan FOR succeeded.

SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT 
-------------------------------------------
Plan hash value: 1500327972

---------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |       |     1 |    13 |   136  (11)| 00:00:02 |       |       |
|   1 |  SORT AGGREGATE           |       |     1 |    13 |            |          |       |       |
|   2 |   PARTITION RANGE ITERATOR|       |   230K|  2932K|   136  (11)| 00:00:02 |    13 |    16 |
|*  3 |    TABLE ACCESS FULL      | SALES |   230K|  2932K|   136  (11)| 00:00:02 |    13 |    16 |
---------------------------------------------------------------------------------------------------
                                                                                                  
Predicate Information (identified by operation id): 
--------------------------------------------------- 

   3 - filter("TIME_ID"<=TO_DATE(' 2000-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

 15 rows selected 

No comments:

Post a Comment