Home » RDBMS Server » Performance Tuning » Help reading TOAD Explain Plan
| Help reading TOAD Explain Plan [message #199685] |
Wed, 25 October 2006 18:41  |
bogey
Messages: 53
Registered: February 2006
|
Member |
|
|
|
I have a query that continues to show up in EM GC as a problem. Its running to long. Can someone help by taking a look at this output from TOAD's explain plan.
|
|
|
|
| Re: Help reading TOAD Explain Plan [message #199698 is a reply to message #199685] |
Wed, 25 October 2006 21:26  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Post the SQL, the number of rows in each table, and the expected number of rows returned. If there are constraining WHERE filters in the SQL, identify how constraining they are (eg. STATUS = 'OPEN' : 25% of table)
Ross Leishman
|
|
|
|
|
|
|
|
|
|
| Re: Help reading TOAD Explain Plan [message #199774 is a reply to message #199744] |
Thu, 26 October 2006 03:22 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
OK, this discussion is about as old as Oracle, but I'll sum it up here because I'm passionate about it.
Consider an unconstrained SELECT * FROM tab on a large table. It retrieves every column of every row, so what could be faster than reading every row top to bottom, right?
Now, what if we SELECT * FROM tab WHERE pk >= chr(1)? We're still reading every row, but we have the option of performing an unbounded range scan on the index. So what would happen if we did that? We'd read every row from the index AND every row from the table? Thats more IO than the full table scan, so its gotta be slower (and it is!).
OK, so now we SELECT * FROM tab WHERE pk >= 10000. Now we're NOT reading every row from the index, and also not reading every row from the table. So is it faster to use the index?
Well, clearly if there is only 1 row >= 10000, the index will be heaps faster because it only reads 1 row. But if there is only 1 row < 10000, then the FTS will be faster because 1 row is not enough of a discount to offset all of those index blocks we have to read over and above the FTS.
So there's a break-even point somewhere in the middle. A percentage of the table where - if we read MORE than that proportion it will be faster to do a FTS, if we read LESS than that proportion it will be faster to access via index.
So what is the break-even point? What is this golden percentage?
Logically, you could work it out based on IO. If an index scan requires 100 blocks from the index and 500 blocks from the table, then it will be faster than FTS if the table is bigger than 600 blocks, right?
Ohhhhhh, soooooo wrong!!!
Think about an FTS, you start at the top of the table and read down to the bottom. You read every block only once, and you read them in order (not much work for the disk head to move to the next block). Also, when Oracle interfaces with the disk, it will say something like "I'm going to be reading a fair bit here, so not only read the block I asked for, but get the next few while you're at it". the initialisation parameter DB_MULTI_BLOCK_READ_COUNT controls this.
As a metaphor, think of picking up all of the leaves on your front lawn. You'd work from one side to the other, and collect all of those within reach each time you bent down.
Now think about an Index Range Scan or Full Index Scan. An index is sorted, and it points to UNSORTED rows in the table by rowid. So when you need to read lots of rows via the index, it's really fast to read the index - but the table rows you read will be all over the place. The first row in the index might point to the 100th row in the table, next to the 150th, then the 3rd. The poor disk has to move all over the place.
But it gets worse. A block holds many rows, but if you are reading them in order of the index, you only need one row out of each block that you read. If your index scan comes to a second row from that block block later on, you can't guarantee that it will still be cached - you may have to read the same row all over again from disk. If you can fit 50 rows in a block, it is technically possible to set up an Index Range Scan that reads the table 50 times over.
As a metaphore, think of picking up all of the leaves on your front lawn ... in order of smallest to largest!
Oracle's CBO knows all of this. If it thinks you are going to need a significant proportion of the table, it will FTS the table. If that table is part of a join or a sub-query, then it will use an appropriate join method where possible (Hash or Sort Merge).
So, back to the original question: what's the break-even point? It varies for every table, every environment, at different times of day. As a guide, less than 1% of a table will almost always be faster to read via an index, and more than 10% will almost always be faster to read with FTS. Anything in-between is NOT "doesn't matter", performance can vary wildly - you have to benchmark.
I wasn't intending it to be this long, and I barely scratched the surface. If anyone's still with me at this point, and - God forbid - still interested, read the sections on High Volume SQL in my Tuning Guide.
Ross Leishman
|
|
|
|
|
|
| Re: Help reading TOAD Explain Plan [message #200077 is a reply to message #199698] |
Fri, 27 October 2006 15:05 |
bogey
Messages: 53
Registered: February 2006
|
Member |
|
|
Thanks for eveyone getting back to me. Like I said, this query has been showing up in EM GC as an alert so any help is much appreciated.
I've added some more output from TOAD.
Row Count
.FRIP_USER = 3345
.UNIT - = 161
.FACULTY_INFO = 3344
.SELECTIONS = 25829
.ALIAS_TERM = 502094
SELECT DISTINCT frip_user.ID, lastname, firstname, mi, suffix, department,
division, school, email, alias, personalwebsite,
schoolwebsite, website, divisionwebsite, isemailvisible,
iswebsiteverified, s3.selection, s3.majororder, s3.ID selid
FROM frip.frip_user,
frip.unit,
frip.faculty_info,
frip.selections s1,
frip.selections s2,
frip.selections s3,
frip.alias_term
WHERE frip_user.id_unit = unit.ID
AND frip_user.ID = faculty_info.id_user
AND frip_user.ID = s2.id_user
AND frip_user.ID = s3.id_user
AND frip_user.ID = alias_term.id_user
AND s1.ID = 8891
AND s1.selection = s2.selection
AND issearchable = 'yes'
-
Attachment: TOAD_PLAN.jpg
(Size: 112.03KB, Downloaded 1441 times)
[Updated on: Fri, 27 October 2006 15:09] Report message to a moderator |
|
|
|
| Re: Help reading TOAD Explain Plan [message #200098 is a reply to message #199698] |
Fri, 27 October 2006 19:09 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
| rleishman wrote on Thu, 26 October 2006 12:26 | Post the SQL, the number of rows in each table, and the expected number of rows returned. If there are constraining WHERE filters in the SQL, identify how constraining they are (eg. STATUS = 'OPEN' : 25% of table)
Ross Leishman
|
Still not enoungh information.
How many rows do you expect it to return?
How constraining is AND s1.ID = 8891?
How constraining is AND issearchable = 'yes'? And which table does it come from?
Ross Leishman
|
|
|
|
| Re: Help reading TOAD Explain Plan [message #200142 is a reply to message #200098] |
Sat, 28 October 2006 09:13 |
bogey
Messages: 53
Registered: February 2006
|
Member |
|
|
issearchable is from the faculty_info table. There could be 3000 rows returned from the query. Remember, I didn't write this query.
| rleishman wrote on Fri, 27 October 2006 19:09 | | rleishman wrote on Thu, 26 October 2006 12:26 | Post the SQL, the number of rows in each table, and the expected number of rows returned. If there are constraining WHERE filters in the SQL, identify how constraining they are (eg. STATUS = 'OPEN' : 25% of table)
Ross Leishman
|
Still not enoungh information.
How many rows do you expect it to return?
How constraining is AND s1.ID = 8891?
How constraining is AND issearchable = 'yes'? And which table does it come from?
Ross Leishman
|
|
|
|
|
| Re: Help reading TOAD Explain Plan [message #200212 is a reply to message #200098] |
Sun, 29 October 2006 05:50 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Still haven't answered all my questions  , but I'm a bit pshychic, so I'll make do. 
So, about 3000 rows when you run it. I wonder how many if you take out the DISTINCT? Lots more I'm guessing.
This is what I call LAZY SQL. 
Join in a few table, and "Oops, thats too many rows, guess I'll throw in a DISTINCT. Hmmm, that's better; now if only it was fast..."
The query starts off with a unique scan on s1 - good start. Then it picks up s2: all SELECTIONS with the same SELECTION as in the S1 row. We can only imagine there are several. Then we pick up all SELECTIONS with the same USER as s2.
Problem here: we don't know that USER is unique in the s1+s2 result set. It might have returned 100 rows, but only 10 users. If each of those 10 users have another 50 selections, then the result is 100x50 = 5000 rows, not 10x50 = 500 rows. ie. we could be picking up the same row many times.
Then for all of these rows, we pick up USER and FACULTY info. What's not clear to me is where the ALIAS_TERM table comes in. The columns in the SELECT list are not all prefixed, so I cannot tell if any rows are being selected from it. But the explain plan seems to indicate that there are 143 rows in this table per USER, so that final join just multiplied the size of the result set by about 143.
Your problem - if its not already clear - is that you are joining on non-unique keys and blowing out the size of the result set. This will kill the query even though the tables are relatively small. When you lookup a table on a non-unique key with 10 rows per key, you get 10 rows. When you JOIN to a table on the same key, you get 10 rows per row in the join table. And your query is doing this several times.
It seems that your query wants details of a bunch of users with their selections: ie.
SELECT frip_user.ID, lastname, firstname, mi, suffix, department,
division, school, email, alias, personalwebsite,
schoolwebsite, website, divisionwebsite, isemailvisible,
iswebsiteverified, s.selection, s.majororder, s.ID selid
FROM frip.frip_user,
frip.unit,
frip.faculty_info,
frip.selections s
WHERE frip_user.id_unit = unit.ID
AND frip_user.ID = faculty_info.id_user
AND frip_user.ID = s.id_user
AND faculty_info.issearchable = 'yes'
Right, but that gets EVERY user. We don't want every user. We only want those users who have the same SELECTION as a user with a particular SELECTION.
SELECT frip_user.ID, lastname, firstname, mi, suffix, department,
division, school, email, alias, personalwebsite,
schoolwebsite, website, divisionwebsite, isemailvisible,
iswebsiteverified, s.selection, s.majororder, s.ID selid
FROM frip.frip_user,
frip.unit,
frip.faculty_info,
frip.selections s,
frip.alias_term
WHERE frip_user.id_unit = unit.ID
AND frip_user.ID = faculty_info.id_user
AND frip_user.ID = s.id_user
AND faculty_info.issearchable = 'yes'
AND frip_user.ID IN (
SELECT id_user
FROM selections
WHERE selection IN (
SELECT selection
FROM selection
WHERE id = 8891
)
)
Of course I could be wrong. I have no way of knowing what the query is actually supposed to return, so this is a guess. I still can't work out how ALIAS_TERM fits in, so I'll leave that to you.
If this is right and returns around 3000 SELECTIONS, then - despite all my preaching to Littlefoot - indexed access will be good. You may need and index on selections.selection unless you already have one.
Ross Leishman
|
|
|
|
| Re: Help reading TOAD Explain Plan [message #272190 is a reply to message #199685] |
Thu, 04 October 2007 03:45 |
lm_suresh
Messages: 24
Registered: September 2007
Location: Chennai,India
|
Junior Member |
|
|
Can someone please guide me regarding the explain plan and also to tune this query.
Update PS_NAMES a set a.NAME_FORMAL= (select b.new_NAME_FORMAL from
HX_NAMES_STG b
where
b.old_NAME_FORMAL = a.NAME_FORMAL and b.old_NAME_DISPLAY = a.NAME_DISPLAY
and b.old_LAST_NAME_PREF_NLD = a.LAST_NAME_PREF_NLD and
b.old_PARTNER_ROY_PREFIX = a.PARTNER_ROY_PREFIX and b.old_PARTNER_LAST_NAME
= a.PARTNER_LAST_NAME and b.old_PREF_FIRST_NAME = a.PREF_FIRST_NAME and
b.old_NAME_AC = a.NAME_AC and b.old_SECOND_LAST_SRCH = a.SECOND_LAST_SRCH
and b.old_SECOND_LAST_NAME = a.SECOND_LAST_NAME and b.old_MIDDLE_NAME =
a.MIDDLE_NAME and b.old_FIRST_NAME = a.FIRST_NAME and b.old_LAST_NAME =
a.LAST_NAME and b.old_FIRST_NAME_SRCH = a.FIRST_NAME_SRCH and
b.old_LAST_NAME_SRCH = a.LAST_NAME_SRCH and b.old_NAME_TITLE = a.NAME_TITLE
and b.old_NAME_ROYAL_SUFFIX = a.NAME_ROYAL_SUFFIX and
b.old_NAME_ROYAL_PREFIX = a.NAME_ROYAL_PREFIX and b.old_NAME_SUFFIX =
a.NAME_SUFFIX and b.old_NAME_PREFIX = a.NAME_PREFIX and b.old_NAME_INITIALS
= a.NAME_INITIALS and b.old_NAME = a.NAME and b.old_COUNTRY_NM_FORMAT =
a.COUNTRY_NM_FORMAT and rownum<:"SYS_B_00") , a.NAME_DISPLAY= (select
b.new_NAME_DISPLAY from HX_NAMES_STG b where b.old_NAME_FORMAL =
a.NAME_FORMAL and b.old_NAME_DISPLAY = a.NAME_DISPLAY and
b.old_LAST_NAME_PREF_NLD = a.LAST_NAME_PREF_NLD and
b.old_PARTNER_ROY_PREFIX = a.PARTNER_ROY_PREFIX and b.old_PARTNER_LAST_NAME
= a.PARTNER_LAST_NAME and b.old_PREF_FIRST_NAME = a.PREF_FIRST_NAME and
b.old_NAME_AC = a.NAME_AC and b.old_SECOND_LAST_SRCH = a.SECOND_LAST_SRCH
and b.old_SECOND_LAST_NAME = a.SECOND_LAST_NAME and b.old_MIDDLE_NAME =
a.MIDDLE_NAME and b.old_FIRST_NAME = a.FIRST_NAME and b.old_LAST_NAME =
a.LAST_NAME and b.old_FIRST_NAME_SRCH = a.FIRST_NAME_SRCH and
b.old_LAST_NAME_SRCH = a.LAST_NAME_SRCH and b.old_NAME_TITLE = a.NAME_TITLE
and b.old_NAME_ROYAL_SUFFIX = a.NAME_ROYAL_SUFFIX and
b.old_NAME_ROYAL_PREFIX = a.NAME_ROYAL_PREFIX and b.old_NAME_SUFFIX =
a.NAME_SUFFIX and b.old_NAME_PREFIX = a.NAME_PREFIX and b.old_NAME_INITIALS
= a.NAME_INITIALS and b.old_NAME = a.NAME and b.old_COUNTRY_NM_FORMAT =
a.COUNTRY_NM_FORMAT and rownum<:"SYS_B_01") , a.LAST_NAME_PREF_NLD= (select
b.new_LAST_NAME_PREF_NLD from HX_NAMES_STG b where b.old_NAME_FORMAL =
a.NAME_FORMAL and b.old_NAME_DISPLAY = a.NAME_DISPLAY and
b.old_LAST_NAME_PREF_NLD = a.LAST_NAME_PREF_NLD and
b.old_PARTNER_ROY_PREFIX = a.PARTNER_ROY_PREFIX and b.old_PARTNER_LAST_NAME
= a.PARTNER_LAST_NAME and b.old_PREF_FIRST_NAME = a.PREF_FIRST_NAME and
b.old_NAME_AC = a.NAME_AC and b.old_SECOND_LAST_SRCH = a.SECOND_LAST_SRCH
and b.old_SECOND_LAST_NAME = a.SECOND_LAST_NAME and b.old_MIDDLE_NAME =
a.MIDDLE_NAME and b.old_FIRST_NAME = a.FIRST_NAME and b.old_LAST_NAME =
a.LAST_NAME and b.old_FIRST_NAME_SRCH = a.FIRST_NAME_SRCH and
b.old_LAST_NAME_SRCH = a.LAST_NAME_SRCH and b.old_NAME_TITLE = a.NAME_TITLE
and b.old_NAME_ROYAL_SUFFIX = a.NAME_ROYAL_SUFFIX and
b.old_NAME_ROYAL_PREFIX = a.NAME_ROYAL_PREFIX and b.old_NAME_SUFFIX =
a.NAME_SUFFIX and b.old_NAME_PREFIX = a.NAME_PREFIX and b.old_NAME_INITIALS
= a.NAME_INITIALS and b.old_NAME = a.NAME and b.old_COUNTRY_NM_FORMAT =
a.COUNTRY_NM_FORMAT and rownum<:"SYS_B_02") , a.PARTNER_ROY_PREFIX= (select
b.new_PARTNER_ROY_PREFIX from HX_NAMES_STG b where b.old_NAME_FORMAL =
a.NAME_FORMAL and b.old_NAME_DISPLAY = a.NAME_DISPLAY and
b.old_LAST_NAME_PREF_NLD = a.LAST_NAME_PREF_NLD and
b.old_PARTNER_ROY_PREFIX = a.PARTNER_ROY_PREFIX and b.old_PARTNER_LAST_NAME
= a.PARTNER_LAST_NAME and b.old_PREF_FIRST_NAME = a.PREF_FIRST_NAME and
b.old_NAME_AC = a.NAME_AC and b.old_SECOND_LAST_SRCH = a.SECOND_LAST_SRCH
and b.old_SECOND_LAST_NAME = a.SECOND_LAST_NAME and b.old_MIDDLE_NAME =
a.MIDDLE_NAME and b.old_FIRST_NAME = a.FIRST_NAME and b.old_LAST_NAME =
a.LAST_NAME and b.old_FIRST_NAME_SRCH = a.FIRST_NAME_SRCH and
b.old_LAST_NAME_SRCH = a.LAST_NAME_SRCH and b.old_NAME_TITLE = a.NAME_TITLE
and b.old_NAME_ROYAL_SUFFIX = a.NAME_ROYAL_SUFFIX and
b.old_NAME_ROYAL_PREFIX = a.NAME_ROYAL_PREFIX and b.old_NAME_SUFFIX =
a.NAME_SUFFIX and b.old_NAME_PREFIX = a.NAME_PREFIX and b.old_NAME_INITIALS
= a.NAME_INITIALS and b.old_NAME = a.NAME and b.old_COUNTRY_NM_FORMAT =
a.COUNTRY_NM_FORMAT and rownum<:"SYS_B_03") , a.PARTNER_LAST_NAME= (select
b.new_PARTNER_LAST_NAME from HX_NAMES_STG b where b.old_NAME_FORMAL =
a.NAME_FORMAL and b.old_NAME_DISPLAY = a.NAME_DISPLAY and
b.old_LAST_NAME_PREF_NLD = a.LAST_NAME_PREF_NLD and
b.old_PARTNER_ROY_PREFIX = a.PARTNER_ROY_PREFIX and b.old_PARTNER_LAST_NAME
= a.PARTNER_LAST_NAME and b.old_PREF_FIRST_NAME = a.PREF_FIRST_NAME and
b.old_NAME_AC = a.NAME_AC and b.old_SECOND_LAST_SRCH = a.SECOND_LAST_SRCH
and b.old_SECOND_LAST_NAME = a.SECOND_LAST_NAME and b.old_MIDDLE_NAME =
a.MIDDLE_NAME and b.old_FIRST_NAME = a.FIRST_NAME and b.old_LAST_NAME =
a.LAST_NAME and b.old_FIRST_NAME_SRCH = a.FIRST_NAME_SRCH and
b.old_LAST_NAME_SRCH = a.LAST_NAME_SRCH and b.old_NAME_TITLE = a.NAME_TITLE
and b.old_NAME_ROYAL_SUFFIX = a.NAME_ROYAL_SUFFIX and
b.old_NAME_ROYAL_PREFIX = a.NAME_ROYAL_PREFIX and b.old_NAME_SUFFIX =
a.NAME_SUFFIX and b.old_NAME_PREFIX = a.NAME_PREFIX and b.old_NAME_INITIALS
= a.NAME_INITIALS and b.old_NAME = a.NAME and b.old_COUNTRY_NM_FORMAT =
a.COUNTRY_NM_FORMAT and rownum<:"SYS_B_04") , a.PREF_FIRST_NAME= (select
b.new_PREF_FIRST_NAME from HX_NAMES_STG b where b.old_NAME_FORMAL =
a.NAME_FORMAL and b.old_NAME_DISPLAY = a.NAME_DISPLAY and
b.old_LAST_NAME_PREF_NLD = a.LAST_NAME_PREF_NLD and
b.old_PARTNER_ROY_PREFIX = a.PARTNER_ROY_PREFIX and b.old_PARTNER_LAST_NAME
= a.PARTNER_LAST_NAME and b.old_PREF_FIRST_NAME = a.PREF_FIRST_NAME and
b.old_NAME_AC = a.NAME_AC and b.old_SECOND_LAST_SRCH = a.SECOND_LAST_SRCH
and b.old_SECOND_LAST_NAME = a.SECOND_LAST_NAME and b.old_MIDDLE_NAME =
a.MIDDLE_NAME and b.old_FIRST_NAME = a.FIRST_NAME and b.old_LAST_NAME =
a.LAST_NAME and b.old_FIRST_NAME_SRCH = a.FIRST_NAME_SRCH and
b.old_LAST_NAME_SRCH = a.LAST_NAME_SRCH and b.old_NAME_TITLE = a.NAME_TITLE
and b.old_NAME_ROYAL_SUFFIX = a.NAME_ROYAL_SUFFIX and
b.old_NAME_ROYAL_PREFIX = a.NAME_ROYAL_PREFIX and b.old_NAME_SUFFIX =
a.NAME_SUFFIX and b.old_NAME_PREFIX = a.NAME_PREFIX and b.old_NAME_INITIALS
= a.NAME_INITIALS and b.old_NAME = a.NAME and b.old_COUNTRY_NM_FORMAT =
a.COUNTRY_NM_FORMAT and rownum<:"SYS_B_05") , a.NAME_AC= (select
b.new_NAME_AC from HX_NAMES_STG b where b.old_NAME_FORMAL = a.NAME_FORMAL
and b.old_NAME_DISPLAY = a.NAME_DISPLAY and b.old_LAST_NAME_PREF_NLD =
a.LAST_NAME_PREF_NLD and b.old_PARTNER_ROY_PREFIX = a.PARTNER_ROY_PREFIX
and b.old_PARTNER_LAST_NAME = a.PARTNER_LAST_NAME and b.old_PREF_FIRST_NAME
= a.PREF_FIRST_NAME and b.old_NAME_AC = a.NAME_AC and
b.old_SECOND_LAST_SRCH = a.SECOND_LAST_SRCH and b.old_SECOND_LAST_NAME =
a.SECOND_LAST_NAME and b.old_MIDDLE_NAME = a.MIDDLE_NAME and
b.old_FIRST_NAME = a.FIRST_NAME and b.old_LAST_NAME = a.LAST_NAME and
b.old_FIRST_NAME_SRCH = a.FIRST_NAME_SRCH and b.old_LAST_NAME_SRCH =
a.LAST_NAME_SRCH and b.old_NAME_TITLE = a.NAME_TITLE and
b.old_NAME_ROYAL_SUFFIX = a.NAME_ROYAL_SUFFIX and b.old_NAME_ROYAL_PREFIX =
a.NAME_ROYAL_PREFIX and b.old_NAME_SUFFIX = a.NAME_SUFFIX and
b.old_NAME_PREFIX = a.NAME_PREFIX and b.old_NAME_INITIALS = a.NAME_INITIALS
and b.old_NAME = a.NAME and b.old_COUNTRY_NM_FORMAT = a.COUNTRY_NM_FORMAT
and rownum<:"SYS_B_06") , a.SECOND_LAST_SRCH= (select
b.new_SECOND_LAST_SRCH from HX_NAMES_STG b where b.old_NAME_FORMAL =
a.NAME_FORMAL and b.old_NAME_DISPLAY = a.NAME_DISPLAY and
b.old_LAST_NAME_PREF_NLD = a.LAST_NAME_PREF_NLD and
b.old_PARTNER_ROY_PREFIX = a.PARTNER_ROY_PREFIX and b.old_PARTNER_LAST_NAME
= a.PARTNER_LAST_NAME and b.old_PREF_FIRST_NAME = a.PREF_FIRST_NAME and
b.old_NAME_AC = a.NAME_AC and b.old_SECOND_LAST_SRCH = a.SECOND_LAST_SRCH
and b.old_SECOND_LAST_NAME = a.SECOND_LAST_NAME and b.old_MIDDLE_NAME =
a.MIDDLE_NAME and b.old_FIRST_NAME = a.FIRST_NAME and b.old_LAST_NAME =
a.LAST_NAME and b.old_FIRST_NAME_SRCH = a.FIRST_NAME_SRCH and
b.old_LAST_NAME_SRCH = a.LAST_NAME_SRCH and b.old_NAME_TITLE = a.NAME_TITLE
and b.old_NAME_ROYAL_SUFFIX = a.NAME_ROYAL_SUFFIX and
b.old_NAME_ROYAL_PREFIX = a.NAME_ROYAL_PREFIX and b.old_NAME_SUFFIX =
a.NAME_SUFFIX and b.old_NAME_PREFIX = a.NAME_PREFIX and b.old_NAME_INITIALS
= a.NAME_INITIALS and b.old_NAME = a.NAME and b.old_COUNTRY_NM_FORMAT =
a.COUNTRY_NM_FORMAT and rownum<:"SYS_B_07") , a.SECOND_LAST_NAME= (select
b.new_SECOND_LAST_NAME from HX_NAMES_STG b where b.old_NAME_FORMAL =
a.NAME_FORMAL and b.old_NAME_DISPLAY = a.NAME_DISPLAY and
b.old_LAST_NAME_PREF_NLD = a.LAST_NAME_PREF_NLD and
b.old_PARTNER_ROY_PREFIX = a.PARTNER_ROY_PREFIX and b.old_PARTNER_LAST_NAME
= a.PARTNER_LAST_NAME and b.old_PREF_FIRST_NAME = a.PREF_FIRST_NAME and
b.old_NAME_AC = a.NAME_AC and b.old_SECOND_LAST_SRCH = a.SECOND_LAST_SRCH
and b.old_SECOND_LAST_NAME = a.SECOND_LAST_NAME and b.old_MIDDLE_NAME =
a.MIDDLE_NAME and b.old_FIRST_NAME = a.FIRST_NAME and b.old_LAST_NAME =
a.LAST_NAME and b.old_FIRST_NAME_SRCH = a.FIRST_NAME_SRCH and
b.old_LAST_NAME_SRCH = a.LAST_NAME_SRCH and b.old_NAME_TITLE = a.NAME_TITLE
and b.old_NAME_ROYAL_SUFFIX = a.NAME_ROYAL_SUFFIX and
b.old_NAME_ROYAL_PREFIX = a.NAME_ROYAL_PREFIX and b.old_NAME_SUFFIX =
a.NAME_SUFFIX and b.old_NAME_PREFIX = a.NAME_PREFIX and b.old_NAME_INITIALS
= a.NAME_INITIALS and b.old_NAME = a.NAME and b.old_COUNTRY_NM_FORMAT =
a.COUNTRY_NM_FORMAT and rownum<:"SYS_B_08") , a.MIDDLE_NAME= (select
b.new_MIDDLE_NAME from HX_NAMES_STG b where b.old_NAME_FORMAL =
a.NAME_FORMAL and b.old_NAME_DISPLAY = a.NAME_DISPLAY and
b.old_LAST_NAME_PREF_NLD = a.LAST_NAME_PREF_NLD and
b.old_PARTNER_ROY_PREFIX = a.PARTNER_ROY_PREFIX and b.old_PARTNER_LAST_NAME
= a.PARTNER_LAST_NAME and b.old_PREF_FIRST_NAME = a.PREF_FIRST_NAME and
b.old_NAME_AC = a.NAME_AC and b.old_SECOND_LAST_SRCH = a.SECOND_LAST_SRCH
and b.old_SECOND_LAST_NAME = a.SECOND_LAST_NAME and b.old_MIDDLE_NAME =
a.MIDDLE_NAME and b.old_FIRST_NAME = a.FIRST_NAME and b.old_LAST_NAME =
a.LAST_NAME and b.old_FIRST_NAME_SRCH = a.FIRST_NAME_SRCH and
b.old_LAST_NAME_SRCH = a.LAST_NAME_SRCH and b.old_NAME_TITLE = a.NAME_TITLE
and b.old_NAME_ROYAL_SUFFIX = a.NAME_ROYAL_SUFFIX and
b.old_NAME_ROYAL_PREFIX = a.NAME_ROYAL_PREFIX and b.old_NAME_SUFFIX =
a.NAME_SUFFIX and b.old_NAME_PREFIX = a.NAME_PREFIX and b.old_NAME_INITIALS
= a.NAME_INITIALS and b.old_NAME = a.NAME and b.old_COUNTRY_NM_FORMAT =
a.COUNTRY_NM_FORMAT and rownum<:"SYS_B_09") , a.FIRST_NAME= (select
b.new_FIRST_NAME from HX_NAMES_STG b where b.old_NAME_FORMAL =
a.NAME_FORMAL and b.old_NAME_DISPLAY = a.NAME_DISPLAY and
b.old_LAST_NAME_PREF_NLD = a.LAST_NAME_PREF_NLD and
b.old_PARTNER_ROY_PREFIX = a.PARTNER_ROY_PREFIX and b.old_PARTNER_LAST_NAME
= a.PARTNER_LAST_NAME and b.old_PREF_FIRST_NAME = a.PREF_FIRST_NAME and
b.old_NAME_AC = a.NAME_AC and b.old_SECOND_LAST_SRCH = a.SECOND_LAST_SRCH
and b.old_SECOND_LAST_NAME = a.SECOND_LAST_NAME and b.old_MIDDLE_NAME =
a.MIDDLE_NAME and b.old_FIRST_NAME = a.FIRST_NAME and b.old_LAST_NAME =
a.LAST_NAME and b.old_FIRST_NAME_SRCH = a.FIRST_NAME_SRCH and
b.old_LAST_NAME_SRCH = a.LAST_NAME_SRCH and b.old_NAME_TITLE = a.NAME_TITLE
and b.old_NAME_ROYAL_SUFFIX = a.NAME_ROYAL_SUFFIX and
b.old_NAME_ROYAL_PREFIX = a.NAME_ROYAL_PREFIX and b.old_NAME_SUFFIX =
a.NAME_SUFFIX and b.old_NAME_PREFIX = a.NAME_PREFIX and b.old_NAME_INITIALS
= a.NAME_INITIALS and b.old_NAME = a.NAME and b.old_COUNTRY_NM_FORMAT =
a.COUNTRY_NM_FORMAT and rownum<:"SYS_B_10") , a.LAST_NAME= (select
b.new_LAST_NAME from HX_NAMES_STG b where b.old_NAME_FORMAL =
a.NAME_FORMAL and b.old_NAME_DISPLAY = a.NAME_DISPLAY and
b.old_LAST_NAME_PREF_NLD = a.LAST_NAME_PREF_NLD and
b.old_PARTNER_ROY_PREFIX = a.PARTNER_ROY_PREFIX and b.old_PARTNER_LAST_NAME
= a.PARTNER_LAST_NAME and b.old_PREF_FIRST_NAME = a.PREF_FIRST_NAME and
b.old_NAME_AC = a.NAME_AC and b.old_SECOND_LAST_SRCH = a.SECOND_LAST_SRCH
and b.old_SECOND_LAST_NAME = a.SECOND_LAST_NAME and b.old_MIDDLE_NAME =
a.MIDDLE_NAME and b.old_FIRST_NAME = a.FIRST_NAME and b.old_LAST_NAME =
a.LAST_NAME and b.old_FIRST_NAME_SRCH = a.FIRST_NAME_SRCH and
b.old_LAST_NAME_SRCH = a.LAST_NAME_SRCH and b.old_NAME_TITLE = a.NAME_TITLE
and b.old_NAME_ROYAL_SUFFIX = a.NAME_ROYAL_SUFFIX and
b.old_NAME_ROYAL_PREFIX = a.NAME_ROYAL_PREFIX and b.old_NAME_SUFFIX =
a.NAME_SUFFIX and b.old_NAME_PREFIX = a.NAME_PREFIX and b.old_NAME_INITIALS
= a.NAME_INITIALS and b.old_NAME = a.NAME and b.old_COUNTRY_NM_FORMAT =
a.COUNTRY_NM_FORMAT and rownum<:"SYS_B_11") , a.FIRST_NAME_SRCH= (select
b.new_FIRST_NAME_SRCH from HX_NAMES_STG b where b.old_NAME_FORMAL =
a.NAME_FORMAL and b.old_NAME_DISPLAY = a.NAME_DISPLAY and
b.old_LAST_NAME_PREF_NLD = a.LAST_NAME_PREF_NLD and
b.old_PARTNER_ROY_PREFIX = a.PARTNER_ROY_PREFIX and b.old_PARTNER_LAST_NAME
= a.PARTNER_LAST_NAME and b.old_PREF_FIRST_NAME = a.PREF_FIRST_NAME and
b.old_NAME_AC = a.NAME_AC and b.old_SECOND_LAST_SRCH = a.SECOND_LAST_SRCH
and b.old_SECOND_LAST_NAME = a.SECOND_LAST_NAME and b.old_MIDDLE_NAME =
a.MIDDLE_NAME and b.old_FIRST_NAME = a.FIRST_NAME and b.old_LAST_NAME =
a.LAST_NAME and b.old_FIRST_NAME_SRCH = a.FIRST_NAME_SRCH and
b.old_LAST_NAME_SRCH = a.LAST_NAME_SRCH and b.old_NAME_TITLE = a.NAME_TITLE
and b.old_NAME_ROYAL_SUFFIX = a.NAME_ROYAL_SUFFIX and
b.old_NAME_ROYAL_PREFIX = a.NAME_ROYAL_PREFIX and b.old_NAME_SUFFIX =
a.NAME_SUFFIX and b.old_NAME_PREFIX = a.NAME_PREFIX and b.old_NAME_INITIALS
= a.NAME_INITIALS and b.old_NAME = a.NAME and b.old_COUNTRY_NM_FORMAT =
a.COUNTRY_NM_FORMAT and rownum<:"SYS_B_12") , a.LAST_NAME_SRCH= (select
b.new_LAST_NAME_SRCH from HX_NAMES_STG b where b.old_NAME_FORMAL =
a.NAME_FORMAL and b.old_NAME_DISPLAY = a.NAME_DISPLAY and
b.old_LAST_NAME_PREF_NLD = a.LAST_NAME_PREF_NLD and
b.old_PARTNER_ROY_PREFIX = a.PARTNER_ROY_PREFIX and b.old_PARTNER_LAST_NAME
= a.PARTNER_LAST_NAME and b.old_PREF_FIRST_NAME = a.PREF_FIRST_NAME and
b.old_NAME_AC = a.NAME_AC and b.old_SECOND_LAST_SRCH = a.SECOND_LAST_SRCH
and b.old_SECOND_LAST_NAME = a.SECOND_LAST_NAME and b.old_MIDDLE_NAME =
a.MIDDLE_NAME and b.old_FIRST_NAME = a.FIRST_NAME and b.old_LAST_NAME =
a.LAST_NAME and b.old_FIRST_NAME_SRCH = a.FIRST_NAME_SRCH and
b.old_LAST_NAME_SRCH = a.LAST_NAME_SRCH and b.old_NAME_TITLE = a.NAME_TITLE
and b.old_NAME_ROYAL_SUFFIX = a.NAME_ROYAL_SUFFIX and
b.old_NAME_ROYAL_PREFIX = a.NAME_ROYAL_PREFIX and b.old_NAME_SUFFIX =
a.NAME_SUFFIX and b.old_NAME_PREFIX = a.NAME_PREFIX and b.old_NAME_INITIALS
= a.NAME_INITIALS and b.old_NAME = a.NAME and b.old_COUNTRY_NM_FORMAT =
a.COUNTRY_NM_FORMAT and rownum<:"SYS_B_13") , a.NAME_TITLE= (select
b.new_NAME_TITLE from HX_NAMES_STG b where b.old_NAME_FORMAL =
a.NAME_FORMAL and b.old_NAME_DISPLAY = a.NAME_DISPLAY and
b.old_LAST_NAME_PREF_NLD = a.LAST_NAME_PREF_NLD and
b.old_PARTNER_ROY_PREFIX = a.PARTNER_ROY_PREFIX and b.old_PARTNER_LAST_NAME
= a.PARTNER_LAST_NAME and b.old_PREF_FIRST_NAME = a.PREF_FIRST_NAME and
b.old_NAME_AC = a.NAME_AC and b.old_SECOND_LAST_SRCH = a.SECOND_LAST_SRCH
and b.old_SECOND_LAST_NAME = a.SECOND_LAST_NAME and b.old_MIDDLE_NAME =
a.MIDDLE_NAME and b.old_FIRST_NAME = a.FIRST_NAME and b.old_LAST_NAME =
a.LAST_NAME and b.old_FIRST_NAME_SRCH = a.FIRST_NAME_SRCH and
b.old_LAST_NAME_SRCH = a.LAST_NAME_SRCH and b.old_NAME_TITLE = a.NAME_TITLE
and b.old_NAME_ROYAL_SUFFIX = a.NAME_ROYAL_SUFFIX and
b.old_NAME_ROYAL_PREFIX = a.NAME_ROYAL_PREFIX and b.old_NAME_SUFFIX =
a.NAME_SUFFIX and b.old_NAME_PREFIX = a.NAME_PREFIX and b.old_NAME_INITIALS
= a.NAME_INITIALS and b.old_NAME = a.NAME and b.old_COUNTRY_NM_FORMAT =
a.COUNTRY_NM_FORMAT and rownum<:"SYS_B_14") , a.NAME_ROYAL_SUFFIX= (select
b.new_NAME_ROYAL_SUFFIX from HX_NAMES_STG b where b.old_NAME_FORMAL =
a.NAME_FORMAL and b.old_NAME_DISPLAY = a.NAME_DISPLAY and
b.old_LAST_NAME_PREF_NLD = a.LAST_NAME_PREF_NLD and
b.old_PARTNER_ROY_PREFIX = a.PARTNER_ROY_PREFIX and b.old_PARTNER_LAST_NAME
= a.PARTNER_LAST_NAME and b.old_PREF_FIRST_NAME = a.PREF_FIRST_NAME and
b.old_NAME_AC = a.NAME_AC and b.old_SECOND_LAST_SRCH = a.SECOND_LAST_SRCH
and b.old_SECOND_LAST_NAME = a.SECOND_LAST_NAME and b.old_MIDDLE_NAME =
a.MIDDLE_NAME and b.old_FIRST_NAME = a.FIRST_NAME and b.old_LAST_NAME =
a.LAST_NAME and b.old_FIRST_NAME_SRCH = a.FIRST_NAME_SRCH and
b.old_LAST_NAME_SRCH = a.LAST_NAME_SRCH and b.old_NAME_TITLE = a.NAME_TITLE
and b.old_NAME_ROYAL_SUFFIX = a.NAME_ROYAL_SUFFIX and
b.old_NAME_ROYAL_PREFIX = a.NAME_ROYAL_PREFIX and b.old_NAME_SUFFIX =
a.NAME_SUFFIX and b.old_NAME_PREFIX = a.NAME_PREFIX and b.old_NAME_INITIALS
= a.NAME_INITIALS and b.old_NAME = a.NAME and b.old_COUNTRY_NM_FORMAT =
a.COUNTRY_NM_FORMAT and rownum<:"SYS_B_15") , a.NAME_ROYAL_PREFIX= (select
b.new_NAME_ROYAL_PREFIX from HX_NAMES_STG b where b.old_NAME_FORMAL =
a.NAME_FORMAL and b.old_NAME_DISPLAY = a.NAME_DISPLAY and
b.old_LAST_NAME_PREF_NLD = a.LAST_NAME_PREF_NLD and
b.old_PARTNER_ROY_PREFIX = a.PARTNER_ROY_PREFIX and b.old_PARTNER_LAST_NAME
= a.PARTNER_LAST_NAME and b.old_PREF_FIRST_NAME = a.PREF_FIRST_NAME and
b.old_NAME_AC = a.NAME_AC and b.old_SECOND_LAST_SRCH = a.SECOND_LAST_SRCH
and b.old_SECOND_LAST_NAME = a.SECOND_LAST_NAME and b.old_MIDDLE_NAME =
a.MIDDLE_NAME and b.old_FIRST_NAME = a.FIRST_NAME and b.old_LAST_NAME =
a.LAST_NAME and b.old_FIRST_NAME_SRCH = a.FIRST_NAME_SRCH and
b.old_LAST_NAME_SRCH = a.LAST_NAME_SRCH and b.old_NAME_TITLE = a.NAME_TITLE
and b.old_NAME_ROYAL_SUFFIX = a.NAME_ROYAL_SUFFIX and
b.old_NAME_ROYAL_PREFIX = a.NAME_ROYAL_PREFIX and b.old_NAME_SUFFIX =
a.NAME_SUFFIX and b.old_NAME_PREFIX = a.NAME_PREFIX and b.old_NAME_INITIALS
= a.NAME_INITIALS and b.old_NAME = a.NAME and b.old_COUNTRY_NM_FORMAT =
a.COUNTRY_NM_FORMAT and rownum<:"SYS_B_16") , a.NAME_SUFFIX= (select
b.new_NAME_SUFFIX from HX_NAMES_STG b where b.old_NAME_FORMAL =
a.NAME_FORMAL and b.old_NAME_DISPLAY = a.NAME_DISPLAY and
b.old_LAST_NAME_PREF_NLD = a.LAST_NAME_PREF_NLD and
b.old_PARTNER_ROY_PREFIX = a.PARTNER_ROY_PREFIX and b.old_PARTNER_LAST_NAME
= a.PARTNER_LAST_NAME and b.old_PREF_FIRST_NAME = a.PREF_FIRST_NAME and
b.old_NAME_AC = a.NAME_AC and b.old_SECOND_LAST_SRCH = a.SECOND_LAST_SRCH
and b.old_SECOND_LAST_NAME = a.SECOND_LAST_NAME and b.old_MIDDLE_NAME =
a.MIDDLE_NAME and b.old_FIRST_NAME = a.FIRST_NAME and b.old_LAST_NAME =
a.LAST_NAME and b.old_FIRST_NAME_SRCH = a.FIRST_NAME_SRCH and
b.old_LAST_NAME_SRCH = a.LAST_NAME_SRCH and b.old_NAME_TITLE = a.NAME_TITLE
and b.old_NAME_ROYAL_SUFFIX = a.NAME_ROYAL_SUFFIX and
b.old_NAME_ROYAL_PREFIX = a.NAME_ROYAL_PREFIX and b.old_NAME_SUFFIX =
a.NAME_SUFFIX and b.old_NAME_PREFIX = a.NAME_PREFIX and b.old_NAME_INITIALS
= a.NAME_INITIALS and b.old_NAME = a.NAME and b.old_COUNTRY_NM_FORMAT =
a.COUNTRY_NM_FORMAT and rownum<:"SYS_B_17") , a.NAME_PREFIX= (select
b.new_NAME_PREFIX from HX_NAMES_STG b where b.old_NAME_FORMAL =
a.NAME_FORMAL and b.old_NAME_DISPLAY = a.NAME_DISPLAY and
b.old_LAST_NAME_PREF_NLD = a.LAST_NAME_PREF_NLD and
b.old_PARTNER_ROY_PREFIX = a.PARTNER_ROY_PREFIX and b.old_PARTNER_LAST_NAME
= a.PARTNER_LAST_NAME and b.old_PREF_FIRST_NAME = a.PREF_FIRST_NAME and
b.old_NAME_AC = a.NAME_AC and b.old_SECOND_LAST_SRCH = a.SECOND_LAST_SRCH
and b.old_SECOND_LAST_NAME = a.SECOND_LAST_NAME and b.old_MIDDLE_NAME =
a.MIDDLE_NAME and b.old_FIRST_NAME = a.FIRST_NAME and b.old_LAST_NAME =
a.LAST_NAME and b.old_FIRST_NAME_SRCH = a.FIRST_NAME_SRCH and
b.old_LAST_NAME_SRCH = a.LAST_NAME_SRCH and b.old_NAME_TITLE = a.NAME_TITLE
and b.old_NAME_ROYAL_SUFFIX = a.NAME_ROYAL_SUFFIX and
b.old_NAME_ROYAL_PREFIX = a.NAME_ROYAL_PREFIX and b.old_NAME_SUFFIX =
a.NAME_SUFFIX and b.old_NAME_PREFIX = a.NAME_PREFIX and b.old_NAME_INITIALS
= a.NAME_INITIALS and b.old_NAME = a.NAME and b.old_COUNTRY_NM_FORMAT =
a.COUNTRY_NM_FORMAT and rownum<:"SYS_B_18") , a.NAME_INITIALS= (select
b.new_NAME_INITIALS from HX_NAMES_STG b where b.old_NAME_FORMAL =
a.NAME_FORMAL and b.old_NAME_DISPLAY = a.NAME_DISPLAY and
b.old_LAST_NAME_PREF_NLD = a.LAST_NAME_PREF_NLD and
b.old_PARTNER_ROY_PREFIX = a.PARTNER_ROY_PREFIX and b.old_PARTNER_LAST_NAME
= a.PARTNER_LAST_NAME and b.old_PREF_FIRST_NAME = a.PREF_FIRST_NAME and
b.old_NAME_AC = a.NAME_AC and b.old_SECOND_LAST_SRCH = a.SECOND_LAST_SRCH
and b.old_SECOND_LAST_NAME = a.SECOND_LAST_NAME and b.old_MIDDLE_NAME =
a.MIDDLE_NAME and b.old_FIRST_NAME = a.FIRST_NAME and b.old_LAST_NAME =
a.LAST_NAME and b.old_FIRST_NAME_SRCH = a.FIRST_NAME_SRCH and
b.old_LAST_NAME_SRCH = a.LAST_NAME_SRCH and b.old_NAME_TITLE = a.NAME_TITLE
and b.old_NAME_ROYAL_SUFFIX = a.NAME_ROYAL_SUFFIX and
b.old_NAME_ROYAL_PREFIX = a.NAME_ROYAL_PREFIX and b.old_NAME_SUFFIX =
a.NAME_SUFFIX and b.old_NAME_PREFIX = a.NAME_PREFIX and b.old_NAME_INITIALS
= a.NAME_INITIALS and b.old_NAME = a.NAME and b.old_COUNTRY_NM_FORMAT =
a.COUNTRY_NM_FORMAT and rownum<:"SYS_B_19") , a.NAME= (select b.new_NAME
from HX_NAMES_STG b where b.old_NAME_FORMAL = a.NAME_FORMAL and
b.old_NAME_DISPLAY = a.NAME_DISPLAY and b.old_LAST_NAME_PREF_NLD =
a.LAST_NAME_PREF_NLD and b.old_PARTNER_ROY_PREFIX = a.PARTNER_ROY_PREFIX
and b.old_PARTNER_LAST_NAME = a.PARTNER_LAST_NAME and b.old_PREF_FIRST_NAME
= a.PREF_FIRST_NAME and b.old_NAME_AC = a.NAME_AC and
b.old_SECOND_LAST_SRCH = a.SECOND_LAST_SRCH and b.old_SECOND_LAST_NAME =
a.SECOND_LAST_NAME and b.old_MIDDLE_NAME = a.MIDDLE_NAME and
b.old_FIRST_NAME = a.FIRST_NAME and b.old_LAST_NAME = a.LAST_NAME and
b.old_FIRST_NAME_SRCH = a.FIRST_NAME_SRCH and b.old_LAST_NAME_SRCH =
a.LAST_NAME_SRCH and b.old_NAME_TITLE = a.NAME_TITLE and
b.old_NAME_ROYAL_SUFFIX = a.NAME_ROYAL_SUFFIX and b.old_NAME_ROYAL_PREFIX =
a.NAME_ROYAL_PREFIX and b.old_NAME_SUFFIX = a.NAME_SUFFIX and
b.old_NAME_PREFIX = a.NAME_PREFIX and b.old_NAME_INITIALS = a.NAME_INITIALS
and b.old_NAME = a.NAME and b.old_COUNTRY_NM_FORMAT = a.COUNTRY_NM_FORMAT
and rownum<:"SYS_B_20") , a.COUNTRY_NM_FORMAT= (select
b.new_COUNTRY_NM_FORMAT from HX_NAMES_STG b where b.old_NAME_FORMAL =
a.NAME_FORMAL and b.old_NAME_DISPLAY = a.NAME_DISPLAY and
b.old_LAST_NAME_PREF_NLD = a.LAST_NAME_PREF_NLD and
b.old_PARTNER_ROY_PREFIX = a.PARTNER_ROY_PREFIX and b.old_PARTNER_LAST_NAME
= a.PARTNER_LAST_NAME and b.old_PREF_FIRST_NAME = a.PREF_FIRST_NAME and
b.old_NAME_AC = a.NAME_AC and b.old_SECOND_LAST_SRCH = a.SECOND_LAST_SRCH
and b.old_SECOND_LAST_NAME = a.SECOND_LAST_NAME and b.old_MIDDLE_NAME =
a.MIDDLE_NAME and b.old_FIRST_NAME = a.FIRST_NAME and b.old_LAST_NAME =
a.LAST_NAME and b.old_FIRST_NAME_SRCH = a.FIRST_NAME_SRCH and
b.old_LAST_NAME_SRCH = a.LAST_NAME_SRCH and b.old_NAME_TITLE = a.NAME_TITLE
and b.old_NAME_ROYAL_SUFFIX = a.NAME_ROYAL_SUFFIX and
b.old_NAME_ROYAL_PREFIX = a.NAME_ROYAL_PREFIX and b.old_NAME_SUFFIX =
a.NAME_SUFFIX and b.old_NAME_PREFIX = a.NAME_PREFIX and b.old_NAME_INITIALS
= a.NAME_INITIALS and b.old_NAME = a.NAME and b.old_COUNTRY_NM_FORMAT =
a.COUNTRY_NM_FORMAT and rownum<:"SYS_B_21") where exists(select
:"SYS_B_22" from HX_NAMES_STG b where b.old_NAME_FORMAL = a.NAME_FORMAL
and b.old_NAME_DISPLAY = a.NAME_DISPLAY and b.old_LAST_NAME_PREF_NLD =
a.LAST_NAME_PREF_NLD and b.old_PARTNER_ROY_PREFIX = a.PARTNER_ROY_PREFIX
and b.old_PARTNER_LAST_NAME = a.PARTNER_LAST_NAME and b.old_PREF_FIRST_NAME
= a.PREF_FIRST_NAME and b.old_NAME_AC = a.NAME_AC and
b.old_SECOND_LAST_SRCH = a.SECOND_LAST_SRCH and b.old_SECOND_LAST_NAME =
a.SECOND_LAST_NAME and b.old_MIDDLE_NAME = a.MIDDLE_NAME and
b.old_FIRST_NAME = a.FIRST_NAME and b.old_LAST_NAME = a.LAST_NAME and
b.old_FIRST_NAME_SRCH = a.FIRST_NAME_SRCH and b.old_LAST_NAME_SRCH =
a.LAST_NAME_SRCH and b.old_NAME_TITLE = a.NAME_TITLE and
b.old_NAME_ROYAL_SUFFIX = a.NAME_ROYAL_SUFFIX and b.old_NAME_ROYAL_PREFIX =
a.NAME_ROYAL_PREFIX and b.old_NAME_SUFFIX = a.NAME_SUFFIX and
b.old_NAME_PREFIX = a.NAME_PREFIX and b.old_NAME_INITIALS = a.NAME_INITIALS
and b.old_NAME = a.NAME and b.old_COUNTRY_NM_FORMAT = a.COUNTRY_NM_FORMAT
and rownum<:"SYS_B_23")
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.02 0 0 0 0
Execute 1 91.23 91.43 62 7930410 1922487 100000
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 91.25 91.46 62 7930410 1922487 100000
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 58 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
0 UPDATE PS_NAMES (cr=7930410 pr=62 pw=0 time=91358979 us)
100000 FILTER (cr=347010 pr=62 pw=0 time=3911725 us)
100000 TABLE ACCESS FULL PS_NAMES (cr=2266 pr=0 pw=0 time=200075 us)
100000 COUNT STOPKEY (cr=344744 pr=62 pw=0 time=2869969 us)
100000 TABLE ACCESS BY INDEX ROWID HX_NAMES_STG (cr=344744 pr=62 pw=0 time=2499840 us)
100000 INDEX RANGE SCAN HX_NAMES_STG_IDX_003 (cr=200039 pr=57 pw=0 time=1158662 us)(object id 155153)
100000 COUNT STOPKEY (cr=344700 pr=0 pw=0 time=2908319 us)
100000 TABLE ACCESS BY INDEX ROWID HX_NAMES_STG (cr=344700 pr=0 pw=0 time=2265227 us)
100000 INDEX RANGE SCAN HX_NAMES_STG_IDX_003 (cr=200000 pr=0 pw=0 time=994948 us)(object id 155153)
100000 COUNT STOPKEY (cr=344700 pr=0 pw=0 time=2713769 us)
100000 TABLE ACCESS BY INDEX ROWID HX_NAMES_STG (cr=344700 pr=0 pw=0 time=2079560 us)
100000 INDEX RANGE SCAN HX_NAMES_STG_IDX_003 (cr=200000 pr=0 pw=0 time=951633 us)(object id 155153)
100000 COUNT STOPKEY (cr=344700 pr=0 pw=0 time=2713596 us)
100000 TABLE ACCESS BY INDEX ROWID HX_NAMES_STG (cr=344700 pr=0 pw=0 time=2089288 us)
100000 INDEX RANGE SCAN HX_NAMES_STG_IDX_003 (cr=200000 pr=0 pw=0 time=937818 us)(object id 155153)
100000 COUNT STOPKEY (cr=344700 pr=0 pw=0 time=2667391 us)
100000 TABLE ACCESS BY INDEX ROWID HX_NAMES_STG (cr=344700 pr=0 pw=0 time=2043886 us)
100000 INDEX RANGE SCAN HX_NAMES_STG_IDX_003 (cr=200000 pr=0 pw=0 time=919537 us)(object id 155153)
100000 COUNT STOPKEY (cr=344700 pr=0 pw=0 time=2701664 us)
100000 TABLE ACCESS BY INDEX ROWID HX_NAMES_STG (cr=344700 pr=0 pw=0 time=2074057 us)
100000 INDEX RANGE SCAN HX_NAMES_STG_IDX_003 (cr=200000 pr=0 pw=0 time=942481 us)(object id 155153)
100000 COUNT STOPKEY (cr=344700 pr=0 pw=0 time=2709529 us)
100000 TABLE ACCESS BY INDEX ROWID HX_NAMES_STG (cr=344700 pr=0 pw=0 time=2082822 us)
100000 INDEX RANGE SCAN HX_NAMES_STG_IDX_003 (cr=200000 pr=0 pw=0 time=928138 us)(object id 155153)
100000 COUNT STOPKEY (cr=344700 pr=0 pw=0 time=2765101 us)
100000 TABLE ACCESS BY INDEX ROWID HX_NAMES_STG (cr=344700 pr=0 pw=0 time=2130573 us)
100000 INDEX RANGE SCAN HX_NAMES_STG_IDX_003 (cr=200000 pr=0 pw=0 time=943773 us)(object id 155153)
100000 COUNT STOPKEY (cr=344700 pr=0 pw=0 time=2807752 us)
100000 TABLE ACCESS BY INDEX ROWID HX_NAMES_STG (cr=344700 pr=0 pw=0 time=2182214 us)
100000 INDEX RANGE SCAN HX_NAMES_STG_IDX_003 (cr=200000 pr=0 pw=0 time=925174 us)(object id 155153)
100000 COUNT STOPKEY (cr=344700 pr=0 pw=0 time=2724875 us)
100000 TABLE ACCESS BY INDEX ROWID HX_NAMES_STG (cr=344700 pr=0 pw=0 time=2103026 us)
100000 INDEX RANGE SCAN HX_NAMES_STG_IDX_003 (cr=200000 pr=0 pw=0 time=924252 us)(object id 155153)
100000 COUNT STOPKEY (cr=344700 pr=0 pw=0 time=2686998 us)
100000 TABLE ACCESS BY INDEX ROWID HX_NAMES_STG (cr=344700 pr=0 pw=0 time=2049968 us)
100000 INDEX RANGE SCAN HX_NAMES_STG_IDX_003 (cr=200000 pr=0 pw=0 time=928041 us)(object id 155153)
100000 COUNT STOPKEY (cr=344700 pr=0 pw=0 time=2677319 us)
100000 TABLE ACCESS BY INDEX ROWID HX_NAMES_STG (cr=344700 pr=0 pw=0 time=2051735 us)
100000 INDEX RANGE SCAN HX_NAMES_STG_IDX_003 (cr=200000 pr=0 pw=0 time=921001 us)(object id 155153)
100000 COUNT STOPKEY (cr=344700 pr=0 pw=0 time=2733147 us)
100000 TABLE ACCESS BY INDEX ROWID HX_NAMES_STG (cr=344700 pr=0 pw=0 time=2089113 us)
100000 INDEX RANGE SCAN HX_NAMES_STG_IDX_003 (cr=200000 pr=0 pw=0 time=928105 us)(object id 155153)
100000 COUNT STOPKEY (cr=344700 pr=0 pw=0 time=2718230 us)
100000 TABLE ACCESS BY INDEX ROWID HX_NAMES_STG (cr=344700 pr=0 pw=0 time=2092499 us)
100000 INDEX RANGE SCAN HX_NAMES_STG_IDX_003 (cr=200000 pr=0 pw=0 time=936310 us)(object id 155153)
100000 COUNT STOPKEY (cr=344700 pr=0 pw=0 time=2696605 us)
100000 TABLE ACCESS BY INDEX ROWID HX_NAMES_STG (cr=344700 pr=0 pw=0 time=2060868 us)
100000 INDEX RANGE SCAN HX_NAMES_STG_IDX_003 (cr=200000 pr=0 pw=0 time=926691 us)(object id 155153)
100000 COUNT STOPKEY (cr=344700 pr=0 pw=0 time=2670230 us)
100000 TABLE ACCESS BY INDEX ROWID HX_NAMES_STG (cr=344700 pr=0 pw=0 time=2048194 us)
100000 INDEX RANGE SCAN HX_NAMES_STG_IDX_003 (cr=200000 pr=0 pw=0 time=922112 us)(object id 155153)
100000 COUNT STOPKEY (cr=344700 pr=0 pw=0 time=2690606 us)
100000 TABLE ACCESS BY INDEX ROWID HX_NAMES_STG (cr=344700 pr=0 pw=0 time=2063061 us)
100000 INDEX RANGE SCAN HX_NAMES_STG_IDX_003 (cr=200000 pr=0 pw=0 time=921611 us)(object id 155153)
100000 COUNT STOPKEY (cr=344700 pr=0 pw=0 time=2688767 us)
100000 TABLE ACCESS BY INDEX ROWID HX_NAMES_STG (cr=344700 pr=0 pw=0 time=2063078 us)
100000 INDEX RANGE SCAN HX_NAMES_STG_IDX_003 (cr=200000 pr=0 pw=0 time=926304 us)(object id 155153)
100000 COUNT STOPKEY (cr=344700 pr=0 pw=0 time=2710469 us)
100000 TABLE ACCESS BY INDEX ROWID HX_NAMES_STG (cr=344700 pr=0 pw=0 time=2083108 us)
100000 INDEX RANGE SCAN HX_NAMES_STG_IDX_003 (cr=200000 pr=0 pw=0 time=934314 us)(object id 155153)
100000 COUNT STOPKEY (cr=344700 pr=0 pw=0 time=2671604 us)
100000 TABLE ACCESS BY INDEX ROWID HX_NAMES_STG (cr=344700 pr=0 pw=0 time=2045393 us)
100000 INDEX RANGE SCAN HX_NAMES_STG_IDX_003 (cr=200000 pr=0 pw=0 time=924878 us)(object id 155153)
100000 COUNT STOPKEY (cr=344700 pr=0 pw=0 time=2649945 us)
100000 TABLE ACCESS BY INDEX ROWID HX_NAMES_STG (cr=344700 pr=0 pw=0 time=2028665 us)
100000 INDEX RANGE SCAN HX_NAMES_STG_IDX_003 (cr=200000 pr=0 pw=0 time=920162 us)(object id 155153)
100000 COUNT STOPKEY (cr=344700 pr=0 pw=0 time=2715754 us)
100000 TABLE ACCESS BY INDEX ROWID HX_NAMES_STG (cr=344700 pr=0 pw=0 time=2087502 us)
100000 INDEX RANGE SCAN HX_NAMES_STG_IDX_003 (cr=200000 pr=0 pw=0 time=928785 us)(object id 155153)
100000 COUNT STOPKEY (cr=344700 pr=0 pw=0 time=2736784 us)
100000 TABLE ACCESS BY INDEX ROWID HX_NAMES_STG (cr=344700 pr=0 pw=0 time=2111676 us)
100000 INDEX RANGE SCAN HX_NAMES_STG_IDX_003 (cr=200000 pr=0 pw=0 time=925359 us)(object id 155153)
|
|
|
|
|
|
|
|
| Re: Help reading TOAD Explain Plan [message #272282 is a reply to message #199685] |
Thu, 04 October 2007 07:33 |
lm_suresh
Messages: 24
Registered: September 2007
Location: Chennai,India
|
Junior Member |
|
|
sorry for not formatting the query.
I have an update statement like this
UPDATE T1 TAB1 SET
TAB1.X = (SELECT tab2.a FROM T2 TAB2 WHERE TAB2.A = TAB1.X AND row_id > 2),
TAB1.Y = (SELECT tab2.a FROM T2 TAB2 WHERE TAB2.A = TAB1.X AND row_id > 2),
TAB1.Z = (SELECT tab2.a FROM T2 TAB2 WHERE TAB2.A = TAB1.X AND row_id > 2); etc....
and table t2 has got almost 20 index for various fields.
this is my explain plan... Why rows cloumn is still showing 100000 rows for all index scanning,.... Could you please guide me ..
Rows Row Source Operation
------- ---------------------------------------------------
0 UPDATE PS_NAMES (cr=7930410 pr=62 pw=0 time=91358979 us)
100000 FILTER (cr=347010 pr=62 pw=0 time=3911725 us)
100000 TABLE ACCESS FULL PS_NAMES (cr=2266 pr=0 pw=0 time=200075 us)
100000 COUNT STOPKEY (cr=344744 pr=62 pw=0 time=2869969 us)
100000 TABLE ACCESS BY INDEX ROWID HX_NAMES_STG (cr=344744 pr=62 pw=0 time=2499840 us)
100000 INDEX RANGE SCAN HX_NAMES_STG_IDX_003 (cr=200039 pr=57 pw=0 time=1158662 us)(object id 155153)
100000 COUNT STOPKEY (cr=344700 pr=0 pw=0 time=2908319 us)
100000 TABLE ACCESS BY INDEX ROWID HX_NAMES_STG (cr=344700 pr=0 pw=0 time=2265227 us)
100000 INDEX RANGE SCAN HX_NAMES_STG_IDX_003 (cr=200000 pr=0 pw=0 time=994948 us)(object id 155153)
100000 COUNT STOPKEY (cr=344700 pr=0 pw=0 time=2713769 us)
100000 TABLE ACCESS BY INDEX ROWID HX_NAMES_STG (cr=344700 pr=0 pw=0 time=2079560 us)
100000 INDEX RANGE SCAN HX_NAMES_STG_IDX_003 (cr=200000 pr=0 pw=0 time=951633 us)(object id 155153)
100000 COUNT STOPKEY (cr=344700 pr=0 pw=0 time=2713596 us)
100000 TABLE ACCESS BY INDEX ROWID HX_NAMES_STG (cr=344700 pr=0 pw=0 time=2089288 us)
100000 INDEX RANGE SCAN HX_NAMES_STG_IDX_003 (cr=200000 pr=0 pw=0 time=937818 us)(object id 155153)
100000 COUNT STOPKEY (cr=344700 pr=0 pw=0 time=2667391 us)
100000 TABLE ACCESS BY INDEX ROWID HX_NAMES_STG (cr=344700 pr=0 pw=0 time=2043886 us)
100000 INDEX RANGE SCAN HX_NAMES_STG_IDX_003 (cr=200000 pr=0 pw=0 time=919537 us)(object id 155153)
100000 COUNT STOPKEY (cr=344700 pr=0 pw=0 time=2701664 us)
100000 TABLE ACCESS BY INDEX ROWID HX_NAMES_STG (cr=344700 pr=0 pw=0 time=2074057 us)
100000 INDEX RANGE SCAN HX_NAMES_STG_IDX_003 (cr=200000 pr=0 pw=0 time=942481 us)(object id 155153)
100000 COUNT STOPKEY (cr=344700 pr=0 pw=0 time=2709529 us)
100000 TABLE ACCESS BY INDEX ROWID HX_NAMES_STG (cr=344700 pr=0 pw=0 time=2082822 us)
100000 INDEX RANGE SCAN HX_NAMES_STG_IDX_003 (cr=200000 pr=0 pw=0 time=928138 us)(object id 155153)
100000 COUNT STOPKEY (cr=344700 pr=0 pw=0 time=2765101 us)
100000 TABLE ACCESS BY INDEX ROWID HX_NAMES_STG (cr=344700 pr=0 pw=0 time=2130573 us)
100000 INDEX RANGE SCAN HX_NAMES_STG_IDX_003 (cr=200000 pr=0 pw=0 time=943773 us)(object id 155153)
100000 COUNT STOPKEY (cr=344700 pr=0 pw=0 time=2807752 us)
100000 TABLE ACCESS BY INDEX ROWID HX_NAMES_STG (cr=344700 pr=0 pw=0 time=2182214 us)
100000 INDEX RANGE SCAN HX_NAMES_STG_IDX_003 (cr=200000 pr=0 pw=0 time=925174 us)(object id 155153)
100000 COUNT STOPKEY (cr=344700 pr=0 pw=0 time=2724875 us)
100000 TABLE ACCESS BY INDEX ROWID HX_NAMES_STG (cr=344700 pr=0 pw=0 time=2103026 us)
100000 INDEX RANGE SCAN HX_NAMES_STG_IDX_003 (cr=200000 pr=0 pw=0 time=924252 us)(object id 155153)
100000 COUNT STOPKEY (cr=344700 pr=0 pw=0 time=2686998 us)
100000 TABLE ACCESS BY INDEX ROWID HX_NAMES_STG (cr=344700 pr=0 pw=0 time=2049968 us)
100000 INDEX RANGE SCAN HX_NAMES_STG_IDX_003 (cr=200000 pr=0 pw=0 time=928041 us)(object id 155153)
100000 COUNT STOPKEY (cr=344700 pr=0 pw=0 time=2677319 us)
100000 TABLE ACCESS BY INDEX ROWID HX_NAMES_STG (cr=344700 pr=0 pw=0 time=2051735 us)
100000 INDEX RANGE SCAN HX_NAMES_STG_IDX_003 (cr=200000 pr=0 pw=0 time=921001 us)(object id 155153)
100000 COUNT STOPKEY (cr=344700 pr=0 pw=0 time=2733147 us)
100000 TABLE ACCESS BY INDEX ROWID HX_NAMES_STG (cr=344700 pr=0 pw=0 time=2089113 us)
100000 INDEX RANGE SCAN HX_NAMES_STG_IDX_003 (cr=200000 pr=0 pw=0 time=928105 us)(object id 155153)
100000 COUNT STOPKEY (cr=344700 pr=0 pw=0 time=2718230 us)
100000 TABLE ACCESS BY INDEX ROWID HX_NAMES_STG (cr=344700 pr=0 pw=0 time=2092499 us)
100000 INDEX RANGE SCAN HX_NAMES_STG_IDX_003 (cr=200000 pr=0 pw=0 time=936310 us)(object id 155153)
100000 COUNT STOPKEY (cr=344700 pr=0 pw=0 time=2696605 us)
100000 TABLE ACCESS BY INDEX ROWID HX_NAMES_STG (cr=344700 pr=0 pw=0 time=2060868 us)
100000 INDEX RANGE SCAN HX_NAMES_STG_IDX_003 (cr=200000 pr=0 pw=0 time=926691 us)(object id 155153)
100000 COUNT STOPKEY (cr=344700 pr=0 pw=0 time=2670230 us)
100000 TABLE ACCESS BY INDEX ROWID HX_NAMES_STG (cr=344700 pr=0 pw=0 time=2048194 us)
100000 INDEX RANGE SCAN HX_NAMES_STG_IDX_003 (cr=200000 pr=0 pw=0 time=922112 us)(object id 155153)
100000 COUNT STOPKEY (cr=344700 pr=0 pw=0 time=2690606 us)
100000 TABLE ACCESS BY INDEX ROWID HX_NAMES_STG (cr=344700 pr=0 pw=0 time=2063061 us)
100000 INDEX RANGE SCAN HX_NAMES_STG_IDX_003 (cr=200000 pr=0 pw=0 time=921611 us)(object id 155153)
100000 COUNT STOPKEY (cr=344700 pr=0 pw=0 time=2688767 us)
100000 TABLE ACCESS BY INDEX ROWID HX_NAMES_STG (cr=344700 pr=0 pw=0 time=2063078 us)
100000 INDEX RANGE SCAN HX_NAMES_STG_IDX_003 (cr=200000 pr=0 pw=0 time=926304 us)(object id 155153)
100000 COUNT STOPKEY (cr=344700 pr=0 pw=0 time=2710469 us)
100000 TABLE ACCESS BY INDEX ROWID HX_NAMES_STG (cr=344700 pr=0 pw=0 time=2083108 us)
100000 INDEX RANGE SCAN HX_NAMES_STG_IDX_003 (cr=200000 pr=0 pw=0 time=934314 us)(object id 155153)
100000 COUNT STOPKEY (cr=344700 pr=0 pw=0 time=2671604 us)
100000 TABLE ACCESS BY INDEX ROWID HX_NAMES_STG (cr=344700 pr=0 pw=0 time=2045393 us)
100000 INDEX RANGE SCAN HX_NAMES_STG_IDX_003 (cr=200000 pr=0 pw=0 time=924878 us)(object id 155153)
100000 COUNT STOPKEY (cr=344700 pr=0 pw=0 time=2649945 us)
100000 TABLE ACCESS BY INDEX ROWID HX_NAMES_STG (cr=344700 pr=0 pw=0 time=2028665 us)
100000 INDEX RANGE SCAN HX_NAMES_STG_IDX_003 (cr=200000 pr=0 pw=0 time=920162 us)(object id 155153)
100000 COUNT STOPKEY (cr=344700 pr=0 pw=0 time=2715754 us)
100000 TABLE ACCESS BY INDEX ROWID HX_NAMES_STG (cr=344700 pr=0 pw=0 time=2087502 us)
100000 INDEX RANGE SCAN HX_NAMES_STG_IDX_003 (cr=200000 pr=0 pw=0 time=928785 us)(object id 155153)
100000 COUNT STOPKEY (cr=344700 pr=0 pw=0 time=2736784 us)
100000 TABLE ACCESS BY INDEX ROWID HX_NAMES_STG (cr=344700 pr=0 pw=0 time=2111676 us)
100000 INDEX RANGE SCAN HX_NAMES_STG_IDX_003 (cr=200000 pr=0 pw=0 time=925359 us)(object id 155153)
|
|
|
|
|
|
|
|
| Re: Help reading TOAD Explain Plan [message #272340 is a reply to message #272315] |
Thu, 04 October 2007 11:31 |
mson77
Messages: 208
Registered: August 2007
Location: Brazil
|
Senior Member |
|
|
Hello lm_suresh,
Please:
1) start your own new thread;
2) read this OraFAQ Forum Guide and apply its recommendations;
3) have in mind to easy and fulfill as much as reliable information as possible. People here are not mind readers, neither have the environment that you are on.
4) Use a code formatter... OraFAQ has one here:
http://www.orafaq.com/utilities/sqlformatter.htm
Also there are others free sql formatters in the web... just google and you will find the best formatter that fits your needs in seconds. I use this one SQLinForm (java based desktop version... works in linux/win32...)
5) show us that you also do effort to get positive results by your own... and **NOT** just posting your problem here and waits for a magical free solution (I mean free in terms of efforts).
Do this. It is my true recommendation.
We'll see you on your new thread.
Regards,
mson77
[Updated on: Thu, 04 October 2007 11:35] Report message to a moderator |
|
|
|
| Re: Help reading TOAD Explain Plan [message #272507 is a reply to message #272282] |
Fri, 05 October 2007 05:32 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Update does support the ability to update multiple columns from the same source.
You could rewrite:UPDATE T1 TAB1 SET
TAB1.X = (SELECT tab2.a FROM T2 TAB2 WHERE TAB2.A = TAB1.X AND row_id > 2),
TAB1.Y = (SELECT tab2.b FROM T2 TAB2 WHERE TAB2.A = TAB1.X AND row_id > 2),
TAB1.Z = (SELECT tab2.c FROM T2 TAB2 WHERE TAB2.A = TAB1.X AND row_id > 2); etc....
UPDATE T1 TAB1 SET
(TAB1.X,TAB1.Y,TAB1.Z) = (SELECT tab2.a,tab2.b,tab2.c
FROM T2 TAB2
WHERE TAB2.A = TAB1.X
AND row_id > 2);
The second method accesses table T2 once, the first query accesses it 3 times.
|
|
|
|
Goto Forum:
Current Time: Mon Nov 04 22:18:56 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
