[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 942
  • Last Modified:

oracle - sql or plsql

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?
0
Sara_j_11
Asked:
Sara_j_11
  • 11
  • 8
1 Solution
 
konektorCommented:
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
0
 
Sara_j_11Author 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?


0
 
Mark GeerlingsDatabase AdministratorCommented:
When you run a "union" query like this, you force Oracle to write all of the matching records into temporary segments in the temp tablespace, then sort them.  One thing to try would be "union all" instead of "union" since then Oracle knows it does not have to sort the results to remove possible duplicates.  This may allow the query to succeed.

If that still causes the error, you will have to limit the query by a range of fedex_acct_nbr values.

For large volumes of data, you certainly should be using table partitions (which are only available with the Enterprise Edition of Oracle, not the cheaper Standard Edition).  Partitions can offer big performance advantages for queries and reports, and they make the process of removing older records that are no longer needed *MUCH* more efficient (like 1,000 - 1,000,000 times faster!).  One other option you have then is to compress the older, historical partitions (but keep the current, active partition uncompressed).  Oracle's data compression offers improved query performance plus smaller datafile sizes.
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

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




0
 
Sara_j_11Author Commented:
please give me some examples related to this and how I should handle partions here?
0
 
Sara_j_11Author Commented:
I read this link http://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?
0
 
Mark GeerlingsDatabase AdministratorCommented:
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;
0
 
Sara_j_11Author 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!!??
0
 
Mark GeerlingsDatabase AdministratorCommented:
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.
0
 
Sara_j_11Author 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 ..
0
 
Sara_j_11Author 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,);


0
 
Mark GeerlingsDatabase AdministratorCommented:
"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);
0
 
Sara_j_11Author 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 ..

0
 
Mark GeerlingsDatabase AdministratorCommented:
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;
/

0
 
Mark GeerlingsDatabase AdministratorCommented:
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
0
 
Sara_j_11Author 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..
0
 
Mark GeerlingsDatabase AdministratorCommented:
"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?
0
 
Sara_j_11Author 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 ....??
0
 
Mark GeerlingsDatabase AdministratorCommented:
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.
0
 
Sara_j_11Author Commented:
Thankyou expert markgeer for all your help.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 11
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now