Parallel Insert In Subpartitioned Table

Dec 29, 2010

At work we have a linux server with Oracle 10.2.0.5 g, and we have the need, to save time, to use parallel process launched in background (&) or (at now).

The table that we are going to insert is partitioned for date and subpartitioned for name of file_source, and we are having a lot of problem with this.

Our DBA staff say that is impossible to work in this way but for me, after ten year of oracle-work, sounds strange , sure may be that we don'r use really really correctly this procedure but i suppose that Oracle is able to recevie 20 ask of insert on the same table.

The situation is that we usede this procedure in other server and it's work but the answer of our DBA-staff is that we have less data storage and so on. In the end some times this proceduers have a low time of execution and some times no, this goes out of my ability to find out the problem.

We have a DWH with STAGING AREA, ODS Level, DDS Level and DM Level, the Staing area is paritionend for file_source, the ODS Area is partitioned by range (date) and subpartitioned for name (file_source), the DDS area is partitioned by range(date) and subpartitioned for Origin system(that include all the file_source) and the DM area is only partitioned by date.

So the most big problems that we meet are between the Staging are and ODS area, and between the Ods area and the DDS Area, the most important thing is that this table (DDS) is a monster of near 500.000.000 of rows (ITr of data) but we look only at the date to elaborate.

The solution is clear, divide this table in two, one online and one of storage as usually and correctly a normal situation require, but unfortunatly is a situation that we Erhedit from an old system and at the moment is not approoved the change request on this site.

The really strange thing is that sometimes work and some times not, without understand the cause of this. My opinion on this is that the DB is not correctly configured but the System Staff say that everything is correct and there are no problem. My first problem is to understand, if possible, wich is the limit of this way to operate, can i insert in a subpartition in the same time with twenty parallel process that write on same partition and different subpartition? Is correct to act in this way to save time about the data-load or better doing it one by one? On my experience i realized that Oracle can manage(is his work) a lot of request in the same time, but in this DB that we are using i continue to see problems that sounds like if we are usuing a tool that is not working in the correct way...

May be we went beyond some limit but in the end are less then 5.000.000 records per day that we move i think that a DWH have to support more than this...

View 4 Replies


ADVERTISEMENT

Performance Tuning :: Parallel Index With Non-partitioned No-parallel Table?

Apr 30, 2012

If we have not set parallel degree for a table then we can ( try to ) force parallel execution on a table using a parallel hint Does this 'parallelism' works on the index search in the query as well?

In which situations non-parallel non-partitioned table but parallel index (degree>2) will facilitate a query?

View 5 Replies View Related

SQL & PL/SQL :: Multiple Parallel Queries Against Table

Jun 23, 2011

I would like to know the following.

If multiple queries are run in parallel(at the same time) against a table or a set of tables (query referencing multiple tables), does it reduce the performance.

In other words is Oracle capable of reading (selecting from) the same table multiple times in parllel.

View 2 Replies View Related

PL/SQL :: Created Partition And Parallel Degree 4 On Table A

Oct 10, 2012

I have table A with size 120 Million and two more tables are of size 2 Million on Table B and less than 1 Million size on table C.I had created Partition and Parallel degree 4 on the table A. Created table B with Parallel degree 2 and Created table C with NOPARALLEL.

My query is using above tables with joins and inserting into table D using HINT /*+ APPEND NOLOGGING*/I had executed the explain on the above criteria the cost is showing 20767.

Later created Tables A,B and C with NOPARALLEL. Applied HINT on Table D /*+ APPEND NOLOGGING*/ and als applied HINT /*+ PARALLEL(A, 4) PARALLEL(B C, 2) on select query which uses to insert into Table D.

My question which is best practice on PARALLEL degree creation at table level or query level:

a) Creating table with Paralle (degree 4)
b) Applying HINT /*+ PARALLEL (TABLE A , 4) */ at query level

View 1 Replies View Related

SQL & PL/SQL :: Parallel In Pipe-lined Table Function Not Working?

Feb 1, 2011

'Oracle fast parallel data unload into ASCII file(s)' in this blog: URL....I have compiled the code and created the objects and the directory in my DB...But when I execute :

