| primary keys and indexes [message #7715] |
Wed, 02 July 2003 16:15  |
Santosh George
Messages: 27
Registered: June 2002
|
Junior Member |
|
|
Two questions:
If a table has a primary key, do we need to have an additional index on the table using the same columns. If so, why is that?
When we build indexes on a table, should the columns in the index be in the same order as that of the primary key?
Thanks,
Big Tummy
|
|
|
|
| Re: primary keys and indexes [message #7716 is a reply to message #7715] |
Wed, 02 July 2003 16:50  |
 |
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
When you create a primary key, it creates the index. I can't think of a reason why you would need or want a duplicate index. Please see the example below that demonstrates that creation of a primary key results in creation of an index.
SQL> CREATE TABLE test_table
2 (test_col1 NUMBER,
3 test_col2 NUMBER,
4 test_col3 NUMBER,
5 CONSTRAINT test_table_pk PRIMARY KEY (test_col1, test_col2, test_col3))
6 /
Table created.
SQL> SELECT index_name, SUBSTR (column_name, 1, 30)
2 FROM user_ind_columns
3 WHERE table_name = 'TEST_TABLE'
4 /
INDEX_NAME SUBSTR(COLUMN_NAME,1,30)
------------------------------ ------------------------------
TEST_TABLE_PK TEST_COL1
TEST_TABLE_PK TEST_COL2
TEST_TABLE_PK TEST_COL3
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
