ANSI Standard Join Syntax vs. Oracle 8i Syntax

[Creepers]

The DBA just finished moving our test instance of Oracle from 8i to 9i. After doing so, I noticed that some of my views are running much slower. After doing some research, I learned that 9i supports ANSI Standard for join syntax (joins are in the FROM clause). Should I see a performance hit? If not, can someone offer a possible explaination as to why the views run slower in 9i vs. 8i

Thanks

 

[lewisp]

Have all the indexes been built correctly? Have statistics been generated against the tables in your view(s)?

Switch trace on for a session and check the explain plan for the queries on those views.

 

[Creepers]

Yes, indexes have been built correctly. Yes, statistics have been generated (by DBA who did not notice anything "unusual"). Have not had a chance to look at explain plan (ANSI vs. 8i).

Generally speaking, will I see a difference in performance when it comes to ANSI vs. 8i?
Thanks

 

[lewisp]

AFAIK there *should* be no noticeable difference in performance between the sytaxes.

Has your DBA set up the new DB in identical fashion? ie all tablespaces etc. are distributed the same way?

If you still have no joy I suggest you post this query to the Oracle 9i forum where it can be read by more experienced DBAs and 9i users.

http://www.tek-tips.com/threadminder.cfm?pid=759

 

[sem]

2 possible reasons: 9i resource greediness; lack of statistics.
I'm sure that both Oracle and ANSI join syntaxes produce similar plans, so don't worry and let your DBA to manage.


Regards, Dima

http://seminihin.narod.ru