Home » RDBMS Server » Server Administration » New concept of Indexes...
New concept of Indexes... [message #51706] Sun, 09 June 2002 02:07 Go to next message
kiran srirama
Messages: 14
Registered: March 2002
Junior Member
Hi friends,

I am an Oracle DBA with an organization.
I want to share a new concept about indexes with all of you.
I would love to see all of you react and suggest on the topic.

I am explaining the conventional and proposed concept of Index evaluation:

Suppose there are 2 tables:

1) EMP with columns: EMPNO, ENAME, SAL, DEPTNO
2) DEPT with columns: DEPTNO, DEPTNAME

DATA:
(ROWID IS JUST SHOWN FOR FURTHER REFERENCE, IT DOES NOT EXIST IN THE TABLE AS A COLUMN)

EMPLOYEE TABLE:

EMPNO ENAME SAL DEPTNO ROWID

1 KIRAN 1000 10 A001
2 KUMAR 2000 10 A002
3 SRIRAM 3000 20 A003

DEPT TABLE:

DEPTNO DEPTNAME ROWID

10 MATHEMATICS A004
20 SCIENCE A005

In the current scenario , buisiness logic says that these two tables will always be joined
on DEPTNO column (which is common to both of them).

So, conventionally a DBA designs an index scheme like:

INDX_EMP ON TABLENAME: EMP , COLUMN NAME: DEPTNO
INDX_DEPT ON TABLENAME: DEPT , COLUMN NAME: DEPTNO

Hence a query like:

SELECT A.EMPNO,A.ENAME,A.SAL,A.DEPTNO,B.DEPTNAME
FROM EMP A, DEPT B
WHERE A.DEPTNO = B.DEPTNO

should execute in the following way:

Access of Index INDX_EMP
Access of Table Dept.

Value Comparision on Column DEPTNO of the 2 indexes.
And a result set like this is obtained:

DEPTNO ROWID_EMP ROWID_DEPT
10 A001 A004
10 A002 A004
10 A003 A005

From here,
For each row in the above row source, data is obtained for ENAME, SAL, DEPTNAME
from the corresponding tables, using the ROWID from the above rowsource.

Then the resulted data is displayed as output.

NEW CONCEPT WHICH I THOUGHT OF:

If a single index could be created upon DEPTNO column of both tables EMP and DEPT such that,
the new index (INDX_NEW) could be of the following structure:

DEPTNO ROWID_EMP ROWID_DEPT
10 A001 A004
10 A002 A004
10 A003 A005

then the value comparision occuring in the conventional method could be skipped.
This could increase the performace of the query by many folds.

I would like to call this new concept as 'Clustered Index'.

Now, the query:

SELECT A.EMPNO,A.ENAME,A.SAL,A.DEPTNO,B.DEPTNAME
FROM EMP A, DEPT B
WHERE A.DEPTNO = B.DEPTNO

would directly access the index INDX_NEW and
obtain the columns ENAME,SAL,DEPTNAME from tables based on the rowid found.

I request Oracle Corp. to give my idea a deep study and provide the users a
good implentation methodology for the same.

I am currently evaluating my idea against handling the scenario when outer join / self join
is needed.

Thanks and Regards
Srirama kiran Kumar

Report message to a moderator

Re: New concept of Indexes... [message #51710 is a reply to message #51706] Sun, 09 June 2002 22:19 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
I'm sorry to disappoint you, but such an index already exists in Oracle 9i: You can index joins: It is called a bitmap join index.
Look here:
http://otn.oracle.com/docs/products/oracle9i/doc_library/release2/server.920/a96520/indexes.htm#98463

In fact, the whole idea has been introduced a while ago. but since it is a bitmap index, I don't think it can be used everywhere.

Just a thought: If the employee table has an index on deptno, Oracle will use it in your query. And since you don't have any other where clauses than the equi-join, a full table scan of Dept is unavoidable. I don't see the practical gain in your case. Then there is also the issue of size. How large would such an index be? It could grow to absurd proportions.

It's still early for me, but my day sure has started interesting...

MHE

Report message to a moderator

Re: New concept of Indexes... [message #51712 is a reply to message #51710] Sun, 09 June 2002 23:33 Go to previous messageGo to next message
kiran srirama
Messages: 14
Registered: March 2002
Junior Member
Dear Maaher,

Thanks for informing me about the implementation of the new kind of indexes in Oracle 9i. I have went through the link you have posted.

As far as my understanding goes, this concept has been implemented successfully with bitmap indexes only.

My idea differs from the 9i implementation, from here:

I want that even b-tree indexes work so.
As explained, in my previous mail, space efficiency is much more and even quickness of the query.

Yeah, now I should say, even my day has started better.

Its great discussing about the same issue with you.

Regards
Srirama Kiran Kumar

Report message to a moderator

Re: New concept of Indexes... [message #51727 is a reply to message #51706] Mon, 10 June 2002 22:13 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
I still fail to see the real improvement. An index on the Foreign key in emp is enough. The addition of the deptno field of dept isn't necessary since there are no real searches against this table. You simply search dept and the associated emps.

MHE

Report message to a moderator

Re: New concept of Indexes... [message #52648 is a reply to message #51706] Sun, 04 August 2002 20:34 Go to previous messageGo to next message
Gatot Mukti Wibowo
Messages: 1
Registered: August 2002
Junior Member
Hii.. Srirama,
My Name Gatot, Iam Work At BNI 46, in Indonesia. For the first, I aks for you if my English Not good. Ohh yaa Soryy if my question not relevant with this subject. Can you give me information or something like paper, for all information about DBA Organization, Staffing, Expertice, etc because I will Create a Concept DBA.

Thans
Gatot M Wibowo

Report message to a moderator

Re: New concept of Indexes... [message #140393 is a reply to message #51706] Tue, 04 October 2005 00:48 Go to previous messageGo to next message
chandanbhamra
Messages: 84
Registered: April 2005
Location: India
Member
Hi,

I am not able to get what you want to say about the single index. Can you please explain it in detiail ??
Again, the structure you are getting is the same even using the single index.

Thanks
Chandan Singh

Report message to a moderator

Re: New concept of Indexes... [message #153562 is a reply to message #140393] Tue, 03 January 2006 03:13 Go to previous message
valeria.joquina
Messages: 1
Registered: January 2006
Junior Member
chandanbhamra wrote on Tue, 04 October 2005 00:48

Hi,

I am not able to get what you want to say about the single index. Can you please explain it in detiail ??
Again, the structure you are getting is the same even using the single index.

Thanks
Chandan Singh



Which messages did u reply???

best regards,

Report message to a moderator

Previous Topic: Logminer
Next Topic: how to Deinstall previous version of Oracle Objects for OLE.
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sun Jan 26 13:23:48 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.