Home » RDBMS Server » Performance Tuning » bad view - slow reports
bad view - slow reports [message #157859] Mon, 06 February 2006 14:28 Go to next message
mjschwenger
Messages: 137
Registered: July 2005
Location: US
Senior Member
I have an old bad setup of a bunch of reports running off a regular views. With the table growth, the reports are running slower and slower. The question is that I cannot change the reports - they'll run the way they are now for at least for 2-3 months till the development team puts the new one on production. In the same time, may be there's something I can do. I suggested replacing the views with Materialized views as in the past that have saved us a lot of time. But the management did not agree.
What else I can do the improve the views. Here is one of them:
CREATE VIEW DATA1 AS
select '1' as ord, s.test_id, s. ent1 as ent_id, 'Name' as data_type,
n.last_name || ', ' || n.first_name || ' ' || n.mid_name || ' ' || n.name_gen as data_value
from name n, s_con s
where s.conf_ent1 = n.ent_id
union
select '2' as ord, s.s_test_id, s.conf_ent1 as ent_id, 'Address' as data_type,
a.addr1 || ' ' || addr2 || ' ' || addr3 || ' ' || city || ' ' || state_code || ' ' || country_code as data_value
from s_con s, address a
where s.conf_ent1 = a.ent_id
union
select '3' as ord, s.s_test_id, s.conf_ent1 as ent_id,
(select t.num_desc from num_type t where n.num_type_id = t.num_type_id) as data_type, n.num_value as data_value
from s_con s, nums n
where s.conf_ent1 = n.ent_id
union
select '5' as ord, s.s_test_id, s.conf_ent1 as ent_id,
'Email' as data_type,e.email as data_value
from s_con s, emaile
where s.conf_ent1 = e.ent_id;

It uses the indexes on the name, addresss, e-mail tables but always does a FTS on s_con as it selects almost all of the tables.
Thanks a lot for the help, MJ
Re: bad view - slow reports [message #157871 is a reply to message #157859] Mon, 06 February 2006 16:39 Go to previous messageGo to next message
Art Metzer
Messages: 2480
Registered: December 2002
Senior Member
Ugh, can I just say that this data model reeks to high heaven.

The only real improvement I could suggest (other than designing the data model from scratch) would be to use UNION ALL instead of UNION.
CREATE OR REPLACE VIEW data1 (
    ord
,   test_id
,   ent_id
,   data_type
,   data_value
)
AS
SELECT  x.ord
,       s.test_id
,       DECODE(x.ord
        ,      '1', s.ent1
        ,      x.ent_id)                        ent_id
,       x.data_type
,       x.data_value
FROM   (SELECT '1'                              ord
        ,      n.ent_id                         ent_id
        ,      'Name'                           data_type
        ,      n.last_name
               || ', ' 
               || n.first_name
               || DECODE(n.mid_name
                  ,      NULL, NULL
                  ,      ' ' || n.mid_name)
               || DECODE(n.name_gen
                  ,      NULL, NULL
                  ,      ' ' || n.name_gen)     data_value
        FROM   name                  n
        UNION ALL
        SELECT '2'                              ord
        ,      a.ent_id                         ent_id
        ,      'Address'                        data_type
        ,      a.addr1
               || DECODE(a.addr2
                  ,      NULL, NULL
                  ,      a.addr2 || ' ')
               || DECODE(a.addr3
                  ,      NULL, NULL
                  ,      a.addr3 || ' ')
               || a.city
               || ' '
               || a.state_code
               || DECODE(a.country_code
                  ,      NULL, NULL
                  ,      ' ' || a.country_code) data_value
        FROM   address               a
        UNION ALL
        SELECT '3'                              ord
        ,      nu.ent_id                        ent_id
        ,      t.num_desc                       data_type
        ,      nu.num_value                     data_value
        FROM   nums                  nu
        ,      num_type              t
        WHERE  nu.num_type_id = t.num_type_id
        UNION ALL
        SELECT '5'                              ord
        ,      e.ent_id                         ent_id
        ,      'Email'                          data_type
        ,      e.email                          data_value
        FROM   email                 e) x
,       s_con                           s
WHERE   s.conf_ent1 = x.ent_id
The full table scan on S_CON is understandable. Remember, just because it's a full table scan doesn't mean it's bad.
Re: bad view - slow reports [message #157952 is a reply to message #157859] Tue, 07 February 2006 08:11 Go to previous message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
Just gave this a quick scan, but couldn't this be rewritten as a "single" query with all tables in the from clause and outer joins if needed because some s_con doesn't have a matching name, email, or address? Don't know if it would be any better or not, but might be another option to try if the rewrite is doable in your situation.

And is this view trying to take a perfectly nice normalized set of table and turn them into one of those entity attribute value sorts of non- data model types of data models? Ugh is right.
Previous Topic: help plzz, oracle slow response
Next Topic: Hint - First_Row
Goto Forum:
  


Current Time: Thu Mar 28 18:02:29 CDT 2024