| Indexes Problem [message #59883] |
Wed, 31 December 2003 01:15  |
ora
Messages: 47
Registered: June 2002
|
Member |
|
|
Hi,
I am running a query in which 5 tables are used and all the tables are properly analyzed and contains not more than 3 lakh records each. And the application which we are using is actually a product and each table contain a column CO_ID which we are not using. All Tables contain composite constraints starting with CO_ID column but since we are not using that column we have created another indexes starting with appropriate column like policy_id, client_id etc.
Now in my query all the tables contain proper indexes created by us as well as by system (starting with CO_ID). In explain plan of my query it is showing me index scan SYS_C0034489/SYS_C0035469 for two tables which is an index starting with CO_ID but i am not using this column anywhere in my query, rest of the tables are using indexes created by us. I am shocked to see this i have analyzed the tables again but still the result is same. I want to know that should i rely on execution plan that it will use that index bcz that tables are most heavy tables in my query or should i do something else.
Please help me out.
|
|
|
|
| Re: Indexes Problem [message #59926 is a reply to message #59883] |
Sun, 04 January 2004 09:17  |
croca
Messages: 50
Registered: January 2004
|
Member |
|
|
If you are very sure you are not going to uso column co_id, you can SET UNUSED that column. Instead of droping the column, Oracle will marked as unused and it will be no longer valid, neither you can see that column with DESC command. The space used by the data stored in that column will NOT be available until you drop the column or export/import the table, but all your applications will no longer see that column, so, i asume, your indexes will become unavailable and your explain plan will change according with this change.
Hope this helps.
|
|
|
|
| Re: Indexes Problem [message #60798 is a reply to message #59883] |
Sun, 07 March 2004 17:52 |
vadlamani
Messages: 39
Registered: November 2003
|
Member |
|
|
hi
i think u have to use the index-hint , by which u r maximising the cahnces that CBO will choose the index specified by you.
I think this should help because u dont to drop the column CO_ID.
regards
satish
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
