Feedback wanted: Simulating @@Identitt behavior

Traditionally, implementing an alternative for the MS SQL Server
IDENTITY in Oracle is pretty straight forward:
e.g:
CREATE TABLE MyTable(
ID NUMBER(10) NOT NULL,
Description VARCHAR2(128)
)
/
CREATE SEQUENCE SeqMyTable
/
CREATE OR REPLACE TRIGGER TiMyTable BEFORE INSERT ON MyTable
FOR EACH ROW
BEGIN
SELECT NVL(:new.ID, SeqMyTable.NEXTVAL) INTO :new.ID FROM DUAL;
END;
/
So far so good.
However, in my case I am dealing with a large application which is
running 'mixed', eg, originally designed for MS SQL Sever, and now
being adapted to run under Oracle as well. As the @.@.Identity is wildly
used, I thought of an alternative for returning the last identiry
value. As the queries themselves are already parsed, replacing all
occurrences of @.@.Identity with something else is a breeze.
So, my idea is to implement a global identity value with a package.
Can anybody tell me if the following is a good or a bad plan, and if
there are any caveats?
Thanks
Code:
CREATE OR REPLACE PACKAGE SomePackageName
IS
FUNCTION GetIdentity RETURN NUMBER;
PROCEDURE SetIdentity(Identity IN NUMBER);
END SomePackageName;
/
CREATE OR REPLACE PACKAGE BODY SomePackageName
IS
Identity NUMBER(10) := NULL;
FUNCTION GetIdentity
RETURN NUMBER
IS
BEGIN
RETURN Identity;
END;
PROCEDURE SetIdentity(Identity IN NUMBER)
IS
BEGIN
SomePackageName.Identity := Identity;
END;
END SomePackageName;
/
CREATE TABLE MyTable(
ID NUMBER(10) NOT NULL,
Description VARCHAR2(128)
)
/
CREATE SEQUENCE SeqMyTable
/
CREATE OR REPLACE TRIGGER TiMyTable BEFORE INSERT ON MyTable
FOR EACH ROW
BEGIN
SELECT NVL(:new.ID, SeqMyTable.NEXTVAL) INTO :new.ID FROM DUAL;
SomePackageName.SetIdentity(:new.ID);
END;
/
Application example:
SET SERVEROUTPUT ON;
DECLARE
NewID NUMBER(10);
BEGIN
INSERT INTO MyTable(Description) VALUES('Hello world');
NewID := SomePackageName.GetIdentity;
dbms_output.put_line('New ID = ' || TO_CHAR(NewID));
END;
/
Hmmm, actually, I'd better post this in the Oracle groups. Sorry for
spamming Oracle stuff here.

Feedback wanted: Simulating @@Identitt behavior

Traditionally, implementing an alternative for the MS SQL Server
IDENTITY in Oracle is pretty straight forward:
e.g:
---
CREATE TABLE MyTable(
ID NUMBER(10) NOT NULL,
Description VARCHAR2(128)
)
/
CREATE SEQUENCE SeqMyTable
/
CREATE OR REPLACE TRIGGER TiMyTable BEFORE INSERT ON MyTable
FOR EACH ROW
BEGIN
SELECT NVL(:new.ID, SeqMyTable.NEXTVAL) INTO :new.ID FROM DUAL;
END;
/
---
So far so good.
However, in my case I am dealing with a large application which is
running 'mixed', eg, originally designed for MS SQL Sever, and now
being adapted to run under Oracle as well. As the @.@.Identity is wildly
used, I thought of an alternative for returning the last identiry
value. As the queries themselves are already parsed, replacing all
occurrences of @.@.Identity with something else is a breeze.
So, my idea is to implement a global identity value with a package.
Can anybody tell me if the following is a good or a bad plan, and if
there are any caveats?
Thanks
Code:
---
CREATE OR REPLACE PACKAGE SomePackageName
IS
FUNCTION GetIdentity RETURN NUMBER;
PROCEDURE SetIdentity(Identity IN NUMBER);
END SomePackageName;
/
CREATE OR REPLACE PACKAGE BODY SomePackageName
IS
Identity NUMBER(10) := NULL;
FUNCTION GetIdentity
RETURN NUMBER
IS
BEGIN
RETURN Identity;
END;
PROCEDURE SetIdentity(Identity IN NUMBER)
IS
BEGIN
SomePackageName.Identity := Identity;
END;
END SomePackageName;
/
CREATE TABLE MyTable(
ID NUMBER(10) NOT NULL,
Description VARCHAR2(128)
)
/
CREATE SEQUENCE SeqMyTable
/
CREATE OR REPLACE TRIGGER TiMyTable BEFORE INSERT ON MyTable
FOR EACH ROW
BEGIN
SELECT NVL(:new.ID, SeqMyTable.NEXTVAL) INTO :new.ID FROM DUAL;
SomePackageName.SetIdentity(:new.ID);
END;
/
Application example:
---
SET SERVEROUTPUT ON;
DECLARE
NewID NUMBER(10);
BEGIN
INSERT INTO MyTable(Description) VALUES('Hello world');
NewID := SomePackageName.GetIdentity;
dbms_output.put_line('New ID = ' || TO_CHAR(NewID));
END;
/Hmmm, actually, I'd better post this in the Oracle groups. Sorry for
spamming Oracle stuff here.

