Home » RDBMS Server » Performance Tuning » Join on index and adding default_where (Forms 11G)
Join on index and adding default_where [message #664956] Tue, 15 August 2017 03:58 Go to next message
Ilonke
Messages: 7
Registered: August 2017
Junior Member
Hi
I have a datablock with Master-Detail join based on an indexed field. I also have radio-buttons with pre-defined filter conditions, by using the default_where property. Upon first record retrieval I have a default where clause (i.e. it is not Set_Block_Property('BLOCKB',DEFAULT_WHERE,''), defined in the PRE-QUERY trigger of datablock. The problem is, Forms seem to build the select statement as follows:

select *
from x
where DEFAULT_WHERE
and MASTER_DETAIL JOIN

This is causing very bad performance as the table (or view in my case) is scanning through major tables. I need Forms to use the JOIN first and the where_clause second, which immediately and exponentially increases the selection performance.

I am fairly new to Oracle Forms and hope above make sense, I searched many forums but did not find any relating to this specifically.

Regards,
Ilonke
Re: Join on index and adding default_where [message #664957 is a reply to message #664956] Tue, 15 August 2017 04:07 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
While you've put this in forms it's really a performance problem so I've moved it.
The order of the where clause doesn't generally make a difference. So what you think you need to fix it is unlikely to help.
You need to post the full query forms is running (you can get this by tracing the session of using get_block_propery(<block name> , last_query)) along with the explain plan. Since there's a view involved the SQL of that, along with the structure and index lists for the underlying tables would also be a good idea.
Re: Join on index and adding default_where [message #664958 is a reply to message #664957] Tue, 15 August 2017 04:16 Go to previous messageGo to next message
Ilonke
Messages: 7
Registered: August 2017
Junior Member
When I run the 2 selections on the db via say sqltools, the order of the where DOES make a significant difference, the right way around returns records immediately using the index, the wrong way round, the selection is first scanning all the records for field2 criteria and THEN goes to the field1 criteria which is already indexed - it takes forever!

I was not asking how to increase the performance of the view or the table structure, but instead wanted to know how to control (if at all possible), how FORMS is packing the selection criteria, i.e. why not use the JOIN first and THEN add the where clause.

[Updated on: Tue, 15 August 2017 04:25]

Report message to a moderator

Re: Join on index and adding default_where [message #664959 is a reply to message #664958] Tue, 15 August 2017 04:27 Go to previous messageGo to next message
Ilonke
Messages: 7
Registered: August 2017
Junior Member
Can you please move this back to FORMS?
Re: Join on index and adding default_where [message #664961 is a reply to message #664959] Tue, 15 August 2017 04:52 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Well I could but I don't believe you can control the order in which forms constructs the where clause anyway. Can you post the query it's actually running
Re: Join on index and adding default_where [message #664964 is a reply to message #664959] Tue, 15 August 2017 05:07 Go to previous messageGo to next message
Ilonke
Messages: 7
Registered: August 2017
Junior Member
Select 1 (Use INDEXED field first (Master-Detail join), DEFAULT_WHERE second)
Plan hash value: 2323405023							
 							
-----------------------------------------------------------------------------------------------------------------							
	 Id  	 Operation                          	 Rows  	 Bytes 	TempSpc	 Cost (%CPU)	 Time     
-----------------------------------------------------------------------------------------------------------------							
	0	 SELECT STATEMENT                   	576	  2278K	       	  1537   (1)	 00:00:10 
	1	  SORT ORDER BY                     	576	  2278K	  4616K	  1537   (1)	 00:00:10 
	2	   VIEW                             	576	  2278K	       	  1127   (1)	 00:00:07 
	3	    SORT UNIQUE                     	576	   206K	       	  1127   (1)	 00:00:07 
	4	     UNION-ALL                      	       	       	       	            	          
	*  5 	      HASH JOIN RIGHT OUTER         	363	76956	       	    17   (0)	 00:00:01 
	*  6 	       INDEX RANGE SCAN             	6	270	       	     2   (0)	 00:00:01 
	7	       NESTED LOOPS OUTER           	363	60621	       	    15   (0)	 00:00:01 
	*  8 	        TABLE ACCESS FULL           	363	44649	       	    11   (0)	 00:00:01 
	9	        TABLE ACCESS BY INDEX ROWID 	1	44	       	     1   (0)	 00:00:01 
	* 10 	         INDEX RANGE SCAN           	1	       	       	     0   (0)	 00:00:01 
	11	      TABLE ACCESS BY INDEX ROWID   	1	68	       	     2   (0)	 00:00:01 
	* 12 	       INDEX UNIQUE SCAN            	1	       	       	     1   (0)	 00:00:01 
	13	      NESTED LOOPS                  	213	28968	       	  1108   (1)	 00:00:07 
	14	       NESTED LOOPS                 	213	28968	       	  1108   (1)	 00:00:07 
	15	        NESTED LOOPS OUTER          	193	15247	       	   697   (0)	 00:00:05 
	16	         TABLE ACCESS BY INDEX ROWID	193	8685	       	   118   (0)	 00:00:01 
	* 17 	          INDEX RANGE SCAN          	193	       	       	     7   (0)	 00:00:01 
	* 18 	         TABLE ACCESS BY INDEX ROWID	1	34	       	     3   (0)	 00:00:01 
	* 19 	          INDEX RANGE SCAN          	1	       	       	     2   (0)	 00:00:01 
	* 20 	        INDEX RANGE SCAN            	1	       	       	     2   (0)	 00:00:01 
	21	       TABLE ACCESS BY INDEX ROWID  	1	57	       	     3   (0)	 00:00:01 
-----------------------------------------------------------------------------------------------------------------
Select 2 (Use DEFAULT_WHERE first and then INDEXED field second (Master-Detail join))
---------------------------------------------------------------------------------------------------------------							
	 Id  	 Operation                         	 Rows  	 Bytes 	TempSpc	 Cost (%CPU)	 Time     
----------------------------------------------------------------------------------------------------------------							
	0	 SELECT STATEMENT                  	    14M	    53G	       	    13M  (1)	 22:34:34 
	1	  SORT ORDER BY                    	    14M	    53G	   107G	    13M  (1)	 22:34:34 
	2	   VIEW                            	    14M	    53G	       	  1047K  (2)	 01:47:01 
	3	    SORT UNIQUE                    	    14M	  3667M	  2084M	  1047K  (2)	 01:47:01 
	4	     UNION-ALL                     	       	       	       	            	          
	*  5 	      HASH JOIN OUTER              	13	2756	       	    14   (0)	 00:00:01 
	6	       NESTED LOOPS OUTER          	13	2171	       	    12   (0)	 00:00:01 
	*  7 	        TABLE ACCESS FULL          	13	1599	       	    11   (0)	 00:00:01 
	8	        TABLE ACCESS BY INDEX ROWID	1	44	       	     1   (0)	 00:00:01 
	*  9 	         INDEX RANGE SCAN          	1	       	       	     0   (0)	 00:00:01 
	* 10 	       INDEX RANGE SCAN            	6	270	       	     2   (0)	 00:00:01 
	11	      TABLE ACCESS BY INDEX ROWID  	1	68	       	     2   (0)	 00:00:01 
	* 12 	       INDEX UNIQUE SCAN           	1	       	       	     1   (0)	 00:00:01 
	* 13 	      HASH JOIN RIGHT OUTER        	    14M	  1833M	  6560K	   688K  (3)	 01:10:23 
	* 14 	       TABLE ACCESS FULL           	   145K	  4843K	       	  4660   (2)	 00:00:29 
	* 15 	       HASH JOIN                   	    14M	  1375M	   930M	   662K  (3)	 01:07:44 
	* 16 	        TABLE ACCESS FULL          	    14M	   768M	       	 32934   (3)	 00:03:22 
	17	        TABLE ACCESS FULL          	   101M	  4351M	       	   540K  (3)	 00:55:12 
----------------------------------------------------------------------------------------------------------------							

--moderator update: added [code] tags, please do so yourself in future posts.

[Updated on: Tue, 15 August 2017 05:16] by Moderator

Report message to a moderator

Re: Join on index and adding default_where [message #664966 is a reply to message #664964] Tue, 15 August 2017 05:10 Go to previous messageGo to next message
Ilonke
Messages: 7
Registered: August 2017
Junior Member
From above information, you can see the order of the where clause makes a difference (CookieMonster Statement: The order of the where clause doesn't generally make a difference.)

I do not have access to alter these table structures, indexes, etc. Therefore please assume that in the current state, I need to establish whether Oracle FORMS has an ability to use the JOIN first and foremost and then add/build the DEFAULT_WHERE content.

[Updated on: Tue, 15 August 2017 05:10]

Report message to a moderator

Re: Join on index and adding default_where [message #664967 is a reply to message #664964] Tue, 15 August 2017 05:10 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
We need the actual selects as well.
Re: Join on index and adding default_where [message #664969 is a reply to message #664967] Tue, 15 August 2017 05:14 Go to previous messageGo to next message
Ilonke
Messages: 7
Registered: August 2017
Junior Member
Does this help? CLIENT_NO is the indexed field.

Selection 1 - Correct
SELECT A,B,C,D,...
FROM VIEW
WHERE (CLIENT_NO=1234)
AND transaction_type = 'XXXX' or (transaction_type != 'XXXX' and return_code = '0')
order by effective_date DESC;

Selection 2 - Incorrect
SELECT A,B,C,D,...
FROM VIEW
WHERE transaction_type = 'XXXX' or (transaction_type != 'XXXX' and return_code = '0')
AND (CLIENT_NO=1234)
order by effective_date DESC;
Re: Join on index and adding default_where [message #664970 is a reply to message #664967] Tue, 15 August 2017 05:14 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
You have no control over where oracle adds the stuff from the datablock relationship to the where clause.
But you never know - if you post all the required information we may be able to spot a workaround you can implement.
Re: Join on index and adding default_where [message #664971 is a reply to message #664970] Tue, 15 August 2017 05:21 Go to previous messageGo to next message
Ilonke
Messages: 7
Registered: August 2017
Junior Member
Thanks Cookiemonster, let me try some other work around(s) first!
Re: Join on index and adding default_where [message #664972 is a reply to message #664969] Tue, 15 August 2017 05:22 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
I can assure you that your two queries will run identically. Since the earliest release of the Cost Based Optimizer, the ordering of conditions in your predicates has no impact on the execution plans. The only exception would be if you were using the Rule Based Otpmizer, which you are not doing or the plan would not show cost calculations.
Your queries must be different in other ways too.

Trust me - I'm a DBA.
Re: Join on index and adding default_where [message #664973 is a reply to message #664970] Tue, 15 August 2017 05:27 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
You've fallen prey to AND/OR precedence.
1st query:
SQL> with data as (select 1 client_no, 'XXX' type, 1 ret from dual
  2  union all select 1, 'BBB', 0 from dual
  3  union all select 2, 'XXX', 1 from dual
  4  )
  5  select *
  6  from data
  7  where client_no = 1
  8  and type = 'XXX' or (type != 'XXX' and ret = 0);
 
 CLIENT_NO TYPE        RET
---------- ---- ----------
         1 XXX           1
         1 BBB           0
 
2nd query:
SQL> with data as (select 1 client_no, 'XXX' type, 1 ret from dual
  2  union all select 1, 'BBB', 0 from dual
  3  union all select 2, 'XXX', 1 from dual
  4  )
  5  select *
  6  from data
  7  where type = 'XXX' or (type != 'XXX' and ret = 0)
  8  and client_no = 1;
 
 CLIENT_NO TYPE        RET
---------- ---- ----------
         1 XXX           1
         1 BBB           0
         2 XXX           1
 
SQL> 
See we've got an extra line there with a different client_no - you always need to use brackets to make sure the OR isn't applied to conditions that must always be true. Like this:
SQL> with data as (select 1 client_no, 'XXX' type, 1 ret from dual
  2  union all select 1, 'BBB', 0 from dual
  3  union all select 2, 'XXX', 1 from dual
  4  )
  5  select *
  6  from data
  7  where (type = 'XXX' or (type != 'XXX' and ret = 0))
  8  and client_no = 1;
 
 CLIENT_NO TYPE        RET
---------- ---- ----------
         1 XXX           1
         1 BBB           0
 
SQL> 

It was taking ages because it was retrieving data you don't want.
You need to include the extra brackets in the default_where
Re: Join on index and adding default_where [message #664975 is a reply to message #664973] Tue, 15 August 2017 05:47 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Oh my gosh. I fell for it too.
Re: Join on index and adding default_where [message #664979 is a reply to message #664975] Tue, 15 August 2017 06:47 Go to previous message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Since most people use brackets round OR it rarely comes up so is easy to forget.
And when you're doing something like set a default_where in forms it's easy to overlook the implications of other stuff getting added.
OP set 'type = 'XXX' or (type != 'XXX' and ret = 0)', which would be fine if that was the entirety of the where clause.
Since it's not it needs to be
'(type = 'XXX' or (type != 'XXX' and ret = 0))'
Previous Topic: Performance issue to Update data
Next Topic: sql need tuning - high cpu_time and buffer gets
Goto Forum:
  


Current Time: Fri Mar 29 09:41:17 CDT 2024