SQL & PL/SQL :: How To Transfer Data From Non-partitioning Table To Partitioning Table
Apr 4, 2013
I have problem to transfer data in non partitioning table to partitioning table.
I have non partitioning table and i create new table partitioning that have same column and type like in non partitioning. So how can i transfer data from table in non partitioning to table in partitioning?
I partitioned a source table of around 100 million rows (62GB) in DEV server. The target database was created new. It was range partioned on a date column as follows:
PARTITION BY RANGE (ENTRY_DATE_TIME) ( PARTITION ppre2012 values less than (TO_DATE('01/01/2012','DD/MM/YYYY')) TABLESPACE WST_LRG_D, PARTITION p2012 values less than (TO_DATE('01/01/2013','DD/MM/YYYY')) TABLESPACE WST_LRG_D, PARTITION p2013 values less than (TO_DATE('01/01/2014','DD/MM/YYYY')) TABLESPACE WST_LRG_D, PARTITION p2014 values less than (MAXVALUE) TABLESPACE WST_LRG_D )
That is yearly basis. Anything before 2012 went to ppre2012, then p2012, p2013 and so forth. There is 20 million rows in p2012. and around 75 million rows in ppre2012. We needed both the source (un-partitioned) and target (partitioned) tables in DEv for comparision. The queries are normally on the current year partition. Just to state taht I am a developer and don't have full visibility to the production instance.
Now that our tests are complete, we would like to promote this in production. Obviously in production we would not not need both source and target tables. In all probability this will be performed over a weekend window. Therefore I would like to suggest the following .
1) use expdp to export source table 2) drop the source table 3) create a new source table "partitioned" with no indexes 4) use impdp to get data back into table 5) create global index (it is a unique index to enforce uniquness) and the rest of indexes as local 6) perform dbms_stats.gather_table_stats(user,'SOURCE', cascade=>true). This takes around 2 hours in dev
My point is that whether importing 100 million rows will not cause issues with undo segments. Can we import data say first to the current partition p2012 (20 million rows) first.
I recently started working with legacy code and noticed that some huge tables (5 years worth of data, don't have more details on me right now but can post later if needed) are partitioned based on time sequence number column while majority of queries are done based on time (different column). Queries performance is degrading and I'd like to try to modify partitioning and run some tests to evaluate performance improvement.
My only concern is with so much live data I have to come up with solution on how to switch partitioning with the least impact on applications running 24 x 7. Something you have done in the same situation and it worked?
DECLARE v_name VARCHAR2(256); BEGIN SELECT sys_context('userenv', 'current_user') INTO v_name FROM dual; DBMS_REDEFINITION.CAN_REDEF_TABLE(v_name, 'SO33070_ORIGINAL', dbms_redefinition.CONS_USE_ROWID); END;
Success
3. Creating a duplicate table
CREATE TABLE SO33070_NEW ( SERIAL_ID NUMBER(15,0), INSERTED_TIME DATE DEFAULT SYSDATE ) PARTITION BY RANGE ("INSERTED_TIME") INTERVAL (NUMTODSINTERVAL(1,'DAY')) ( PARTITION "p1_1" VALUES LESS THAN (TO_DATE(' 2012-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) )
I have a big table in which we load about 37M recrods. We have informatica ETL which Loads the data in bulk Mode and creats index after completion. The data load takes about 1Hr and Index Creation takes about 1/2 hr. In total it takes about 90 to 95 Mnts.
Now I thought if Partition and Load paralley, It will improve perfromance. We did 4 partition and and each Partition about 9M records. The data load in Bulk mode is completing in 25 Mnts. Again When I am creating index over it, It is taking about 40 Mnts. and in Total Load time is 65 Mnts.
Is there way I can better performance to complete the load in 1/2 hr ?
At moment we use range-hash partitioning of a large dimension table (dimension model warehouse) table with 2 levels - range partitioned on columns only available at bottom level of hierarchy - date and issue_id.
Result is a partition with null value - assume would get a null partition in large fact table if was partitioned with reference to the large dimension.Large fact table similarly partitioned date range-hash local bitmap indexes
Suggested to use would get automatic partition-wise joins if used reference partitioningWould have thought would get that with range-hash on both dimension.
Database Version : DB : Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionOS : HP-UX nduhi18 B.11.31 U ia64 1022072414 unlimited-user licenseAPP : SAP - ERP I have to RANGE partition on UPDATED_ON or PROFILE either one table which is having below
structure : Name Null? Type -------------------- -------- -------------------------------- MANDT NOT NULL VARCHAR2(9) MR_ID NOT NULL VARCHAR2(60) PROFILE NOT NULL VARCHAR2(54) REGISTER_ID NOT NULL VARCHAR2(30) INTERVAL_DATE NOT NULL VARCHAR2(24) AGGR_CONSUMPTION NOT NULL NUMBER(21,6) MDM_VERS_NO NOT NULL VARCHAR2(9) MDP_UPDATE_DATE NOT NULL VARCHAR2(24) MDP_UPDATE_TIME NOT NULL VARCHAR2(18) NMI_CONFIG NOT NULL VARCHAR2(120) NMI_CONFIG_FLAG NOT NULL VARCHAR2(3) MDM_DATA_STRM_ID NOT NULL VARCHAR2(6) NSRD NOT NULL VARCHAR2
[Code]....
As per my knowledge, RANGE is better suited for DATE or NUMBER. and INTERVAL partition is possible on DATE or NUMBEr . Column PROFILEIts is of VARCHAR2 datatype. I know still I can partition as Oracle internally convert varchar2 to number while inserting data. But INTERVAL is not possible. How to RANGE partition on PROFILE ? Column CREATED_ON :It is of NUMBER with decimal
I have little bit confuse in data partitioning in database, I have read about it and i understand that there ate two type called vertical partitioning and another horizontal partitioning...I have three questions are
1- is data partitioning used of in networks or can be in one PC? 2- the data partitioning divided data that in table to partitions (groups) , according what ??? is to quantity or meaning of data that inside table? 3- Is clustering that can be execute by Oracle using CTX_CLS.CLUSTERING type of it or partitioning not related to it?
I want to practice partitioning, I have schema which has sales table but that is already partitioned. I want to know if there are some schema available for download which has non partitioned tables and records more than 10000.
There is a very large fact table that is range partitioned by a column DATE_KEY of type NUMBER(38), such that every hour is a different partition. There is a bitmap index BX$FACT#DATE_KEY on field DATE_KEY, which also is a foreign key referencing DATE_DIM (DATE_KEY). There is a different DATE_KEY for every hour, generated as YYYYMMDDHH24.
When I run
"SELECT * FROM FACT WHERE DATE_KEY >= 2012031207 AND DATE_KEY < 2012031208"
to get all the records for 7 am on March 12th, partition pruning kicks in and sees that only one partition is used. The CBO then decides to do a full scan of the partition. This behavior is correct/desired.
If, however, I run
"SELECT * FROM FACT WHERE DATE_KEY = 2012031207 AND DATE_KEY < 2012031209"
to get all the records for 7 and 8 am, Oracle knows that it will have to scan two partitions. The CBO then decides that using the BX$FACT#DATE_KEY must be a good idea and, instead of doing a full scan of the partitions, does access by local index rowid, which is many times slower.
I think I understand the cause - when more than a single partition is involved, Oracle has to use the global index stats (instead of the local ones, like in the first scenario) and the CBO decides to use it because that the selectivity for the global index is great, when in fact the query will return all the rows for that particular partition (no selectivity).
How to get the CBO to choose a full scan in the second scenario as well? I need to support ad-hoc queries generated by a BI tool, so I cannot add hints to the queries. I also can't get rid of the index on DATE_KEY, because in real life the predicates are on fields of the date dimension, not directly on the key, so I need to join on it.
Interval partitioning I keep getting the below error on a table.A more discerning eye is needed
PARTITION DEC_2012 VALUES LESS THAN (TO_DATE('01-01-2013', 'DD-MM-YYYY')), * ERROR at line 26: ORA-14037: partition bound of partition "DEC_2012" is too high
CREATE TABLE STATISTICS_PART ( ID_KEY NUMBER(10) NOT NULL, LUD DATE DEFAULT sysdate, [code]....
Other than the obvious to me, where interval partitioning creates partitions as needed. Is there any performance benefit from using interval partitions vs date range partitions.
One draw back for me is that developers do access the partition name in some of their queries, so if I use date range partitioning this will not break their code. I could not find a way to assign a name to a partition when using intervals, is this always system generated or can this be over-ridden.
I am running Oracle 11.1.0.7 soon to be running on 11.2.0.0
I am having 7 tables consider A,B,C,D,E,F,G. whereas A is master table and others are dependent on table A. columnA is referential key for all tables. Table A is having one DateRange column.I have successfully created range partitioning with partitioning key is DateRange. and Refeernce partitioning on other tables with column referring to ColumnA with foreign key. Also created local indexes on partitioned key DateRange.
Problem is that, while fetching complex queries for reports, it is taking more time as compared to non partitioning structure. Is ref partitioning affecting on complex queries, queries returning more rows?
I Have created a interval partitioned table with local index.But when automatic partitions are created based record insertion, whether local indexes will be created for each newly created partition or not? If created, how to check
Below is the code which I tried
CREATE TABLE interval_date ( date1 date, days VARCHAR2(50) ) PARTITION BY RANGE(date1) INTERVAL (NUMTODSINTERVAL(45,'DAY'))
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product PL/SQL Release 10.2.0.1.0 - Production CORE 10.2.0.1.0 Production TNS for 32-bit Windows: Version 10.2.0.1.0 - Production NLSRTL Version 10.2.0.1.0 - Production.
I am trying to create a partitioned table so that a number (which date converted to number ) partition is created on inserting a new row for release_date column.
note that release_date column is having number data type (as per design) and people want to create an interval based partition on this.
They want data type NOT to be altered.
create table product( prod_id number, prod_code varchar2(3), release_date number) partition by range(release_date) interval(NUMTOYMINTERVAL (1,'MONTH')) (partition p0 values less than (20120101))
11gr2, We need to do partition a existing table of size 20g, But partition key column is NUMBER type and data stored in unix date format.I would like to create a monthly partition table as below. But not able to create.
create table student ( ENTRY_ID number(5,1), NAME varchar2(30 BYTE) ) partition by range ( fun_unix_to_date (ENTRY_ID) ) --> fun_unix_to_date is a customized function to convert unix time stamp to date format.
INTERVAL (100) ( PARTITION CATCH_ALL values LESS THAN (to_date('01-MAR-12','DD-MON-YY')));
ERROR at line 5:ORA-00907: missing right parenthesis
I'm experiencing some infinite loop for my delete. I tried many way to deal with this problem but still take too much time. I will try to be clear as possible.
I have 4 implicated table in this problem.
The deletion is done depending of the pool_id given
Table 1 contain the pool_id Table 2 the ticket_id foreign join ticket_pool_id with the pool_id Table 3 ticket_child_id foreign join ticket_id with the ticket_id Table 4 ticket_grand_child_id foreign ticket_child_id join with the ticket_child_id
Concerned count for each table 1---->1 table 2---->1 200 000 table 3---->6 300 000 table 4---->6 300 000
So in fact it`s 6.3M+6.3M+1.2M+1 row to be deleted
Here`s the constraint : -No partintionning -Oracle version 9 -Online all the time so no downtime neither CTAS -We cannot use cascade constraint -The normalization is very important
Here`s what I tried: -Bulk delete -Delete with statement (In and Exists clause) -temp table for each level and 1 level join -procedure and commit each 20k
None of those worked in a decent time frame like less then one hour. The fact that we cannot base a delete on one of the column value is not working. Is there a way I'm getting desperate now
Our organization is attempting to learn more about the partitioning features of Oracle 11g. I've been reading the partitioning manuals, and I have not found a clear answer on this topic, but I suspect I know the answer.
If you create a range partitioned table; using interval partitioning, say something like this:
CREATE table range_parti ( CODE NUMBER(5), DESCRIPTION VARCHAR2(50), CREATED_DATE DATE) PARTITION BY RANGE (created_date) INTERVAL (NUMTOYMINTERVAL(1,'MONTH')) ( PARTITION my_parti VALUES LESS THAN (TO_DATE('01-NOV-2007','DD-MON-YYYY')) );
but you try to insert a null value as the partition key, you get the following error:
SQL> INSERT INTO range_parti VALUES (1,'one',NULL); INSERT INTO range_parti VALUES (1,'one',NULL) * ERROR at line 1: ORA-14400: inserted partition key does not map to any partition Elapsed: 00:00:00.07
Is there no way to tell it to use a default partition for NULL values? Or specifically designate a partition for NULL values WITHOUT having to manually list out each partition? It seems it works if you don't use the INTERVAL keyword, list out your partitions, and use MAXVALUE. However, our hope to avoid having that as it creates monstrously huge DDL statements for tables that have lots of date ranges, and we will be forced to manually add new partitions each month as data is added/time passes.
It appears from my experience so far, if your column can allow nulls, you cannot use interval range partitioning on that column.
What could be the strategy on deciding which columns to create partitions on? I understand for deciding this, first, we need to know the columns we are using in the WHERE clause
consider following scenario assume that emp table is very large
(1) Query - select * from emp where empno=<pk_value> what could the partitioning column here?
This is confusing as we access with, quite selective criteria here but we access lot of data. No particular date range Or No particular flag, value to check with! Would hash partition on the pk_column will be useful here?
(2) select * from emp where empno=<pk_value> and deptno=<some value> what could the partitioning column here? I assume deptno here. Right?
In general what could be the considerations in deciding the partitioning columns? whether the column is not a unique key column Or the column is preferrable if used in joins Or the column is not updateable
Finally will the pruning (take place) if the query spans across multiple partitions, though Not all partitions?