Purging records

[minapatel]

Hi,

I am trying to purge cases using an sql cursor:-


cursor all_cases is
cursor all_cases is
select c.id00_warehouse, c.id00_case_nbr, c.id00_status_flag, c.id00_dlm
from phpick00 a, chcart00 b, idcase00 c
where a.ph00_shipto = 'Telford'
and a.ph00_soldto = 'Buncrana'
and b.ch00_pkt_ctl_nbr = a.ph00_pkt_ctl_nbr
and b.ch00_ref_case_nbr = c.id00_case_nbr
and c.id00_warehouse = 'KL'
and a.ph00_warehouse = 'KL'
and b.ch00_warehouse = 'KL'
and c.id00_status_flag >= '90'
and a.ph00_pkt_stat_flg = 'I'
and trunc(id00_dlm) >= (select trunc(last_day(add_months(sysdate,-81))+1) from dual)
and trunc(id00_dlm)<= (select trunc(last_day(add_months(sysdate,-1)))+1 from dual)
order by c.id00_dlm

union all
select c.id00_warehouse, c.id00_case_nbr, c.id00_status_flag, c.id00_dlm
from phpick00 a, chcart00 b, idcase00 c
where a.ph00_shipto = 'Kaisersl'
and a.ph00_soldto = 'Buncrana'
and b.ch00_pkt_ctl_nbr = a.ph00_pkt_ctl_nbr
and b.ch00_ref_case_nbr = c.id00_case_nbr
and c.id00_warehouse = 'TE'
and a.ph00_warehouse = 'TE'
and b.ch00_warehouse = 'TE'
and c.id00_status_flag >= '90'
and a.ph00_pkt_stat_flg = 'I'
and trunc(id00_dlm) >= (select trunc(last_day(add_months(sysdate,-81))+1) from dual)
and trunc(id00_dlm)<= (select trunc(last_day(add_months(sysdate,-1)))+1 from dual)
order by c.id00_dlm;

/* I have had it once that the users had somehow swapped cases and some of the cases shipped had
escaped my first query. That's why there is a second !! */

cursor all_cases_backup is
select cases.id00_warehouse, cases.id00_case_nbr
from
( select id00_reference_carton_nbr, id00_case_nbr, id00_warehouse,id00_dlm
from idcase00
where id00_warehouse = 'TE'
and trunc(id00_dlm) >= (select trunc(last_day(add_months(sysdate,-81))+1) from dual)
and trunc(id00_dlm)<= (select trunc(last_day(add_months(sysdate,-1)))+1 from dual)
order by id00_dlm
) cases, chcart00 b, phpick00 c
where b.ch00_case_nbr = cases.id00_reference_carton_nbr
and b.ch00_pkt_ctl_nbr = c.ph00_pkt_ctl_nbr
and c.ph00_shipto = 'Kaisersl'
and c.ph00_soldto >= 'Buncrana' /* to trick the optimizer */
and c.ph00_soldto <= 'Buncrana'
and c.ph00_pkt_stat_flg = 'I'
and trunc(id00_dlm) >= (select trunc(last_day(add_months(sysdate,-81))+1) from dual)
and trunc(id00_dlm)<= (select trunc(last_day(add_months(sysdate,-1)))+1 from dual)
order by cases.id00_dlm
union all
select cases.id00_warehouse, cases.id00_case_nbr
from
( select id00_reference_carton_nbr, id00_case_nbr, id00_warehouse,id00_dlm
from idcase00
where id00_warehouse = 'KL'
and id00_status_flag = '95'
and trunc(id00_dlm) >= (select trunc(last_day(add_months(sysdate,-81))+1) from dual)
and trunc(id00_dlm)<= (select trunc(last_day(add_months(sysdate,-1)))+1 from dual)
order by id00_dlm
) cases, chcart00 b, phpick00 c
where b.ch00_case_nbr = cases.id00_reference_carton_nbr
and b.ch00_pkt_ctl_nbr = c.ph00_pkt_ctl_nbr
and c.ph00_shipto = 'Telford'
and c.ph00_soldto >= 'Buncrana' /* to trick the optimizer */
and c.ph00_soldto <= 'Buncrana'
and c.ph00_pkt_stat_flg = 'I'
and trunc(id00_dlm) >= (select trunc(last_day(add_months(sysdate,-81))+1) from dual)
and trunc(id00_dlm)<= (select trunc(last_day(add_months(sysdate,-1)))+1 from dual)
order by cases.id00_dlm
UNION ALL
select id00_warehouse, id00_case_nbr
from
( select id00_reference_carton_nbr, id00_case_nbr, id00_warehouse,id00_dlm
from idcase00
where id00_warehouse = 'KL'
and id00_status_flag = '95'
and trunc(id00_dlm) >= (select trunc(last_day(add_months(sysdate,-81))+1) from dual)
and trunc(id00_dlm)<= (select trunc(last_day(add_months(sysdate,-1)))+1 from dual)
) cases,
chcart00 b,
phpick00 c
where b.ch00_case_nbr = id00_reference_carton_nbr
and b.ch00_pkt_ctl_nbr = ph00_pkt_ctl_nbr
and c.ph00_shipto in ('Edingen','EDINGEN','edingen')
and c.ph00_soldto >= 'Buncrana' /* to trick the optimizer */
and c.ph00_soldto <= 'Buncrana'
and trunc(id00_dlm) >= (select trunc(last_day(add_months(sysdate,-81))+1) from dual)
and trunc(id00_dlm)<= (select trunc(last_day(add_months(sysdate,-1)))+1 from dual)
order by cases.id00_dlm;


