Path: news.netfront.net!news.glorb.com!newshub.sdsu.edu!newscon04.news.prodigy.net!prodigy.net!newsdst01.news.prodigy.net!prodigy.com!postmaster.news.prodigy.com!nlpi068.nbdc.sbc.com.POSTED!0ddd666a!not-for-mail
Newsgroups: comp.databases.oracle.server
From: spambait@milmac.com (Doug Miller)
Subject: Re: Primary key question
References: <0mnsj.57441$Pv2.50537@newssvr23.news.prodigy.net> <1f827ded-9f2b-44ef-9879-dd7cc5b67542@i7g2000prf.googlegroups.com>
X-Newsreader: News Xpress 2.01
Lines: 150
Message-ID: <kGDsj.168$Mw.30@nlpi068.nbdc.sbc.com>
NNTP-Posting-Host: 12.186.80.1
X-Complaints-To: abuse@prodigy.net
X-Trace: nlpi068.nbdc.sbc.com 1202915088 ST000 12.186.80.1 (Wed, 13 Feb 2008 10:04:48 EST)
NNTP-Posting-Date: Wed, 13 Feb 2008 10:04:48 EST
Organization: AT&T http://yahoo.sbc.com
X-UserInfo1: FKPO@SVEYJUABPXXKROD]V@APZXTPO\MAPVZKB]MPXH@ETUCCNSKQFCY@TXDX_WHSVB]ZEJLSNY\^J[CUVSA_QLFC^RQHUPH[P[NRWCCMLSNPOD_ESALHUK@TDFUZHBLJ\XGKL^NXA\EVHSP[D_C^B_^JCX^W]CHBAX]POG@SSAZQ\LE[DCNMUPG_VSC@VJM
Date: Wed, 13 Feb 2008 15:04:48 GMT
Xref: news.netfront.net comp.databases.oracle.server:182405

In article <1f827ded-9f2b-44ef-9879-dd7cc5b67542@i7g2000prf.googlegroups.com>, Charles Hooper <hooperc2000@yahoo.com> wrote:
>On Feb 12, 3:30=A0pm, spamb...@milmac.com (Doug Miller) wrote:
>> Background: I have 5+ years experience in SQL on Tandem systems, but I'm an
>> Oracle newbie trying to adjust to the differences in the two implementations.
>>
>> This would work in Tandem SQL/MP:
>>
>> create table abc (a char(8), b char(8), c char(8),
>>    primary key (a, b, c descending));
>>
>> but it doesn't seem to work in SQL*Plus 10.1.0.4.2 -- do I have the syntax
>> wrong somehow, or does Oracle simply not support descending sequence in a
>> primary key column?
>
>At least on Oracle, char(8) columns are always 8 characters, padded
>with spaces if necessary.  VARCHAR2 is commonly used to avoid this
>potential issue.

Yeah, same on the Tandem -- just laziness on my part in constructing the 
example.
>
>Let's try an experiment:
>Create a basic table without a primary key:
>CREATE TABLE ABC(
>  A VARCHAR2(8),
>  B VARCHAR2(8),
>  C VARCHAR2(8));
>
>Create an index that *might* be used to assist in the enforcement of
>the primary key constraint:
>CREATE UNIQUE INDEX ABC_IND ON ABC(A,B,C DESC);
>
>Now, let's add the primary key constraint:
>ALTER TABLE ABC ADD (PRIMARY KEY (A,B,C));
>
>Then, check the indexes on the table:
>SELECT
>  INDEX_NAME,
>  INDEX_TYPE
>FROM
>  USER_INDEXES
>WHERE
>  TABLE_NAME=3D'ABC';
>
>INDEX_NAME       INDEX_TYPE
>---------------- ---------------------
>ABC_IND          FUNCTION-BASED NORMAL
>SYS_C0024279     NORMAL
>
>The ABC_IND index is a function based index (caused by the DESC
>clause), not a normal index.  Oracle created a system generated index
>to assist in the enforcement of the primary key constraint.

Right, already discovered I could do that, but it's not quite what I was 
aiming for.
>
>Test #2:
>DROP TABLE ABC;
>
>CREATE TABLE ABC(
>  A VARCHAR2(8),
>  B VARCHAR2(8),
>  C VARCHAR2(8));
>
>CREATE UNIQUE INDEX ABC_IND ON ABC(A,B,C);
>
>ALTER TABLE ABC ADD (PRIMARY KEY (A,B,C));
>
>SELECT
>  INDEX_NAME,
>  INDEX_TYPE
>FROM
>  USER_INDEXES
>WHERE
>  TABLE_NAME=3D'ABC';
>
>INDEX_NAME       INDEX_TYPE
>---------------- ----------
>ABC_IND          NORMAL
>
>Only 1 index listed this time.

Yes, but the sequence is ascending on all three columns. This specific 
application needs the third column to be in descending sequence.
>
>Test #3:
>DROP TABLE ABC;
>
>CREATE TABLE ABC(
>  A VARCHAR2(8),
>  B VARCHAR2(8),
>  C VARCHAR2(8),
>  PRIMARY KEY (A,B,C DESC));
>
>ERROR at line 5:
>ORA-00907: missing right parenthesis

Yep, same problem I ran into.
>
>Test #4:
>CREATE TABLE ABC(
>  A VARCHAR2(8),
>  B VARCHAR2(8),
>  C VARCHAR2(8),
>  PRIMARY KEY (A,B,C));
>
>SELECT
>  INDEX_NAME,
>  INDEX_TYPE
>FROM
>  USER_INDEXES
>WHERE
>  TABLE_NAME=3D'ABC';
>
>INDEX_NAME       INDEX_TYPE
>---------------- ----------
>SYS_C0024281     NORMAL

Again, while this works, it's necessary to have the third column in descending 
sequence.
>
>Test #5:
>DROP TABLE ABC;
>
>CREATE TABLE ABC(
>  A VARCHAR2(8),
>  B VARCHAR2(8),
>  C VARCHAR2(8));
>
>CREATE UNIQUE INDEX ABC_IND ON ABC(A,B,C DESC);
>
>ALTER TABLE ABC ADD (
>  PRIMARY KEY (A,B,C DESC));
>
>ERROR at line 2:
>ORA-00907: missing right parenthesis

Yep, same thing I encountered.
>
>Looks like Oracle does not like to use function based indexes to
>assist in the enforement of primary key constraints, or more likely,
>the primary key cannot include DESC to determine the sort order of the
>primary key index.
>
>Charles Hooper
>IT Manager/Oracle DBA
>K&M Machine-Fabricating, Inc.

Thanks for the detailed reply, Charles. Looks like the way to go is the one 
shown in your first example, with a unique non-primary index.