Home » RDBMS Server » Performance Tuning » LEFT OUTER JOIN of Large Tables - Performance issue (ORACLE, 12.1.0.2.0, WINDOWS 10)
LEFT OUTER JOIN of Large Tables - Performance issue [message #650654] Thu, 28 April 2016 13:22 Go to next message
anil_mk
Messages: 146
Registered: August 2006
Location: Bangalore, India
Senior Member

Hi All,

Please find below sample table creation script with data loading.
Each time "FOR" loop is inserting 100000 records

--Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit
DROP TABLE TST_1
/
CREATE TABLE TST_1 AS SELECT * FROM DBA_OBJECTS WHERE 1=2
/

BEGIN 

FOR I IN 1..400 LOOP
  INSERT INTO TST_1 SELECT * FROM DBA_OBJECTS;
END LOOP;
commit;
END;
/

CREATE INDEX INDX_TST_1_OBJID ON TST_1 (OBJECT_ID)
/
DROP TABLE TST_2
/
CREATE TABLE TST_2 AS SELECT * FROM DBA_OBJECTS WHERE 1=2
/

BEGIN
FOR I IN 1..300 LOOP
  INSERT INTO TST_2 SELECT * FROM DBA_OBJECTS;
END LOOP;
COMMIT;
END;
/



/*
SQL> @@ "load_test_data.sql"

Table dropped.


Table created.


PL/SQL procedure successfully completed.


Index created.


Table dropped.


Table created.


PL/SQL procedure successfully completed.

SQL>
*/




I am running below query using LEFT OUTER JOIN which is taking 10-15 mins to fetch result set.
Please help me to improve performance of query.


SELECT A.* FROM TST_1 A 
              LEFT OUTER JOIN TST_2 B
              ON (A.OBJECT_ID=B.OBJECT_ID);



Thanks,
Anil MK
Re: LEFT OUTER JOIN of Large Tables - Performance issue [message #650657 is a reply to message #650654] Thu, 28 April 2016 13:51 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Can you explain why you are doing thisand what result you expect?
Also, what do you mean by "improve the performance": the time to run the query to completion, or the time to get the first rows back to the user?
Re: LEFT OUTER JOIN of Large Tables - Performance issue [message #650658 is a reply to message #650654] Thu, 28 April 2016 14:39 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

If you don't select anything from B then you can remove everything from B table in the query and so the outer join.
If you don't the result coming from table A then the index on it is useless but there should be one on table B.

I think the example is a oversimplified one from the actual case. You should post something that is closer to it.

Re: LEFT OUTER JOIN of Large Tables - Performance issue [message #650659 is a reply to message #650658] Thu, 28 April 2016 14:46 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Not quite - if OP really wants the result he is getting, you can't remove B from the query. The join column is not unique, so B is needed to multiply the rows. This is why I asked what result is actually wanted.

However, OP seems to have lost interest.
Re: LEFT OUTER JOIN of Large Tables - Performance issue [message #650661 is a reply to message #650659] Thu, 28 April 2016 21:49 Go to previous messageGo to next message
anil_mk
Messages: 146
Registered: August 2006
Location: Bangalore, India
Senior Member

Hi Watson,

The query which I have given requires columns from both tables, I have just created sample tables. I have same scenario.
I want performance improvement means "reduce time to run the query to completion".

Thanks
Re: LEFT OUTER JOIN of Large Tables - Performance issue [message #650662 is a reply to message #650661] Thu, 28 April 2016 22:00 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Quote:
The query which I have given requires columns from both tables, I have just created sample tables.
So your actual problem is with different tables and a different query. I do not see how tuning the query you have provided would help tune the query that you have not provided.
Re: LEFT OUTER JOIN of Large Tables - Performance issue [message #650663 is a reply to message #650661] Thu, 28 April 2016 22:11 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
anil_mk wrote on Thu, 28 April 2016 19:49
Hi Watson,

The query which I have given requires columns from both tables, I have just created sample tables. I have same scenario.
I want performance improvement means "reduce time to run the query to completion".

Thanks


http://www.orafaq.com/forum/mv/msg/84315/433888/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof
Re: LEFT OUTER JOIN of Large Tables - Performance issue [message #650676 is a reply to message #650661] Fri, 29 April 2016 05:34 Go to previous messageGo to next message
flyboy
Messages: 1903
Registered: November 2006
Senior Member
So, to summarize the input which you provided: you have tables TST_1 with 40 million rows and TST_2 with 30 million rows.

You want to join them on the column which is not unique in either of them, so the result set will contain 12 billion (12*10^9 = 400*30M = 40M*300) rows. I would expect this is going to take some time.

Maybe you should take a step back and redefine the data processing if it leads to such high numbers of rows; as you did not post anything useful about it, I cannot comment more.

By the way, the left join is irrelevant in the posted case as all OBJECT_IDs are present in both tables.
Re: LEFT OUTER JOIN of Large Tables - Performance issue [message #652333 is a reply to message #650676] Tue, 07 June 2016 08:15 Go to previous message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
In the case of not needing one of the tables in the query, the optimizer already knows how to do this. Assuming conditions are correct

1. the table join cannot add or delete rows to the result
2. the query does not need any columns from the table in order to do the query

Then there is a feature as of 10.2.0.2.0 that does Dunsel Join Removal (Oracle calls it Join Elimination).

Usually, it requires constraints, and you would be fetching data from the child table.
Previous Topic: count(*) anomaly
Next Topic: Performance degrade after migration from 11g exadata to 12c
Goto Forum:
  


Current Time: Thu Mar 28 07:15:01 CDT 2024