Home » RDBMS Server » Performance Tuning » Multiple index on Multiple tables
Multiple index on Multiple tables [message #167623] Fri, 14 April 2006 13:46 Go to next message
kishinevetz
Messages: 10
Registered: April 2006
Location: Maryland, USA
Junior Member

Hello!
As I understand correct syntax for INDEX is:

SELECT /*+ INDEX (A,TABLE_NAME) */
A.FIELD1,
A.FIELD2
FROM TABLE_NAME A
WHERE
A.FIEDL1 = 'ABC' AND
A.FIEDL2 = 123;

What should syntax be if I need to have index on both tables

SELECT /*+ INDEX (A,PS_PC_EMP_RES_TM) */ ?????I need an index on table B specified in here
A.FIELD1,
A.FIELD2,
B.FIELD3
FROM
TABLE_NAME A,
TABLE_NAME B
WHERE
A.FIELD1= B.FIELD1 AND
A.FIELD1 = 'ABC'


Please let me know, if you have any suggestions

[Updated on: Fri, 14 April 2006 13:54]

Report message to a moderator

Re: Multiple index on Multiple tables [message #167625 is a reply to message #167623] Fri, 14 April 2006 13:50 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
One or more indexes will not improve performance on a cartesian join.
Re: Multiple index on Multiple tables [message #167627 is a reply to message #167623] Fri, 14 April 2006 13:53 Go to previous messageGo to next message
kishinevetz
Messages: 10
Registered: April 2006
Location: Maryland, USA
Junior Member

I do have WHERE clause, I just did not include it in my originall statements...
There is no cartesian product created by my qury..
I just need to know how to specify two different indexes for two different tables...
Re: Multiple index on Multiple tables [message #167914 is a reply to message #167627] Mon, 17 April 2006 21:53 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Just specify another hint:

SELECT /*+ INDEX(t1, i1) INDEX(t2, i2) */ ...


Ross Leishman
Re: Multiple index on Multiple tables [message #168033 is a reply to message #167914] Tue, 18 April 2006 08:46 Go to previous messageGo to next message
kishinevetz
Messages: 10
Registered: April 2006
Location: Maryland, USA
Junior Member

Thanks for reply... I was able to figure out myself last week....
Maybe you can suggest something else:
I have a table with over 9 million rows. Archival/Compress is not an option. My where clause against this table, brings back just over 3 million rows of data..the amount of time it takes is about 3.5 minutes.
I am using INDEX. what would you do in orer to speed this up some more.. (my query is not only against this huge table, but against other tables as well)
I am thinking of creating a view against this huge table, and then referencing view in my query... do you think this would work? or would you have a better suggestion?
Re: Multiple index on Multiple tables [message #168122 is a reply to message #168033] Tue, 18 April 2006 22:22 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
A view won't help.

To make it faster, you have to reduce I/O. A view only changes your syntax.

3.5 minutes for 3M rows. That's not too bad. How fast are you hoping to make it?

Ross Leishman
Re: Multiple index on Multiple tables [message #168246 is a reply to message #168122] Wed, 19 April 2006 07:57 Go to previous messageGo to next message
kishinevetz
Messages: 10
Registered: April 2006
Location: Maryland, USA
Junior Member

In my test environment, the same table only contains about 1 million records, and the same query as in Production only takes about 16 seconds. Since my production table contains about 3 million rows, I would expect 3 times that time, so about 48-50 seconds, and not 3.5 minutes.
I realize that other tables in my where clause, also play a role on the amount of time it takes, however the difference in number of rows in secondary tables between PROD and TEST is not that significant (about 10,000) rows different between other 4 tables
Re: Multiple index on Multiple tables [message #168299 is a reply to message #167623] Wed, 19 April 2006 11:44 Go to previous messageGo to next message
vjeedigunta
Messages: 201
Registered: March 2005
Location: Hyderabad
Senior Member

you can think of a materialized view for this scenario ..and when ever you need the data you can fetch from the mat view ..and set the interval for refreshes depending upon your requirement \ Data updations ..

-Sai Jeedigunta
Re: Multiple index on Multiple tables [message #168356 is a reply to message #168299] Thu, 20 April 2006 00:25 Go to previous message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Get the EXPLAIN PLAN for both environments - they will almost certainly be different.
The idea will be to get them the same.

Post them both here if you want help doing this.

Ross Leishman
Previous Topic: Re Update index
Next Topic: How can I improve the performance of LOWER (col) LIKE ‘%name%’
Goto Forum:
  


Current Time: Wed May 29 19:56:33 CDT 2024