| Index creation statement [message #323897] |
Fri, 30 May 2008 00:00  |
msriram123
Messages: 11
Registered: May 2008
Location: Hyderabad
|
Junior Member |
|
|
Hi,
There is an index on a table on our prduction database which I want to replicate on our new test database.
I used a rather standard method to create the index and ended up with having to drop it since the performance is unacceptably poor on inserts. However, in the production database, there is no such problem.
How do I know if any special options were used while creating the index in production?
Here is the statement I used:
create index IDX_GP_PRIMARYUSER_CID on GP_PRIMARYUSER (CID,GPID,PUID)
tablespace INDX
What I want to do now is somehow extract the index creation statement from the production database and execute it on the test database.
Is there a way to do this??
Thank you very much!!
|
|
|
|
| Re: Index creation statement [message #323925 is a reply to message #323897] |
Fri, 30 May 2008 01:15  |
asangapradeep
Messages: 128
Registered: October 2005
Location: UK
|
Senior Member |
|
|
if you cna connect a oracle enterprise manager console (oracle client for short) to the production database then connect using that , select the index, right click on it and select show object DDL.
you will get the sql to create the index similar to production system.
if you can then look in the user_indexes table using the index_name as the key. you can get some index creatiion parameters from there.
|
|
|
|
| Re: Index creation statement [message #324007 is a reply to message #323925] |
Fri, 30 May 2008 04:51 |
msriram123
Messages: 11
Registered: May 2008
Location: Hyderabad
|
Junior Member |
|
|
Hi Pradeep... thanks a lot for your response!!
Unfortunately, I can't connect to the database using a console of the type you mentioned. The reason is that I don't have direct access to the server from my PC. I telnet through a string of servers to access the production DB.
I tried the second option you gave me (querying USER_INDEXES), but I can't figure out what information is actually useful to me. An application like PL/SQL developer must be building the SQLs by running queries on that very table. So what I need now is the query to build the SQL!!
|
|
|
|
| Re: Index creation statement [message #324017 is a reply to message #324007] |
Fri, 30 May 2008 05:03 |
asangapradeep
Messages: 128
Registered: October 2005
Location: UK
|
Senior Member |
|
|
these might be useful
| Quote: | TABLESPACE
PCTFREE
INITRANS
MAXTRANS
INITIAL_extent
NEXT_extent
MINEXTENTS
MAXEXTENTS
PCTINCREASE
LOGGING
|
syntax
| Quote: | CREATE UNIQUE INDEX index name "
ON "table" ("columns")
TABLESPACE "tbs" PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE ( INITIAL 64K NEXT 0K MINEXTENTS 1 MAXEXTENTS
2147483645 PCTINCREASE 0)
LOGGING
|
|
|
|
|
|
|
|
|
| Re: Index creation statement [message #324409 is a reply to message #324371] |
Mon, 02 June 2008 06:52 |
msmallya
Messages: 66
Registered: March 2008
Location: AHMEDABAD, GUJARAT
|
Member |
|
|
You have mentioned that after creating index, performance was
slow. This may be due to several reasons.
For getting the index creation statement from existing index,
you can export that table (where index is associated). Then
import with options indexfile=flnm rows=N. (rows=N is a must otherwise rows also get imported).
exp user/pwd@db file=expfl tables=(abc)
imp user/pwd@db file=expfl rows=N indexfile=abcind.sql log=abc.log
Edit the file abcind.sql and extract index creation statements.
Regards,
MSMallya
[Updated on: Mon, 02 June 2008 06:57] Report message to a moderator |
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