case_rec all_cases%rowtype;
case_b_rec all_cases_backup%rowtype;

counter integer;
total_recs integer;
total_back integer;

begin
dbms_output.put_line('Starting procedure FOL_PURGE_CASES');
dbms_output.put_line('Starting main loop');
counter := 1;
total_recs := 0;
for case_rec in all_cases
loop
if (case_rec.id00_status_flag < '90') then
exit; -- this should never happen
end if;

delete from idcase00
where id00_case_nbr = case_rec.id00_case_nbr
and id00_warehouse = case_rec.id00_warehouse
and id00_status_flag >= '90'; -- let's play extra safe !

total_recs := total_recs + 1;
counter := counter + 1;
if counter > 1000 then
commit;
counter := 1;
end if;
end loop;
commit;

counter := 1;
total_back := 0;
for case_b_rec in all_cases_backup
loop
delete from idcase00
where id00_case_nbr = case_b_rec.id00_case_nbr
and id00_warehouse = case_b_rec.id00_warehouse
and id00_status_flag >= '90'; -- let's play extra safe !

total_back := total_back + 1;
counter := counter + 1;
if counter > 1000 then
commit;
counter := 1;
end if;
end loop;
commit;

dbms_output.put_line('Normal end of procedure FOL_PURGE_CASES');
dbms_output.put_line(to_char(total_recs)||' records purged !!');
dbms_output.put_line(to_char(total_back)||' records purged ... WITH BACKUP QUERY !');

/* This has actually nothing to do with purging cases but I have to put it somewhere !
It solves a small error in the pkms programs. */

update illocn00
set il00_com_div = ' '
where il00_com_div > ' '
and il00_sku = ' '
and il00_actl_inventory_cases = 0
and il00_directed_putaway_cases = 0;

commit;

/* You shall probably not believe it but here comes another update to solve a problem in
the pkms programs. */

update illocn00
set il00_max_inventory_units = 0,
il00_remn_units_capacity = 0
where il00_max_inventory_units > 0;

commit;

dbms_output.put_line('Locations cleaned too !');


EXCEPTION
WHEN OTHERS THEN -- handles all other errors
dbms_output.put_line('EXCEPTION occurred !!');
dbms_output.put_line(SQLERRM);
ROLLBACK;
end;
/

I get an error message
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.0 - Production
Starting procedure FOL_PURGE_CASES
Cleaning locations
Locations cleaned!
-----******-----
No putaway locations bug workaround!
No putaway locations bug workaround - cleaned!
Starting main loop
all_cases cusor complete
EXCEPTION occurred !!
ORA-01555: snapshot too old: rollback segment number 24 with name "ROLL24" too small

PL/SQL procedure successfully completed.

I would like to change the query for it to be more robust and use maybe another cursor to choose data inbetween a certain date period,
The rollback segment has been increases but still get error? can anyone help?

