OraFAQ Forum: SQL & PL/SQL » Create Index on Multiple tables

Home » SQL & PL/SQL » SQL & PL/SQL » Create Index on Multiple tables (10.2.0.5 standard edition Micsoft windows 2008)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Create Index on Multiple tables [message #585029]

Tue, 21 May 2013 08:13

AnilKampiri
Messages: 69
Registered: September 2012
Location: Chennai

Member

HI,

Good day,Please advice can we create index on multiple tables in oracle,Please advice is it possible.

Regards,
Anil

Report message to a moderator

Re: Create Index on Multiple tables [message #585030 is a reply to message #585029]

Tue, 21 May 2013 08:18

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Good day,Please advice can we create index on multiple tables in oracle,Please advice is it possible.

Not possible.

Report message to a moderator

Re: Create Index on Multiple tables [message #585031 is a reply to message #585029]

Tue, 21 May 2013 08:18

pablolee
Messages: 2882
Registered: May 2007
Location: Scotland

Senior Member

Report message to a moderator

Re: Create Index on Multiple tables [message #585032 is a reply to message #585031]

Tue, 21 May 2013 08:19

AnilKampiri
Messages: 69
Registered: September 2012
Location: Chennai

Member

Thanks for reply,

Regards,
Anil

Report message to a moderator

Re: Create Index on Multiple tables [message #585034 is a reply to message #585030]

Tue, 21 May 2013 08:30

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

To be fair, OP may have asked incorrectly. Maybe OP wants to have multiple indexes, not index on multiple tables. To which, my answer would be create as many indexes as appropriate.

Report message to a moderator

Re: Create Index on Multiple tables [message #585036 is a reply to message #585029]

Tue, 21 May 2013 08:35

Michel Cadot
Messages: 68773
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

No.
What problem are you trying to solve.

Regards
Michel

Report message to a moderator

Re: Create Index on Multiple tables [message #585377 is a reply to message #585036]

Fri, 24 May 2013 08:01

AnilKampiri
Messages: 69
Registered: September 2012
Location: Chennai

Member

Dear Michel,

Sorry for my delay respose,It is just a doubt if we create index on Two table,Some of join queries Might be faster as of my understanding.

Regards,
Anil

Report message to a moderator

Re: Create Index on Multiple tables [message #585395 is a reply to message #585377]

Fri, 24 May 2013 10:37

Michel Cadot
Messages: 68773
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

It is possible to create a bitmap join index but only in 11g and up.
See bitmap_join_index_clause in SQL Reference and Using Bitmap Join Indexes in Database Data Warehousing Guide.

In all versions you can store the tables in a cluster so rows of the 2 (or more) tables are physically joined by the cluster column and can be access through the cluster index.

Regards
Michel

Report message to a moderator

Re: Create Index on Multiple tables [message #585783 is a reply to message #585395]

Wed, 29 May 2013 21:34

Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA

Senior Member

It depends on what kind of index you are talking about. You can create a Context index on multiple tables, using a user_datastore and a procedure that joins the tables. You can also created a materialized view that joins the tables and create an index on the materialized view. It would help to see your actual problem, such as create table statements, insert statements for some sample data, and the query you are currently using.

Report message to a moderator

Re: Create Index on Multiple tables [message #585851 is a reply to message #585029]

Thu, 30 May 2013 06:50

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

If you are using multiple tables so frequently to fetch data, then you would probably want those particular columns from those tables to be in a cluster. Create a clustered index.

A table cluster is a group of tables that share the same data blocks, since they share common columns and are often used together. When you create cluster tables, Oracle physically stores all rows for each table in the same data blocks. The cluster key value is the value of the cluster key columns for a particular row.

Index cluster tables can be either multi-table or single-table. In a multi-table index cluster, related table rows are grouped together to reduce disk I/O.

Hence, you would save a lot of unnecessary physical I/O. Also, keep in mind the clustering_factor of the indexes.

Report message to a moderator

Re: Create Index on Multiple tables [message #585856 is a reply to message #585395]

Thu, 30 May 2013 07:34

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

Michel Cadot wrote on Sat, 25 May 2013 01:37

It is possible to create a bitmap join index but only in 11g and up.

Haven't Bitmap Join indexes been available since 9i?

Ross Leishman

Report message to a moderator

Re: Create Index on Multiple tables [message #585993 is a reply to message #585856]

Fri, 31 May 2013 13:53

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

Bitmap join index is there since Oracle9i Release 2 (9.2).

Please refer link, http://docs.oracle.com/cd/B10501_01/server.920/a96520/indexes.htm#98463

Report message to a moderator

Re: Create Index on Multiple tables [message #586435 is a reply to message #585993]

Thu, 06 June 2013 05:07

AnilKampiri
Messages: 69
Registered: September 2012
Location: Chennai

Member

Dear All,

Thanks for active response.

Regards,
Anil

Report message to a moderator

Previous Topic:	How can I list data dynamically monthwise picking month from the same table.
Next Topic:	Unable to insert georgain characters using 6i forms and reports

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Dec 24 13:59:01 CST 2025