Indexes

[badrinathn]

Hi,

For any query on the tables have the indexes, can we explicitly make use of the index by saying
table.rowid = index.rowid..
For example
select a.* from table_a a, index_b b
where a.rowid = b.rowid;

badrinath

[sambavan]

Though oracle does the indexing through the table's rowid, I don't think that oracle had thought about this type of comparison.

Sam

[gpsingh]

You cannot use a index like this in the query.Index rowid do not match with table rowid, instead the key values stored in index point to the rowid of the table data.

[badrinathn]

I was under the impression that the indexes store the rowid that is same as the table row's rowid. Isn't it correct?

[jmodic]

Yes, this is correct, howevere you can't address index entries directly in any way. So you can't reference rowids stored in index, you can only reference table row by its rowid. Indexes can't be referenced in queries or DMLs, they are only available to optimizer if it chooses to use them. In queries you can only reference tables, views, snapshots, etc..., but not indexes!

[badrinathn]

That's very clear.
I have another question,
Having an index on the table slows down the 'insert' on that table. Does indexes have any bearing on 'delete' operation on the table.(Would it make the deletion from the table, slower)


Badrinath

[jmodic]

Yes, although the owerhead during deletes is slightly lower than during the inserts....

[adrianomp]

Yes. Every time you use a DML statement (INSERT, DELETE, UPDATE), indexes have to be updated too. That's why it's not recommended to have many indexes in tables that are constantly updated.