Analytic function performance costly

Question

I have a table_A (C1,C2,C3,.....,Cn, datestamp)

Note : Column datestamp is indexed

QRY1

select * from 
(select max(datestamp) dates from table_A) t,
table_A
where a.datestamp = t.dates

QRY2

   select * from (
   select a.* , max (datestamp) over() dates from table_A a))
   where datestamp = dates

Explain Paln of QRY1 using indexed scan (execyted in 1 sec) cost was very less.

But for QRY2 going for full table scan (execyted in 8 sec) cost was very high.

Just wanted to know why analytical function ignores indexes.

Thanks (I am usig PL/SQL Oracle 10g)

this is actually an interesting example. if you have an index on dates then the first sub query in QRY1 will probably do a `MIN/MAX` scan (cost of going down the tree, probably ~3), then fetching the row using the same index - one single row. i'm assuming that it's not unique so a range scan (probably cost about ~4) so it's pretty fast. the analytic function on the other hand - probably sort the data which cause a full table scan so it's much more expensive. you should use analytic functions where you use aggregate function on *portions* of the data (the partition by clause). — haki, Apr 10 '13 at 08:02

score 3 · Accepted Answer · answered Apr 10 '13 at 07:05

From the documentation:

Analytic functions compute an aggregate value based on a group of rows. They differ from aggregate functions in that they return multiple rows for each group.
...
Analytic functions are the last set of operations performed in a query except for the final ORDER BY clause. All joins and all WHERE, GROUP BY, and HAVING clauses are completed before the analytic functions are processed.

Your inner select is pulling all rows from your table because it has no where clause; since it has to fetch every row the index isn't helpful and it might as well do a full table scan. Using the index on datestamp would be worse - it would have to do a full scan of the index and then access every data row anyway, increasing the IO. If you run the inner select on its own you'll see it returns a lot of rows, not just one.

The outer select then matches a specific row (or rows) from the inner one. That comparison can't be pushed into the inner select because of when in the processing the analytic value is calculated.

Thanks Alex So you are saying to get latest data from daily transaction table we should go for conventional sub qry concept (QRY 1 in above example) Analytical will not work out efficently. — Avi, Apr 10 '13 at 07:11
@Avi - in this scenario, pulling a single record based on an indexed value, then yes, your testing has shown it to be more efficient. — Alex Poole, Apr 10 '13 at 07:15

Analytic function performance costly

1 Answers1