Home » RDBMS Server » Server Administration » Index creation statement (Oracle 8i/10g)
Index creation statement [message #323897] Fri, 30 May 2008 00:00 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #324059 is a reply to message #323897] Fri, 30 May 2008 08:27 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Use DBMS_METADATA.GET_DDL to get the source of the index.
Re: Index creation statement [message #324371 is a reply to message #324059] Mon, 02 June 2008 03:42 Go to previous messageGo to next message
msriram123
Messages: 11
Registered: May 2008
Location: Hyderabad
Junior Member
Pradeep - thanks for that. I think joy_division has provided the name of the function that returns the DDL statement for creating the index. However, there seems to be a problem....

Joy - Thanks a lot!! That was exactly what I was looking for... But, I just discovered that this doesn't work for Oracle 8i Sad

If you know any workaround for Oracle 8i, please let me know... thank you!!
Re: Index creation statement [message #324409 is a reply to message #324371] Mon, 02 June 2008 06:52 Go to previous messageGo to next message
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

Re: Index creation statement [message #324431 is a reply to message #324371] Mon, 02 June 2008 08:15 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
msriram123 wrote on Mon, 02 June 2008 04:42

Joy - Thanks a lot!! That was exactly what I was looking for... But, I just discovered that this doesn't work for Oracle 8i Sad



I'm sorry, I didn't see that you were using 8i. MSMallya gave you a solution, otherwise you can export with ROWS=N and then edit the "mostly" ascii file and find the create index you are looking for.
Re: Index creation statement [message #324523 is a reply to message #324409] Mon, 02 June 2008 23:39 Go to previous message
msriram123
Messages: 11
Registered: May 2008
Location: Hyderabad
Junior Member
Thanks MSMallya,joy_division!! I combined your solutions and got what I wanted.

I did an export with rows=N and extracted the index creation DDL from the file.
Previous Topic: deleting data files
Next Topic: ORA-01033: ORACLE initialization or shutdown in progress
Goto Forum:
  


Current Time: Sat Dec 03 09:46:52 CST 2016

Total time taken to generate the page: 0.08138 seconds