Feedback wanted: Simulating @@Identitt behavior

Traditionally, implementing an alternative for the MS SQL Server
IDENTITY in Oracle is pretty straight forward:
e.g:
---
CREATE TABLE MyTable(
ID NUMBER(10) NOT NULL,
Description VARCHAR2(128)
)
/
CREATE SEQUENCE SeqMyTable
/
CREATE OR REPLACE TRIGGER TiMyTable BEFORE INSERT ON MyTable
FOR EACH ROW
BEGIN
SELECT NVL(:new.ID, SeqMyTable.NEXTVAL) INTO :new.ID FROM DUAL;
END;
/
---
So far so good.
However, in my case I am dealing with a large application which is
running 'mixed', eg, originally designed for MS SQL Sever, and now
being adapted to run under Oracle as well. As the @.@.Identity is wildly
used, I thought of an alternative for returning the last identiry
value. As the queries themselves are already parsed, replacing all
occurrences of @.@.Identity with something else is a breeze.
So, my idea is to implement a global identity value with a package.
Can anybody tell me if the following is a good or a bad plan, and if
there are any caveats?
Thanks
Code:
---
CREATE OR REPLACE PACKAGE SomePackageName
IS
FUNCTION GetIdentity RETURN NUMBER;
PROCEDURE SetIdentity(Identity IN NUMBER);
END SomePackageName;
/
CREATE OR REPLACE PACKAGE BODY SomePackageName
IS
Identity NUMBER(10) := NULL;
FUNCTION GetIdentity
RETURN NUMBER
IS
BEGIN
RETURN Identity;
END;
PROCEDURE SetIdentity(Identity IN NUMBER)
IS
BEGIN
SomePackageName.Identity := Identity;
END;
END SomePackageName;
/
CREATE TABLE MyTable(
ID NUMBER(10) NOT NULL,
Description VARCHAR2(128)
)
/
CREATE SEQUENCE SeqMyTable
/
CREATE OR REPLACE TRIGGER TiMyTable BEFORE INSERT ON MyTable
FOR EACH ROW
BEGIN
SELECT NVL(:new.ID, SeqMyTable.NEXTVAL) INTO :new.ID FROM DUAL;
SomePackageName.SetIdentity(:new.ID);
END;
/
Application example:
---
SET SERVEROUTPUT ON;
DECLARE
NewID NUMBER(10);
BEGIN
INSERT INTO MyTable(Description) VALUES('Hello world');
NewID := SomePackageName.GetIdentity;
dbms_output.put_line('New ID = ' || TO_CHAR(NewID));
END;
/Hmmm, actually, I'd better post this in the Oracle groups. Sorry for
spamming Oracle stuff here. :)sql

Faster Indexes using order by statement

