Rama Krishna

• Aug 15th, 2006

 

Friend!! We cant create an Index on Index.. Index is stoed in user_index table.Every object that has been created on Schema is Schema Object like Table,View etc.If we want to share the particular data to various users we have to use the virtual table for the Base table...So tht is a view..Thanks&RegdsRama Krishna,TCSHyd

SateeshReddy

 Profile 

• Oct 9th, 2006

 

Hello frnd,

why cant we create an index on the index....i think according to the database concepts we can create it. it may called as multilevel indexes...like B trees and B+trees are examples for that....

we need this type of index is due to if the index size is also too large we can go with index for that index....

Sateesh Reddy

jaspreet.nagra

 Profile Answers by jaspreet.nagra 

• Jul 14th, 2009

 

Sometime the special index you get automatically with every primary key and unique constraint might not help in certain situations. If this happens then maybe it's time to explicitly create a second index on the primary key.

An explicit index can help when the structure of the automatic index is all wrong; e.g., when the columns are placed in the wrong order for certain sorting or searching criteria or the default ASC sort sequence in a multi-column primary key doesn't help with a particular ORDER BY clause.

Another situation where an explicit index might help is when a huge number of child table rows exist for each parent row. A large "fan-out" from primary-to-foreign key values can cause the special primary-and-foreign-key index to grow fat and slow. For example, if there are 1000 rows in a child table for every parent row then a query based on the primary key must deal with 1000 index entries for every different primary key value.

If you read the concept of Mutlileve Indexing it doesn;t tell to create index on index instead it says to create index at another level. So in above mentioned 2 examples, implicit index created on creation of Primary Key will be at 2nd level and the explicit unique index created will be at 1st level.