As I was creating a table with a primary key, I was in the Options of
the PK constraint create, I was wishing there was an opportunity to
create, name and associate with a different tablespace the PK index. The
standard of my company is to use separate indexes in different
tablespaces than the data for PK. Right now, I have to create the table,
then create the index separately. It seems a natural to have it in one
place there (or another tab).
Any takers?
–
David A. Hicken
UtahToad at gmail dot com
---------- Kiva.org - Make a Small Loan. Make a Big difference.
A motion to adjourn is always in order.
– Lazarus Long by Robert A Heinlein
Not a bad idea. If all you want to specify is index name and tablespace, we
could squeeze it in there…if you want to specify full index storage, it
sure would be crowded in the create table -> constraint tab.
Until something better comes along, an easier workaround might be to just send
the sql statement to the editor and type in the index stuff. The bold below is
what I had to type in manually to do what you’re asking.
CREATE TABLE JDORLON . test_tbl
(
test_col NUMBER
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
NOMONITORING;
ALTER TABLE JDORLON . test_tbl ADD (
CONSTRAINT test_tbl_PK
PRIMARY KEY ( test_col )
using index
(CREATE unique INDEX test_tbl_ind ON test_tbl ( test_col ) tablespace example )
)
I think this is a useful idea. I like having more control. Just a couple
comments.
You can create the table, then create the index, which should give you all the
index storage options. Then create the constraint using the index. Lots of
steps, though.
(Bert will probably also comment here) The use of separate tablespaces for
indexes was recommended by some people back in the day of Oracle 7, say 1993 to
“improve performance”. The value was questionable then, and it really hasn’t
added anything since storage techniques have improved in the last 15-20 years.
It may be reducing your performance.
Of course, your DBA’s may have that policy for administrative reasons. That’s up
to them. But if they’re doing it for “performance”, they should be thinking
about updating your standards into the current millennium.
FYI - I know you said it’s your standard – but question nonetheless.
This is a very old “best practice” that in today’s world of
SAN’s, NAS, iSCSI and Oracle ASM does not carry the same benefits as in
years past. In fact unless you know that your tablespace data files are on
different LUN’s with different spindles and/or different ASM groups using
different disks, then this practice generally does not improve performance. It
still offers the logical benefit of making certain administrative operations
easier – but that’s about it in many cases.
FYI - I know you said it's your standard - but question nonetheless. This is
a very old "best practice" that in today's world of SAN's, NAS, iSCSI and
Oracle ASM does not carry the same benefits as in years past. In fact unless
you know that your tablespace data files are on different LUN's with
different spindles and/or different ASM groups using different disks, then
this practice generally does not improve performance. It still offers the
logical benefit of making certain administrative operations easier - but
that's about it in many cases.
Agreed, but some of us don't have the luxury of managing our segment storage
the way we like. JDEdwards, for example, separates indexes into their own
tablespace, but isn't apparently able to specify storage for LOBs and their
respective indexes.
My preference is for segments to be assigned tablespaces based on size and
growth (and potentially usage if a few are "hot") rather than segment type.
Ooooh -- and also recovery scenarios! This discussion just gave me an
idea....Thx, me maties!
Rich -- [TeamT]
Disclaimer: I've never forgotten to ask for an extra $10B for my projects.
I remember back in the days when we had to blow on the disks to make them spin! (I’ve actually been doing this stuff since Oracle 4)
I realize that for performance issues, it is a moot point to separate the
objects like we used to do.
The only reason that we do this is more for maintenance than performance.
Rebuilding an index is easy. Rebuilding a table is not.
When dealing in Terabytes, all these things take on completely different
meanings that back when we thought VLDB was applicable for 100 Megabytes!
David A. Hicken
UtahToad at gmail dot com
---------- Kiva.org - Make a Small Loan. Make a Big difference.
We give up leisure in order that we may have leisure, just as we go to war in order that we may have peace.
– Aristotle
Bert Scalzo wrote:
FYI - I know you said it’s your standard – but question
nonetheless. This is a very old “best practice” that in
today’s world of SAN’s, NAS, iSCSI and Oracle ASM does not carry
the same benefits as in years past. In fact unless you know that your
tablespace data files are on different LUN’s with different spindles
and/or different ASM groups using different disks, then this practice
generally does not improve performance. It still offers the logical benefit
of making certain administrative operations easier – but that’s
about it in many cases.
The use of separate
tablespaces for indexes was recommended by some people back
in the day of Oracle 7, say 1993 to "improve performance".
I remember on my very first DBA course (for Oracle 7) being told exactly
that. Did I question it? No, I didn't know any better. These days though
....
The value was questionable then, and it really hasn't added
anything since storage techniques have improved in the last
15-20 years. It may be reducing your performance.
It wasn't really questionable, it was plain wrong!
Considering how using an index works:
Read the various index blocks, one at a time, until you have read the
correct leaf block.
Use the ROWID from the leaf block entry to go to the table and read
one block to get the correct row.
Now, the above is a sequential sequence (!) and having the index and
table in separate tablespaces simply cannot speed things up! You must
complete the index block read(s) before you can even think of starting
the table read!
Of course, your DBA's may have that policy for
administrative reasons. That's up to them.
This is a useful outcome to having tables and indices kept separateley,
you can (often) see where you have too many indices - if the index
tablespace files are getting more writes than the tables tablespace
files.
But if they're
doing it for "performance", they should be thinking about
updating your standards into the current millennium.
Or, the DBA's should be made to undergo a re-education session or
two!!!!
Cheers,
Norm [TeamT]
Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else. We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes. If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk
Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.
We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.
If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk
(I’ve actually been doing this stuff since Oracle 4)