244th questions, 053244

244th questions, 053244

224. You need to create a partitioned table to store historical data and you issued the following command:
Create table purchase_interval partition by range (time_id) INTERVAL
(NUMTOYMINTERVAL (1, 'month') store in (tbs1, tbs2, tbs3) (PARTITION p1 VALUES LESS
THAN (TO_DATE ('1-1-2005 ', 'dd-mm-yyyy'), PARTITION p2 values less than (TO_DATE ('1-1-2007 ',
'Dd-mm-yyyy') as select * FROM purchases WHERE time_id <TO_DATE ('1-1-2007 ', 'dd-mm-yyyy ');
What is the outcome of the above command?
A. It returns an error because the range partitions P1 and P2 shocould be of the same range.
B. It creates two range partitions (P1, P2). Within each range partition, it creates monthwise subpartitions.
C. It creates two range partitions of varying range. For data beyond '1-1-2007, 'it creates partitions with
Width of one month each.
D. It returns an error because the number of tablespaces (TBS1, TBS2, TBS3) specified does not match
The number of range partitions (P1, P2) specified.
Answer:C

INTERVAL Clause

Use this clause to establish interval partitioning for the table. interval partitions are partitions based on a numeric range or datetime interval. they extend range partitioning by instructing the database to create partitions of the specified range or interval automatically when data inserted into the table exceeds all of the range partitions.

Interval Partitioning Example The following example creates a variation ofoe.customersTable that is partitioned by interval oncredit_limitColumn. one range partition is created to establish the transition point. all of the original data in the table is within the bounds of the range partition. then data is added that exceeds the range partition, and the database creates a new interval partition.

CREATE TABLE customers_demo (  customer_id number(6),  cust_first_name varchar2(20),  cust_last_name varchar2(20),  credit_limit number(9,2))PARTITION BY RANGE (credit_limit)INTERVAL (1000)(PARTITION p1 VALUES LESS THAN (5001)); INSERT INTO customers_demo  (customer_id, cust_first_name, cust_last_name, credit_limit)  (select customer_id, cust_first_name, cust_last_name, credit_limit  from customers);

QueryUSER_TAB_PARTITIONSData dictionary view before the database creates the interval partition:

SELECT partition_name, high_value FROM user_tab_partitions  WHERE table_name = 'CUSTOMERS_DEMO';PARTITION_NAME                 HIGH_VALUE------------------------------ ---------------P1                             5001 

Insert data into the table that exceeds the high value of the range partition:

INSERT INTO customers_demo  VALUES (699, 'Fred', 'Flintstone', 5500);

QueryUSER_TAB_PARTITIONSView again after the insert to learn the system-generated name of the interval partition created to accommodate the inserted data. (The system-generated name will vary for each session .)

SELECT partition_name, high_value FROM user_tab_partitions  WHERE table_name = 'CUSTOMERS_DEMO'  ORDER BY partition_name;PARTITION_NAME                 HIGH_VALUE------------------------------ ---------------P1                             5001SYS_P44                        6001

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.