We help IT Professionals succeed at work.

oracle - sql or plsql

Sara_j_11
Sara_j_11 asked
on
1,019 Views
Last Modified: 2012-08-13
I have a table of accounts called accts. Now for each account in that table I need to get the shipment revenue for the last five years from a table called ship. But the table called ship does not have 5 years of data. So I need to go to teh corresponding history table.

How do i do this.
acct table has acct_no
ship table has account_no, ship_YYYYMM, revenue
ship_history  table has account_no, ship_YYYYMM, revenue
when i ran the query against data for five years , I am getting the foloowing error:
ORA-12801: error signaled in parallel query server P009
ORA-01652: unable to extend temp segment by 64 in tablespace TEMP_MKV02
What does this mean?> I know that that is a lot of data...is that the problem? Also I am going to run this query just one time to get the shipment history for 5 years  for all accounts that exist in the alliance table
SELECT fedex_acct_nbr,net_rev_amt, shp_dt_YYYYMM  
FROM dom_trans d,
alliance1 ali
where d.fedex_acct_nbr = ali.expr_acct_nbr
--where rownum<100
UNION
SELECT fedex_acct_nbr, net_rev_amt, shp_dt_YYYYMM  
FROM dom_trans_hist d,
 alliance1 ali
WHERE shp_dt_YYYYMM <= to_char(ADD_MONTHS((trunc(sysdate)),-60),'YYYYMM')
and d.fedex_acct_nbr = ali.expr_acct_nbr
--and  rownum<100