Hello all,
I have a specific sql statement and I am looking to return the result
set a lot faster. Currently the setup is like so:
myTable schema:
Counter decimal 9 (pk)
Machine varchar 60
LogEntry varchar 1000
Active varchar 50
SysInfo varchar 255
Idle varchar 50
IP varchar 15
KioskDate datetime
KioskTime datetime
ServerDate datetime
ServerTime datetime
Application varchar 15
WebDomain varchar 50
NSCode varchar 10
There is a Clustered index on Counter and two Non-clustered:
1) Counter & ServerDate - unique values, fill factor 90% on PRIMARY
2) Counter, Machine, NSCode, KioskDate, KioskTime - - unique values,
fill factor 90% on PRIMARY
sql statment:
select top 1000 machine, logentry,
convert(varchar(8),kioskdate,5) as Kiosk_date,
convert(varchar(8),kiosktime,8) as Kiosk_time,
convert(varchar(8),serverdate,5) as ServerDate,
convert(varchar(8),serverdate,8) as ServerTime,
application,
nscode,
webdomain
from myTable
where machine = 'machinename'
order by kioskdate desc, kiosktime desc
Currently the table holds over 18 million records and the above search
returns in under 3 seconds, however when I change the order by
statement to ServerDate desc only (which is what I need), it takes
over four minutes. I have tried altering/tweaking the indexes, but
have had no success.
Any ideas greatly appreciated.
Thanks
ScottThe only index that would be useful for that query is
Counter, Machine, NSCode, KioskDate, KioskTime
And that only for a scan as Counter is the first field.
How unique is machinename - if it's good then put an index on that.
Not sure how the optimiser would handle it but try an index
machinename, kioskdate desc, kiosktime desc
This will be in the oerder of the required resultset and the server could
take the top thousand entries for the machinename without any other
processing. Doubt if it will do that but it's worth a try.
You might be able to give it hint by finding the range of dates required and
using that.
"scott" wrote:
> Hello all,
> I have a specific sql statement and I am looking to return the result
> set a lot faster. Currently the setup is like so:
> myTable schema:
>
> Counter decimal 9 (pk)
> Machine varchar 60
> LogEntry varchar 1000
> Active varchar 50
> SysInfo varchar 255
> Idle varchar 50
> IP varchar 15
> KioskDate datetime
> KioskTime datetime
> ServerDate datetime
> ServerTime datetime
> Application varchar 15
> WebDomain varchar 50
> NSCode varchar 10
> There is a Clustered index on Counter and two Non-clustered:
> 1) Counter & ServerDate - unique values, fill factor 90% on PRIMARY
> 2) Counter, Machine, NSCode, KioskDate, KioskTime - - unique values,
> fill factor 90% on PRIMARY
>
> sql statment:
> select top 1000 machine, logentry,
> convert(varchar(8),kioskdate,5) as Kiosk_date,
> convert(varchar(8),kiosktime,8) as Kiosk_time,
> convert(varchar(8),serverdate,5) as ServerDate,
> convert(varchar(8),serverdate,8) as ServerTime,
> application,
> nscode,
> webdomain
> from myTable
> where machine = 'machinename'
> order by kioskdate desc, kiosktime desc
>
> Currently the table holds over 18 million records and the above search
> returns in under 3 seconds, however when I change the order by
> statement to ServerDate desc only (which is what I need), it takes
> over four minutes. I have tried altering/tweaking the indexes, but
> have had no success.
>
> Any ideas greatly appreciated.
> Thanks
> Scott
>|||Scott,
both your nonclustered indexes are not very useful (ever). This is
because Counter is a unique column and is already indexed.
As Nigel suggested, if "machine = 'machinename'" is highly selective
(returns just a few percent of all rows), then your query could benefit
from an index on (machinename, kioskdate, kiosktime). Specifying
ascending or descending in the index definition is not useful for your
query.
If the expression "machine = 'machinename'" returns a very high
percentage of all rows, then the query could benefit from an index on
(kioskdate, kiosktime, machinename).
You could also try the Index Tuning Wizard and see what that comes up
with.
Hope this helps,
Gert-Jan
scott wrote:
> Hello all,
> I have a specific sql statement and I am looking to return the result
> set a lot faster. Currently the setup is like so:
> myTable schema:
> Counter decimal 9 (pk)
> Machine varchar 60
> LogEntry varchar 1000
> Active varchar 50
> SysInfo varchar 255
> Idle varchar 50
> IP varchar 15
> KioskDate datetime
> KioskTime datetime
> ServerDate datetime
> ServerTime datetime
> Application varchar 15
> WebDomain varchar 50
> NSCode varchar 10
> There is a Clustered index on Counter and two Non-clustered:
> 1) Counter & ServerDate - unique values, fill factor 90% on PRIMARY
> 2) Counter, Machine, NSCode, KioskDate, KioskTime - - unique values,
> fill factor 90% on PRIMARY
> sql statment:
> select top 1000 machine, logentry,
> convert(varchar(8),kioskdate,5) as Kiosk_date,
> convert(varchar(8),kiosktime,8) as Kiosk_time,
> convert(varchar(8),serverdate,5) as ServerDate,
> convert(varchar(8),serverdate,8) as ServerTime,
> application,
> nscode,
> webdomain
> from myTable
> where machine = 'machinename'
> order by kioskdate desc, kiosktime desc
> Currently the table holds over 18 million records and the above search
> returns in under 3 seconds, however when I change the order by
> statement to ServerDate desc only (which is what I need), it takes
> over four minutes. I have tried altering/tweaking the indexes, but
> have had no success.
> Any ideas greatly appreciated.
> Thanks
> Scott
--
(Please reply only to the newsgroup)|||Hi,
many thanks for all your replies, however I'm still have no joy on
this. Whatever non-clustered indexes I create, searching by kioskdate
and time (desc) is still way faster than searchig by serverdate (desc)
which is what I want.
Using the Index Tuning Wizard gives me nothing whichever way I do it
(create new indexes, or scan with existing indexes).
Back to the drawing board methinks!
Cheers
Scott