[pragatiswain]

Before starting the procedure check for the biggest available rollback segment.
Select * from DBA_ROLLBACK_SEGS where STATUS = 'AVAILABLE';

If the AVAILABLE biggest rollback segment is RBS_XX, use the following statement and in the same session, start executing the procedure.
SET TRANSACTION USE ROLLBACK SEGMENT RBS_XX;

If the rollback segment is corrupted, use the following statements to recreate it again.
Before that check for availability by using "Select * from DBA_ROLLBACK_SEGS where STATUS = 'AVAILABLE';"

ALTER ROLLBACK SEGMENT RBS_XX OFFLINE;
DROP ROLLBACK SEGMENT RBS_XX;
CREATE ROLLBACK SEGMENT RBS_XX TABLESPACE RBS_REPORT;
ALTER ROLLBACK SEGMENT RBS_XX ONLINE;

Otherwise,
You can try doing a partial commit (May be after processing every 1000 rows).

[pragatiswain]

I REALLY MEANT TO COMMIT AFTER 100 ROWS INSTEAD OF 1000. (A TYPO ERROR).
I FEEL, THE PROCEDURE IS A MESS. YOU SHOULD REALLY CLEAN IT. SEE THE FOLLOWING:

cursor all_cases is
select c.id00_warehouse, c.id00_case_nbr, c.id00_status_flag, c.id00_dlm
from phpick00 a, chcart00 b, idcase00 c
where a.ph00_shipto = 'Telford'
and a.ph00_soldto = 'Buncrana'
and b.ch00_pkt_ctl_nbr = a.ph00_pkt_ctl_nbr
and b.ch00_ref_case_nbr = c.id00_case_nbr
--and c.id00_warehouse = 'KL'
--and a.ph00_warehouse = 'KL'
AND A.PH00_WAREHOUSE = C.ID00_WAREHOUSE
AND A.PH00_WAREHOUSE = B.CH00_WAREHOUSE
and c.ch00_warehouse = 'KL'
and c.id00_status_flag >= '90'
-- what kinda datatype/fieldtype is c.id00_status_flag????
and a.ph00_pkt_stat_flg = 'I'
--and trunc(id00_dlm) >= (select trunc(last_day(add_months(sysdate,-81))+1) from dual)
--and trunc(id00_dlm)<= (select trunc(last_day(add_months(sysdate,-1)))+1 from dual)
-- THIS MAKES THE SEARCH FASTER.
AND TRUNC(ID00_DLM) BETWEEN (SELECT TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE,-81))+1) FROM DUAL)
AND (SELECT TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE,-1)))+1 FROM DUAL)
order by c.id00_dlm
-- SIMILARLY TRY THESE TO ALL THE FOLLOWING QUERIES
union all
select c.id00_warehouse, c.id00_case_nbr, c.id00_status_flag, c.id00_dlm
from phpick00 a, chcart00 b, idcase00 c
where a.ph00_shipto = 'Kaisersl'
and a.ph00_soldto = 'Buncrana'
and b.ch00_pkt_ctl_nbr = a.ph00_pkt_ctl_nbr
and b.ch00_ref_case_nbr = c.id00_case_nbr
and c.id00_warehouse = 'TE'
and a.ph00_warehouse = 'TE'
and b.ch00_warehouse = 'TE'
and c.id00_status_flag >= '90'
and a.ph00_pkt_stat_flg = 'I'
and trunc(id00_dlm) >= (select trunc(last_day(add_months(sysdate,-81))+1) from dual)
and trunc(id00_dlm)<= (select trunc(last_day(add_months(sysdate,-1)))+1 from dual)
order by c.id00_dlm;

/* I have had it once that the users had somehow swapped cases and some of the cases shipped had
escaped my first query. That's why there is a second !! */

