Home » SQL & PL/SQL » SQL & PL/SQL » Aggregate Window Functions Causes Full Table Scan (Oracle 10.1.0.4.2 - HP UX PA RISC 11)
Aggregate Window Functions Causes Full Table Scan [message #336690] Mon, 28 July 2008 13:03 Go to next message
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 #336693 is a reply to message #336690] Mon, 28 July 2008 13:14 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

You have tables.
You have indexes.
You have data.
You may or may not have EXPLAIN PLANs.
You are stumped.

We have not actual SQL.
We are stumped, too.

Good Luck with solving your mystery!
Re: Aggregate Window Functions Causes Full Table Scan [message #336694 is a reply to message #336690] Mon, 28 July 2008 13:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
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 #336718 is a reply to message #336717] Mon, 28 July 2008 15:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In your last query Oracle rewrites your query to put the condition inside the inner query and then can use the index.
As I said analytical function avoid from this as the meaning is different if the condition is in inner query or in the outer one.

Regards
Michel
Re: Aggregate Window Functions Causes Full Table Scan [message #336759 is a reply to message #336718] Mon, 28 July 2008 22:25 Go to previous messageGo to next message
rleishman
Messages: 3724
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 #336789 is a reply to message #336759] Tue, 29 July 2008 00:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
rleishman wrote on Tue, 29 July 2008 05:25
Try
OVER (PARTITION BY BY ID ORDER BY changed_date)


But the meaning and result are different than the original query(queries).

Regards
Michel

[Updated on: Tue, 29 July 2008 00:55]

Report message to a moderator

Re: Aggregate Window Functions Causes Full Table Scan [message #337020 is a reply to message #336759] Tue, 29 July 2008 12:33 Go to previous messageGo to next message
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 #337022 is a reply to message #337020] Tue, 29 July 2008 12:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Take care of what I said: the 3 queries are different and will give different results.
Carefully think to what you want.

Regards
Michel

[Updated on: Tue, 29 July 2008 12:37]

Report message to a moderator

Re: Aggregate Window Functions Causes Full Table Scan [message #337103 is a reply to message #337022] Tue, 29 July 2008 22:34 Go to previous message
rleishman
Messages: 3724
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
Previous Topic: INSTEAD OF UPDATE TRIGGER DOUBT
Next Topic: Create random Date/and Time
Goto Forum:
  


Current Time: Fri Dec 02 16:52:23 CST 2016

Total time taken to generate the page: 0.17054 seconds