You Asked

Hi team,
Could you please help to get answer of One of my interview question -

Consider a non-partition table x having date column with 1000 rows.
How can we insert future rows i.e. from 1001 .. onward into partition (without modifying table structure i.e. non-partition table)

Is is really possible to keep non-partition table as is and new INSERTS on table can be moved to Partitions?

and Connor said...

The interviewer probably means 1 of 2 things

1) existing table T becomes a partition in a new table

2) insert new rows into a *new* table T_PAR (that is partitioned), and leave existing table T unpartitioned, or

For (1), you could create a new partitioned table (T_PAR), and then issue an 'alter table T_PAR exchange partition P1 with table T", and then table is now partitioned and you didnt move any existing data.

For (2), you can create a view sitting on top of the table, and capture inserts with an instead of trigger, eg

SQL> create table t ( x date );

Table created.

SQL>
SQL> insert into t select date '2017-05-01' - rownum
  2  from dual connect by level <= 10;

10 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> create table t_par ( x date )
  2  partition by range ( x )
  3  (
  4    partition p_empty values less than ( date '2017-05-01' ),
  5    partition p1      values less than ( date '2020-01-01' )
  6  );

Table created.

SQL>
SQL> rename t to t_old_data;

Table renamed.

SQL>
SQL> create view t as
  2  select * from t_old_data
  3  union all
  4  select * from t_par;

View created.

SQL>
SQL> create or replace
  2  trigger trg
  3  instead of insert on t
  4  for each row
  5  begin
  6    if :new.x < date '2017-05-01' then
  7       insert into t_old_data values (:new.x);
  8    else
  9       insert into t_par values (:new.x);
 10    end if;
 11  end;
 12  /

Trigger created.

SQL>
SQL> insert into t values ( sysdate );

1 row created.

SQL>
SQL> select * from t;

X
---------
30-APR-17
29-APR-17
28-APR-17
27-APR-17
26-APR-17
25-APR-17
24-APR-17
23-APR-17
22-APR-17
21-APR-17
06-MAY-17

11 rows selected.

SQL> select * from t_par;

X
---------
06-MAY-17

1 row selected.

Rating

(3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

WEEKLY Partitions in 11g

A reader, May 31, 2017 - 12:25 pm UTC

Hi Connor,

Is there any way we can create INTERVAL Partitions on WEEKLY basis.

Both NUMTODSINTERVAL & NUMTOYMINTERVAL doesnt allow to do that.

June 01, 2017 - 2:27 am UTC

SQL> select NUMTODSINTERVAL(7,'DAY') from dual;

NUMTODSINTERVAL(7,'DAY')
---------------------------------------------------
+000000007 00:00:00.000000000

HIGH_VALUE comparision

A reader, October 27, 2017 - 12:18 pm UTC

Hi,

Please have a look at below table partitions.

TABLE_NAME PARTITION_NAME HIGH_VALUE
----------------- ---------------------- -----------------------------------------------------------------------------------
TB_TEST_PARTITION TB_TEST_PARTITION_2009 TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
TB_TEST_PARTITION TB_TEST_PARTITION_2011 TO_DATE(' 2012-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
TB_TEST_PARTITION TB_TEST_PARTITION_2012 TO_DATE(' 2013-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
TB_TEST_PARTITION TB_TEST_PARTITION_2013 TO_DATE(' 2014-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
TB_TEST_PARTITION TB_TEST_PARTITION_2014 TO_DATE(' 2015-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
TB_TEST_PARTITION TB_TEST_PARTITION_2015 TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
TB_TEST_PARTITION TB_TEST_PARTITION_2016 TO_DATE(' 2017-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')
TB_TEST_PARTITION TB_TEST_PARTITION_2017 TO_DATE(' 2018-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

I want to build a query which will give me partition less than 2013 (i.e. TB_TEST_PARTITION_2012, TB_TEST_PARTITION_2011, TB_TEST_PARTITION_2009)
Problem which i am facing is all_tab_partitions.HIGH_VALUE is LONG datatype and its not allowing me to use any date function on it.
Couls you please help how to modify below select condition to make it work.

select a.owner, a.table_name, a.partitioning_type, t.partition_name, a.interval
from all_part_tables a, all_tab_partitions t
where a.owner in ('SCOTT')
and a.partitioning_type = 'RANGE'
and a.table_name not like 'BIN$%'
and a.interval is not null
and t.table_owner = a.owner
and t.table_name = 'TB_TEST_PARTITION'
and t.table_name = a.table_name
-----------------------------
and TO_CHAR((t.high_value - 1), 'YYYY') < 2013
-----------------------------
;

October 27, 2017 - 2:35 pm UTC