the difference in the code from the original to the modified is in the where clause on the apax2.party_key line where it's calling a sys_guid as a value.
My problem is that in the original code the statement takes 10 seconds to happen and the modified takes less than 1 second to happen. I get similar results if I wrap the apax2.party_key with to_char().. Can anyone explain what the ||'' does to the value of apax2.party_key to make this query change speed?
The value of apax2.party_key is a varchar2(32) field that we are using with sys_guid.
Here is my original code: - SELECT lic.lcsn_num,
-
apnt.apntd_nm,
-
apnt.arms_apntmnt_key,
-
apnt.effective_dt,
-
a.agncy_cd,
-
apnt.state_prov_cd,
-
aast.apntmnt_status_tp_desc,
-
aat.arms_apntmnt_tp_desc,
-
wco.abbreviated_nm
-
FROM arms_licensee lice,
-
arms_appointment_status_type aast,
-
arms_apntmnt_type aat,
-
arms_license lic,
-
arms_appointment apnt,
-
arms_agency a,
-
ORGANIZATION wco,
-
arms_party_agncy_xref apax1,
-
arms_party_agncy_xref apax2
-
WHERE apnt.valid_ind = 'Y'
-
AND aast.state_apntmnt_ind = 'Y'
-
AND a.agncy_cd = '5400106'
-
AND apax2.party_key = '2A3E09682084497CE044001083FDAD1E'
-
AND lice.licensee_key = lic.licensee_key
-
AND lic.arms_lcsn_key = apnt.arms_lcsn_key
-
AND apnt.arms_apntmnt_status_tp_cd = aast.arms_apntmnt_status_tp_cd
-
AND apnt.arms_apntmnt_tp_cd = aat.arms_apntmnt_tp_cd
-
AND apnt.agcy_xref_key = apax1.arms_party_agcy_xref_key
-
AND apnt.wco_xref_key = apax2.arms_party_agcy_xref_key
-
AND apax1.arms_agncy_key = a.arms_agncy_key
-
AND apax2.party_key = wco.org_key
Here is my modified code: -
SELECT lic.lcsn_num,
-
apnt.apntd_nm,
-
apnt.arms_apntmnt_key,
-
apnt.effective_dt,
-
a.agncy_cd,
-
apnt.state_prov_cd,
-
aast.apntmnt_status_tp_desc,
-
aat.arms_apntmnt_tp_desc,
-
wco.abbreviated_nm
-
FROM arms_licensee lice,
-
arms_appointment_status_type aast,
-
arms_apntmnt_type aat,
-
arms_license lic,
-
arms_appointment apnt,
-
arms_agency a,
-
ORGANIZATION wco,
-
arms_party_agncy_xref apax1,
-
arms_party_agncy_xref apax2
-
WHERE apnt.valid_ind = 'Y'
-
AND aast.state_apntmnt_ind = 'Y'
-
AND a.agncy_cd = '5400106'
-
AND apax2.party_key ||'' = '2A3E09682084497CE044001083FDAD1E'
-
AND lice.licensee_key = lic.licensee_key
-
AND lic.arms_lcsn_key = apnt.arms_lcsn_key
-
AND apnt.arms_apntmnt_status_tp_cd = aast.arms_apntmnt_status_tp_cd
-
AND apnt.arms_apntmnt_tp_cd = aat.arms_apntmnt_tp_cd
-
AND apnt.agcy_xref_key = apax1.arms_party_agcy_xref_key
-
AND apnt.wco_xref_key = apax2.arms_party_agcy_xref_key
-
AND apax1.arms_agncy_key = a.arms_agncy_key
-
AND apax2.party_key = wco.org_key
1  2107 
Is there any index on the column ?
Sign in to post your reply or Sign up for a free account.
Similar topics | |
by: hemal |
last post by:
I came across a very strange situation at work. There is an order of
magnitude difference in execution time for the following two queries
(10 v/s ~130 msec):
select count(*) from table_name where column_name = 'value' or 1 = 0
select count(*) from table_name where column_name = 'value'
I do not want to go into the reason why the redundent condition
exists, the example is representative of the real query where it
|
by: Christian |
last post by:
HI,
I have a function that is used to constrain a query:
Select COl1, Col2
From MyTable
WHERE col1 = ...
AND col2 = ...
And MyFunction(col1) = ...
My problem is that MyFunction is executed as many times that there are
|
by: Gerald Aichholzer |
last post by:
Hello,
I need to specify the following attribute in an xhtml-file
containing TAL templates:
<div tal:attributes="onMouseOver concat('func(',xyz,')')">
which results in
<div onMouseOver=func( )>
|
by: William M. Miller |
last post by:
We've recently encountered a bug in the optimization of
floating point computations inside loops. To summarize, the
optimizer reorders floating point operations in a fashion that
is not permitted by the C++ Standard; even worse, when the
optimizer unrolls a loop, the operations are ordered
differently in different iterations. (This occurs in version
13.10.3077.)
Here's a sample program simplified from production code:
|
by: Hannes Dorbath |
last post by:
First - I'm not sure whether this should go to .bugs, .hackers oder
..sql, so I posted here :/
The query and the corresponding EXPLAIN is at
http://hannes.imos.net/query.txt
I'd like to use the column q.replaced_serials for multiple calculations
in the SELECT clause, but every time it is referenced there in some way
the whole query in the FROM clause returning q is executed again.
| | |
by: Venkatesh Subbaramu |
last post by:
Hi Expert DBAs,
I am facing an issue with a query, can anyone help me through?
I have a Query:
UPDATE VMUS.MSG_MSTR a SET MSG_STA_ID =5
WHERE a.PROC_ID =2 AND
a.file_type_id =1 AND
MSG_STA_ID IN (1, 3) AND
DATA_REQ_DT IS NOT NULL
We have 2 indexes for the table:
Indname Column names
|
by: boa sema |
last post by:
Way back when, and at least in version 7 IIRC, the query optimizer gave
up when the where clause in a statement contained more than 4 search
conditions.
Does anyone know if such a limitation still exist in MS SQL 2005? The
BOL seems to be silent on the issue.
Boa
|
by: MeBuggyYouJane |
last post by:
Looked everywhere (Date, Celko, Elmasri, this ng, c.d.t, etc), but can't
find any discussion of the following:
Proof that these predicates will (or not) return the same rows
where (col1 concat col2 concat col3 ...) =<SomeSuitableString>
where col1 =itsPartOfSomeSuitableString and
col2 =itsPartOfSomeSuitableString and
col3 =itsPartOfSomeSuitableString ...
|
by: Henry J. |
last post by:
I have a composite index on two columns in a table. However, the
index is not used in a query that restricts the 2nd column to a
constant. If both columns are linked with columns in other join
tables, the index will be used.
To illustrate it with an example, I have a query like this:
select s.ticker, p.quantity
from stock s, positions p
where s.type_id = 4
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| | |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| | |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
