function based indexes.

[Chris Stephens]

Title: RE: Oh Where Oh Where Is My Redo Coming From

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----- From: Nick Wagner [mailto:[EMAIL PROTECTED] Sent: Friday, February 21, 2003 12:50 PM To: Multiple recipients of list ORACLE-L Subject: RE: Oh Where Oh Where Is My Redo Coming From   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 ([EMAIL PROTECTED]) 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----- From: Post, Ethan [mailto:[EMAIL PROTECTED]] Sent: Friday, February 21, 2003 7:44 AM To: Multiple recipients of list ORACLE-L Subject: Oh Where Oh Where Is My Redo Coming From   Just had a thought here, have not tried it yet.  I have a database that I am working with that is generating 28 GB of redo each day.  I would really like to know what objects are generating all this redo without going through the hassle of mining a bunch of log files.  It occurred to me that if table monitoring is active and my stats are up to date I should be able to multiply the total number of updates, inserts and commits by the average row size and get a rough % of what objects are generating the most redo.  I am sure there are a number of other factors I need to consider, any ideas what they are? * Should I weight inserts, updates and deletes? * ?? The goal is to identify the objects, then identify the jobs that work on those objects and see if I can reduce redo.  I suspect a lot of this redo is being generated because of some poor design issues. Thanks! - Ethan -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Post, Ethan   INET: [EMAIL PROTECTED] Fat City Network Services    -- 858-538-5051 http://www.fatcity.com San Diego, California        -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from).  You may also send the HELP command for other information (like subscribing).