| DBMS_UTILTY [message #51366] |
Fri, 17 May 2002 17:46  |
Tony
Messages: 190
Registered: June 2001
|
Senior Member |
|
|
Is it possible that running this can cause problems for an application that generates it's own sql statements?? Here's what the statement should look like:
SELECT
ST_USERS.USERNAME,ST_USERS.FIRST_NAME,ST_USERS.LAST_NAMEST_USERS.MEMBER_TYPE FROM
ST_USERS,ST_USER_GROUPS WHERE ST_USERS.USERNAME =
ST_USER_GROUPS.USERNAME AND ST_USER_GROUPS.GROUP_NAME =
'CustomerCareRep_Level1' UNION SELECT ST_USERS.USERNAME FROM
ST_USERS,ST_USER_GROUPS WHERE ST_USERS.USERNAME =
ST_USER_GROUPS.USERNAME UNION SELECT ST_USERS.USERNAME FROM
ST_USERS,ST_USER_GROUPS WHERE ST_USERS.USERNAME =
ST_USER_GROUPS.USERNAME UNION SELECT
ST_USERS.FIRST_NAME,ST_USERS.LAST_NAME,
ST_USERS.MEMBER_TYPE FROM
ST_USERS,ST_USER_GROUPS WHERE ST_USERS.USERNAME =
ST_USER_GROUPS.USERNAME AND ST_USER_GROUPS.GROUP_NAME ='CustomerCareRep_Level2'
But, here is actually what is being generated and I'm trying to figure out if the DBA ran something that may have cause Oracle to change the query somehow.
SELECT
ST_USERS.FIRST_NAME,ST_USERS.LAST_NAME,ST_USERS.MEMBER_TYPE FROM
ST_USERS,ST_USER_GROUPS WHERE ST_USERS.USERNAME =
ST_USER_GROUPS.USERNAME AND ST_USER_GROUPS.GROUP_NAME =
'CustomerCareRep_Level1' UNION SELECT ST_USERS.USERNAME FROM
ST_USERS,ST_USER_GROUPS WHERE ST_USERS.USERNAME =
ST_USER_GROUPS.USERNAME UNION SELECT ST_USERS.USERNAME FROM
ST_USERS,ST_USER_GROUPS WHERE ST_USERS.USERNAME =
ST_USER_GROUPS.USERNAME UNION SELECT
ST_USERS.FIRST_NAME,ST_USERS.LAST_NAME,ST_USERS.MEMBER_TYPE FROM
ST_USERS,ST_USER_GROUPS WHERE ST_USERS.USERNAME =
ST_USER_GROUPS.USERNAME AND ST_USER_GROUPS.GROUP_NAME =
'CustomerCareRep_Level2'
The only thing that is different is it's excluding the st_users.username in the select.
Any thoughts?
Thanks
Tony
|
|
|
|
| Re: DBMS_UTILTY [message #51367 is a reply to message #51366] |
Fri, 17 May 2002 18:06  |
Tony
Messages: 190
Registered: June 2001
|
Senior Member |
|
|
This is what he ran:
EXECUTE SYS.DBMS_UTILITY.ANALYZE_SCHEMA(UPPER('SITE2'), 'ESTIMATE');
Thanks
|
|
|
|
| Re: DBMS_UTILTY [message #51384 is a reply to message #51366] |
Mon, 20 May 2002 08:50 |
Grant
Messages: 578
Registered: January 2002
|
Senior Member |
|
|
Yes it is possible. It depends on what optimization method you use. If you use Cost Based Optimization (CBO) you will want to analyze stats often to keep them up to date so it can CHOOSE the best access method. If you use Rule Based (RBO) you will not want to analyze stats on your tables/indexes. To check your mode do:
svrmgrl> show parameter optimzer_method
If it is CHOOSE you are using CBO. If not remove the statistics and run your query again.
SQL> exec dbms_utility.analyze_schema('SCHEMANAME','REMOVE');
I would suggest you search MetaLink on "optimizer_mode" and read up on this subject.
|
|
|
|
| Re: DBMS_UTILTY [message #51386 is a reply to message #51384] |
Mon, 20 May 2002 09:55 |
Grant
Messages: 578
Registered: January 2002
|
Senior Member |
|
|
|
If you use OPTIMIZR_MODE=CHOOSE and delete statistics you are using RULE based. If you analyze statistics you are using COST.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
