Improving performance of a VIEW [message #443876] |
Wed, 17 February 2010 16:41  |
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   |
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   |
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 #443882 is a reply to message #443880] |
Wed, 17 February 2010 18:41   |
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 #443884 is a reply to message #443883] |
Wed, 17 February 2010 18:57   |
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 #443958 is a reply to message #443915] |
Thu, 18 February 2010 02:47   |
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  |
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).
|
|
|