| Index [message #280107] |
Mon, 12 November 2007 08:52  |
foreigndba
Messages: 7
Registered: November 2007
Location: Dhaka
|
Junior Member |
|
|
Hellow
I'm a assistant oracle DBA.Last 1 year I worked on 10g.
I created a tablespace for primary key index.I placed another location for primary key index.
Now I create another 3 indexes that need to faster serching data.
1.My question is what is the difference between primary key index and others index?.
2.Does this primary key index need to another tablespace?
3.Does this primary index faster ?(I know oracle automatically create index for primary key,can I get extra benefit)
4.How shall I use these index?
Please Help me ,I'm trying to solve but I could not understand how to use index appropritely.
|
|
|
|
| Re: Index [message #280114 is a reply to message #280107] |
Mon, 12 November 2007 09:08  |
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
|
Senior Member |
|
|
1. a)There is no such thing as a Primary key index. There is an index on a Primary key.
b) here is no such thing as an others index. Can you please be more specific in what you mean.
2. I'm going to guess a little here, I think you are asking if you need to put an index that is on a primary key in a different tablespace. No.
3. Faster than what? Are you asking if the index will be built faster? Will alow faster searching... something else, sorry I'm really not sure what you mean.
4. You don't use the index, Oracle uses the index as a when Oracle believes it would be appropriate.
As a DBA of a year, I'm assuming that you know where to get the documentation. I strongly advise you to read the Concepts guide and the DBA reference.
|
|
|
|
| Re: Index [message #280402 is a reply to message #280107] |
Tue, 13 November 2007 07:27 |
foreigndba
Messages: 7
Registered: November 2007
Location: Dhaka
|
Junior Member |
|
|
Hello
Thanks pablolee.I'm not good in English.sorry, I could not understand you what I want to know.
I create a user(sky).There are 200 tables in this schema.40 tables are master table.
some tables have single column primary key some tables have concanated primary key. huge rows will propagate each table.
To query faster I create 50 indexes and each index resides their own tablespace(there are 50 tablespaces).
I create 40 master tables and 40 primary keys.so 40 indexes on primary key created automatically(oracle creates automatically).
And These index place by default with table's tablespace.But I didnot want this,I want these index will place another tablespace.
Query
1. I want to know can I place these index(index on primary key ) another location with another tablespace?. what is the benefit or drawback?
2. Can I depend on oracle? because oracle automatically create these index.if I depend on oracle ,oracle creates index where datafile(table's tablespace ) also resides.To query faster oracle recommends index and datafile(table's tablespace) place different location.
3. Will any problem create , index on primary key and others index in where clause?
Plz
Asraf
|
|
|
|
| Re: Index [message #280424 is a reply to message #280402] |
Tue, 13 November 2007 08:42 |
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
|
Senior Member |
|
|
| Quote: |
To query faster I create 50 indexes and each index resides their own tablespace(there are 50 tablespaces).
|
Why would you do that? Do you also have 50 disks, 1 for each tablespace?
1. You can use the USING INDEX clause (look it up in the documentation)
2. I don't know what you mean by "Can I depend on Oracle". Please copy and paste from the documentation where it says "To query faster oracle recommends index and datafile(table's tablespace) place different location."
(Yes, I know it does say that, but I have a feeling that you are misunderstanding by either taking it out of context or not seeing the whole picture.)
3. Sorry, I can't understand what you mean here.
|
|
|
|
| Re: Index [message #281025 is a reply to message #280424] |
Thu, 15 November 2007 07:29 |
foreigndba
Messages: 7
Registered: November 2007
Location: Dhaka
|
Junior Member |
|
|
Thanks pablolee
I have two disks.I want to place all indexes(suppose D:\ ) in one disk and datafiles another disk.
How can I configure index on primary key and other indexes?.
|
|
|
|
| Re: Index [message #281057 is a reply to message #281025] |
Thu, 15 November 2007 09:52 |
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
|
Senior Member |
|
|
| Pablolee wrote |
1. You can use the USING INDEX clause (look it up in the documentation)
|
And you don't need 50 tablespaces for this. 1 tablespace for the indexes.
|
|
|
|
| Re: Index [message #281783 is a reply to message #281057] |
Mon, 19 November 2007 05:29 |
foreigndba
Messages: 7
Registered: November 2007
Location: Dhaka
|
Junior Member |
|
|
Thanks Pablolee
I have six modules in my database.Can I use six tablespaces for six modules.
if I use six tablespaces,therefore any performance problem will occur in future.
|
|
|
|
|
|
| Re: Index [message #282266 is a reply to message #281820] |
Wed, 21 November 2007 06:01 |
foreigndba
Messages: 7
Registered: November 2007
Location: Dhaka
|
Junior Member |
|
|
Thanks Pablolee
Under my Database an application will run.This application has six modules(Human resource Manager,Account Management,
Store and Procurement,Services so on). Each Module has about 30 forms(some of Master entry form,Transaction form,Report form so on).
I want to know can I use six tablespaces for six modules.
suppose for Human Resource Manager(HRM) I want to put all HRM data in one tablesapce.
Account Management I want to put all Account data in one tablesapce.so on.
If I use six tablespaces in database,therefore any database problem(query slower,space wasted,in data block High water occure) can occure.
|
|
|
|
|
|
| Re: Index [message #282575 is a reply to message #282353] |
Thu, 22 November 2007 07:11 |
foreigndba
Messages: 7
Registered: November 2007
Location: Dhaka
|
Junior Member |
|
|
I will build a database.In Oracle 10g(Release 10.2.0.1.0) database creates 5 tablespaces automatically.I want to know can I use another 6 tablespaces for developing application.And all tablespaces may reside in one disk.I will use ASM(Automatic storage management ) with Oracle Real Application cluster.There are 2 nodes in RAC.
You quote "Forms" do not reside in the database.
< "Form" application form not Oracle developing Form >
<"Module>
Many forms integrate in a module.
|
|
|
|
| Re: Index [message #282649 is a reply to message #280107] |
Thu, 22 November 2007 21:19 |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
Modules (PL/SQL) reside in the SYSTEM tablespace.
Especially if all the tablespaces (datafiles) reside on the same physical disk; you've gained NOTHING by having 6 tablespaces.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
