| Tuning a sql statement [message #20681] |
Thu, 13 June 2002 10:15  |
Joan
Messages: 36
Registered: February 2002
|
Member |
|
|
I do I start tuning a sqlstateme.
I have big table and possible query against the table.
I need to know how to determine which column to add index on since there are several possibilities of the columns to choose from..Only two column are required in the query
|
|
|
|
| Re: Tuning a sql statement [message #20683 is a reply to message #20681] |
Thu, 13 June 2002 13:03  |
Sanjay Bajracharya
Messages: 279
Registered: October 2001
Location: Florida
|
Senior Member |
|
|
Index should be based on the columns used in the where clause.
A big table, with an index, and hitting the index with the query does help a LOT. You will be avoiding FULL TABLE SCANs.
Good luck.
|
|
|
|
| Re: Tuning a sql statement [message #20687 is a reply to message #20681] |
Thu, 13 June 2002 18:30 |
Su
Messages: 154
Registered: April 2002
|
Senior Member |
|
|
An index requirement is always for quicker search in a database. For the question what columns have to be indexed, answer, whatever the columns you use in your query's WHERE clause checking against some values. But make sure that columns should have been defined NOT NULL (no nulls allowed).
For example, if I give the following SQL query to search TableA, I would index the table on ColumnB
SELECT * FROM TABLEA WHERE COLUMNB = 123;
And one more thing, when you define or add a primary key to your table, the ORACLE automatically creates an index on the primary key of that table. It is implicit and you dont have to do any thing. And about the rest, you have to determine what columns you use in the query, so that those columns would be indexed. Check it out.
Good luck :)
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
