Materialized View

From: The Magnet <art_at_unsu.com>
Date: Fri, 23 Oct 2009 16:56:38 -0700 (PDT)
Message-ID: <d4c55b0d-740a-4581-9d1c-9440ebe429f0_at_p36g2000vbn.googlegroups.com>

Hi,

Hope I can explain this properly:

I'm hoping this can be done in just 1 long SQL statement. We have several queries that gather counts from different tables. Each takes a while and since they are part of the same app, the screen takes a while. So, we want to use a materialized view.

Problem is that each column would need to be a query. The tables are different, though we basically need the same data from them, a date and a count of records on that date. So, each query will return different results, but yet we want 1 materialized view to contain the counts of the 6 tables. For example, here are 2 of the queries:

SELECT
   event_date, count(*) DIVIDENDS
FROM
  (
   SELECT

      DISTINCT(ticker) ticker,
      company_name company,
      div_rate_per_share dividend,
      announcement_date event_date,
      announcement_date announcement_date,
      div_expected_date ex_date,
      record_date record_date,
      payment_date payment_date
   FROM
      data_holder.expected_dividends

 UNION
   SELECT

      DISTINCT(ticker) ticker,
      company_name company,
      div_rate_per_share dividend,
      div_expected_date event_date,
      announcement_date announcement_date,
      div_expected_date ex_date,
      record_date record_date,
      payment_date payment_date

   FROM data_holder.expected_dividends
 UNION
.
.
.

And another one:

SELECT
   t.q0_expected_report_date, count(*) earnings FROM
   master_table m,
   estimates e,
   data_holder.eps_reports_ex5 t
WHERE

   e.m_ticker(+) = m.m_ticker AND
   m.ticker = t.ticker AND
   t.late_last_flag = 0 AND
   t.report_date = (SELECT MAX(a.report_date)

FROM
   data_holder.eps_reports_ex5 a
WHERE
   t.ticker = a.ticker AND
   a.late_last_flag = 0)
group by t.q0_expected_report_date
order by t.q0_expected_report_date;

So, each column in the view will represent a query of counts from a particular table. Is this possible? It would speed everything up since we have the results already.

I was able to create a view, but not with the proper data. My guess is some combination of ROW_NUMBER or COUNT or something like that.... Received on Fri Oct 23 2009 - 18:56:38 CDT