SELECT *
FROM TABLE(
DATA_UNLOAD(
CURSOR(
SELECT /*+ PARALLEL(A, 2, 1) */
TABLE_NAME || '|' ||
COLUMN_NAME || '|' ||
DATA_TYPE
FROM MYTABLE A
[code]....

It is supposed to return 2 rows (because of parallel execution), but it just returns 1..Do I have to do something special in order to make parallel pipelined function work

View 2 Replies View Related

SQL & PL/SQL :: Primary Constraint On Table Affecting Procedure To Insert Rest Of Rows In Table?

Jun 12, 2012

primary key constraint on transaction_dtl_bk is affecting the insertion of next correct rows.

CREATE OR REPLACE PROCEDURE NP_DB.san_po_nt_wnpg_1 (
dt DATE
)
IS
v_sql_error VARCHAR2 (100); -- added by sanjiv
v_sqlcode VARCHAR2 (100); ---- added by sanjiv added by sanjiv

[code]...

View 2 Replies View Related

PL/SQL :: Selecting Records From 125 Million Record Table To Insert Into Smaller Table?

Jul 17, 2013

Oracle 11gI have a large table of 125 million records - t3_universe.  This table never gets updated or altered once loaded,  but holds data that we receive from a lead company. I need to select records from this large table that fit certain demographic criteria and insert those into a smaller table - T3_Leads -  that will be updated with regard to when the lead is mailed and for other relevant information.  select records from this 125 million record table to insert into the smaller table. 

I have tried a variety of things - views, materialized views, direct insert into smaller table...I think I am probably missing other approaches. My current attempt has been to create a View using the query that selects the records as shown below.  Then use a second query that inserts into T3_Leads from this View V_Market.  This is very slow. Can I just use an Insert Into T3_Leads with this query - it did not seem to work with the WITH clause?    My Index on the large table is t3_universe_composite and includes zip_code, address_key, household_key.   

CREATE VIEW V_Market  asWITH got_pairs    AS     (         SELECT /*+ INDEX_FFS(t3_universe t3_universe_composite) */  l.zip_code, l.zip_plus_4, l.p1_givenname, l.surname, l.address, l.city, l.state, l.household_key, l.hh_type as l_hh_type, l.address_key, l.narrowband_income, l.p1_ms, l.p1_gender, l.p1_exact_age, l.p1_personkey, e.hh_type as filler_data, 1.p1_seq_no, l.p2_seq_no       ,      ROW_NUMBER () OVER ( PARTITION BY  l.address_key                                    ORDER BY      l.hh_verification_date  DESC                    ) AS r_num         FROM   t3_universe  e         JOIN   t3_universe  l  ON                l.address_key  = e.address_key             AND l.zip_code = e.zip_code           AND   l.p1_gender != e.p1_gender      

[code]....

View 2 Replies View Related

Forms :: Read Data From Table And Insert To Another Table With A Cursor?

Feb 20, 2013

I have a table with a BLOB column ;

I want read data from table and insert to another table with a cursor

My code is :

procedure read_data is
cursor get_data is
select id,image from picture1;
id1 number;
pic blob;
begin
open get_data;

[code]....

when I run form , error FRM-40734 occurred

error in line " fetch .... "

View 1 Replies View Related

Application Express :: Insert Into History Table When Update Action Is Performed On Tabular Form View Or Table

Aug 24, 2013

My scenario is I need to insert into History table when a record is been updated into a tabular form(insert the updated record along with the additional columns Action_by,Action_type(Like Update or delete) and Action Date Into History table i.e History table contains all the records as the main table which is been visible in tabular form along with these additional columns ...Action_by,action_type and action_date.

So now i dont want to create a befor/after update trigger on base table rather i would like to create a generic procedure which will insert the updated record into history table taking the page alias and pade ID as the parameters(GENERIC procedure is nothing but whcih applies to all the tabular forms(Tables) contained int he application ).

View 2 Replies View Related

Oracle 10g - Take Snapshot Of Table Before Insert Or Update Happens To That Table

Dec 28, 2010

I need to take a snapshot of a table before insert or update happens to that table.... in oracle 10g. I am reading the MV docs from oracle and below link..

[URL].......

how MV should be written for this and how to schedule it in dbms_jobs for auto refresh?

assuming that t1 is the table where DML operation are goin to happen so before any insert or update, snapshot has to be taken, and I am assuming that to do this it would look something like this?

create materialized view my_view refresh fast as select * from t1;

create materialized view log on my_view;

and then schedule in a dbms_jobs?

View 1 Replies View Related

SQL & PL/SQL :: Insert All Records From External Table Into Export Table

Mar 25, 2013

following is the requirement

External Table
WKSHT_FILE_EXT
wksht_line
Export Table
Wksht_export
global_idvarchar2(10)
wksht_linevarchar2(250)
[code]....

Step 1.Insert all records from the external table into the export table. Truncate the export table first

Step 2.Read in a record from the export map table

Step 3.Search through export table records looking for the key words BRANCH =. Compare the branch code with the branch code form the map table

Step 4.If a match is found mark all records in the export table for the worksheet with the global ID from the export map table as follows..The first line of a worksheet is marked by the words WKSHTS..The last line of the work sheet is marked by the words COMPANY CONFIDENTIAL..We will need to capture the line break so also mark the next line after the COMPANY CONFIDENTIAL line

Step 5.Continue with Steps 2 - 4 until all records have been processed from the export map table.

first I have to create a procedure ti insert data from external table to export table.Global id will be blank.it will be updated by the mapping table's Global Id when The EB COLUMN's data(i.e 8p,2Betc ) will match with the BRANC=NA,2Betc of the datasheet loaded from the external table.. FOLLOWING IS THE SAMPLE DATASHEET

WKSHTS AAAAA BBBBBBBBBBB ELECTRONICS INC. TIME REPORT-DATE PAGE
SORT - BR, SLSREP AEC FIELD SALES REPRESENTATIVE 16:14 09/21/12 1
BRANCH = 2B
EMPLOYEE NAME SALVAAG, GREGG Days in the Month 28
[code]....

THERE ARE 2 pages..I have to split this LONG REPORT STORED IN WKSHT_LINE COLUMN OF EXPORT TABLE to 2 records..like wise 500 pages are there means 500 records.. AND THEN FIND BRANCH= after that which two words will come i.e NA,2B etc if it will MATCH WITH MAPPING TABLE"S EB COLUMN"S DATA,THEN MAPPING TABLE's GLOBAL ID WILL BE UPDATED TO EXPORT TABLE's GLOBAL ID WHICH IS BLANK

View 1 Replies View Related

Insert Newest Records From One Table Into Another Table

Mar 9, 2004

Trying to auto insert the newest records from one table into another Table. I have a vendor provided table that is part of my database (running Oracle 9i) so I can't change the underlying structure to it or their process stops fluxing. However, I can add a trigger to it. What I want to do is this:

When the vendor's software inserts a new row (through their own automated process) I want to insert data from that same new record into another table of my own. (where of course I can re-format it, etc., and make the data my own)

The original vendor table does not have a insertion timestamp field to work off of.What is the best way to trigger an insert off the latest inserted record? It works to replace all the records in the entire vendor table but I only want to insert one record at a time.

View 2 Replies View Related

PL/SQL :: Trigger To Insert In One Table From Other And Truncate 2nd Table

Aug 2, 2012

I have table t1 and t1 , I want a procedure that will insert all records from t1 into table t2 and after successfull insert table t1 should be truncated .

If their is any problem in insert in to table t2 , the truncate command should not work .

Truncate command should work only after successfully insert command .

View 3 Replies View Related

SQL & PL/SQL :: Update Table After Insert To Another Table

Aug 8, 2012

how to adjust a total (counter) after a record is inserted into a table.

the dilemma i am facing is we are using third party software for our fundraising operations so I have no control over what gets done in the background as users process their daily batches into the system. below is the scenario:

create table paytable(
idnumber number(12),
appealcode varchar2(20),
batchno number(8),
trantype varchar2(3),
transnum number(8),
split_transnum number(8));

[Code]..

IDNUMBER APPEALCODE BATCHNO TRA TRANSNUM SPLIT_TRANSNUM
---------- -------------------- ---------- --- ---------- --------------
16084 DVFG1206 20120808 PP 23853576 1133821
16084 DVFG1206 20120808 PPO 23853577 1133821
1234 DVFG1206 20120808 PP 23853578 1133822

create table appealtable
(appealcode varchar2(20),
total# number(9),
total$ number(13,2));

[Code]...

APPEALCODE TOTAL# TOTAL$
-------------------- ---------- ----------
DVFG1206 100 2500

during batch posting records are inserted into the paytable, on some pledge donations donors will send overpayments when fulfilling a PLEDGE(as is the case with donor 16084) therefore the system will split the payment during the process and will assign a trantype of 'PP' to the exact pledge amount and a 'PPO'(pledge payment overage) towards the balance. additionally as records get inserted into paytable there is counter of those paytable records going into the appealtable for that particular appealcode so in the case above when batchno 20120808 is completed appealtable.total# will show 103 and total$ will show $2532($10,$12,$10,,,I did not include payment$ since that is not the focus of this issue and will not change).

mgt wants the counter into the appealtable to be 2 instead of 3 records since the two records that were split(same split_transnum) should be recorded as one response not two.

I have tried writing an after insert trigger(dreaded mutating table error) and can't seem to figure out how to update the counter to the appealtable after records are inserted into paytable. below is some code I've been working with but it's not working.

CREATE OR REPLACE TRIGGER PPO_Payment
AFTER INSERT ON paytable
FOR EACH ROW

DECLARE
p_cnt NUMBER :=0;

[Code]...

show errors

View 1 Replies View Related

PL/SQL :: Update Row In Table And Insert Same Row Into Another Table?

Sep 6, 2013

I want to update a row in a table say Table A and the updated row should be inserted into another table say Table B. I need to do it in a single SQL query and i don't want to do it in PL/SQL with triggers. And i tried with MERGE statement but its working with this scenario.

(Note: I'm using Oracle Database 10g Enterprise Edition Release 10.2.0.1.0).

View 8 Replies View Related

Insert From One Table To Another

Aug 5, 2008

The first problem I have is inserting data from one table to another WHILE incrementing a sequence:

Insert into PROG
(prog_cd, nm, typ_id, grp_id, clas_id, cat_id, shrt_nm, fl, prog_id)
values
((select one, two, three, four, five, six, seven, eight from LOAD), prog_seq.nextval);

I want to load the table PROG with the data in LOAD and increment a seq and put the value in prog_id, which is a field in PROG.

When I run it, I get:
ORA-00947: not enough values

View 4 Replies View Related

PL/SQL :: Insert Into A Table

Sep 14, 2012

For a table tab the values are:

tab
id()      value1      value2      value3      vale4      value5
001      state      wb                
001      year           2012           
001      dist                kol      
001      age                     27

Now I want to insert this data in another table tab1 and the output of that table will be

id      value1 value2     value3 value4     
001      wb      2012      kol      27

find out how i can insert the date like this.

View 9 Replies View Related

Insert Or Update On A Table

Jul 30, 2009

i have 2 identical tables....the trick is with regards to one column say column A,the first table TableA is constantly having data inserted and data updated, what im trying to do is create a before insert or update trigger that looks at column A and if column A=20 it changes this to 5 and inserts this into the tableB, everything else with the exception of Column A will be the same:-

this trigger i have done works but doesnt change the value of column A (INPUT_NADI) as i dont know how to do this:-

CREATE OR REPLACE TRIGGER UPDATE_TRG_NP_NE1_T0
BEFORE INSERT ON TESTNSS.NP_NE1_T1
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
BEGIN
INSERT INTO NP_NE1_T0
VALUES
[code]....

View 6 Replies View Related

SQL & PL/SQL :: Table Insert - Trivial?

Oct 27, 2011

I have one table with two columns col1 and col2 Values in table are like this
Row1 -- (1,'A')
Row2 (1,'B')
Row3 (1,'C')
Row 4 (2,'D')
Row 4 (2,'E')
Row 4 (2,'F')

Now I have second table which has FOUR columns
COL1,COL2,COL3,col4

I want to insert value in second table based on value in first column of T1 Like for rows in T1 with col1 as value 1 , insert the value of second column in second table I want second table like
ROW 1 ('1','A','B','C')
ROW 2 ('2',D','E',F)

how it can be done I tried insert into command but not able to frame the command

View 15 Replies View Related

SQL & PL/SQL :: How To Get Insert Template For Any Given Table

Jun 21, 2010

I would like to know how to get an insert template for any given table.

For example
SQL> @insertTemplate
Enter Table Name: emp
INSERT INTO emp VALUES
(NOT NULL -- EMP_ID NUMBER(15,0)
,NOT NULL -- EMP_NAME NUMBER(15,0)
,NOT NULL -- SALARY NUMBER(15,0)
,NULL -- HIRE_DATE DATE
) ;

For any given table the script should create an insert template like above?

View 13 Replies View Related

SQL & PL/SQL :: INSERT INTO Table Not Working?

Oct 26, 2010

oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
PL/SQL Release 11.1.0.6.0 - Production
"CORE 11.1.0.6.0 Production"

I am doing a simple INSERT INTO from the sql developer window. it shows that 1 row inserted, but when i query, it doesnt give th values.

insert into events_main(event_id,event_number,last_update_Date, last_updated_by)
values(162,999999, sysdate, 'rkhatiwala');
COMMIT ;

this does not give any error, but when i do

select * from events_main
where event_id = 162

it does not return anything.

View 20 Replies View Related

SQL & PL/SQL :: Insert Into Temporary Table

Jun 5, 2005

I migrate procedures MS SQL Server to Oracle.In MS SQL SSERVER the use of instructions INSERT with procedure results which are in storage or dynamic instructions EXECUTE in place of VALUES clause is permissible. This construction is similar to INSERT/SELECT but we have to do with EXEC instead of SELECT. The part of EXEC should include exactly one resulted collection about the equivalent types to the types of table columns. In case of the stored procedure, we can pass on proper parameters, use the form of EXEC('string') and even call up wideranging procedures or remote control procedures from different servers. Calling up remote control procedures from different server, which place data in temporary table, and later realizing join with obtainable data, we can construct diffuse joins. For example. I want insert results stored procedures sp_configure, proc_obj in temporary table.

1)INSERT #konfig
exec sp_configure.

2)
CREATE PROCEDURE proc_test
@Object_ID int,
AS
SET XACT_ABORT ON
BEGIN TRAN
CREATE TABLE #testObjects ( Object_ID int NOT NULL )
INSERT
#testObjects
EXEC
proc_obj @Object_ID,3,1
COMMIT TRAN
RETURN(0)
go

how migrate for example code to Oracle?

View 11 Replies View Related

SQL & PL/SQL :: Insert Into Table Without Using Sequence ID

Apr 9, 2012

Is it possible to insert new id without sequence id.

For e.g. :

1 , 'BOLT'
2 , 'CHAIN'

i need to insert 3rd and 4th row as

3 , 'screw'
4 , 'driver'

without using sequence (autonumber gen).

View 11 Replies View Related

SQL & PL/SQL :: Avoiding Insert Into Table

Nov 21, 2010

I've got situation where one of the procedure causing insert into table rows but some of them are not needed Temporary I need to disable this and exclude some type of data being inserted by changing procedure.

I wouldn't want to change the procedure is there a way to write a trigger that would not allow to insert a row into a table if condition is met? Something of a logic:

CREATE OR REPLACE TRIGGER avoid_insert
BEFORE (AFTER?) INSERT
if entity_id = 123 DO NOT DO THE INSERT (DO NOT DO ANYTHING)

View 6 Replies View Related

SQL & PL/SQL :: From Html To Xml And Insert To Table?

Oct 13, 2010

I basically get it how to insert selected records from html to table by converting into the xml format [URL] (Barbara and Michel know what Im talking about).

But now I cant get the hang of convert one html site where are a very usefull information for my job.

Its about the site[URL]

where I need to get informations about phone specs and insert into the table.
Informations begin with" rozmery telefónu106 x 48 x 16 mm"
and ends "podpora WCSS".

However I try to figure it out always I gonna get some ORA errors.

View 10 Replies View Related

SQL & PL/SQL :: Insert 10 Records From Table A To B?

Jul 18, 2012

I want to insert 10 records from table a to table b. If i m using statement level trigger how many record insert?In row level trigger how many record inserted?

View 2 Replies View Related

SQL & PL/SQL :: Unable To Insert Into Another Table?

Mar 25, 2011

i am facing a small problem (!!) while inserting into a table by selecting from another table.

let me simulate.

while execute the select query, i am able to view the records. but when the data inserted into the new table, empty column value is getting inserted. this problem with only one column.

Table 1 : data to be selected.
CREATE TABLE DRI_TEST2
(
FM_COMPANY VARCHAR2(4 BYTE)
)

[Code]....

if we select the select clause we will be able to see the records. but while inserting empty values are getting inserted.

View 15 Replies View Related

SQL & PL/SQL :: Insert Into Partitioned Table

Mar 14, 2013

I have a table like this:

CREATE TABLE CARGA_P_RECARGA_NEW1
(
TELE_NUM VARCHAR2(10) NOT NULL,
FECHA DATE NOT NULL,

[Code].....

Then I tried to insert some rows in that table, every insert statement is like this:

INSERT INTO CARGA_P_RECARGA_NEW1
VALUES
('3134769595','20/01/2013 07:22:50','1107','CONFB_20121121_20121122175002 60000000000000000090.TXT',0,16,'8327--7991284',1);

Every insert I executed had the month 01 because I expected to query results only from partittion p_0113 but nevermind how query I execute, the result is always the same. I mean if I excute this statement:

SELECT * FROM CARGA_P_RECARGA_NEW1 P_0113;

I get the same result when I execute any other like this:

SELECT * FROM CARGA_P_RECARGA_NEW1 P_0213

What is the error here ?

View 4 Replies View Related

PL/SQL :: How To Insert Into Table Without Specifying The Value Of Attribute

Jul 19, 2012

I have a table with an attribute set to auto increment

how can I insert into the table without specifying the value of that attribute?

View 16 Replies View Related

PL/SQL :: Insert Dataset Into Some Table?

Jul 9, 2013

Can we use result of with query_name ... to insert dataset into some table? I'm usung 11.2.0.3 

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved