I don't think this is possible but I would like to confirm here. I would like to create a function based index to speed up the OH SO SLOW query that follows:
SELECT dmzu.ZIP_CODE FROM dm_zip_unq dmzu WHERE fnc_dist(some number,some number,dmzu.LATITUDE,dmzu.LONGITUDE)< power(100,2) )
Here is the function definition:
CREATE OR REPLACE FUNCTION fnc_Dist ( inp_lat DM_ZIP_UNQ.LATITUDE%TYPE, inp_lng DM_ZIP_UNQ.LONGITUDE%TYPE, dl_lat DM_ZIP_UNQ.LATITUDE%TYPE, dl_lng DM_ZIP_UNQ.LONGITUDE%TYPE) RETURN NUMBER IS
BEGIN
RETURN (POWER((69.1*(dl_lng - inp_lng ) * COS(inp_lat / 57.3)), 2) + POWER((69.1*(dl_lat - inp_lat)),2));
END fnc_Dist; /
....with those first 2 parameters, I won't be able to create the function based index correct? Any ideas on how to alter the design of the query?
The idea is to provide the functionality to locate a dealer of a certain product when given a certain longitude and latitude. I pretty much know the answer to my question but am looking for any and all design suggestions.
Thanks. BEER ME! chris
-----Original Message-----
Since SharePlex for Oracle and LiveReorg are dependant on redo log volumes, transaction sizes, and things like that we have developed a free utility that parses through the redo logs for some pre-defined amount of time, and let's you know how many operations are on each table, average operations per second, and peak operations per second. It should give you a really good idea where the activity is coming from. Send me a private email if you want me to get you set up on this, and I can send you the files, and the instructions on how to use it. Nick p.s. 28GB is good, but we've seen much more... around 100GB is massive, and we've seen maybe 2-3 databases up to 120GB per day. -----Original Message-----
Just had a thought here, have not tried it yet.
I have a database that I am I am sure there are a number of other factors I need
to consider, any ideas * Should I weight inserts, updates and deletes?
The goal is to identify the objects, then identify the
jobs that work on Thanks! - Ethan Fat City Network Services --
858-538-5051 http://www.fatcity.com
|
Title: RE: Oh Where Oh Where Is My Redo Coming From
- RE: function based indexes. Chris Stephens
- RE: function based indexes. Jamadagni, Rajendra
- Re: function based indexes. Bjørn Engsig