|
Home > Archive > Oracle Server > July 2005 > Dumb Question regarding Indexes
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Dumb Question regarding Indexes
|
|
|
| I am a bit confused about creating indexes. If I have a table with 5
columns, do I create an index for each column, or create an index for
all columns in the same index, or what??
I have noticed some applications create multiple indexes for a certain
table, each containing 1 or more columns from the table, and some
indexes contain the same columns as others...
Thx for any info.
BP
| |
| DA Morgan 2005-07-29, 11:23 am |
| Perm wrote:
> I am a bit confused about creating indexes. If I have a table with 5
> columns, do I create an index for each column, or create an index for
> all columns in the same index, or what??
>
> I have noticed some applications create multiple indexes for a certain
> table, each containing 1 or more columns from the table, and some
> indexes contain the same columns as others...
>
> Thx for any info.
> BP
<RANT>
You do no such thing.
What you do is go to http://tahiti.oracle.com
Look up Indexes and read the concept books
Indexes serve one and only one purpose in a database unless they have
been built as part of constraint creation such as a PK or UC. That is
to speed up a DML statement.
You have no Oracle version
You have no Explain Plan
You have no trace file
You have no WHERE clause
You have no basis to even discuss creating any index whatsoever
The first question that should be on the table is are there already
indexes created by a primary key or unique constraint?
You should seriously consider taking a beginning Oracle class.
</RANT>
--
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)
| |
|
|
Then I guess this newgroup is useless then Mr. Personality.
You really need to get out and have some fun, maybe you won't be quite
as unhappy.
| |
| Sybrand Bakker 2005-07-29, 1:23 pm |
| On Fri, 29 Jul 2005 10:22:10 -0700, "Andreas Sheriff"
<spamcontrol@iion.com> wrote:
><focus target="Perm">
>Look up Index Organized Tables (IOT). An IOT combines an index and a table
>for a faster means of access to data.
>Clusters are also good for tables that are typically used in a join, and
>materialized views recomputed data so access is faster still.
></focus>
I don't think it can be recommended (which you can digest from your
response) to use IOTs everywhere. Also I notice exactly no one has
ever used clusters. They are used in the dictionary and that is it.
The usual policy ought to be that all foreign key columns are indexed.
--
Sybrand Bakker, Senior Oracle DBA
| |
| Mark A 2005-07-29, 1:23 pm |
| "Sybrand Bakker" <postbus@sybrandb.demon.nl> wrote in message
> <snip>
> The usual policy ought to be that all foreign key columns are indexed.
--
> Sybrand Bakker, Senior Oracle DBA
Indexes on foreign keys may be a good thing, but saying that all foreign
keys should be indexed can cause of lot indexes to be created that are never
used.
For example, assume that a division_code has only 3 unique values in an
sales_transaction table, and the division_code is a foreign key to the
division_table to insure that a valid division_code is always used. It is
very unlikely that an single-column index on division_code would be used
when accessing the sales_transaction table, but the overhead of maintaining
the index exists for each row inserted.
The index on the division_code foreign key could be useful if the parent
division_code table values were changed or deleted, but that is would likely
be a rare situation, and not a scenario that one would want to optimize at
the expense of inserting rows into the sales_transaction table.
I often find that by eliminating these kind of unnecessary indexes on
foreign keys (usually automatically generated by modeling tools) that I can
eliminate about half the indexes in a database, and not effect query
performance at all (but greatly improving insert performance).
| |
|
| On Fri, 29 Jul 2005 09:04:48 -0700, Perm interested us by writing:
> I am a bit confused about creating indexes. If I have a table with 5
> columns, do I create an index for each column, or create an index for
> all columns in the same index, or what??
From the Oracle9i R2 Database Administrator's manual, Intro to Chapter 16
"
Indexes are optional structures associated with tables and clusters that
allow SQL statements to execute more quickly against a table. Just as the
index in this manual helps you locate information faster than if there
were no index, an Oracle index provides a faster access path to table
data. You can use indexes without rewriting any queries. Your results are
the same, but you see them more quickly.
"
Please note the 'optional structures' part.
Feel free to read the entire chapter and ask questions on areas that
confuse you. The manual is available at http://docs.oracle.com
--
Hans Forbrich
Canada-wide Oracle training and consulting
mailto: Fuzzy.GreyBeard_at_gmail.com
*** I no longer assist with top-posted newsgroup queries ***
| |
| Matthias Hoys 2005-07-29, 8:23 pm |
|
"Perm" <rhugga@yahoo.com> wrote in message
news:1122653087.932424.29980@g47g2000cwa.googlegroups.com...
>I am a bit confused about creating indexes. If I have a table with 5
> columns, do I create an index for each column, or create an index for
> all columns in the same index, or what??
>
> I have noticed some applications create multiple indexes for a certain
> table, each containing 1 or more columns from the table, and some
> indexes contain the same columns as others...
>
> Thx for any info.
> BP
>
As I would like to say : "there are no dumb questions, only dumb answers"
;-)
| |
| DA Morgan 2005-07-29, 8:23 pm |
| Andreas Sheriff wrote:
> "DA Morgan" <damorgan@psoug.org> wrote in message
> news:1122653765.484188@yasure...
>
>
>
> <flame level="medium" target="Daniel A. Morgan">
> Indexes do not speed up DML
> (http://download-west.oracle.com/doc...lsql.htm#i18503),
> generally. They speed up SELECT statements, specifically. Indexes actually
> slow down UPDATE, DELETE, and INSERT statements because the index also has
> to be updated, deleted, and inserted, as well as the data in the base table,
> though using indexes, the RDBMS can locate faster which data to update and
> delete.
> In general, indexes help locate data faster, and if the data you need
> through a select statement is already in the index, then Oracle doesn't
> bother to read the base table; It just uses the data from the index.
>
> <focus target="Perm">
> Look up Index Organized Tables (IOT). An IOT combines an index and a table
> for a faster means of access to data.
> Clusters are also good for tables that are typically used in a join, and
> materialized views recomputed data so access is faster still.
> </focus>
> </flame>
You update or delete one row out of a 5,000,000,000 row table by primary
key and tell me the index didn't help. I'll be watching for your
benchmark.
--
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)
| |
| fitzjarrell@cox.net 2005-07-30, 3:23 am |
|
Mark A wrote:
> "Sybrand Bakker" <postbus@sybrandb.demon.nl> wrote in message
> --
>
> Indexes on foreign keys may be a good thing, but saying that all foreign
> keys should be indexed can cause of lot indexes to be created that are never
> used.
>
> For example, assume that a division_code has only 3 unique values in an
> sales_transaction table, and the division_code is a foreign key to the
> division_table to insure that a valid division_code is always used. It is
> very unlikely that an single-column index on division_code would be used
> when accessing the sales_transaction table, but the overhead of maintaining
> the index exists for each row inserted.
>
And in DB2 this may not be an issue, however with Oracle unindexed
foreign keys create problems you can read about here:
http://asktom.oracle.com/~tkyte/unindex/
> The index on the division_code foreign key could be useful if the parent
> division_code table values were changed or deleted, but that is would likely
> be a rare situation, and not a scenario that one would want to optimize at
> the expense of inserting rows into the sales_transaction table.
>
And, if you read the above link you'll see the expense of NOT indexing
is greater.
> I often find that by eliminating these kind of unnecessary indexes on
> foreign keys (usually automatically generated by modeling tools) that I can
> eliminate about half the indexes in a database, and not effect query
> performance at all (but greatly improving insert performance).
I'm glad you're not working with Oracle, as you'd be replacing them for
performance reasons.
David Fitzjarrell
| |
| Richard Foote 2005-07-30, 3:23 am |
| "DA Morgan" <damorgan@psoug.org> wrote in message
news:1122686139.40952@yasure...
> Andreas Sheriff wrote:
>
> You update or delete one row out of a 5,000,000,000 row table by primary
> key and tell me the index didn't help. I'll be watching for your
> benchmark.
Hi Daniel
I think your point however that "Indexes serve one and only one purpose in a
database unless they have been built as part of constraint creation such as
a PK or UC. That is to speed up a DML statement" is either totally incorrect
or is highly misleading at best.
Indexes serve the purpose of speeding up the *retrieval* of data and
although I agree they may benefit updates and deletes, they also rather
benefit select statements as well (which is clearly not a DML statement).
Also indexes potentially hurt insert performance which clearly is a DML
statement (unless the insert has a subquery of course in which case the
index may benefit the *retrieval* of data in relation to the subquery). I
therefore totally disagree with your above point that indexes only having
the one purpose of speeding up DML.
In answer to the OP's question of when to create indexes, it therefore
depends on whether such an index would actually benefit the *retrieval* of
data and so questions such as: are the columns frequently used in WHERE
conditions, what's the cardinality of the columns, could sorting operations
be avoided, etc. etc. etc. need to answered.
In relation to whether or not have separate indexes or concatenated indexes,
it then rather depends on the makeup of the WHERE conditions and the such
and so questions such as: are the columns usually referenced together in
where conditions, are some columns likely not be referenced or be unknown,
do you frequently access only an additional column or two in addition to the
columns in where conditions, etc. etc. etc. The advantages of having
separate indexes on columns is that they can be useful for a wider range of
where predicates but the disadvantages are that they may not be as efficient
as a corresponding concatenated index as it may require more index probes or
table lookups. The advantages of concatenated indexes are that it could be
more efficient than corresponding separate indexes and could potentially
negate table lookups altogether if the index columns are sufficient for all
the referenced columns of the statement but the disadvantages are that it's
somewhat reliant on the leading column being referenced (index skip scanning
may help but it's rare and generally a poor substitute).
So there's quite a lot to it all and IMHO, it's actually quite a reasonable
question for a newbie to ask.
Also if I may say, again IMHO, every time I have a peep back here, there
appears to be too much ranting going on and perhaps not enough friendly and
helpful advice being offered :(
Just my impression of course ...
Cheers
Richard
| |
| Mark A 2005-07-30, 3:23 am |
| <fitzjarrell@cox.net> wrote in message
> And in DB2 this may not be an issue, however with Oracle unindexed
> foreign keys create problems you can read about here:
>
> http://asktom.oracle.com/~tkyte/unindex/
>
> And, if you read the above link you'll see the expense of NOT indexing
> is greater.
>
> I'm glad you're not working with Oracle, as you'd be replacing them for
> performance reasons.
>
> David Fitzjarrell
>
I work with both Oracle and DB2. The article you cited in the link above
reinforces exactly what I said about indexes on foreign keys (and when they
are not necessary).
Let's quote one point at a time from the article and then compare it to what
I said.
1. "The [issue] first is the fact that a table lock will result if you
update the parent records primary key (very very unusual) or if you delete
the parent record and the child's foreign key is not indexed."
Yes that is correct, but if the parent table is a code table, like
division_code, it is not going to be updated or deleted on the parent code
table except in very, very unusual circumstances.
2. "The second issue has to do with performance in general of a parent child
relationship. Consider that if you have an on delete cascade and have not
indexed the child table."
I don't believe that anyone would define a FK constraint to a parent code
table (like division_code) with a delete cascade rule. As already stated, it
would be extremely rare that the division_code rows would be deleted or
updated on the parent code table (maybe inserted if a new division is
added).
3. "Also consider that for most (not all, most) parent child relationships,
we query the objects from the 'master' table to the 'detail' table. The
glaring exception to this is a code table (short code to long description)."
Yes, the glaring exception is the code table, as I have explained in
excruciating detail. In my experience the indexes on FK's that relate to
code tables (the glaring exception cited above) can represent about half the
indexes in a database when the index on FK rule is strictly enforced. These
indexes are usually never used in queries, and are just overhead for inserts
and deletes (and occasionally updates) of rows on the child table.
Thank you for providing documentation for all the points I made.
| |
| Andreas Sheriff 2005-07-30, 3:23 am |
|
"DA Morgan" <damorgan@psoug.org> wrote in message
news:1122686139.40952@yasure...
> Andreas Sheriff wrote:
(http://download-west.oracle.com/doc.../b14220/sqlplsq
l. htm#i18503),[color=d
arkred]
actually[color=darkr
ed]
has[color=darkred]
table,[color=darkred
]
and[color=darkred]
table[color=darkred]
>
> You update or delete one row out of a 5,000,000,000 row table by primary
> key and tell me the index didn't help. I'll be watching for your
> benchmark.
> --
> Daniel A. Morgan
> http://www.psoug.org
> damorgan@x.washington.edu
> (replace x with u to respond)
Isn't that what I said? Or haven't you been paying attention?
RE:[color=darkred]
and[color=darkred]
--
Andreas Sheriff
Oracle 9i Certified Professional
Oracle 10g Certified Professional
Oracle 9i Certified PL/SQL Developer
----
"If you don't eat your meat, you cannot have any pudding.
"How can you have any pudding, if you don't eat your meat?"
DO NOT REPLY TO THIS EMAIL
Reply only to the group.
| |
| DA Morgan 2005-07-30, 3:23 am |
| Richard Foote wrote:
> "DA Morgan" <damorgan@psoug.org> wrote in message
> news:1122686139.40952@yasure...
>
>
>
> Hi Daniel
>
> I think your point however that "Indexes serve one and only one purpose in a
> database unless they have been built as part of constraint creation such as
> a PK or UC. That is to speed up a DML statement" is either totally incorrect
> or is highly misleading at best.
>
> Indexes serve the purpose of speeding up the *retrieval* of data and
> although I agree they may benefit updates and deletes, they also rather
> benefit select statements as well (which is clearly not a DML statement).
> Also indexes potentially hurt insert performance which clearly is a DML
> statement (unless the insert has a subquery of course in which case the
> index may benefit the *retrieval* of data in relation to the subquery). I
> therefore totally disagree with your above point that indexes only having
> the one purpose of speeding up DML.
>
> In answer to the OP's question of when to create indexes, it therefore
> depends on whether such an index would actually benefit the *retrieval* of
> data and so questions such as: are the columns frequently used in WHERE
> conditions, what's the cardinality of the columns, could sorting operations
> be avoided, etc. etc. etc. need to answered.
>
> In relation to whether or not have separate indexes or concatenated indexes,
> it then rather depends on the makeup of the WHERE conditions and the such
> and so questions such as: are the columns usually referenced together in
> where conditions, are some columns likely not be referenced or be unknown,
> do you frequently access only an additional column or two in addition to the
> columns in where conditions, etc. etc. etc. The advantages of having
> separate indexes on columns is that they can be useful for a wider range of
> where predicates but the disadvantages are that they may not be as efficient
> as a corresponding concatenated index as it may require more index probes or
> table lookups. The advantages of concatenated indexes are that it could be
> more efficient than corresponding separate indexes and could potentially
> negate table lookups altogether if the index columns are sufficient for all
> the referenced columns of the statement but the disadvantages are that it's
> somewhat reliant on the leading column being referenced (index skip scanning
> may help but it's rare and generally a poor substitute).
>
> So there's quite a lot to it all and IMHO, it's actually quite a reasonable
> question for a newbie to ask.
>
> Also if I may say, again IMHO, every time I have a peep back here, there
> appears to be too much ranting going on and perhaps not enough friendly and
> helpful advice being offered :(
>
> Just my impression of course ...
>
> Cheers
>
> Richard
Thanks for the correction. I did an appallingly bad job of saying what
you said. Of course it is about data access.
I'd like to claim it was because of jet lag having just returned from
Hawaii but the truth is I was just sloppy.
Again ... thanks.
--
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)
| |
| DA Morgan 2005-07-30, 3:23 am |
| Andreas Sheriff wrote:
> Isn't that what I said? Or haven't you been paying attention?
>
Obviously I didn't think so. But then I'll step back into the
shadow and heartily endorse Richard's correction of what I wrote.
--
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)
| |
| Matthias Hoys 2005-07-30, 7:23 am |
|
"Mark A" <nobody@nowhere.com> wrote in message
news:RaWdnY4_B7qjk3b
fRVn-hg@comcast.com...
> <fitzjarrell@cox.net> wrote in message
>
> I work with both Oracle and DB2. The article you cited in the link above
> reinforces exactly what I said about indexes on foreign keys (and when
> they are not necessary).
>
> Let's quote one point at a time from the article and then compare it to
> what I said.
>
> 1. "The [issue] first is the fact that a table lock will result if you
> update the parent records primary key (very very unusual) or if you delete
> the parent record and the child's foreign key is not indexed."
>
> Yes that is correct, but if the parent table is a code table, like
> division_code, it is not going to be updated or deleted on the parent code
> table except in very, very unusual circumstances.
>
Mmm ... from my experience, I would like to disagree. For the DBA, it's
often not clear which tables are "static" code tables and which ones are
highly dynamic. If you have hundreds of table to manage in a schema,
selectively creating indexes on FK columns can be an administrative
nightmare. Also, applications can change over time (again, often without the
DBA knowing so). I give the example of some batch that is started in the
weekend to reload the code table. Without indexes on the FK columns of the
child tables, it will take hours to complete, people will start complaining,
and everyone will blame the DBA ... It's true that indexes slow down
insert/update/delete statements - but if there are so many indexes on a
table that this leads to *significant* performance problems, then there's
something wrong with the design of your db. And for batch jobs, it's always
possible to drop the indexes or make them UNUSABLE and set
skip_unusable_indexe
s to true followed by an index rebuild in parallel.
Matthias
| |
|
|
"Perm" <rhugga@yahoo.com> wrote:
>Then I guess this newgroup is useless then Mr. Personality.
>You really need to get out and have some fun, maybe you won't be quite
>as unhappy.
As Daniel wrote
-----------------------------------------
You have no Oracle version
You have no Explain Plan
You have no trace file
You have no WHERE clause
You have no basis to even discuss creating any index whatsoever
----------------------------------------
You have a table with 5 columns. You index those column(s) where doing
so will give you an increase in performance in some areas (as
measured) without decreasing in other areas (again, as measured).
This is a subject where a simple "Yes, go ahead and index the lot"
and/or a "don't index anything" response is useless (or worse).
*_You_*, the person charged with administering the db have to make
choices based on the application(s) that are accessing the data,
frequency of same, and your own experience(s).
Your original question is like going to "alt.english.literature" and
asking "I'm thinking of writing a book, how many pages should there
be?". The answer to your question is "it depends". More info is
necessary to be able to answer it.
Insulting those who tell you the truth isn't going to win you any
friends.
Paul...
--
plinehan __at__ yahoo __dot__ __com__
XP Pro, SP 2,
Oracle, 9.2.0.1.0 (Enterprise Ed.)
Interbase 6.0.1.0;
When asking database related questions, please give other posters
some clues, like operating system, version of db being used and DDL.
The exact text and/or number of error messages is useful (!= "it didn't work!").
Thanks.
Furthermore, as a courtesy to those who spend
time analysing and attempting to help, please
do not top post.
| |
| Mark A 2005-07-30, 7:23 am |
| "Matthias Hoys" < idmwarpzone_NOSPAM_@
yahoo.com> wrote in message
news:42eb5c96$0$1204
9
> Mmm ... from my experience, I would like to disagree. For the DBA, it's
> often not clear which tables are "static" code tables and which ones are
> highly dynamic. If you have hundreds of table to manage in a schema,
> selectively creating indexes on FK columns can be an administrative
> nightmare. Also, applications can change over time (again, often without
> the DBA knowing so). I give the example of some batch that is started in
> the weekend to reload the code table. Without indexes on the FK columns of
> the child tables, it will take hours to complete, people will start
> complaining, and everyone will blame the DBA ... It's true that indexes
> slow down insert/update/delete statements - but if there are so many
> indexes on a table that this leads to *significant* performance problems,
> then there's something wrong with the design of your db. And for batch
> jobs, it's always possible to drop the indexes or make them UNUSABLE and
> set skip_unusable_indexe
s to true followed by an index rebuild in
> parallel.
>
> Matthias
>
Yes, it does take some work to sit down and figure out which FK indexes are
worthless (usually the ones which refer back to low cardinality code
tables), but their is a payoff for doing that (better performance for
inserts, and deletes (and less often dates) of the dependent table. We get
paid to make those decisions, and to come up with the optimum physical
database design.
Having too few indexes is a terrible thing for performance, but so is having
too many indexes also bad for performance.
|
|
|
|
|