Create a partition on the Oracle 11g virtual column, oracle11g

Create a partition on the Oracle 11g virtual column, oracle11g

On Oracle 11g, partitions can be performed on virtual columns. This feature is useful. Let's perform a test:

SQL> select * from v $ version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit Production
PL/SQL Release 11.2.0.1.0-Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0-Production

NLSRTL Version 11.2.0.1.0-Production

SQL> drop table test purge;
SQL> create table test
(Bureau_code varchar2 (20) not null,
Province_code as (CAST (SUBSTR (bureau_code, 0, 2) AS VARCHAR2 (2 )))
)
Partition by list (province_code)
(
Partition p1 values ('01 '),
Partition p2 values ('02 '),
Partition p3 values ('03 '),
Partition p4 values ('04 '),
Partition p5 values ('05 ')
);

SQL> insert into test (bureau_code) values ('20140901 ');
SQL> insert into test (bureau_code) values ('20140901 ');
SQL> insert into test (bureau_code) values ('20140901 ');
SQL> insert into test (bureau_code) values ('20140901 ');
SQL> insert into test (bureau_code) values ('20140901 ');
SQL> insert into test (bureau_code) values ('20140901 ');
SQL> insert into test (bureau_code) values ('20140901 ');
SQL> insert into test (bureau_code) values ('20140901 ');
SQL> commit;

SQL> select * from test partition (p1 );
BUREAU_CODE PR
----------------------
0101 01
0102 01

SQL> set autotrace traceonly
SQL> select * from test partition (p1 );
Execution Plan
----------------------------------------------------------
Plan hash value: 213508695
Bytes ----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | Pstart | Pstop |
Bytes ----------------------------------------------------------------------------------------------
| 0 | select statement | 2 | 30 | 4 (0) | 00:00:01 |
| 1 | partition list single | 2 | 30 | 4 (0) | 00:00:01 | 1 | 1 |
| 2 | table access full | TEST | 2 | 30 | 4 (0) | 00:00:01 | 1 | 1 |-- It turns out that it is partitioned.
Bytes ----------------------------------------------------------------------------------------------
Note
-----
-Dynamic sampling used for this statement (level = 2)
Statistics
----------------------------------------------------------
0 recursive cballs
0 db block gets
8 consistent gets
0 physical reads
0 redo size
434 bytes sent via SQL * Net to client
338 bytes encoded ed via SQL * Net from client
2 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed

How to Create a syntax for Oracle 11G interval partitions

Interval partitions are only applicable to partitions of the range type.

Create table interval_sales
(Prod_id NUMBER (6)
, Cust_id NUMBER
, Time_id DATE
, Channel_id CHAR (1)
, Promo_id NUMBER (6)
, Quantity_sold NUMBER (3)
, Amount_sold NUMBER (10, 2)
)
Partition by range (time_id)
INTERVAL (NUMTOYMINTERVAL (1, 'month '))
(PARTITION p0 values less than (TO_DATE ('1-1-2008 ', 'dd-MM-YYYY ')),
PARTITION p1 values less than (TO_DATE ('1-1-2009 ', 'dd-MM-YYYY ')),
PARTITION p2 values less than (TO_DATE ('1-7-2009 ', 'dd-MM-YYYY ')),
PARTITION p3 values less than (TO_DATE ('1-1-2010 ', 'dd-MM-YYYY ')));
The preceding example shows that, apart from the partitions defined above, a new partition is created every month as long as there is data.

INTERVAL (NUMTOYMINTERVAL (1, 'month') by MONTH
INTERVAL (NUMTODSINTERVAL (1, 'day') by day
INTERVAL (NUMTOYMINTERVAL (1, 'Year') by YEAR

What is virtual column in oracle?

New Features of Oracle 11g-virtual Columns
In the old Oracle version, when we need expressions or some calculation formulas, we will create a database view. If we need to use indexes on this view, we will create a function-based index.
Oracle 11g now allows us to store expressions directly on tables using virtual columns.
Virtual column values are not stored on disks. They are calculated temporarily based on the defined expression during query.

We cannot insert data into a virtual column or implicitly add data to a virtual column:

We can only use physical columns to insert data. Then you can query the virtual column value: the expression is calculated in real time during the query.
Indexes and constraints can also be applied to virtual columns. You can also create a foreign key for a virtual column.