cursor all_cases_backup is
select cases.id00_warehouse, cases.id00_case_nbr
from
( select id00_reference_carton_nbr, id00_case_nbr, id00_warehouse,id00_dlm
from idcase00
where id00_warehouse = 'TE'
and trunc(id00_dlm) >= (select trunc(last_day(add_months(sysdate,-81))+1) from dual)
and trunc(id00_dlm)<= (select trunc(last_day(add_months(sysdate,-1)))+1 from dual)
order by id00_dlm
) cases, chcart00 b, phpick00 c
where b.ch00_case_nbr = cases.id00_reference_carton_nbr
and b.ch00_pkt_ctl_nbr = c.ph00_pkt_ctl_nbr
and c.ph00_shipto = 'Kaisersl'
and c.ph00_soldto >= 'Buncrana' /* to trick the optimizer */
and c.ph00_soldto <= 'Buncrana'
and c.ph00_pkt_stat_flg = 'I'
and trunc(id00_dlm) >= (select trunc(last_day(add_months(sysdate,-81))+1) from dual)
and trunc(id00_dlm)<= (select trunc(last_day(add_months(sysdate,-1)))+1 from dual)
order by cases.id00_dlm
union all
select cases.id00_warehouse, cases.id00_case_nbr
from
( select id00_reference_carton_nbr, id00_case_nbr, id00_warehouse,id00_dlm
from idcase00
where id00_warehouse = 'KL'
and id00_status_flag = '95'
and trunc(id00_dlm) >= (select trunc(last_day(add_months(sysdate,-81))+1) from dual)
and trunc(id00_dlm)<= (select trunc(last_day(add_months(sysdate,-1)))+1 from dual)
order by id00_dlm
) cases, chcart00 b, phpick00 c
where b.ch00_case_nbr = cases.id00_reference_carton_nbr
and b.ch00_pkt_ctl_nbr = c.ph00_pkt_ctl_nbr
and c.ph00_shipto = 'Telford'
and c.ph00_soldto >= 'Buncrana' /* to trick the optimizer */
and c.ph00_soldto <= 'Buncrana'
and c.ph00_pkt_stat_flg = 'I'
and trunc(id00_dlm) >= (select trunc(last_day(add_months(sysdate,-81))+1) from dual)
and trunc(id00_dlm)<= (select trunc(last_day(add_months(sysdate,-1)))+1 from dual)
order by cases.id00_dlm
UNION ALL
select id00_warehouse, id00_case_nbr
from
( select id00_reference_carton_nbr, id00_case_nbr, id00_warehouse,id00_dlm
from idcase00
where id00_warehouse = 'KL'
and id00_status_flag = '95'
and trunc(id00_dlm) >= (select trunc(last_day(add_months(sysdate,-81))+1) from dual)
and trunc(id00_dlm)<= (select trunc(last_day(add_months(sysdate,-1)))+1 from dual)
) cases,
chcart00 b,
phpick00 c
where b.ch00_case_nbr = id00_reference_carton_nbr
and b.ch00_pkt_ctl_nbr = ph00_pkt_ctl_nbr
--and c.ph00_shipto in ('Edingen','EDINGEN','edingen')
-- THIS FASTER
AND UPPER(C.PH00_SHIPTO) = 'EDINGEN'
-- I REALLY DON'T UNDERSTAND THE FOLLOWING TWO CONDITIONS
and c.ph00_soldto >= 'Buncrana' /* to trick the optimizer */
and c.ph00_soldto <= 'Buncrana'
and trunc(id00_dlm) >= (select trunc(last_day(add_months(sysdate,-81))+1) from dual)
and trunc(id00_dlm)<= (select trunc(last_day(add_months(sysdate,-1)))+1 from dual)
order by cases.id00_dlm;

case_rec all_cases%rowtype;
case_b_rec all_cases_backup%rowtype;

counter integer;
total_recs integer;
total_back integer;

begin
dbms_output.put_line('Starting procedure FOL_PURGE_CASES');
dbms_output.put_line('Starting main loop');
counter := 1;
total_recs := 0;
for case_rec in all_cases
loop
if (case_rec.id00_status_flag < '90') then
exit; -- this should never happen
end if;

delete from idcase00
where id00_case_nbr = case_rec.id00_case_nbr
and id00_warehouse = case_rec.id00_warehouse
and id00_status_flag >= '90'; -- let's play extra safe !

total_recs := total_recs + 1;
counter := counter + 1;
if counter > 1000 then
commit;
counter := 1;
end if;
end loop;
commit;

