OraFAQ Forum: SQL & PL/SQL » Using an Index

Home » SQL & PL/SQL » SQL & PL/SQL » Using an Index

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Using an Index [message #275199]

Thu, 18 October 2007 14:58

egray
Messages: 9
Registered: October 2007
Location: Kenner, LA

Junior Member

Hi all,

I am trying to figure out how to get a unique ID number for a records I would like to Insert/Update on a table. I think I am missing a step in all the articles I have read, so I am turning here for help. My background is in MS SQL, and Oracle PL/SQL is new to me.

I have found the following index below and I need help figuring out how to gather the unique id and complete the insert/update statements.

CREATE UNIQUE INDEX "GN"."SHIPPING_IDX" ON "GN"."SHIPPING" ("PID", "SID", "ENTRYID")
PCTFREE 1 INITRANS 32 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
STORAGE(INITIAL 655360 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "GREEN_INDEX" ;

SQL> INSERT INTO GN.SHIPPING (PID,SID,ENTRYID,SVALUE) VALUES (:hvPID,:hvSID,:hvENTRYID,:hvSVALUE)

SQL> UPDATE GN.SHIPPING SET ENTRYID=:hvENTRYID,SVALUE=:hvSVALUE WHERE PID=:hvPIDw AND SID=:hvSIDw AND ENTRYID=:hvENTRYIDw

Any help or direction is appreciated. Thanks!

Report message to a moderator

Re: Using an Index [message #275202 is a reply to message #275199]

Thu, 18 October 2007 15:16

Bill B
Messages: 1971
Registered: December 2004

Senior Member

What exactly are you trying to do with the insert? If you are trying to update a specific row and there is a unique index on PID, SID, and ENTRYID then using the three columns in the where clause on the update will only update one row. There is one exception. If all three columns contain null, then you could have 50 rows inserted and an update of the form

where PID IS NULL AND SID IS NULL AND ENTRYID IS NULL would update all 50 rows.

[Updated on: Thu, 18 October 2007 15:19]

Report message to a moderator

Re: Using an Index [message #275205 is a reply to message #275199]

Thu, 18 October 2007 15:35

egray
Messages: 9
Registered: October 2007
Location: Kenner, LA

Junior Member

On an insert, I have the PID, SID, and SVALUE, and only need to generate an ENTRYID value.

On an update, the ENTRYID and SVALUE will be updated, and I only need to generate a new ENTRYID value.

I see on the Create Table statement that PID, SID, and ENTRYID are all NUMBER(38,0) NOT NULL ENABLE.

I'm I looking in the right place in the database for the information we need to do an insert or update?

Report message to a moderator

Re: Using an Index [message #275218 is a reply to message #275199]

Thu, 18 October 2007 17:27

egray
Messages: 9
Registered: October 2007
Location: Kenner, LA

Junior Member

When the existing application inserts or updates a record, the ENTRYID appears to be incremented by 50000 to create a unique ID for the entry. Currently the ENTRYID is 1508342236700880. Am I looking in the right place? I checked the Triggers and did not see anything suspicious.

If I can find where the ENTRYID is stored and how it is incremented, keep the data consistent between my inserted/updated records and the existing inserted/updated records.

Report message to a moderator

Re: Using an Index [message #275290 is a reply to message #275199]

Fri, 19 October 2007 04:19

scorpio_biker
Messages: 154
Registered: November 2005
Location: Kent, England

Senior Member

Is it a sequence what you are looking for? This gives a quick overview sequences

If it might be, try a select from ALL_SEQUENCES, see if there is one in there that matches the values you are using.

Report message to a moderator

Re: Using an Index [message #275356 is a reply to message #275290]

Fri, 19 October 2007 10:56

egray
Messages: 9
Registered: October 2007
Location: Kenner, LA

Junior Member

Thank you scorpio_biker!

OK ... I found the sequence SYS.IDGen1$ that increments by 50 (it was the only sequence that incremented by 50 in all databases). Unfortunately when I tested it out, it does not increment when the current application inserts/updates a record. I read about sequence caching and I know that is not the issue.

Code: [Select all] [Show/ hide]

CREATE SEQUENCE  "SYS"."IDGEN1$"  MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 50 START WITH 8790801 CACHE 20 NOORDER  NOCYCLE;

Under the GN database, the only procedure is PROCINFO_SEQ which increments by 1.

Code: [Select all] [Show/ hide]

CREATE SEQUENCE  "GN"."PROCINFO_SEQ"  MINVALUE 1 MAXVALUE 10000000 INCREMENT BY 1 START WITH 30292 CACHE 10 NOORDER  CYCLE ;

Any direction to look in for the next clue to unlock this evil evil mystery? I think sequences were the right direction. Thanks for getting me there.

- The Village Idiot (me)

Report message to a moderator

Re: Using an Index [message #275378 is a reply to message #275356]

Fri, 19 October 2007 12:53

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

egray wrote on Fri, 19 October 2007 11:56

OK ... I found the sequence SYS.IDGen1$ that increments by 50 (it was the only sequence that incremented by 50 in all databases). Unfortunately when I tested it out, it does not increment when the current application inserts/updates a record. I read about sequence caching and I know that is not the issue.

I think you are confused about what a sequence is. A sequence is not associated with a table. It is its own object. It is used in code. YOU have to increment it with a NEXTVAL clause to use it.

Report message to a moderator

Re: Using an Index [message #275388 is a reply to message #275378]

Fri, 19 October 2007 13:38

egray
Messages: 9
Registered: October 2007
Location: Kenner, LA

Junior Member

I am using SYS.IDGEN1$.NEXTVAL in my insert/update statements and it increments for me. Unfortunately it is not the sequence I am looking for since it does not increment when the third-party application inserts/updates a record. They are using a different sequence or accessing the sequence differently.

There is only one sequence in the database that increments by 50, so I am puzzled how the third-party application is generating unique ids if they are using SYS.IDGEN1$.NEXTVAL.

Since all the data is being written to the GN database, do I need to access the IDGEN1$.NEXTVAL through the only sequence in the GN database? Something like GN.PROCINFO_SEQ(SYS.IDGEN1$.NEXTVAL)?

Hope that makes sense.

Report message to a moderator

Re: Using an Index [message #275403 is a reply to message #275388]

Fri, 19 October 2007 15:30

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

Is it 50 or 50000? You give conflicting information.

Ay Caramba! The sequence IDGEN1$ is a sequence in SYS dealing with Transportable tablespace, therefore you should under no circumstances touch it. Who knows what harm you may have caused.

Have you searched for sequences that have a CACHE of 50? A session which does a single insert would cause this too.

[Updated on: Fri, 19 October 2007 15:32]

Report message to a moderator

Re: Using an Index [message #275410 is a reply to message #275403]

Fri, 19 October 2007 16:27

egray
Messages: 9
Registered: October 2007
Location: Kenner, LA

Junior Member

Sorry for the conflicting information. It is 50. The last three digits of the ENTRYID number I posted belong to the the user's ID in the system. The third-party application is tacking that onto to the end of the number as if it were a string.

There is one sequence that has a cache of 50 (MVIEW$_ADVSEQ_GENERIC) and the sequence owner is SYSTEM.

Hopefully I did not cause any harm by running SYS.IDGEN1$.NEXTVAL and SYS.IDGEN1$.CURRVAL. Records are still being processed by the system fine for now. I will keep my fingers crossed and throw a penny in the good luck fountain on the way home.

Report message to a moderator

Previous Topic:	Pivot like this using DECODE???
Next Topic:	About Ref Cursor

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat May 03 21:42:21 CDT 2025