1. Now going forward I want to get the "new" shipment data every month ((from the shipment table is is aggregated at the monthly level) for accounts for which history has already been populated by the one time lod of 5 year history
2. for the new accounts(that got inserted into the alliance table new and did not exist in the table previously, I want to get 5 years shipment data
3. I want to run a clean up process that removes data that is greater than 5 years of history

Please help with me these 3 points above. I am right now changing the question in this forum..
Can you please assist with this?
Comment
Watch Question

Commented:
as for 3 point, you can do the best if you create partitioned table with archive data, partitioned by date column by months. than deleting old data is much easier than deleting it from single table.
as for lack space in temp tablespace - check it whether it has unoform size of extents, if doesn't - it's "fragmented" - there is a problem that you have a lot of empty space in it, but none of them such big to satisfy your needs

Author

Commented:
----as for 3 point, you can do the best if you create partitioned table with archive data, partitioned by date column by months. than deleting old data is much easier than deleting it from single table.
I have not done partitions - can u please give an example and also some docs that give syntax. example will be very helpful

---as for lack space in temp tablespace - check it whether it has unoform size of extents, if doesn't - it's "fragmented" - there is a problem that you have a lot of empty space in it, but none of them such big to satisfy your needs
I dont understand above statement. pardon my ignorance, and please explain what is the solution for this. Ho should I approach this problem? WHat are the different types of solutions other than asking a dba to increase my table space?


Database Administrator
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
- I have not done partitions - can u please give an example and also some docs that give syntax. example will be very helpful.




Author

Commented:
please give me some examples related to this and how I should handle partions here?

Author

Commented:
I read this link https://www.experts-exchange.com/Database/Oracle/Q_10260144.html?sfQueryTermInfo=1+partit
and got a fair idea of what partion means ; but can u please tell me how to apply it here. How should I partion - what syntax to sue - how to define etc?
Mark GeerlingsDatabase Administrator
CERTIFIED EXPERT

Commented:
Here is an example of a "create table..." statement that includes partitions (in this case, by quarter, but you may want them by month or week, or some other interval) and following that, an "alter table..." statement that creates a primary key using a "locally-prefixed" index with the same number of partitions as the table.  The advantage of this is: when you drop a table partition, Oracle will drop the corresponding index partition automatically, and you do not need to worry about indexes becoming unusable, and/or needing to be rebuilt.  The disadvantage is: the index *MUST* include the "partitioning" column, but that may not be part of the natural primary key in your case.

How should you handle partitions?  That is a complex question that doesn't have a simple answer for all cases.  But partitions (when used correctly) can offer huge advantages for both query performance and data management/purging/archiving.

CREATE TABLE GL_TRANSACTIONS
(
  TRANSACTION_ID            NUMBER,
  TRANSACTION_TYPE          VARCHAR2(15 BYTE) not null,
  TRANSACTION_DATE          DATE,
  TRANSACTION_DESC          VARCHAR2(100 BYTE),
  ACCOUNT_NBR                NUMBER not null,
  TRANSACTION_AMT           NUMBER not null,
  CREATED_BY                VARCHAR2(30 BYTE) not null,
  DATE_CREATED             DATE not null,
  MODIFIED_BY               VARCHAR2(30 BYTE),
  DATE_MODIFIED             DATE,
  NEEDS_POSTING_FLAG  VARCHAR2(1 CHAR)          DEFAULT 'Y'
)
pctfree 2
partition by range (TRANSACTION_DATE)
(partition gl_trans_200504 values less than (to_date('05/01/2005','MM/DD/YYYY'))
  TABLESPACE dt4c_mlg,
partition gl_trans_200508 values less than (to_date('09/01/2005','MM/DD/YYYY'))
  TABLESPACE dt4c_mlg,
partition gl_trans_200512 values less than (to_date('01/01/2006','MM/DD/YYYY'))
  TABLESPACE dt4c_mlg,
partition gl_trans_200604 values less than (to_date('05/01/2006','MM/DD/YYYY'))
  TABLESPACE dt4c_mlg,
partition gl_trans_200608 values less than (to_date('09/01/2006','MM/DD/YYYY'))
  TABLESPACE dt4c_mlg,
partition gl_trans_200612 values less than (to_date('01/01/2007','MM/DD/YYYY'))
  TABLESPACE dt4c_mlg);

alter TABLE GL_TRANSACTIONS add constraint GL_TRANSACTIONS_PK primary key
(TRANSACTION_ID, TRANSACTION_DATE) using index tablespace ix4c_mlg local;

Author

Commented:
I noticethat the values for the partion code given above by our expert markgeer has dates hardcoded. And I want it partitioned by month - so is there any way to automate this so that I dont have to build partions every year - this table wiull probably stay for years... How do I do that experts!!??
Mark GeerlingsDatabase Administrator
CERTIFIED EXPERT

Commented:
These have to be hard-coded, at least when you create the table initially. (But that is usually a manual process anyway.)   I do use a PL\SQL procedure that I schedule to run near the end of each month that can dynamically construct "alter table..." commands for each of our partitioned tables that are partitoned by month, then use "execute immediate" to run each of these commands.  So, I don't have to manually add partitions each month.

Author

Commented:
expert markgeer,
can you please send the plsql procedure that you are talking about (for partions by month). I am not an expert like you , a real beginer - so it would really help me if you could post it ..

Author

Commented:

Will it a good idea to partition this table by month.
Desc alliance_shipments
(
FEDEX_ACCT_NBR                             NUMBER(9) not null,
NET_REV_AMT                                        NUMBER(13,2),
SHP_PCE_QTY                                        NUMBER(9),
SHP_RATE_WGT                                       NUMBER(11,2),
SHP_DT_YYYYMM                              VARCHAR2(6),
dwg_flag VARCHAR2(1)
)

For now this table has 10563000 rows. It will keep growing. The column called SHP_DT_YYYYMM is aggregated by month. I need to do the following with this table:  
1. Every month, delete the shipment data for the last 3 months and then, insert it again  using the script called   alliance_shipments_table_script_3Months.sql (that I have created)and then insert it into the alliance_shipments table. This is being done because it sometimes takes upto 3 months to complete a shipment. By doing this we will have accurate information in the tables.

2. Every , month run a sql that deletes shipment data that is older than 5 years.


--- CAN U PLEASE CORRECT THE FOLLOWING CODE TO PARTITION THE TABLE MENTIONED ABOVE:
AND SEND THE CODE FOR PL\SQL procedure that U schedule to run near the end of each month that can dynamically construct "alter table..." commands for each of our partitioned tables that are partitoned by month, then use "execute immediate" to run each of these commands.
create table alliance_shipments
(
FEDEX_ACCT_NBR                             NUMBER(9) not null,
NET_REV_AMT                                        NUMBER(13,2),
SHP_PCE_QTY                                        NUMBER(9),
SHP_RATE_WGT                                       NUMBER(11,2),
SHP_DT_YYYYMM                              VARCHAR2(6),
dwg_flag VARCHAR2(1)
)
partition by range (SHP_DT_YYYYMM)
(partition gl_trans_200701 values less than  (to_date('01/30/2005','MM/DD/YYYY'))
  TABLESPACE dt4c_mlg,);


Mark GeerlingsDatabase Administrator
CERTIFIED EXPERT

Commented:
"Will it a good idea to partition this table by month?"  I think it would be a very good idea to partition this table by month.

I am confused by this statement: "Every month, delete the shipment data for the last 3 months and then, insert it again using the script called alliance_shipments_table_script_3Months.sql (that I have created)and then insert it into the alliance_shipments."  So, some data from this table is being deleted, then re-inserted into the same table?  What is the advantge or business reason for doing that?

"Every month run a sql that deletes shipment data that is older than 5 years."
That is very easy (and very fast) if the data is partitioned by month.  You simply need to find the partition(s) with data older than five years old, then for each of those partitions do:
alter table alliance_shipments drop partition [partition_name];

"PLEASE CORRECT THE FOLLOWING CODE TO PARTITION THE TABLE MENTIONED ABOVE."
I am concerned about the datatype of the partitioning column: VARCHAR2(6).  It is named: SHP_DT_YYYYMM, so does than mean the data is actually stored in "YYYYMM" format, liike: "200701" for January, 2007 or "200708" for August, 2007, etc.?

If that is true, then you can partition it like this:
(Notes
1. This example supports the first two months of 2007 only, you would need to add similar lines for each other month that the table needs to hold data for.
2. You must use a valid tablespace name for your database, not the tablespace_name from an example I had given you earlier.
3. Each partition may go into the same tablespace (as long as it is big enough) or you may choose to put some partitions in different tablespaces if you like.)

create table alliance_shipments
(
FEDEX_ACCT_NBR                             NUMBER(9) not null,
NET_REV_AMT                                        NUMBER(13,2),
SHP_PCE_QTY                                        NUMBER(9),
SHP_RATE_WGT                                       NUMBER(11,2),
SHP_DT_YYYYMM                              VARCHAR2(6),
dwg_flag VARCHAR2(1)
)
partition by range (SHP_DT_YYYYMM)
(partition alliance_ship_200701 values less than  ('200702')
  TABLESPACE user_data,
partition alliance_ship_200702 values less than  ('200703')
  TABLESPACE user_data);

Author

Commented:
Thanks expert markgeer for following up with me...!!
 1. comment from expert - ( I  am confused by this statement: "Every month, delete the shipment data for the last 3 months and then, insert it again using the script called alliance_shipments_table_script_3Months.sql (that I have created)and then insert it into the alliance_shipments."  So, some data from this table is being deleted, then re-inserted into the same table?  What is the advantge or business reason for doing that?)

Answer: reason is sometimes it takes upto 3 months to complete a shipment and so data is out of sync...


2.  These have to be hard-coded, at least when you create the table initially. (But that is usually a manual process anyway.)   I do use a PL\SQL procedure that I schedule to run near the end of each month that can dynamically construct "alter table..." commands for each of our partitioned tables that are partitoned by month, then use "execute immediate" to run each of these commands.  So, I don't have to manually add partitions each month.
can you please send the plsql procedure that you are talking about (for partions by month). I am not an expert like you , a real beginer - so it would really help me if you could post it ..

Mark GeerlingsDatabase Administrator
CERTIFIED EXPERT

Commented:
1. But how does deleting some data, then re-inserting (the same?) data make it "in sync" with anything?  There must be something missing here that you aren't telling us, like some kind of processing or updating that happens along with the re-insert.

2. The process I use each month uses a little table that I created with these columns:
Name                       Null?    Type
-------------------------- -------- ----
TABLE_OWNER                NOT NULL VARCHAR2(30)
TABLE_NAME                 NOT NULL VARCHAR2(30)
MONTHS                              NUMBER(2)

I populated that with records for the partitioned tables I have that are partitioned by month, with a value of 1,2 or 4 in the "months" column to indicate how many months of data I want in each partition.  (Some of our mid-sized tables have just four or six partitions per year, while the bigger ones have 12, or one for each month.)

Here is the procedure that I compile (in a schema with DBA privileges) and schedule to run on the 22th of each month using DBMS_JOB:

(Note that it uses utl_file, and a logical directory named: "LOG" so you will need to have a logical directory by that name, or change this to a logical directory taht you do have, or comment out or remove any lines that include "utl_file...".)

create or replace procedure add_partitions (subtract_months in number default 0) as
  start_tm   date;
  v_owner    varchar2(30);
  v_table    varchar2(32);
  v_lastprt  varchar2(30);
  v_newpart  varchar2(30);
  v_long     long;
  v_tbsp     varchar2(30);
  v_new_hi   varchar2(20);
  v_month    pls_integer;
  year_pos   pls_integer;
  newmonth   varchar2(6);
  log_file   utl_file.file_type;
  msg_base   varchar2(80);
  msg_text   varchar2(160);
  curr_mon   varchar2(6);
  v_datafnd  boolean;
  top_part   varchar2(6);
  col_type   varchar2(30);
  v_ignore   pls_integer;
  v_added    pls_integer;
  cursor c1 is select TABLE_OWNER, TABLE_NAME, max(PARTITION_NAME)
    from dba_tab_partitions
    where table_owner not in ('SYS','SYSTEM')
    group by TABLE_OWNER, TABLE_NAME
    order by table_owner, table_name;
  cursor c2 is select TABLESPACE_NAME
    from dba_tab_partitions
    where table_owner = v_owner
    and table_name = v_table
    and partition_name = v_lastprt;
  cursor c3 is select months
    from partitioned_tables
    where table_owner = v_owner
    and table_name = v_table;
  cursor c4 is select tc.data_type
    from all_tab_columns tc
    where tc.owner = v_owner
    and tc.table_name = v_table
    and tc.column_name = (select pc.column_name from dba_part_key_columns pc
      where pc.owner = v_owner
      and pc.name = v_table
      and pc.object_type = 'TABLE');
--
-- sub-procedure to set new partition name:
  procedure new_part (x_month in number) as
    sql_txt1  varchar2(100);
    sql_txt2  varchar2(120);
    top_month date;
  begin
--    utl_file.put_line(log_file,'running "new_part" for last: '||v_lastprt||', top: '||top_part||', curr: '||curr_mon);
    if top_part <= curr_mon then
      top_month := to_date(top_part||'01','YYYYMMDD');
      newmonth := to_char(add_months(top_month,x_month),'YYYYMM');
      v_newpart := substr(v_lastprt,1,year_pos -1)||newmonth;
--    check added for Gentran with mixed-case table names:
      if v_table = upper(v_table) then
        null;
      else
        v_table := '"'||v_table||'"';
      end if;
      sql_txt1 := 'alter table '||v_owner||'.'||v_table||' add partition '||v_newpart;
      if col_type = 'DATE' then
        v_new_hi := to_char(add_months(top_month,x_month +1),'DD-MON-YYYY');
        sql_txt2 := ' values less than (to_date('''||v_new_hi||''',''DD-MON-YYYY'')) tablespace '||v_tbsp;
      else
        newmonth := to_char(add_months(top_month,x_month +1),'YYYYMM');
        sql_txt2 := ' values less than ('''||newmonth||''') tablespace '||v_tbsp;
      end if;
--      utl_file.put_line(log_file,sql_txt1);
--      utl_file.put_line(log_file,sql_txt2);
      execute immediate (sql_txt1||sql_txt2);
      msg_text := v_lastprt||' '||to_char(v_month)||' '||v_newpart||' '||v_new_hi;
      v_added := v_added +1;
    else
      msg_text := v_lastprt||' already greater than current month';
    end if;
  exception
    when others then
      msg_text := substr(sqlerrm,1,80);
      utl_file.put_line(log_file,msg_text||' on: '||v_owner||'.'||v_table);
      msg_text := v_lastprt||' '||to_char(v_month)||' '||substr(v_lastprt,year_pos,6);
  end;
begin
  v_ignore := 0;
  v_added := 0;
  start_tm := add_months(sysdate, - subtract_months) ;
  curr_mon := to_char(start_tm,'YYYYMM');
  log_file := utl_file.fopen('LOG','add_part_'||to_char(start_tm,'YYYY-MM')||'.log','A');
  utl_file.put_line(log_file,'Started: '||to_char(start_tm,'HH24:MI:SS'));
  utl_file.fflush(log_file);
  open c1;
  loop
    fetch c1 into v_owner, v_table, v_lastprt;
    exit when c1%notfound;
    msg_base := rpad(v_owner,20)||rpad(v_table,31);
    msg_text := '?';
    open c2;
    fetch c2 into v_tbsp;
    close c2;
    open c3;
    fetch c3 into v_month;
    if c3%found then
      if v_month = 0 then
        v_ignore := v_ignore +1;
      else
        open c4;
        fetch c4 into col_type;
        close c4;
        year_pos := instr(v_lastprt,'_',-1) +1;
        top_part := substr(v_lastprt,year_pos,6);
        if v_month = 1 or substr(curr_mon,5,2) = '12' then
          new_part(1);
        elsif v_month > 1 then
          if top_part <= curr_mon then
            new_part(v_month);
          else
            msg_text := v_lastprt||' already greater than next month';
          end if;  
        else
          msg_text := 'Unexpected "months" value: "'||to_char(v_month);
        end if;
      end if;
    else
      msg_text := 'No control record for: '||v_owner||'.'||v_table||', so no partition added.';
    end if;
    close c3;
    utl_file.put_line(log_file,msg_base||msg_text);
  end loop;
  close c1;
  utl_file.put_line(log_file,'Done at: '||to_char(sysdate,'HH24:MI:SS'));
  utl_file.fclose(log_file);
end;
/

Mark GeerlingsDatabase Administrator
CERTIFIED EXPERT

Commented:
Oops!  i forgot to give you the name of the table I created in my previous posting, that my procedure uses each month.  You could see it in the cursor: "c3" and guess it, but here it is so you don't have to guess:
partitioned_tables

Author

Commented:
1. But how does deleting some data, then re-inserting (the same?) data make it "in sync" with anything?  There must be something missing here that you aren't telling us, like some kind of processing or updating that happens along with the re-insert.

is this a wrong approach... then? No other additional processing is involved. I simply find all the shipments that happended in the last 3 months , then delete it and then query it again(from base shipment tables) and then reinsert it.. so that my tables will have the latest and current and updated data..
Mark GeerlingsDatabase Administrator
CERTIFIED EXPERT

Commented:
"Is this a wrong approach... then?"  I would say certainly: YES, at least if it is true that: "No other additional processing is involved." This looks to me like a huge waste of CPU time and disk space (for archived redo logs-assuming your database is in archivelog mode) and it likely causes fragmentation of the table, its index(es) and of the freespace in the tablespace(s) involved.

Have you ever done a comparison of the data before it is deleted to the data that you plan to replace it with?  If so, are there any differences?  If not, don't delete and re-insert anything!  If yes, could they be applied as updates instead?

Author

Commented:
so will that be something like for all accounts who have shipped in the last 3 month , update their shipments fields, (if there has been a change). Would that be the update sql ....??
Mark GeerlingsDatabase Administrator
CERTIFIED EXPERT

Commented:
Maybe.  But I don't know for sure, because I don't know what differences there are in your data between what you delete, and what you replace it with.  I do know that deletes and inserts in Oracle are usually *MUCH* more expensive (in terms of CPU cycles, disk space, and disk I/O) than updates are, so I try to avoid deletes and inserts if it is possible to use updates instead.  Sure the programming may be easier to do deletes and inserts, but the performance is usually much better for updates.  This assumes that the updates do not add enough bytes per row to cause row-chaining, if they do, you may be better off with deletes and inserts.

If you do need to do deletes and inserts, these would work *MUCH* faster if your table is partitioned.  Then you could simply do:
alter table alliance_shipments truncate partition [partition_name];

for each month that you want to delete and re-insert.  Then do:
alter table alliance_shipments nologging;

And for each index on the table (assuming that they are "locally partitioned" to match the partitions of the table) do this:
alter index [index_name] nologging;

You only need to do these "nologging" commands once, not every time.

Then for the insert, code it with the "append" hint like this:

insert /*+APPEND */ into alliance_shipments
([columns...])
select [whatever...]
from [wherever...]

This will allow the insert to be much faster, since the normal redo logging is turned off.  This assumes though that you do not have a standby database that needs the redo logs (or that you will manually rebuild the standby after the truncate and insert) and that you can take a backup of the tablespace(s) that contain this table and its index(es) right after the insert.

Author

Commented:
Thankyou expert markgeer for all your help.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.