counter := 1;
total_back := 0;
for case_b_rec in all_cases_backup
loop
delete from idcase00
where id00_case_nbr = case_b_rec.id00_case_nbr
and id00_warehouse = case_b_rec.id00_warehouse
and id00_status_flag >= '90'; -- let's play extra safe !

total_back := total_back + 1;
counter := counter + 1;
if counter > 1000 then
commit;
counter := 1;
end if;
end loop;
commit;

dbms_output.put_line('Normal end of procedure FOL_PURGE_CASES');
dbms_output.put_line(to_char(total_recs)||' records purged !!');
dbms_output.put_line(to_char(total_back)||' records purged ... WITH BACKUP QUERY !');

/* This has actually nothing to do with purging cases but I have to put it somewhere !
It solves a small error in the pkms programs. */

update illocn00
set il00_com_div = ' '
where il00_com_div > ' '
and il00_sku = ' '
and il00_actl_inventory_cases = 0
and il00_directed_putaway_cases = 0;

commit;

/* You shall probably not believe it but here comes another update to solve a problem in the pkms programs. */

update illocn00
set il00_max_inventory_units = 0,
il00_remn_units_capacity = 0
where il00_max_inventory_units > 0;

commit;

dbms_output.put_line('Locations cleaned too !');

EXCEPTION
WHEN OTHERS THEN -- handles all other errors
dbms_output.put_line('EXCEPTION occurred !!');
dbms_output.put_line(SQLERRM);
ROLLBACK;
end;
/

[minapatel]

Thanks for the information I checked the rolback segment and no rows were return but I have asked the DBA to set one of the rolback segments to the max and put the rest offline and this seems to work.

The id00_status_flag is a CHAR datatype.

The date part of the select is running uch slower so I have exchanged it with
and id00_dlm between '16-SEP-2006' and '16-NOV-2006' becuase I have so many records. Although even for this date I still have more records.

this is what I have changed it to:-

CREATE OR REPLACE procedure FOL_BACKUP_PURGE_CASES
as

v_date NUMBER :=25;

cursor all_cases is
select id00_warehouse, id00_case_nbr, id00_status_flag
from phpick00, chcart00, idcase00
where ph00_shipto = 'Telford'
and ph00_soldto = 'Buncrana'
and ch00_pkt_ctl_nbr = ph00_pkt_ctl_nbr
and ch00_ref_case_nbr = id00_case_nbr
and id00_warehouse = 'KL'
and ph00_warehouse = 'KL'
and ch00_warehouse = 'KL'
and id00_status_flag >= '90'
and ph00_pkt_stat_flg = 'I'
union all
select id00_warehouse, id00_case_nbr, id00_status_flag
from phpick00, chcart00, idcase00
where ph00_shipto = 'Kaisersl'
and ph00_soldto = 'Buncrana'
and ch00_pkt_ctl_nbr = ph00_pkt_ctl_nbr
and ch00_ref_case_nbr = id00_case_nbr
and id00_warehouse = 'TE'
and ph00_warehouse = 'TE'
and ch00_warehouse = 'TE'
and id00_status_flag >= '90'
and ph00_pkt_stat_flg = 'I';

cursor all_cases_backup IS
select cases.id00_warehouse, cases.id00_case_nbr
from
( select id00_reference_carton_nbr, id00_case_nbr, id00_warehouse,id00_dlm
from idcase00
where id00_warehouse = 'TE'
and id00_status_flag = ’95’
and id00_dlm between '20-OCT-2006' and '21-OCT-2006'

) cases, chcart00 b, phpick00 c
where c.ph00_shipto = 'Kaisersl'
and c.ph00_soldto = 'Buncrana'
and c.ph00_pkt_stat_flg = 'I'
and b.ch00_pkt_ctl_nbr = c.ph00_pkt_ctl_nbr
and b.ch00_case_nbr = cases.id00_reference_carton_nbr

UNION ALL
select cases.id00_warehouse, cases.id00_case_nbr
from
( select id00_reference_carton_nbr, id00_case_nbr, id00_warehouse,id00_dlm
from idcase00
where id00_warehouse = 'KL'
and id00_status_flag = ’95’
and id00_dlm between '20-OCT-2006' and '21-OCT-2006'
) cases, chcart00 b, phpick00 c
where c.ph00_shipto = 'Kaisersl'
and c.ph00_soldto = 'Buncrana'
and b.ch00_case_nbr = cases.id00_reference_carton_nbr
and b.ch00_pkt_ctl_nbr = c.ph00_pkt_ctl_nbr
and c.ph00_pkt_stat_flg = 'I';

