Home » SQL & PL/SQL » SQL & PL/SQL » Improving performance of a VIEW (11g)
Improving performance of a VIEW [message #443876] Wed, 17 February 2010 16:41 Go to next message
prax_14
Messages: 64
Registered: July 2008
Member
Hello Oracle champs,

I have two tables

1) Objects_table (Object_name)
2) Objects_in_tape_table (Object_name,Tape_Name)

Object_table will have all the objects in disk and tapes.
Objects_in_tape_table will have only objects in tapes.

I have a view joining the above two tables and have a column Object_in_tape which will have values 'Y' or 'N' to show if a object exist in tape or disk.


Create or replace view OBJECT_LOCATION
(object_name,Object_in_tape) as 
(select a.object_name,
        nvl(b.in_tape,'N') 
 from Objects_table a,
     (select object_name,'Y' "in_tape" from    
      objects_in_tape_table) b
 Where a.object_name = b.object_name (+));

OR

Create or replace view OBJECT_LOCATION
(object_name,Object_in_tape) as 
(select a.object_name,
        nvl(select 'Y' from Objects_in_tape_table b 
           where b.object_name = a.object_name  ,'N') 
 from Objects_table a);
  


Imagine this view can have Million records. When i do a select on this view filtering with a where clause on Object_in_tape it is really really slow.

Select * from OBJECT_LOCATION where Object_in_tape = 'Y';


Is there any way to to make it work faster.

Thanks in advance for any suggestion's.
Re: Improving performance of a VIEW [message #443878 is a reply to message #443876] Wed, 17 February 2010 16:53 Go to previous messageGo to next message
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
You could do a normal left outer join for the select.

(untested, since there is no test case) :

select a.object_name,
       nvl2(b.object_name,'N','Y') 
  from Objects_table a
  left outer join Objects_in_tape_table b
               on b.object_name = a.object_name;
Re: Improving performance of a VIEW [message #443880 is a reply to message #443876] Wed, 17 February 2010 17:18 Go to previous messageGo to next message
wakula
Messages: 150
Registered: February 2008
Location: Poland
Senior Member
Can you re-design the database?
Seems that you have duplicated data in the tables. Maybe you could use the table with "objects on tape" only and set the tape name to null for non-tape data?
I believe that you have not provided enough information to give a full answer. How many data rows are in the tables? How many rows are retrieved? Are there any additional constraints on the DB design? What is the expected response time?
It might be better is an experienced Oracle DBA can have a look at that DB to suggest improvement.
Re: Improving performance of a VIEW [message #443881 is a reply to message #443878] Wed, 17 February 2010 18:13 Go to previous messageGo to next message
prax_14
Messages: 64
Registered: July 2008
Member
ThomasG,

Thank you for your response, I tried creating the View based on your suggestion , but still the query on the view filtering on Object_in_tape column did not improve.
Re: Improving performance of a VIEW [message #443882 is a reply to message #443880] Wed, 17 February 2010 18:41 Go to previous messageGo to next message
prax_14
Messages: 64
Registered: July 2008
Member
wakula,

Thank you for your reply, I cannot redesign the tables right now as it is on our current working system.

Both the tables Objects_table and Objects_in_tape_table are huge.

How many data rows are in the tables?
Objects_table - 720,000 records
Objects_in_tape_table - 450,000 records

How many rows are retrieved?

I will have to use this view to find the count matching the filter and when select rows I only select 3000 records at a time.

Select count(*) from OBJECT_LOCATION where Object_in_tape = 'Y';

Select * from OBJECT_LOCATION where rownum <= 3000 and Object_in_tape = 'Y';


What is the expected response time?
Less than 5 seconds.
Re: Improving performance of a VIEW [message #443883 is a reply to message #443882] Wed, 17 February 2010 18:47 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
post results from following SQL statements

Select count(*) from OBJECT_LOCATION where Object_in_tape = 'Y';

Select count(*) from OBJECT_LOCATION;
Re: Improving performance of a VIEW [message #443884 is a reply to message #443883] Wed, 17 February 2010 18:57 Go to previous messageGo to next message
prax_14
Messages: 64
Registered: July 2008
Member
BlackSwan,

Thank you so much for your reply.Find below the information requested.

Select count(*) from OBJECT_LOCATION;

  COUNT(*)
----------
    728122
1 row selected.

Select count(*) from OBJECT_LOCATION where Object_in_tape = 'Y';

  COUNT(*)
----------
    429132
1 row selected.

Re: Improving performance of a VIEW [message #443885 is a reply to message #443884] Wed, 17 February 2010 19:02 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
429132 / 728122 = 0.589368265

WOW!
An index won't help when returning such a large percentage of rows.
So you do a Full Table Scan for this SQL.
Re: Improving performance of a VIEW [message #443915 is a reply to message #443876] Thu, 18 February 2010 00:29 Go to previous messageGo to next message
ora_baby
Messages: 89
Registered: May 2008
Member
use
objects_in_tape_table for tape='Y'
and
Objects_table - Objects_in_tape_table for tape ='N'

and may be adding conditions
Re: Improving performance of a VIEW [message #443958 is a reply to message #443915] Thu, 18 February 2010 02:47 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
SELECT object_name, 'Y' AS object_in_tape
FROM   objects_in_tape_table 
UNION ALL
SELECT object_name, 'N'
FROM   objects_table o
LEFT JOIN objects_in_tape_table t
       ON o.object_name = t.object_name
WHERE t.rowid IS NULL


This will improve the performance of a query on the view with OBJECT_IN_TAPE = 'Y', but OBJECT_IN_TAPE = 'N' or no predicate on OBJECT_IN_TAPE will still be crappy.

Ross Leishman
Re: Improving performance of a VIEW [message #443966 is a reply to message #443876] Thu, 18 February 2010 03:02 Go to previous message
wakula
Messages: 150
Registered: February 2008
Location: Poland
Senior Member
Let's try to bite it from another side.
You cannot re-design it.
You need to retrieve ~50% of the rows.
You need to get the response within 5 seconds.

How often is the query executed? Can you have a materialized view instead? The materialized view would be updated from time to time (so it won't exactly match the source tables all the time).
Previous Topic: problen in ref cursor
Next Topic: change date format
Goto Forum:
  


Current Time: Sat Feb 15 23:16:06 CST 2025