Home » RDBMS Server » Performance Tuning » Creation of Index
Creation of Index [message #165491] Thu, 30 March 2006 10:22 Go to next message
suryamz
Messages: 17
Registered: March 2006
Junior Member
Hi

we have oracle 8i/win nt. (Prod Server running 24 X7)
Iam new to Performance Tuning

On doing a explain plan on long running queries.
i realised a couple of columns (that were used in the WHERE condition, were not having any indexes.)

More importantly, here we do not have a Test environment.

I would like to know if i can go ahead and create indexes for those 2 columns by issuing
CREATE INDEX index_name on table_name(Column_name);
on our PDB.

Would this be of any impact on our PDB?
Do i check anything more prior to building these indexes.
or
Do have i create them at a down time of PDB.

Please clarify on these questions

Thanks
surya

Re: Creation of Index [message #165493 is a reply to message #165491] Thu, 30 March 2006 10:47 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10694
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
You need to create index and collect statistics on table/indexes.
Else it is of no use.
Are the statistics updated?
Depending on your version, may be you can make use of a virtual index and check whether your query is really making use of it. If so, drop the virtual index and create a regular index.
Please google for 'virtual index'.
My googling, got me here
http://www.databasejournal.com/features/oracle/article.php/3413961
Re: Creation of Index [message #558028 is a reply to message #165493] Tue, 19 June 2012 00:10 Go to previous messageGo to next message
Jack14
Messages: 497
Registered: December 2011
Location: INDIA
Senior Member
Quote:
Depending on your version, may be you can make use of a virtual index and check whether your query is really making use of it. If so, drop the virtual index and create a regular index.

I have a doubt on virtual index.

When we create virtual index and found the query is making use of it ,then why do we need
to drop the virtual index and create a regular index rather than using the virtual index itself
for the query?

In other way , what if we dont drop the virtual index and continue using the same virtual index for the query.

Can somebody explain me on this ?

Regards,
Jack

[Updated on: Tue, 19 June 2012 00:15]

Report message to a moderator

Re: Creation of Index [message #558107 is a reply to message #558028] Tue, 19 June 2012 07:31 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10694
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
quoting from the provided link
Quote:
Below are some attributes of the Virtual Indexes.

1. These are permanent and continue to exist unless we drop them.

2. Their creation will not affect existing and new sessions. Only sessions marked for Virtual Index usage will become aware of their existence.
Re: Creation of Index [message #558137 is a reply to message #558107] Tue, 19 June 2012 11:27 Go to previous message
Jack14
Messages: 497
Registered: December 2011
Location: INDIA
Senior Member
Thank you Mahesh for pointing out the quotation.
Previous Topic: PL/SQL Block (LOOP) Comparison
Next Topic: "px send round-robin "
Goto Forum:
  


Current Time: Thu Sep 23 19:17:29 CDT 2021