Home » SQL & PL/SQL » SQL & PL/SQL » Create Index on Multiple tables (10.2.0.5 standard edition Micsoft windows 2008)
Create Index on Multiple tables [message #585029] Tue, 21 May 2013 08:13 Go to next message
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
Re: Create Index on Multiple tables [message #585030 is a reply to message #585029] Tue, 21 May 2013 08:18 Go to previous messageGo to next message
BlackSwan
Messages: 22725
Registered: January 2009
Senior Member
>Good day,Please advice can we create index on multiple tables in oracle,Please advice is it possible.

Not possible.
Re: Create Index on Multiple tables [message #585031 is a reply to message #585029] Tue, 21 May 2013 08:18 Go to previous messageGo to next message
pablolee
Messages: 2615
Registered: May 2007
Location: Scotland
Senior Member
no
Re: Create Index on Multiple tables [message #585032 is a reply to message #585031] Tue, 21 May 2013 08:19 Go to previous messageGo to next message
AnilKampiri
Messages: 69
Registered: September 2012
Location: Chennai
Member

Thanks for reply,

Regards,
Anil
Re: Create Index on Multiple tables [message #585034 is a reply to message #585030] Tue, 21 May 2013 08:30 Go to previous messageGo to next message
joy_division
Messages: 4504
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.
Re: Create Index on Multiple tables [message #585036 is a reply to message #585029] Tue, 21 May 2013 08:35 Go to previous messageGo to next message
Michel Cadot
Messages: 58954
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No.
What problem are you trying to solve.

Regards
Michel
Re: Create Index on Multiple tables [message #585377 is a reply to message #585036] Fri, 24 May 2013 08:01 Go to previous messageGo to next message
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
Re: Create Index on Multiple tables [message #585395 is a reply to message #585377] Fri, 24 May 2013 10:37 Go to previous messageGo to next message
Michel Cadot
Messages: 58954
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Create Index on Multiple tables [message #585783 is a reply to message #585395] Wed, 29 May 2013 21:34 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7974
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.
Re: Create Index on Multiple tables [message #585851 is a reply to message #585029] Thu, 30 May 2013 06:50 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2208
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.

Re: Create Index on Multiple tables [message #585856 is a reply to message #585395] Thu, 30 May 2013 07:34 Go to previous messageGo to next message
rleishman
Messages: 3700
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
Re: Create Index on Multiple tables [message #585993 is a reply to message #585856] Fri, 31 May 2013 13:53 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2208
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
Re: Create Index on Multiple tables [message #586435 is a reply to message #585993] Thu, 06 June 2013 05:07 Go to previous message
AnilKampiri
Messages: 69
Registered: September 2012
Location: Chennai
Member

Dear All,

Thanks for active response.

Regards,
Anil
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:
  


Current Time: Tue Sep 02 03:43:19 CDT 2014

Total time taken to generate the page: 0.09530 seconds