UNION ALL
select cases.id00_warehouse, cases.id00_case_nbr
from
( select id00_reference_carton_nbr, id00_case_nbr, id00_warehouse,id00_dlm
from idcase00
where id00_warehouse = 'KL'
and id00_status_flag = '95'
and id00_dlm between '20-OCT-2006' and '21-OCT-2006'
) cases, chcart00 b, phpick00 c
where c.ph00_shipto = 'Telford'
and c.ph00_soldto = 'Buncrana'
and b.ch00_case_nbr = cases.id00_reference_carton_nbr
and b.ch00_pkt_ctl_nbr = c.ph00_pkt_ctl_nbr
and c.ph00_pkt_stat_flg = 'I';


Still running for one day I think we need some more indexes but its doing my head in....

[pragatiswain]

Running for a day???

HOW LONG IT TAKES TO FETCH DATA FOR THE SELECT QUERY OF THE CURSORS ALL_CASES AND ALL_CASES_BACKUP?
WHAT IS THE APPROXIMATE RECORD CONTENT OF EACH TABLE MENTIONED IN THE SELECT OF ABOVE CURSOR?
WHAT ARE THE INDEXES ON THE TABLES MENTIONED ABOVE?
DOES ID00_STATUS_FLAG CONTAINS ONLY NUMERIC VALUE IN CHAR FORMAT?

[minapatel]

Running for a day???

HOW LONG IT TAKES TO FETCH DATA FOR THE SELECT QUERY OF THE CURSORS ALL_CASES AND ALL_CASES_BACKUP?
Both queries take time


WHAT IS THE APPROXIMATE RECORD CONTENT OF EACH TABLE MENTIONED IN THE SELECT OF ABOVE CURSOR? The record content is about 150,000 records

WHAT ARE THE INDEXES ON THE TABLES MENTIONED ABOVE?
DOES ID00_STATUS_FLAG CONTAINS ONLY NUMERIC VALUE IN CHAR FORMAT?

The indexes are CREATE INDEX CHCART82 ON CHCART00
(CH00_MANIFEST_NBR, CH00_STATUS_FLAG, CH00_PKT_CTL_NBR)
NOLOGGING
TABLESPACE PKMSINDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 5M
NEXT 5M
MINEXTENTS 1
MAXEXTENTS 200
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
NOPARALLEL;


CREATE INDEX CHCART_IND_1 ON CHCART00
(CH00_PKT_CTL_NBR, CH00_CASE_NBR)
NOLOGGING
TABLESPACE PKMSINDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 5M
NEXT 5M
MINEXTENTS 1
MAXEXTENTS 200
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
NOPARALLEL;


CREATE INDEX CHCART_IND_2 ON CHCART00
(CH00_BILL_OF_LADING, CH00_CASE_NBR)
NOLOGGING
TABLESPACE PKMSINDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 5M
NEXT 5M
MINEXTENTS 1
MAXEXTENTS 200
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
NOPARALLEL;


CREATE INDEX CHCART_IND_3 ON CHCART00
(CH00_PALLET_ID, CH00_CASE_NBR)
NOLOGGING
TABLESPACE PKMSINDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 5M
NEXT 5M
MINEXTENTS 1
MAXEXTENTS 200
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
NOPARALLEL;


CREATE INDEX CHCART_IND_4 ON CHCART00
(CH00_WAREHOUSE, CH00_LOCATION, CH00_CASE_NBR)
NOLOGGING
TABLESPACE PKMSINDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 5M
NEXT 5M
MINEXTENTS 1
MAXEXTENTS 200
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
NOPARALLEL;


CREATE INDEX CHCART_IND_5 ON CHCART00
(CH00_WAREHOUSE, CH00_PRINT_WAVE, CH00_WAVE_SEQ_NBR)
NOLOGGING
TABLESPACE PKMSINDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 5M
NEXT 5M
MINEXTENTS 1
MAXEXTENTS 200
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
NOPARALLEL;


