• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Ron McLeod
  • Rob Spoor
  • Tim Cooke
  • Junilu Lacar
Sheriffs:
  • Henry Wong
  • Liutauras Vilda
  • Jeanne Boyarsky
Saloon Keepers:
  • Jesse Silverman
  • Tim Holloway
  • Stephan van Hulst
  • Tim Moores
  • Carey Brown
Bartenders:
  • Al Hobbs
  • Mikalai Zaikin
  • Piet Souris

Index

 
Ranch Hand
Posts: 724
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Could someone provide me a hint in using index?
In which situations could index improve performance and in which could it slow down?

Thanks in advance
 
Ranch Hand
Posts: 2874
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Improve your performance:
make indices on those columns which are frequently, heavily participate on search conditions.

Slow down your performance:
dont make indices on those columns where DMLs occur frequently.

Tip: index doesn't help in full table scans
[ November 04, 2004: Message edited by: adeel ansari ]
 
Ranch Hand
Posts: 1087
Oracle Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
few point must be noted while creating index.

If your where clause uses more than one condition then create composite index instead of index for individual columns.

An example is you have a table tab1 and it has column A,B,C,D,E,F
and you use everytime A,B and C in your where clause so create composite index on columns A,B,C

Not only this it also depends how you are forming your query

If you have query

Select * from tab1 where A= ? and B=? and C=?

then it will work fine

but in
Select * from tab1 where C= ? and A=? and B=? Use of index is not guaranteed.

however index will be used for

Select * from tab1 where A= ? and B=?

but for

select * from tab1 where B=? and C=? use of index is not guranteed.

Anyone Please Correct me if I am wrong for above statements.

An advice to create index tablespace other then tablespace where data of table is lying.
[ November 04, 2004: Message edited by: Shailesh Chandra ]
 
David Ulicny
Ranch Hand
Posts: 724
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator


but in
Select * from tab1 where C= ? and A=? and B=? Use of index is not guaranteed.



Why?
 
Shailesh Chandra
Ranch Hand
Posts: 1087
Oracle Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I had read it somewhere but didn't bookmark the link , But As far as I remember I put here right thing

If there is composite index on a table then first column of index must be required in query to use the index .


Therse are few more thing I want to add

When two columns are not unique individually but are unique together, composite indexes might work very well.
For example, although columns A and B have few unique values, rows with a particular combination of columns A and B are mostly unique. Look for WHERE clauses with AND operators.


If all values of a SELECT statement are in a composite index, Oracle does not query the table; the result is returned from the index

I will put the URL as I find it.

thanks
[ November 04, 2004: Message edited by: Shailesh Chandra ]
 
Shailesh Chandra
Ranch Hand
Posts: 1087
Oracle Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I found one URL Here


Find for the text >>> ' A composite index is an index that '

probably you will find answer
 
Adeel Ansari
Ranch Hand
Posts: 2874
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Originally posted by David Ulicny:

but in
Select * from tab1 where C= ? and A=? and B=? Use of index is not guaranteed.

Why?



better write composite index, instead of just index.
 
Adeel Ansari
Ranch Hand
Posts: 2874
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Originally posted by Shailesh Chandra:
An advice to create index tablespace other then tablespace where data of table is lying.



Highly recommended. dont make indices in normal data tablespaces.
 
Shailesh Chandra
Ranch Hand
Posts: 1087
Oracle Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
one more is here
 
David Ulicny
Ranch Hand
Posts: 724
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks a lot.
 
Come have lunch with me Arthur. Adventure will follow. This tiny ad:
Thread Boost feature
https://coderanch.com/t/674455/Thread-Boost-feature
reply
    Bookmark Topic Watch Topic
  • New Topic