Home » RDBMS Server » Performance Tuning » Is this example correct? (Generic)
Is this example correct? [message #606483] Fri, 24 January 2014 10:23 Go to next message
manubatham20
Messages: 441
Registered: September 2010
Location: Champaign, IL
Senior Member

Hi,

I was going through index joins example given at

http://www.orafaq.com/tuningguide/

Never used Index joins practically, but is it right example?

Manu
Re: Is this example correct? [message #606512 is a reply to message #606483] Sat, 25 January 2014 02:31 Go to previous messageGo to next message
pablolee
Messages: 2601
Registered: May 2007
Location: Scotland
Senior Member
To what example do you refer. Your link opens up the front page of that guide.
Re: Is this example correct? [message #606515 is a reply to message #606512] Sat, 25 January 2014 07:58 Go to previous messageGo to next message
Kevin Meade
Messages: 1918
Registered: December 1999
Location: Connecticut USA
Senior Member
You refer to this from the section you pointed to?

Quote:
Index Join

This is a feature you can use in a high volume SQL to avoid a table lookup. Most programmers know that if an index contains all of the columns used in a SQL, then Oracle does not need to access the table. But what if the columns you are using are spread over two indexes?

For example:
SELECT emp_no, sal
FROM emp
WHERE sal > 1000

Index emp_i1 : sal
Index emp_i2 : emp_no

No single index contains emp_no and sal. Oracle can read both indexes and perform a hash join of the two. This will often be slower than a full table scan, but if the rows of the table are particularly wide (lots of columns, or big VARCHAR2 / CHAR columns), the advantage of reading the skinny indexes could be significant.

To get Oracle to use an index join, use the INDEX_JOIN hint.

There is no hint to stop Oracle from performing an index join. In the unlikely event that you want to prevent an Index Join, use the INDEX hint to specify just one index, or the FULL hint for a Full Table Scan.


Yes, the example is valid. However, I doubt you will ever see it used. I have only seen this twice in the real world and never on an EXADATA platform. This is not to say that it does not work as advertised; only to say that it is a very "fine tuning feature". What would happen in this case is the database would do an index scan on the SAL index eliminating entries using SAL > 1000 predicate. Then it would do an index fast full scan of the emp_no index. It would then do a join (most likely a hash join) on ROWID between the two indexes. Rows that survive the join would return as the result of the query.

Oracle might use this if it costed out faster than the two other obvious choices of TABLE SCAN on emp, or index range scan on SAL with rowid lookups to emp. This might work if a significant number of rows were being returned (>2%?) and thus rowid lookup was too costly, but also the table emp had wide rows and thus a table scan was far more expensive than the to index scans. It is an alternative to what would happen if this index existed (emp(sal,emp_no)).

Kevin
Re: Is this example correct? [message #606518 is a reply to message #606515] Sat, 25 January 2014 08:59 Go to previous message
manubatham20
Messages: 441
Registered: September 2010
Location: Champaign, IL
Senior Member

Yes Kevin, I was referring to same example.

As always nice explanation.

But on the first thought, I assumed that it should go to table to access emp_no, accessing the table by row_id.

Below is why I thought it's not the example of index join:

Quote:
Because I never seen this before, I saw required columns for index join in where clause only.

Because row_id is direct access, and better
1. Go to the row by physically pointed by row_id
2. fetch the emp_no

While joining indexes is costly matter in any case
1. Get the row_id
2. Do a index join on the basis of row_id (Join is join, it will take at lest nlogn time)
3. Fetch the emp_no


Your explanation cleared by doubt.

Quote:
This might work if a significant number of rows were being returned (>2%?) and thus rowid lookup was too costly, but also the table emp had wide rows and thus a table scan was far more expensive than the to index scans.


Author should include simple example as well.. Wink

Thanks,
Manu
Previous Topic: More Hash Value
Next Topic: Sudden decrease in performance of query
Goto Forum:
  


Current Time: Thu Jul 10 03:30:47 CDT 2014

Total time taken to generate the page: 0.12340 seconds