Can someone tell me how indexes work ?
Does index stores some kind of number which select statement then accesses to make query faster ?
Is it stored in memory ? If so what happens when some one does DML on that table. Now you try to do select, how does index knows the new data ?
I read many documents on this but this is not explained any where. It is just said that good indexes improves performance, which I think everyone knows. How ??
hope this will give you a hint
index is another segment, seperated from table segment. in each "block" it stores the rowid and the value of the column
in b*tree index, the most common one:
if you have empno from 1 to 16
ythe structure would be somthing like
1~4 4~8 9~12 13~16
for example empno column in table emp
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
--------- ---------- --------- --------- -------- --------- --------- ---------
16 SMITH CLERK 7902 17/12/80 800 20
empno is indexed so index empno will store AAAHwJAAFAAAVaSAAA and 16
rowid is the physical address of data in disk so itīs the fastest way to retrieve a data if we know rowid
thanks, I still doesn't understand why inserts and deletes make the query slower if you have index. If it locates it by Rowid-column value combination ? Does Rowid change with insert / deletes ?
When you insert records into a table, all the indexes related to this table have to be updated with the rowid information for the newly inserted record. If you have too many indexes on a table then this synchronization operation takes time.
When u insert or delete, the index also has to be inserted with the new rowid of the newly inserted record or has to delete the rowid from the index leaf page for the deleted records.
Thus, if u have many indexes on a particular table, the insert/delete on the base table causes i/o on the indexes.
Since Sonali brought index issue, I have a question
which index is better bitmap or b-Tree? when do you use b-Tree and Bitmap index?
Let me know.
Whether to create a bit map index or b-tree index depends on the requirement.If the column has low cardinality,then creating a bitmap index is better.If the column has a high cardinality,then creating a b-tree index is better.Hope this helps.
Click Here to Expand Forum to Full Width