Re: Incorrect column numbers allowed

"Malcolm Dew-Jones" <yf110_at_vtn1.victoria.tc.ca> wrote in message news:408979aa_at_news.victoria.tc.ca...
> CREATE INDEX DRAW_RESULTS_I2 ON DRAW_RESULTS ( -1 );
>
> The above appears to work.
>
> I wonder which column does -1 refer to?
>
> In fact the number can be anything, including 0, or numbers higher than
> the number of columns in the table.
>
> very odd
>
> Oracle
> RELEASE
> ---------
> 902000400
>

Malcolm,

In 9.2.0.1 on WinXP Pro, this command adds a new column of type NUMBER at position zero (0) with a system generated name and creates an index on that column.

For this test case, I created the DEPT table based on the standard SCOTT schema.

Prior to performing the command, I looked inside the 'real' description of the DEPT table using the SYS.COL$ view:

SQL> exec tkyte.print_table('SELECT name, col#, type# FROM sys.col$ WHERE obj# =
 (SELECT object_id FROM dba_objects WHERE owner=''TEST_USER'' AND object_name=''DEPT'') ORDER BY col#')

NAME                          : DEPTNO
COL#                          : 1
TYPE#                         : 2
-----------------
NAME                          : DNAME
COL#                          : 2
TYPE#                         : 1
-----------------
NAME                          : LOC
COL#                          : 3
TYPE#                         : 1
-----------------

SQL> CREATE INDEX dept_idx ON dept(-1);

Index created.

When I look inside SYS.COL$, I find a new column has been created.

SQL> exec tkyte.print_table('SELECT name, col#, type# FROM sys.col$ WHERE obj# =
 (SELECT object_id FROM dba_objects WHERE owner=''TEST_USER'' AND object_name=''DEPT'') ORDER BY col#')

NAME                          : SYS_NC00004$
COL#                          : 0
TYPE#                         : 2
-----------------
NAME                          : DEPTNO
COL#                          : 1
TYPE#                         : 2
-----------------
NAME                          : DNAME
COL#                          : 2
TYPE#                         : 1
-----------------
NAME                          : LOC
COL#                          : 3
TYPE#                         : 1
-----------------

And this is recorded in the Data Dictionary view USER/ALL/DBA_IND_COLUMNS

SQL> exec tkyte.print_table('SELECT * FROM user_ind_columns')

INDEX_NAME                    : DEPT_IDX
TABLE_NAME                    : DEPT
COLUMN_NAME                   : SYS_NC00004$
COLUMN_POSITION               : 1
COLUMN_LENGTH                 : 3
CHAR_LENGTH                   : 0
DESCEND                       : ASC
-----------------

Douglas Hawthorne Received on Sat Apr 24 2004 - 01:12:53 CEST