| Join on index and adding default_where [message #664956] |
Tue, 15 August 2017 03:58  |
 |
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  |
cookiemonster
Messages: 13966
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 |
|
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 #664964 is a reply to message #664959] |
Tue, 15 August 2017 05:07 |
|
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
-----------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------
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 |
|
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 #664970 is a reply to message #664967] |
Tue, 15 August 2017 05:14 |
cookiemonster
Messages: 13966
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 #664972 is a reply to message #664969] |
Tue, 15 August 2017 05:22 |
John Watson
Messages: 8966
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 |
cookiemonster
Messages: 13966
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
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>
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 #664979 is a reply to message #664975] |
Tue, 15 August 2017 06:47 |
cookiemonster
Messages: 13966
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))'
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
