OraFAQ Forum: Performance Tuning

Home » RDBMS Server » Performance Tuning » Creation of Index

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Creation of Index [message #165491]

Thu, 30 March 2006 10:22

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

Report message to a moderator

Re: Creation of Index [message #165493 is a reply to message #165491]

Thu, 30 March 2006 10:47

Mahesh Rajendran
Messages: 10708
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

Report message to a moderator

Re: Creation of Index [message #558028 is a reply to message #165493]

Tue, 19 June 2012 00:10

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

Mahesh Rajendran
Messages: 10708
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.

Report message to a moderator

Re: Creation of Index [message #558137 is a reply to message #558107]

Tue, 19 June 2012 11:27

Jack14
Messages: 497
Registered: December 2011
Location: INDIA

Senior Member

Thank you Mahesh for pointing out the quotation.

Report message to a moderator

Previous Topic:	PL/SQL Block (LOOP) Comparison
Next Topic:	"px send round-robin "

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Feb 19 14:31:56 CST 2025