 | |
HowTo find out used (useful) and unused (usesless) indexes?
comp.databases.oracle.misc comp.databases.oracle.misc
 |
| | Thread Tools | Display Modes |
#31
 
| |||
| |||
|
Re: HowTo find out used (useful) and unused (usesless) indexes? -
04-19-2008
, 01:09 PM
|
But an index may become useful over time, true? Shakespeare |
column of every table so I will respectfully disagree unless you
write a very broad definition of "may."
You need to understand your data and how it is being accessed.
The extra overhead of an unused index is not value added.
My recommendation would be to use the DBMS_STATS.SET.... procedures
to see how queries will react to the expected future growth of both
tables and indexes.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
#32
| |||
| |||
|
|
But an index may become useful over time, true? Shakespeare |
column of every table so I will respectfully disagree unless you
write a very broad definition of "may."
You need to understand your data and how it is being accessed.
The extra overhead of an unused index is not value added.
My recommendation would be to use the DBMS_STATS.SET.... procedures
to see how queries will react to the expected future growth of both
tables and indexes.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
#33
| |||
| |||
|
|
But an index may become useful over time, true? Shakespeare |
column of every table so I will respectfully disagree unless you
write a very broad definition of "may."
You need to understand your data and how it is being accessed.
The extra overhead of an unused index is not value added.
My recommendation would be to use the DBMS_STATS.SET.... procedures
to see how queries will react to the expected future growth of both
tables and indexes.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
#34
| |||
| |||
|
|
Shakespeare wrote: But an index may become useful over time, true? Shakespeare On that basis alone one could justify putting an index on every column of every table so I will respectfully disagree unless you write a very broad definition of "may." You need to understand your data and how it is being accessed. The extra overhead of an unused index is not value added. My recommendation would be to use the DBMS_STATS.SET.... procedures to see how queries will react to the expected future growth of both tables and indexes. -- Daniel A. Morgan Oracle Ace Director & Instructor University of Washington damorgan@x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.org |
indexed by design, but in the first year (all records same value) this index
is not useful and won't be used. But on the first entry in the second year
it is useful to find entries of that year and so on. A script to remove or
disable unused indexes would remove/disable this index in the first year.
Shakespeare
#35
| |||
| |||
|
|
Shakespeare wrote: But an index may become useful over time, true? Shakespeare On that basis alone one could justify putting an index on every column of every table so I will respectfully disagree unless you write a very broad definition of "may." You need to understand your data and how it is being accessed. The extra overhead of an unused index is not value added. My recommendation would be to use the DBMS_STATS.SET.... procedures to see how queries will react to the expected future growth of both tables and indexes. -- Daniel A. Morgan Oracle Ace Director & Instructor University of Washington damorgan@x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.org |
indexed by design, but in the first year (all records same value) this index
is not useful and won't be used. But on the first entry in the second year
it is useful to find entries of that year and so on. A script to remove or
disable unused indexes would remove/disable this index in the first year.
Shakespeare
#36
| |||
| |||
|
|
Shakespeare wrote: But an index may become useful over time, true? Shakespeare On that basis alone one could justify putting an index on every column of every table so I will respectfully disagree unless you write a very broad definition of "may." You need to understand your data and how it is being accessed. The extra overhead of an unused index is not value added. My recommendation would be to use the DBMS_STATS.SET.... procedures to see how queries will react to the expected future growth of both tables and indexes. -- Daniel A. Morgan Oracle Ace Director & Instructor University of Washington damorgan@x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.org |
indexed by design, but in the first year (all records same value) this index
is not useful and won't be used. But on the first entry in the second year
it is useful to find entries of that year and so on. A script to remove or
disable unused indexes would remove/disable this index in the first year.
Shakespeare
#37
| |||
| |||
|
|
Shakespeare wrote: But an index may become useful over time, true? Shakespeare On that basis alone one could justify putting an index on every column of every table so I will respectfully disagree unless you write a very broad definition of "may." You need to understand your data and how it is being accessed. The extra overhead of an unused index is not value added. My recommendation would be to use the DBMS_STATS.SET.... procedures to see how queries will react to the expected future growth of both tables and indexes. -- Daniel A. Morgan Oracle Ace Director & Instructor University of Washington damorgan@x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.org |
indexed by design, but in the first year (all records same value) this index
is not useful and won't be used. But on the first entry in the second year
it is useful to find entries of that year and so on. A script to remove or
disable unused indexes would remove/disable this index in the first year.
Shakespeare
#38
| |||
| |||
|
|
"DA Morgan" <damorgan (AT) psoug (DOT) org> schreef in bericht news:1208624947.696580 (AT) bubbleator (DOT) drizzle.com... Shakespeare wrote: But an index may become useful over time, true? Shakespeare On that basis alone one could justify putting an index on every column of every table so I will respectfully disagree unless you write a very broad definition of "may." You need to understand your data and how it is being accessed. The extra overhead of an unused index is not value added. My recommendation would be to use the DBMS_STATS.SET.... procedures to see how queries will react to the expected future growth of both tables and indexes. -- Daniel A. Morgan Oracle Ace Director & Instructor University of Washington damorgan@x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.org I was aiming at data with for example a 'year' column. This column could be indexed by design, but in the first year (all records same value) this index is not useful and won't be used. But on the first entry in the second year it is useful to find entries of that year and so on. A script to remove or disable unused indexes would remove/disable this index in the first year. Shakespeare |
First year the index is worthless. Second year it is valuable for some
short amount of time and then, again, may become useless as the
percentage of records in each of the two years approaches 50%. Go for
the third year ... again possibly usable for some period of time and
then all three years probably tend toward 33.3%. By the end of year
four you are going for 25%.
I would not be all that ready to build that index unless I know, again
using DBMS_STATS.SET_TABLE_STATS and SET_INDEX_STATS how Oracle was
going to use it. That is not to say the index might not make a lot of
sense ... I just would insist on testing any assumption before
deciding to either build it or not.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
#39
| |||
| |||
|
|
"DA Morgan" <damorgan (AT) psoug (DOT) org> schreef in bericht news:1208624947.696580 (AT) bubbleator (DOT) drizzle.com... Shakespeare wrote: But an index may become useful over time, true? Shakespeare On that basis alone one could justify putting an index on every column of every table so I will respectfully disagree unless you write a very broad definition of "may." You need to understand your data and how it is being accessed. The extra overhead of an unused index is not value added. My recommendation would be to use the DBMS_STATS.SET.... procedures to see how queries will react to the expected future growth of both tables and indexes. -- Daniel A. Morgan Oracle Ace Director & Instructor University of Washington damorgan@x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.org I was aiming at data with for example a 'year' column. This column could be indexed by design, but in the first year (all records same value) this index is not useful and won't be used. But on the first entry in the second year it is useful to find entries of that year and so on. A script to remove or disable unused indexes would remove/disable this index in the first year. Shakespeare |
First year the index is worthless. Second year it is valuable for some
short amount of time and then, again, may become useless as the
percentage of records in each of the two years approaches 50%. Go for
the third year ... again possibly usable for some period of time and
then all three years probably tend toward 33.3%. By the end of year
four you are going for 25%.
I would not be all that ready to build that index unless I know, again
using DBMS_STATS.SET_TABLE_STATS and SET_INDEX_STATS how Oracle was
going to use it. That is not to say the index might not make a lot of
sense ... I just would insist on testing any assumption before
deciding to either build it or not.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
#40
| |||
| |||
|
|
"DA Morgan" <damorgan (AT) psoug (DOT) org> schreef in bericht news:1208624947.696580 (AT) bubbleator (DOT) drizzle.com... Shakespeare wrote: But an index may become useful over time, true? Shakespeare On that basis alone one could justify putting an index on every column of every table so I will respectfully disagree unless you write a very broad definition of "may." You need to understand your data and how it is being accessed. The extra overhead of an unused index is not value added. My recommendation would be to use the DBMS_STATS.SET.... procedures to see how queries will react to the expected future growth of both tables and indexes. -- Daniel A. Morgan Oracle Ace Director & Instructor University of Washington damorgan@x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.org I was aiming at data with for example a 'year' column. This column could be indexed by design, but in the first year (all records same value) this index is not useful and won't be used. But on the first entry in the second year it is useful to find entries of that year and so on. A script to remove or disable unused indexes would remove/disable this index in the first year. Shakespeare |
First year the index is worthless. Second year it is valuable for some
short amount of time and then, again, may become useless as the
percentage of records in each of the two years approaches 50%. Go for
the third year ... again possibly usable for some period of time and
then all three years probably tend toward 33.3%. By the end of year
four you are going for 25%.
I would not be all that ready to build that index unless I know, again
using DBMS_STATS.SET_TABLE_STATS and SET_INDEX_STATS how Oracle was
going to use it. That is not to say the index might not make a lot of
sense ... I just would insist on testing any assumption before
deciding to either build it or not.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.