function-based index for SUM(field) and GROUP BY

#1

I have a SQL-Statement which looks like:
SELECT A, B, SUM(C), SUM(D)
FROM MYTABLE
GROUP BY A, B
Is it possible to enhance the performance by creating a
function-based index say

CREATE INDEX BLA ON MYTABLE
(SUM(C),SUM(D) GROUP BY A, B)
TABLESPACE IND ...

curious Thomas

#2

No, but a materialized view comes to mind...

--
Cheers
Nuno Souto
wizofoz2k (AT) yahoo (DOT) com.au.nospam
"porschberg" <thomas.porschberg (AT) osp-dd (DOT) de> wrote

Quote:

I have a SQL-Statement which looks like:
SELECT A, B, SUM(C), SUM(D)
FROM MYTABLE
GROUP BY A, B
Is it possible to enhance the performance by creating a
function-based index say

CREATE INDEX BLA ON MYTABLE
(SUM(C),SUM(D) GROUP BY A, B)
TABLESPACE IND ...

curious Thomas

#3

Since you don't have any predicates in the WHERE clause, a FBI will not
help you. Your best bet is probably Nuno's suggestion of a MV.

HTH,
Brian

porschberg wrote:

Quote:

I have a SQL-Statement which looks like:
SELECT A, B, SUM(C), SUM(D)
FROM MYTABLE
GROUP BY A, B
Is it possible to enhance the performance by creating a
function-based index say

CREATE INDEX BLA ON MYTABLE
(SUM(C),SUM(D) GROUP BY A, B)
TABLESPACE IND ...

curious Thomas

--
================================================== =================

Brian Peasland
oracle_dba (AT) remove_spam (DOT) peasland.com

Remove the "remove_spam." from the email address to email me.

"I can give it to you cheap, quick, and good. Now pick two out of
the three"

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

function-based index for SUM(field) and GROUP BY

comp.databases.oracle.server comp.databases.oracle.server

function-based index for SUM(field) and GROUP BY - 06-26-2003 , 07:07 AM

Re: function-based index for SUM(field) and GROUP BY - 06-26-2003 , 07:42 AM

Re: function-based index for SUM(field) and GROUP BY - 06-26-2003 , 08:15 AM