Home » RDBMS Server » Performance Tuning » Index creation....
| Index creation.... [message #273204] |
Tue, 09 October 2007 08:21  |
lm_suresh
Messages: 24
Registered: September 2007
Location: Chennai,India
|
Junior Member |
|
|
This is my query. I have created the combined index Like
CREATE INDEX SYSADM.HX_NAMES_STG_IDX_001
ON SYSADM.HX_NAMES_STG(HX_SLNO, OLD_NAME_FORMAL, OLD_NAME_DISPLAY, OLD_LAST_NAME_PREF_NLD, OLD_PARTNER_ROY_PREFIX, OLD_PARTNER_LAST_NAME, OLD_PREF_FIRST_NAME, OLD_NAME_AC, OLD_SECOND_LAST_SRCH, OLD_SECOND_LAST_NAME, OLD_MIDDLE_NAME, OLD_FIRST_NAME, OLD_LAST_NAME, OLD_FIRST_NAME_SRCH, OLD_LAST_NAME_SRCH, OLD_NAME_TITLE, OLD_NAME_ROYAL_SUFFIX, OLD_NAME_ROYAL_PREFIX, OLD_NAME_SUFFIX, OLD_NAME_PREFIX, OLD_NAME_INITIALS, OLD_NAME, OLD_COUNTRY_NM_FORMAT) NOLOGGING TABLESPACE PSDEFAULT
--------------------------------------------------------
Update PS_NAMES a
set (NAME_FORMAL,NAME_DISPLAY,LAST_NAME_PREF_NLD,
PARTNER_ROY_PREFIX,PARTNER_LAST_NAME,PREF_FIRST_NAME,NAME_AC,
SECOND_LAST_SRCH,SECOND_LAST_NAME,MIDDLE_NAME,FIRST_NAME,LAST_NAME,
FIRST_NAME_SRCH,LAST_NAME_SRCH,NAME_TITLE,NAME_ROYAL_SUFFIX,
NAME_ROYAL_PREFIX,NAME_SUFFIX,NAME_PREFIX,NAME_INITIALS,NAME,
COUNTRY_NM_FORMAT) =
(Select new_NAME_FORMAL,NAME_DISPLAY,
LAST_NAME_PREF_NLD,PARTNER_ROY_PREFIX,PARTNER_LAST_NAME,PREF_FIRST_NAME,
NAME_AC,SECOND_LAST_SRCH,SECOND_LAST_NAME,MIDDLE_NAME,FIRST_NAME,LAST_NAME,
FIRST_NAME_SRCH,LAST_NAME_SRCH,NAME_TITLE,NAME_ROYAL_SUFFIX,
NAME_ROYAL_PREFIX,NAME_SUFFIX,NAME_PREFIX,NAME_INITIALS,NAME,
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_0")
where exists(select :"SYS_B_1"
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_2")
It is takes more than an hour to update 1,000,00 records.
it completes within 7 mints if create index for each and every column separately. I dont want to create separate index for
all the column as i need to do huge insert/update on the same
tables which affects the performance,.can somoone guide me the better way ..... thanks
|
|
|
|
| Re: Index creation.... [message #273210 is a reply to message #273204] |
Tue, 09 October 2007 09:14  |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
1) Please format that mess. I doubt many people will bother spending the time trying to decipher it at the moment.
2) You are aware that your "AND ROWNUM < :bind_variable" just mean that you're getting a limited number of rows at random.
Given that they seem to be the only bit of your query that prevents you updating a row with it's own values, I strongly suspect that your query isn't doing what you think it's doing.
3) There is a massive difference between having one index on lots of columns, and lots of indexes on one column each.
The single index on many columns will only (in general) be of any use when the leading column of the index is specified in the query.
As your leading column is HX_SLNO, and that isn't referenced in the query, I'd say you're wasting your time with the single index.
4) Replace select :"SYS_B_1" with SELECT null
5) Don't use the same prefix for the different copies of the same table. Even if it doesn't trip you up by letting you refer to the wrong row, it still makes it confusing.
If you want tuning advice, post an explain plan, and tell us what the query is intended to do.
|
|
|
|
| Re: Index creation.... [message #273432 is a reply to message #273204] |
Wed, 10 October 2007 07:53 |
lm_suresh
Messages: 24
Registered: September 2007
Location: Chennai,India
|
Junior Member |
|
|
Thanks for the reply.
I did the formatting using the tool
http://www.orafaq.com/cgi-bin/sqlformat/pp/utilities/sqlformatter.tpl
Since my query is dynamic (set and where clause are dynamic),
Is it possible to force this combined index with leading column in my where clause.
For example: This the another form of the same query..
but it takes long time because it is not using the index.
My leading column is old_MIDDLE_NAME.
Update PS_LOAN_NAME a set (MIDDLE_NAME,LAST_NAME,FIRST_NAME) = (Select
new_MIDDLE_NAME,LAST_NAME,FIRST_NAME from HX_NAMES_STG b
where
b.old_MIDDLE_NAME = a.MIDDLE_NAME and b.old_LAST_NAME = a.LAST_NAME and
b.old_FIRST_NAME = a.FIRST_NAME and rownum<2) where exists(select
:"SYS_B_1" from HX_NAMES_STG b where b.old_MIDDLE_NAME = a.MIDDLE_NAME and
b.old_LAST_NAME = a.LAST_NAME and b.old_FIRST_NAME = a.FIRST_NAME and
rownum<2)
CREATE INDEX SYSADM.HX_NAMES_STG_IDX_001
ON SYSADM.HX_NAMES_STG(HX_SLNO, OLD_NAME_FORMAL,
OLD_NAME_DISPLAY, OLD_LAST_NAME_PREF_NLD, OLD_PARTNER_ROY_PREFIX,
OLD_PARTNER_LAST_NAME, OLD_PREF_FIRST_NAME, OLD_NAME_AC,
OLD_SECOND_LAST_SRCH, OLD_SECOND_LAST_NAME, OLD_MIDDLE_NAME,
OLD_FIRST_NAME, OLD_LAST_NAME, OLD_FIRST_NAME_SRCH, OLD_LAST_NAME_SRCH,
OLD_NAME_TITLE, OLD_NAME_ROYAL_SUFFIX, OLD_NAME_ROYAL_PREFIX, OLD_NAME_SUFFIX,
OLD_NAME_PREFIX, OLD_NAME_INITIALS, OLD_NAME, OLD_COUNTRY_NM_FORMAT)
NOLOGGING TABLESPACE PSDEFAULT
Rows Row Source Operation
------- ---------------------------------------------------
0 UPDATE PS_LOAN_NAME (cr=2195241 pr=1145853 pw=0 time=180626700 us)
11 FILTER (cr=2120436 pr=1145844 pw=0 time=93720566 us)
126 TABLE ACCESS FULL PS_LOAN_NAME (cr=7 pr=0 pw=0 time=834 us)
4 COUNT STOPKEY (cr=2120429 pr=1145844 pw=0 time=179396036 us)
4 TABLE ACCESS FULL HX_NAMES_STG (cr=2120429 pr=1145844 pw=0 time=179395460 us)
4 COUNT STOPKEY (cr=74805 pr=9 pw=0 time=1228857 us)
4 TABLE ACCESS FULL HX_NAMES_STG (cr=74805 pr=9 pw=0 time=1228784 us)
Could you please guide me how can i use the combined index for dynamic sql. Or Is there any
other way to avoid creating index for each and every column because it is affecting the insert
performance.
[Updated on: Wed, 10 October 2007 07:58] Report message to a moderator |
|
|
|
|
|
| Re: Index creation.... [message #273455 is a reply to message #273437] |
Wed, 10 October 2007 09:31 |
mson77
Messages: 208
Registered: August 2007
Location: Brazil
|
Senior Member |
|
|
Hello lm_suresh,
Would you mind be a little bit more thorough?
1) You should format your code under orafaq formatter tool. But you **must** post it within "code" tag;
2) I can take your code and format it using my tool to read / to try to understand. But to read your trace... it is hard! There is no trace/execution plan formatter after web browser.
How do you post is how you will get help.
Hope this help... you and us too!
Regards,
mson77
|
|
|
|
Goto Forum:
Current Time: Sat Jun 01 00:12:18 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
