EddieAwad.com

A River of Useful or Just Fun Stuff

SQLSaturday Open for Registration

On the weekend of May 22, 2010, the Portland Development community is coming together in a way never experienced before.

Using the University of Portland campus, SQLSaturday, Portland Code Camp, and Portland Bar Camp are combining and coordinating efforts to bring 800-1000 regional technology professionals together for the opportunity to immerse themselves in seminars, presentations, group exploration, and networking. Participants will be able to engage in their preferred technology, as well as to 'sample' other options

Admittance to this event is free, all costs are covered by donations and sponsorships. Please register soon as seating is limited, and let friends and colleages know about the event.

>> Click here to register.

Portland SQLSaturday is encouraging presentations related to interoperability of any of the SQL platforms, including T-SQL (SQL Server), PostgreSQL, MySQL, and Oracle PL/SQL. Abstracts for Platform specific sessions are also encouraged. The event is scheduled on Saturday May 22, 2010 from 8:00 AM until 10:00 PM.

Most sessions will be one hour and fifteen minutes (1:15), and a few 'deep dive' sessions will be longer (2.5 hours). The  'deep dive' sessions will be scheduled for 7:00 PM (after the snacks and refreshments.) Also, throughout the day, there may be opportunities for 30 minute 'SQL snack' sessions.

>> Click here to submit your presentation abstract.

I encourage all Oracle SQL and PL/SQL enthusiasts living in the Pacific Northwest to present in and/or attend this not-to-be-missed event.

Oracle’s SQL Performance Analyzer White Paper

(download)

The SQL Performance Analyzer offers a comprehensive solution to enable users to forecast and analyze how a system change will impact SQL query plans and run time performance, so they can tune their system before they make the change in production. The SQL Performance Analyzer identifies potential problems that may occur and makes suggestions for avoiding any SQL performance degradation. It provides quantitative estimates of the system’s performance in the new environment with high confidence and performs a comparative analysis of the response time of the SQL workload thus allowing for an easy assessment of the change. In this paper we describe the architecture of the SQL Performance Analyzer, its usage model, and its integration points with other Oracle database components to form an end-to-end change management solution.

Think Twice When Creating a Materialized View with the ANSI Join Syntax

When we attempt to create a materialized view with the ANSI join syntax we are surprisingly rewarded with an ORA error.

create materialized view mv2
  refresh fast
  as
    select
      t.key    t_key     ,
      t.val    t_val     ,
      t2.key   t2_key    ,
      t2.amt   t2_amt    ,
      t.rowid  t_row_id  ,
      t2.rowid t2_row_id
    from
      T INNER JOIN T2 ON ( T.KEY = T2.T_KEY )
;
      T INNER JOIN T2 ON ( T.KEY = T2.T_KEY )
      *
ERROR at line 12:
ORA-12015: cannot create a fast refresh materialized view from a complex query
 

While this behaviour appears to be a bug at first glance, Metalink note 420856.1 explains that it is really an undocumented limitation of fast refresh materialized views.

An examination of the EXPLAIN_MVIEW results for this case points to some behind-the-scenes transformations with ANSI syntax which may be causing the limitation.

select
  my_mv_capabilities
  ( 'create materialized view mv2
    refresh fast
    as
      select
        t.key    t_key     ,
        t.val    t_val     ,
        t2.key   t2_key    ,
        t2.amt   t2_amt    ,
        t.rowid  t_row_id  ,
        t2.rowid t2_row_id
      from
        T INNER JOIN T2 ON ( T.KEY = T2.T_KEY )'
  , 'REFRESH_FAST_AFTER_INSERT'
  ) as mv_report
from dual ;
 
MV_REPORT
--------------------------------------------------------------------------------
Not Capable of:
  REFRESH_FAST_AFTER_INSERT
    inline view or subquery in FROM list not supported for this type MV
  REFRESH_FAST_AFTER_INSERT
    inline view or subquery in FROM list not supported for this type MV
  REFRESH_FAST_AFTER_INSERT
    view or subquery in from list
 
via sqlsnippets.com

Got bitten by this bug today (or is it just a limitation?). I had to get rid of ANSI join and use the good old Oracle specific join syntax.

Formatting SQL in Oracle SQL Developer Can Output Different Formats

I especially like the "Concatenated SQL" which sometimes can come in handy when writing dynamic SQL. However, I wish I could make it use single quotes instead of double quotes.