| Multiple index on Multiple tables [message #167623] |
Fri, 14 April 2006 13:46  |
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 #168122 is a reply to message #168033] |
Tue, 18 April 2006 22:22  |
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 |
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 #168356 is a reply to message #168299] |
Thu, 20 April 2006 00:25 |
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
