| Aggregate Window Functions Causes Full Table Scan [message #336690] |
Mon, 28 July 2008 13:03  |
chrisK17
Messages: 12
Registered: January 2008
|
Junior Member |
|
|
Hey All,
I have a table that is used for auditing. The table has a multi-column index that is based off of a generated ID and a timestamp. We are looking to create a view that compares the current/previous values of each column to display the change history of a given record. To accomplish this, I am using the LAG aggregate function on each column. This works, but for some reason when I put my statement in a view, a full table scan is done against the base table. It does not use the index. If I run the SQL statement alone, it DOES use the index. What is causing this?
So a simplified example of my code would be:
SELECT column, lag(column,1) OVER (ORDER BY ID, changed_date) ...
FROM my_table
WHERE ID = 1234
This uses the index just fine (ranged scan). But when I create it as a view, it does a full table scan. Treating it as an inline view yields the same result:
SELECT * FROM (
SELECT column, lag(column,1) OVER (ORDER BY ID, changed_date) ...
FROM my_table) a
WHERE a.ID=1234
What causes this? Its the same query, I don't understand why the optimizer chooses to do a full table scan. Any help would be appreciated.
|
|
|
|
|
|
| Re: Aggregate Window Functions Causes Full Table Scan [message #336694 is a reply to message #336690] |
Mon, 28 July 2008 13:17  |
 |
Michel Cadot
Messages: 68765
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
| Quote: | What causes this? Its the same query,
|
The second one is syntaxically incorrect; don't oversimplify.
In short, analytical functions have 2 properties:
- they are execute at the final step in a query
- they are not relational and so prevent optimizer from rewriting.
So in the first query, Oracle applies index on id and then lag function and in the second one Oracle generates the inner query and then restrict to the id.
Note that the 2 queries are not equivalent.
In the first one lag applies ONLY on rows with id=1234 when it applies on the whole table for the second one.
Regards
Michel
[Updated on: Mon, 28 July 2008 13:17] Report message to a moderator |
|
|
|
| Re: Aggregate Window Functions Causes Full Table Scan [message #336717 is a reply to message #336694] |
Mon, 28 July 2008 15:03 |
chrisK17
Messages: 12
Registered: January 2008
|
Junior Member |
|
|
| Quote: |
So in the first query, Oracle applies index on id and then lag function and in the second one Oracle generates the inner query and then restrict to the id.
Note that the 2 queries are not equivalent.
In the first one lag applies ONLY on rows with id=1234 when it applies on the whole table for the second one.
|
I see, that makes sense. But what is curious to me is when I remove the "LAG" columns and use the same type of query, it does use the index. So:
SELECT * FROM (SELECT column1, column2, etc
FROM my_table) a
WHERE a.id = 1234
properly uses the index and returns the data quickly. I would think it would still result in a full scan since it should read each row then apply the filter.
Thanks for the insight though!
[Updated on: Mon, 28 July 2008 15:12] Report message to a moderator |
|
|
|
|
|
| Re: Aggregate Window Functions Causes Full Table Scan [message #336759 is a reply to message #336718] |
Mon, 28 July 2008 22:25 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Oracle cannot merge the ID predicate into the view because ID is in the ORDER BY clause of the analytic function.
Fortunately, I don't think you want to ORDER by ID; you really want to PARTITION by it. More fortunately still, Oracle should allow the ID predicate to be merged into the view past a PARTITION BY clause.
Try OVER (PARTITION BY BY ID ORDER BY changed_date)
Ross Leishman
|
|
|
|
|
|
| Re: Aggregate Window Functions Causes Full Table Scan [message #337020 is a reply to message #336759] |
Tue, 29 July 2008 12:33 |
chrisK17
Messages: 12
Registered: January 2008
|
Junior Member |
|
|
| rleishman wrote on Mon, 28 July 2008 22:25 | Oracle cannot merge the ID predicate into the view because ID is in the ORDER BY clause of the analytic function.
Fortunately, I don't think you want to ORDER by ID; you really want to PARTITION by it. More fortunately still, Oracle should allow the ID predicate to be merged into the view past a PARTITION BY clause.
Try OVER (PARTITION BY BY ID ORDER BY changed_date)
Ross Leishman
|
Well, that definitely addresses the issue with index. When I made this suggested change to my query, it went from 40 seconds to less then 1 second an no longer performed the full table scan. I will experiment with this solution a bit more, but you have definitely set me down the correct path. Thanks a lot!!!!
|
|
|
|
|
|
| Re: Aggregate Window Functions Causes Full Table Scan [message #337103 is a reply to message #337022] |
Tue, 29 July 2008 22:34 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Michel, the point is that the original query with no PARTITION BY was (almost certainly) wrong. The OP wants to compare a previous version of an ID to the current one, implying that comparing one ID to another is not only unnecessary but undesireable.
The PARTITION BY solve both the performance problem and the functional problem.
Ross Leishman
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
