| Group by with null parameters [message #23722] |
Mon, 30 December 2002 14:50  |
favelill
Messages: 3
Registered: December 2002
|
Junior Member |
|
|
Hi to everyone,
I have a cursor like this:
cursor prestamos (p1 VARCHAR2,
p2 VARCHAR2,
p3 VARCHAR2,
p4 VARCHAR2,) is
SELECT
field1,
field2,
field3,
field4,
SUM(field6 + field7) THE_SUM
FROM Table
WHERE nvl(field1,'1') = p1
and nvl(field2,'1') = p2
and nvl(field3,'1') = p3
and nvl(field4,'1') = p4
GROUP BY field1, field2, field3, field4
ORDER BY field1;
We placed the nvl replacement because without it the values provided where erroneous, so we replace the null to '1' before calling this cursor.
The problem is that the field1 to field4 has indexes and with the nvl Oracle doen't look into the indexes and the cursor take a lot of time to resolve each call.
Is there another way to group the null values allowing Oracle to look into the indexes to solve each call?
|
|
|
|
| Re: Group by with null parameters [message #23731 is a reply to message #23722] |
Tue, 31 December 2002 07:41  |
Pete Rigano
Messages: 23
Registered: December 2002
|
Junior Member |
|
|
The nulls won't be indexed in conventional b-tree indexes so I'm assuming your worried about using indexes for the non-null values.
In Oracle8i, you could use function based indexes.
create index idx1 on
"Table"( nvl(field2,'1') );
Replace "Table" with your table names.
Then your query could use the indexes.
hth,
pr
|
|
|
|
| And without an additional Index? [message #23744 is a reply to message #23731] |
Thu, 02 January 2003 10:13 |
favelill
Messages: 3
Registered: December 2002
|
Junior Member |
|
|
The problem is that this table has something like 30 columns, and about 60 indexes (for near each column, and lot of combinations of columns).
Becouse of this for me is forbidden to create 4 more indexes. So, for this is there a workaround?, I know that the quickest solution would be to create them, but for admin. reasons I can't.
Thanks in advance.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
