Home » SQL & PL/SQL » SQL & PL/SQL » Query Optimization question
Query Optimization question [message #255365] Tue, 31 July 2007 09:28 Go to next message
Rustican
Messages: 51
Registered: July 2006
Member
I'm using oracle 10g enterprise edition.

I'm trying to optimize a query that i'm running against my database. Basically I've got T1 table with a composite primary key consisting of 3 columns.

I'm trying to join it to T2 table where my join condition uses the primary key of T2 equals one of the composite columns from T1.

The problem is that the query is doing a full table scan against T1 since i'm not using the whole composite key in the join. I tried creating a non-unique index on the column in T1 to join against T2 but the query is still doing a full table scan instead of using the index i created.

Is there any way i can prevent having to do a full table scan against T1?

[Updated on: Tue, 31 July 2007 09:30]

Report message to a moderator

Re: Query Optimization question [message #255379 is a reply to message #255365] Tue, 31 July 2007 10:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and follow How to get a quick answer to your question: TIPS AND TRICKS
Please always post your Oracle version (4 decimals).

Regards
Michel

Re: Query Optimization question [message #255386 is a reply to message #255379] Tue, 31 July 2007 11:24 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
does your where clause apply to the leading column of the PK - or to the 2nd or 3rd column of the key? e.g. if you index is on cols A, B, C then you typically need to have cols A or A, B or A, B, C in the where clause. I'm not aware of any changes to this long standing "rule" in 10g R1 or R2.
Re: Query Optimization question [message #255439 is a reply to message #255386] Tue, 31 July 2007 22:50 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
If you are joining to a significant proportion of the rows in the table (say >10%), it will be more efficient NOT to use the index. Oracle thinks this is the case and chooses a FTS.

If you want to force an index scan so that you can compare performance, try the tips on this page.

Ross Leishman
Re: Query Optimization question [message #255634 is a reply to message #255386] Wed, 01 August 2007 06:32 Go to previous messageGo to next message
Rustican
Messages: 51
Registered: July 2006
Member
andrew again wrote on Tue, 31 July 2007 11:24
does your where clause apply to the leading column of the PK - or to the 2nd or 3rd column of the key? e.g. if you index is on cols A, B, C then you typically need to have cols A or A, B or A, B, C in the where clause. I'm not aware of any changes to this long standing "rule" in 10g R1 or R2.



I'm joining I'm joining table1 B to the table2 primary key. I wasn't aware of the "rule".


Also to Ross's statement. I've always thought doing a search against the index was always faster compared to a FTS. Can anyone explain why it wouldn't be? The table i'm working with has around a million rows so i'm surprised why oracle wouldn't want to hit the index. Sorry for my ignorance.
Re: Query Optimization question [message #255706 is a reply to message #255634] Wed, 01 August 2007 09:28 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
When you use an index to get at the data, Oracle has to go to the index, read down through the index to get the rowid of the row it is looking for, go to the table, and read that rowid.

If it does a Full Table scan, then it simply reads several blocks of data at a time out of the table, and reads each row in those blocks.

If the index range scan will involve looking at a significant percentage of the table, a FTS will involve less IO than the index approach.
Previous Topic: DDL Operation On Remote database
Next Topic: Static and Member Function
Goto Forum:
  


Current Time: Fri Dec 09 15:25:45 CST 2016

Total time taken to generate the page: 0.10114 seconds