How do I add a sequence that re-starts when a value changes? 1

[Welshbird]

I used to do this bit of analysis in MS-SQL using the following bit of code

-- drop table if its there

begin
  execute immediate 'drop table EfexorTrack';
exception
  when others then
    if sqlcode = -942 then
      null;  -- table does not exist
    else
      raise;
    end if;
end;

-- Create a new table

Create Table EfexorTrack
(
	RecID Number (10) not null,
	CompID integer null,
	PatientGid varchar(50) not null,
	FirstDate date not null,
	NextDate date null,
	TreatmentDays integer null,
	RxDays integer null,
	Fullfil dec (9,2) null,
	FRange char(1) null,
	Quantity integer null,
	Frequency integer null,
	FirstMarket varchar(50) not null,
	NextMarket varchar(50) null,
	StatusIng char(1) null
);

-- create a trigger to mimic an identity column

ALTER TABLE EfexorTrack ADD (
  CONSTRAINT dept_pk PRIMARY KEY (RecID));

CREATE SEQUENCE RecID_seq;

CREATE OR REPLACE TRIGGER Ident_bir 
BEFORE INSERT ON EfexorTrack 
FOR EACH ROW
WHEN (NEW.RecID IS NULL)
BEGIN
  SELECT RecID_seq.NEXTVAL
  INTO   :NEW.RecID
  FROM   dual;
END;
/



-- Innsert some data

insert into EfexorTrack (PatientGid, FirstDate, Quantity, Frequency, FirstMarket)
	select 
		Patient as PatientGid, 
		Rx_DATE as FirstDate, 
		DISPENSED_QTY as Quantity, 
		DOSAGE_FREQUENCY as Frequency,
		Market as FirstMarket
	from
		Edinburgh
	order by
		PatientGid,
		FirstDate;


-- now add the count by PatientGid so it increases by one
-- each time but starts counting again when it encounters
-- a new patient

Update EfexorTrack
	Set CompID = RecID;


Update EfexorTrack
	Set CompID = 
			(select count(*) from EfexorTrack Fee
			where Fee.CompID <= EfexorTrack.CompID
			and Fee.PatientGid = EfexorTrack.PatientGid);

I've already changed the syntax to allow me to use it in Oracle, but the last section has now been running for about 20 hours! (Surely that can't be right..) It used to take about 3 minutes on a SQL box...

Any help from anyone would be really appreciated.

Oh, just to clarify, the bit I'm having trouble with is this bit:

Update EfexorTrack
	Set CompID = 
			(select count(*) from EfexorTrack Fee
			where Fee.CompID <= EfexorTrack.CompID
			and Fee.PatientGid = EfexorTrack.PatientGid);

Fi.

"The question should be, is it worth trying to do, not can it be done"

 

[Welshbird]

I have been looking around on other forums, and people seem to be suggesting that I need to change the optimisation for the session when I run this query, using something like the following:

alter session
set optimizer_mode = first_rows

I'm not sure what this does, and I don't know anything about optimzation setting! Can anyone point me at any useful websites?

Thanks in advance.

Fi.

"The question should be, is it worth trying to do, not can it be done"

 

[sem]

insert into EfexorTrack (PatientGid, FirstDate, Quantity, Frequency, FirstMarket)
select
Patient as PatientGid,
Rx_DATE as FirstDate,
DISPENSED_QTY as Quantity,
DOSAGE_FREQUENCY as Frequency,
Market as FirstMarket
from
Edinburgh

order by
PatientGid,
FirstDate;

order by clause should not be used as Oracle doesn't preserve the order of records in table. Then, you may create trigger as well as add a PK after populating your table. In this case you may populate it as

insert into EfexorTrack (RecId, PatientGid, FirstDate, Quantity, Frequency, FirstMarket, CompID)
    select
        RecID_seq.NEXTVAL,
        Patient, 
        Rx_DATE, 
        DISPENSED_QTY, 
        DOSAGE_FREQUENCY,
        Market,
        row_number() over (partition by Patient order by Rx_DATE)
    from
        Edinburgh

And the last (but in fact the main): your table was not analyzed thus CBO couldn't work.

Regards, Dima

http://seminihin.narod.ru

 

[sem]

Ok, CBO stands for Cost Base Optimizer (needs your table to be analyzed) as an alternative to Rule Based Optimizer, that always uses index if it can.

As for the usefull website - go to otn.oracle.com and get there everything you need.

Regards, Dima

http://seminihin.narod.ru

 

[Welshbird]

Thanks guys - I'm trying again and will let you kno how I get on.

I really appreciate your help!

Fi.

"The question should be, is it worth trying to do, not can it be done"

 

[Welshbird]

Hurrah!

Thanks so much - this works like a dream!

Fi.

"The question should be, is it worth trying to do, not can it be done"