Home » SQL & PL/SQL » SQL & PL/SQL » how to avoid full table scan of base table while querying against view
icon11.gif  how to avoid full table scan of base table while querying against view [message #128814] Wed, 20 July 2005 15:54 Go to next message
sirloudon
Messages: 2
Registered: July 2005
Junior Member
Hi all

I have a query, which is querying against a seeded view but eventually results in full table scan of a large base table. Not sure how to avoid this kind of expensive full table scan. Any pointer to the right direction will be highly appreciated.

for example:

My query looks like as follows, the 'hrfg_salaries o101831' is the seeded view, but eventually it forces a full table scan of the base table (HR.HR_ALL_ORGANIZATION_UNITS_TL) according to the explain plan.



SELECT o101831.employee_number AS e119149, o101755.grade_maximum AS e120709,
o101755.grade_mid_value AS e120711, o101755.grade_minimum AS e120713,
o101801.grade_name AS e120732,
o101801.national_identifier AS e125189,
o101831.person_name AS e128364,
o101831.salary_change_date AS e132142,
o101831.salary_change_reason AS e132145,
o101801.level_50266 AS e368409,
SUM (o101831.percentage_increment) AS e127774_sum,
SUM (o101831.absolute_increment) AS e109077_sum,
TRUNC ( TO_NUMBER ((SUM (o101831.annual_salary)))
/ TO_NUMBER (o101755.grade_mid_value),
2
) AS c_1,
SUM (o101831.annual_salary) AS e110192_sum
FROM hrfg_employee_assignments o101736,
hrifg_grade_rates_bg o101755,
hrfg_person_assignment_history o101801,
hrfg_salaries o101831
WHERE ( (o101736.assignment_id = o101831.assignment_id)
AND ( o101801.grade_id = o101755.grade_id
AND o101801.salary_basis_id = o101755.pay_basis_id
)
AND ( o101801.assignment_id = o101831.assignment_id
AND o101801.record_start_date <= o101831.salary_change_date
AND o101801.record_end_date >= o101831.salary_change_date
)
)
AND (o101755.grade_rule_end_date =
TO_DATE ('47121231000000', 'YYYYMMDDHH24MISS')
)
AND (o101736.level_50266 <> '7H')
AND (o101736.user_assignment_status NOT IN
('Retirement', 'Terminate', 'Long Term Disability')
)
GROUP BY o101831.employee_number,
o101755.grade_maximum,
o101755.grade_mid_value,
o101755.grade_minimum,
o101801.grade_name,
o101801.national_identifier,
o101831.person_name,
o101831.salary_change_date,
o101831.salary_change_reason,
o101801.level_50266;



SQL> desc HR.HR_ALL_ORGANIZATION_UNITS_TL;
Name Null? Type
----------------------------------------- -------- ---------------------------

ORGANIZATION_ID NOT NULL NUMBER(15)
LANGUAGE NOT NULL VARCHAR2(4)
SOURCE_LANG NOT NULL VARCHAR2(4)
NAME NOT NULL VARCHAR2(240)
LAST_UPDATE_DATE DATE
LAST_UPDATED_BY NUMBER(15)
LAST_UPDATE_LOGIN NUMBER(15)
CREATED_BY NUMBER(15)
CREATION_DATE DATE

SQL> desc apps.HRFG_SALARIES;
Name Null? Type
----------------------------------------- -------- ---------------------------

BUSINESS_GROUP_NAME VARCHAR2(240)
PERSON_NAME VARCHAR2(240)
ASSIGNMENT_NUMBER VARCHAR2(30)
ANNUAL_SALARY NUMBER
EMPLOYEE_NUMBER VARCHAR2(30)
SALARY_CHANGE_DATE DATE
SALARY_AMOUNT NUMBER
ABSOLUTE_INCREMENT NUMBER
PERCENTAGE_INCREMENT NUMBER
SALARY_CHANGE_REASON VARCHAR2
....
ASSIGNMENT_ID NUMBER(10)
BUSINESS_GROUP_ID NUMBER(15)
ELEMENT_ENTRY_ID NUMBER(15)
ORGANIZATION_ID NUMBER(15)
.......
Re: how to avoid full table scan of base table while querying against view [message #129005 is a reply to message #128814] Thu, 21 July 2005 08:45 Go to previous messageGo to next message
mariaschreiber
Messages: 11
Registered: June 2005
Junior Member
Hy sirloudon,
maby full table scan is the best Oracle can do in your spezial situation.
But you can look if your tables are analyzed and if ther are the right indexes.

regards Maria
Re: how to avoid full table scan of base table while querying against view [message #129215 is a reply to message #128814] Fri, 22 July 2005 09:07 Go to previous messageGo to next message
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
Senior Member
I haven't looked at your specific query, but in general, I would tune a view by pretending it wasn't there and replacing it with the underlying query. Keeping in mind this from concepts:

Quote:


Use of Indexes Against Views
Oracle determines whether to use indexes for a query against a view by transforming the original query when merging it with the view's defining query.

Consider the following view:

CREATE VIEW employees_view AS
SELECT employee_id, last_name, salary, location_id
FROM employees JOIN departments USING (department_id)
WHERE departments.department_id = 10;

Now consider the following user-issued query:

SELECT last_name
FROM employees_view
WHERE employee_id = 9876;

The final query constructed by Oracle is:

SELECT last_name
FROM employees, departments
WHERE employees.department_id = departments.department_id AND
departments.department_id = 10 AND
employees.employee_id = 9876;

In all possible cases, Oracle merges a query against a view with the view's defining query and those of any underlying views. Oracle optimizes the merged query as if you issued the query without referencing the views. Therefore, Oracle can use indexes on any referenced base table columns, whether the columns are referenced in the view definition or in the user query against the view.

In some cases, Oracle cannot merge the view definition with the user-issued query. In such cases, Oracle may not use all indexes on referenced columns.


See Also:

Oracle Database Performance Tuning Guide for more information about query optimization



Re: how to avoid full table scan of base table while querying against view [message #129222 is a reply to message #129215] Fri, 22 July 2005 09:32 Go to previous message
sirloudon
Messages: 2
Registered: July 2005
Junior Member
Thanks smartin. You input helps a lot to explain the puzzle I have in my mind. Really appreciate it!
Previous Topic: EXECUTE IMMEDIATE Problem
Next Topic: select statement (merged threads)
Goto Forum:
  


Current Time: Sat Sep 20 18:00:33 CDT 2014

Total time taken to generate the page: 0.07525 seconds