CREATE INDEX CHCART_IND_6 ON CHCART00
(CH00_PKT_CTL_NBR, CH00_STATUS_FLAG, CH00_CASE_NBR)
NOLOGGING
TABLESPACE PKMSINDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 5M
NEXT 5M
MINEXTENTS 1
MAXEXTENTS 200
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
NOPARALLEL;


CREATE INDEX CHCART_IND_9 ON CHCART00
(CH00_MANIFEST_NBR, CH00_CASE_NBR)
NOLOGGING
TABLESPACE PKMSINDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 5M
NEXT 5M
MINEXTENTS 1
MAXEXTENTS 200
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
NOPARALLEL;


CREATE INDEX FOL_CHCART00_1 ON CHCART00
(CH00_SPL_INSTR_CODE_01, CH00_SPL_INSTR_CODE_02)
LOGGING
TABLESPACE PKMSINDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 2M
NEXT 2M
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
NOPARALLEL;


CREATE INDEX FOTL_CHCART01 ON CHCART00
(CH00_PKT_CTL_NBR)
LOGGING
TABLESPACE PKMSINDX
PCTFREE 0
INITRANS 5
MAXTRANS 10
STORAGE (
INITIAL 10M
NEXT 5M
MINEXTENTS 1
MAXEXTENTS 200
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
NOPARALLEL;


CREATE UNIQUE INDEX PK_CHCART00 ON CHCART00
(CH00_CASE_NBR)
LOGGING
TABLESPACE PKMSINDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 15M
NEXT 5M
MINEXTENTS 1
MAXEXTENTS 200
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
BUFFER_POOL DEFAULT
)
NOPARALLEL;

ALTER TABLE CHCART00 ADD (
CONSTRAINT PK_CHCART00 PRIMARY KEY (CH00_CASE_NBR)
USING INDEX
TABLESPACE PKMSINDX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 15M
NEXT 5M
MINEXTENTS 1
MAXEXTENTS 200
PCTINCREASE 0
FREELISTS 1
FREELIST GROUPS 1
));

[minapatel]

Running for a day???

HOW LONG IT TAKES TO FETCH DATA FOR THE SELECT QUERY OF THE CURSORS ALL_CASES AND ALL_CASES_BACKUP?
WHAT IS THE APPROXIMATE RECORD CONTENT OF EACH TABLE MENTIONED IN THE SELECT OF ABOVE CURSOR?
WHAT ARE THE INDEXES ON THE TABLES MENTIONED ABOVE?
DOES ID00_STATUS_FLAG CONTAINS ONLY NUMERIC VALUE IN CHAR FORMAT?

Sorry, the first query is very quick and returns no records.

The second query, if I query for one day then it takes forever. if I do for range of dates inbetween one year then the query returns back more than 100,000 records.

Hope this helps....

[pragatiswain]

what are the record counts in tables phpick00, chcart00, idcase00???

[minapatel]

what are the record counts in tables phpick00, chcart00, idcase00???

The record counts are:-

phpick00 -101111
chcart00 - 462098
idcase00 -2026051

[pragatiswain]

Hey add this index and Check the execution time. I am sure, this will increase the execution speed by more than 20 times.

CREATE INDEX <INDEX_NAME> ON IDCASE00 (ID00_WAREHOUSE, ID00_STATUS_FLAG, ID00_DLM) USING TABLESPACE PKMSINDX;

Let me know what happened. I barely check this site. So, if you want contact me at [e-mail]

[pragatiswain]

Hey add this index and Check the execution time. I am sure, this will increase the execution speed by more than 20 times.

CREATE INDEX <INDEX_NAME> ON IDCASE00 (ID00_WAREHOUSE, ID00_STATUS_FLAG, ID00_DLM) USING TABLESPACE PKMSINDX;

Let me know what happened. I barely check this site. So, if you want contact me at [e-mail]

Forgot to add another Index

CREATE INDEX <INDEX_NAME1> ON IDCASE00 (ID00_CASE_NBR, ID00_WAREHOUSE, ID00_STATUS_FLAG) USING TABLESPACE <TABLESPACE_NAME>;