What gets written to redo on a rollback?
I recently had a situation where a process went down and Oracle had to roll back the transaction. The original transaction did around 60,000 inserts and/or updates. When Oracle did its rollback, it generated many redo logs in a short period of time, but there seems to be a wide variety of volume per redo log. I used Logminer to check what was written to these logs during the rollback. Some of them had 1500 - 2000 records written to them and others had as few as 1 or 2 records written. These, for the most part, were the same types of records (for example, delete records to roll back the effects of an insert). Why the disparity? It seems like in many cases, a lot of redo log is wasted. The reason I am asking is that so many logs were generated, it filled up my archive log directory before my script could run to clean them out. Anybody have an explanation? Bill Carle AT&T Database Administrator 816-995-3922 [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Carle, William T (Bill), ALCAS 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).
sqlplus question
Howdy, I am spooling my sqlplus output to a file with no headings and all the fields separated by a delimiter. I have a field that is defined as varchar2(56), but typically only 4 or 5 bytes are filled. Oracle recognizes that and if you select length(fld1) from the table, you will get 4. But if I spool this to a file, I always get the full 56 bytes padded with blanks. In other words, I get 4 bytes of data and 52 blanks for that field. I only want the four valid bytes so that my delimiter comes immediately after that 4th byte. My sqlplus options are as follows: set newpage 0 space 0 linesize 5000 pagesize 0 echo off recsep off feedback off heading off trimspool on colsep "|" Bill Carle AT&T Database Administrator 816-995-3922 [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Carle, William T (Bill), ALCAS 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).
RE: sqlplus question
I don't want just 4. It's variable length and I want the actual number of valid bytes. Bill Carle AT&T Database Administrator 816-995-3922 [EMAIL PROTECTED] -Original Message- Sent: Thursday, September 19, 2002 9:51 AM To: '[EMAIL PROTECTED]'; Carle, William T (Bill), ALCAS Subject:RE: sqlplus question If you want only 4 bytes, us the SUBSTR function to take only what you need. SQL> select ename, job 2 from emp; SMITH CLERK ALLEN SALESMAN WARD SALESMAN JONES MANAGER SQL> set colsep '|' SQL> / SMITH |CLERK ALLEN |SALESMAN WARD |SALESMAN JONES |MANAGER SQL> select substr(ename,1,4), job 2 from emp; SMIT|CLERK ALLE|SALESMAN WARD|SALESMAN JONE|MANAGER -Original Message- Sent: Thursday, September 19, 2002 9:19 AM To: Multiple recipients of list ORACLE-L Howdy, I am spooling my sqlplus output to a file with no headings and all the fields separated by a delimiter. I have a field that is defined as varchar2(56), but typically only 4 or 5 bytes are filled. Oracle recognizes that and if you select length(fld1) from the table, you will get 4. But if I spool this to a file, I always get the full 56 bytes padded with blanks. In other words, I get 4 bytes of data and 52 blanks for that field. I only want the four valid bytes so that my delimiter comes immediately after that 4th byte. My sqlplus options are as follows: set newpage 0 space 0 linesize 5000 pagesize 0 echo off recsep off feedback off heading off trimspool on colsep "|" Bill Carle AT&T Database Administrator 816-995-3922 [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Carle, William T (Bill), ALCAS 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Carle, William T (Bill), ALCAS 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).
RE: oracle 9i desupport dates
Yes. 9i Release 2 is the final version of 9i. That’s why it’s desupport date is so much further out. Bill Carle AT&T Database Administrator 816-995-3922 [EMAIL PROTECTED] -Original Message- From: Boivin, Patrice J [mailto:[EMAIL PROTECTED]] Sent: Tuesday, October 08, 2002 3:34 PM To: Multiple recipients of list ORACLE-L Subject: RE: oracle 9i desupport dates Will 9i Release 2 be the final version for 9i? Patrice Boivin Systems Analyst (Oracle Certified DBA) Systems Admin & Operations | Admin. et Exploit. des systèmes Technology Services | Services technologiques Informatics Branch | Direction de l'informatique Maritimes Region, DFO | Région des Maritimes, MPO E-Mail: [EMAIL PROTECTED] -Original Message- From: JOE TESTA [mailto:[EMAIL PROTECTED]] Sent: Tuesday, October 08, 2002 4:59 PM To: Multiple recipients of list ORACLE-L Subject: oracle 9i desupport dates 9.01, aka 9ir1 Error correction support(ECS) ends jun 30, 2003. <-- yes in like 8 months 9.2, aka9ir2, ECS ends sep 30, 2005. joe
Can I index this query?
Howdy, I have a table that has almost 2 million rows called eventqueueentry. The layout looks like this: Name Null?Type - EVENTID NOT NULL NUMBER(10) VER NOT NULL NUMBER(10) QUEUETYPE NOT NULL CHAR(16) PUBLISHER NOT NULL CHAR(16) CREATETIMENOT NULL DATE LASTREADTIME DATE REMOVETIME DATE CONTENTS NOT NULL VARCHAR2(4000) The users do a query that looks like this: SELECT EventId, QueueType, Publisher, CreateTime, LastReadTime, RemoveTime, Contents, Ver from EventQueueEntry where QueueType = 'CodeUpdate' AND Contents LIKE '%TrackingEventId=27668677%' ORDER BY EventId The queuetype field has only 3 different values. The value in the contents field is close to being unique (high cardinality) but, as you can see, they are picking off a value somewhere in the middle of a varchar2(4000) field. Understandably, their query is slow. Is there anything I can do with an index to speed this up? Bill Carle AT&T Database Administrator 816-995-3922 [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Carle, William T (Bill), ALCAS 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).
log_archive_max_processes
With Oracle version 9.2, it appears that the value of the log_archive_max_processes is defaulting to 2. I don't specify it in my init.ora file, but 2 archive processes are being started, ARC0 and ARC1. The value of this parameter is set to 2. Is this a feature of 9.2 we don't know about? Any documentation I have seen says it is supposed to default to 1. Bill Carle AT&T Database Administrator 816-995-3922 [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Carle, William T (Bill), ALCAS 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).
RE: Join vs. Subselect
Dan, Actually, there is an index on the objid within the subselect. Here is the actually query with the results of the tkprof: SELECT ObjId, ActionCode, RequestNumber, AbcDigits, DefDigits, SoClli, NodalSource, StatusCode, ReplacedRequestNumber, TransactionId, Domain, FinalHandlingTreatment, ServiceType, DivisionRevenueCode, StatusMessage, EffectiveDateTime, CreateDateTime, ProvisionStartDateTime,ProvisionCompleteDateTime, UserId, Notes, Version from ILSRONodalRequest where statusCode != 'X' and objid in ( select unique NodalRequest_ObjId from ILSRONodalRoute where messageRouteClli = 'CHRLNCLQN02') order by effectiveDateTime desc, createDateTime desc call count cpuelapsed disk querycurrentrows --- -- -- -- -- -- -- Parse1 0.05 0.04 0 0 0 0 Execute 2 0.00 0.00 0 0 0 0 Fetch2 2.50 108.18 5470 4660 3 2 --- -- -- -- -- -- -- total5 2.55 108.22 5470 4660 3 2 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 29 (RCONBIG) Rows Row Source Operation --- --- 2 SORT ORDER BY 2 HASH JOIN SEMI 146858TABLE ACCESS FULL ILSRONODALREQUEST 2TABLE ACCESS BY INDEX ROWID ILSRONODALROUTE 2 INDEX RANGE SCAN (object id 6393) Rows Execution Plan --- --- 0 SELECT STATEMENT GOAL: CHOOSE 2 SORT (ORDER BY) 2NESTED LOOPS 146858 VIEW OF 'VW_NSO_1' 2 SORT (UNIQUE) 2 TABLE ACCESS (BY INDEX ROWID) OF 'ILSRONODALROUTE' 0INDEX (RANGE SCAN) OF 'IX_ILSRONODALROUTE3' (NON-UNIQUE) 0 TABLE ACCESS (BY INDEX ROWID) OF 'ILSRONODALREQUEST' 0 INDEX (UNIQUE SCAN) OF 'IX_ILSRONODALREQUEST' (UNIQUE) Bill Carle AT&T Database Administrator 816-995-3922 [EMAIL PROTECTED] -Original Message- Sent: Tuesday, November 12, 2002 4:39 PM To: Multiple recipients of list ORACLE-L Subject:RE: Join vs. Subselect Bill, Without seeing the explain plans, I'll take a shot. I hope it makes sense... 1 assumption, which if incorrect invalidates everything. I assume that there is not an index on table2.objid. I think the issue is how statement 1 is executed. Since the subquery is not-correlated (IN instead of an equality condition), it is executed 1 time as a full table scan with the result set stored in memory (or disk if not sufficient room). The intent of the subquery is to build a result set that is compared in total to the output of the outer statement. Once the table1.objid is located in the result set of table2.objid, the condition evaluates to TRUE and no further reading of table2 result set is required. It sounds like it is doing a nested loop operation against data that is already in memory. In statement2, for each row in table1, matching data in table2 is to be retrieved, perhaps requiring multiple disk reads. In this case, it may be more efficient to use the index. We now have an equality condition, which probably results in a nested loop operation against a table and not a result set in memory. Hmm...clear as mud? In deference to Cary, Anjo, Gaja, Kirti, Tim, et.al., Which query runs faster and performs fewer LIOs? That's the true measure of which is the better one. Dan Fink -Original Message- Sent: Monday, November 11, 2002 9:24 AM To: Multiple recipients of list ORACLE-L Hi, Here is the situation. The application coded a query that looks like this: select * from table1 where objid in (select objid from table2); There is an index on objid in table 1 that isn't being used. An explain shows it is using this system view vw_nso_1 that is used to transform an IN subquery to a join. If you recode the query to: select a.* from table1 a, table2 b where a.objid = b.objid; Then it will use the index. My question is: shouldn't it use the index in both cases. I know the join is a better way to code it and I have told the application that, but I would think that the first way would use an index anyway. Ideas? Bill Carle AT&T Database Administrator 816-995-3922 [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Carle, William T (Bill), ALCAS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - T
RE: Join Query Question
select a.poc_hrid,c.description from pocs a, poctypes b, pocgroups c where a.poctype_id = b.poctype_id and b.pocgroup_id = c.pocgroup_id; Bill Carle AT&T Database Administrator 816-995-3922 [EMAIL PROTECTED] -Original Message- Sent: Thursday, November 14, 2002 12:30 PM To: Multiple recipients of list ORACLE-L Subject:Join Query Question All... I'm not even sure how to ask this question, but given three tables: pocs poc_id number primary key sn varchar2(32) poc_hrid varchar2(16) poctype_id number references poctypes(poctype_id) poctypes poctype_id number primary key tiernumber(2,0) description varchar2(64) pocgroup_id number references pocgroups(pocgroup_id) pocgroups - pocgroup_id number primary key description varchar2(16) I'm attempting to construct a single query that will provide the poc_hrid *AND* the pocgroup description to which that poc belongs. This is part of a web-based application which allows for many points-of-contact (e.g. SysAdmin, Apps, Network, etc.), but allows only members of a particular group to modify certain sections of the record. Any help will be greatly appreciated! Gary Chambers //-- // Lucent Technologies CIO/Servers/Unix // Senior Unix System Administrator // 4 Robbins Road, Westford, MA 01886 // 978-399-0481 / 888-480-6924 (Pager) // Nothing fancy and nothing Microsoft //-- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gary Chambers 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Carle, William T (Bill), ALCAS 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).
RE: oracle 9i
We're doing that and haven't had any problems. Bill Carle AT&T Database Administrator 816-995-3922 [EMAIL PROTECTED] -Original Message- Sent: Thursday, July 25, 2002 10:23 AM To: Multiple recipients of list ORACLE-L Subject:oracle 9i Hi All! We will use Oracle 9i db and I would like to know if Oracle8i client works fine with 9i database? Thanks. Greg. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Greg Faktor INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Carle, William T (Bill), ALCAS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
Importing .dbf files into Oracle
Hi, Is it possible to import .dbf files that were created in dbase III+ into Oracle? Bill Carle AT&T Database Administrator 816-995-3922 [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Carle, William T (Bill), ALCAS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
Download site for Oracle 9.2.0.1 Upgrade
Hi, Where can I find the site to download the 9.2.0.1 upgrade. We currently have 9.0.1' Bill Carle AT&T Database Administrator 816-995-3922 [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Carle, William T (Bill), ALCAS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
Oracle Data Compression
Howdy, I attended a seminar on Oracle data compression in version 9.2 this morning. There are no web-based handouts to look at. However, there are a few items on it that may be of interest: Data compression is based on duplicate fields Compression is only done on direct operations ( for example, an ordinary insert is not compressed) Indexed queries would not be any faster against compressed data (The index is not compressed, just the data.) Updates are 10-20% slower. Deletes are 10% faster. Full table scans are much faster. Bottom line is that this would mainly be used for a data warehousing application with lots of full table scans. It wouldn't be used for anything with lots of updating or online queries. Also, we were told that 9.2 is the terminal release of 9i. The next version will be 10i. Bill Carle AT&T Database Administrator 816-995-3922 [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Carle, William T (Bill), ALCAS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
RE: Schedule Statspack Report Creation
Erik, I don't automate creation of any reports, but I do automate creation of the snapshots. I have a script that is set up to take snapshots every hour and delete any snapshots over 7 days old. That way I always have a week's worth of snapshots taken every hour. If there is a problem and I need to examine the reports, I will produce a report for the time interval between the two appropriate snapshots. Bill Carle AT&T Database Administrator 816-995-3922 [EMAIL PROTECTED] -Original Message- Sent: Wednesday, August 28, 2002 11:52 AM To: Multiple recipients of list ORACLE-L Subject:Schedule Statspack Report Creation I would like to schedule the creation of statspack reports with DBMS_JOBs. The statsrep.sql script does not take any arguments and seems to require user interaction. Has anyone here tried to automate the creation of these reports? I am being asked to produce these reports by other people in my group who do not have access to the database. I am hoping that there is a way to automate this. Erik -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Erik Williams INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Carle, William T (Bill), ALCAS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
RE: SQL Query tuning help
Dennis, You're better off not having an index on the AM table. With 220,000 out of 250,000 rows having the same value, an index will do you more harm than good. You're not much better off on the SO table with only 12 different values out of 1.3 million. The final table SA has 281 different out of 1.3 million. I see why the optimizer chose a table scan. It has to look through most of the table anyway. I would try it with an index of each of your join fields plus a separate index on the ret field of the SA table. I wouldn't even try to index any other fields on the AM or SO tables. Actually, 16 second response time didn't sound too bad to me considering the tables you described. Bill Carle AT&T Database Administrator 816-995-3922 [EMAIL PROTECTED] -Original Message- Sent: Tuesday, September 10, 2002 2:19 PM To: Multiple recipients of list ORACLE-L Subject:SQL Query tuning help I am trying to tune a SQL query on Oracle 8.1.6. I have tried several optimizations, but so far have made no improvements. I would appreciate any suggestions. SELECT am.lid, am.name FROM am, so, sa WHERE so.lid = am.lid AND so.key_ = sa.so_key AND am.active = 1 AND so.code = 11 AND sa.ret = 'SB' ORDER BY am.name Tables: am - 250,000 rows, 220,000 rows have active = 1, the others are 0. so - 1.3 million rows, lid has 250,000 distinct values, key_ is unique, code has 12 values, evenly distributed. sa - 1.3 million rows, ret has 281 values, fairly evenly distributed. so_key is pretty unique. Now, you'll probably say there is essentially a 1-1 relationship between so and sa. You are right, but the developer insists this flexibility is essential. The query executes in 16 seconds and returns 185 rows. This is felt to be too slow for an online lookup screen. explain plan results: SELECT STATEMENT Cost = 2955 SORT ORDER BY HASH JOIN HASH JOIN TABLE ACCESS FULL SA TABLE ACCESS FULL SO TABLE ACCESS FULL AM Here is what I've tried so far: Using hints to force Oracle to use indexes. Query Plan SELECT STATEMENT Cost = 62031 SORT AGGREGATE NESTED LOOPS HASH JOIN TABLE ACCESS BY INDEX ROWID SA INDEX FULL SCAN SO_KEY3 TABLE ACCESS BY INDEX ROWID SO INDEX RANGE SCAN PRG_CODE3 TABLE ACCESS BY INDEX ROWID AM INDEX UNIQUE SCAN LID6 Timing result 25 minutes Next I tried creating new indexes that combine both the accessing column as well as the retrieved column, thinking that Oracle could get the result from the index block and not need to retrieve the data block. create index test1 on am (lid, active); create index test2 on sa (so_key, code); SELECT STATEMENT Cost = 2951 SORT AGGREGATE HASH JOIN HASH JOIN INDEX FULL SCAN TEST2 TABLE ACCESS FULL SO TABLE ACCESS BY INDEX ROWID AM INDEX RANGE SCAN TEST1 Hinting so Oracle will use the new indexes, for one table Oracle uses the index only and for the other table, Oracle hits both the index and table itself. Response time is slightly longer than the original query. At this point I'm fresh out of ideas, so any ideas would be appreciated. Thanks. Dennis Williams DBA Lifetouch, Inc. [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Carle, William T (Bill), ALCAS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
RE: Decode
Ruth, You can pull manuals off the web at this URL: http://download-east.oracle.com/otndoc/oracle9i/901_doc/nav/docindex.htm This is for 9i. If you have 8i, they have that too. Bill Carle AT&T Database Administrator 816-995-3922 [EMAIL PROTECTED] -Original Message- Sent: Wednesday, September 11, 2002 1:59 PM To: Multiple recipients of list ORACLE-L Subject:Decode Hello everyone, I am having a senior moment and I have forgetten the syntax for 'decode'. Can anyone give me a hint. I have leant out my sql books and of course I need them now. Thanks in advance, Ruth Gramolini Oracle DBA, Vermont Department of Taxes -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ruth Gramolini INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Carle, William T (Bill), ALCAS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
SQL Question
Hi, I have a table with 1 field and 2 dates: field1, date1, date2. I need to find the max value of date2 for all the field1, date1 combinations. Then I want to join the table to itself on field1 and find all the rows where field1 matches, date1 < date1, and max(date2) > max(date2). I did this in 2 queries. First I created a view as follows: create view v1 (f1, d1, d2) as select field1,date1,max(date2) from table1 group by field1,date1; Then I joined the 2 views together like this: select a.f1,a.d1,a.d2 from v1 a, v1 b where a.f1 = b.f1 and a.d1 < b.d1 and a.d2 > b.d2; This worked fine, but I was wondering if there was a way to do this in one query without having to create a view. Bill Carle AT&T Database Administrator 816-995-3922 [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Carle, William T (Bill), ALCAS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
RE: SMON Using Lots of Memory in 9i
Our application uses Glance. Bill Carle AT&T Database Administrator 816-995-3922 [EMAIL PROTECTED] -Original Message- Sent: Thursday, June 20, 2002 1:51 PM To: Multiple recipients of list ORACLE-L Subject:Re: SMON Using Lots of Memory in 9i Hello, how utility/tool said that SMON was using 50M+ ? regards... "Carle, William T (Bill), ALCAS" wrote: > Hi, > > We recently converted to 9i and the SMON process seems to be using lots of >memory. (more than 50 mb) Has anyone else seen this or can explain it? > > Bill Carle > AT&T > Database Administrator > 816-995-3922 > [EMAIL PROTECTED] > > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Carle, William T (Bill), ALCAS > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > 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). -- Danisment Gazi Unal http://www.ubTools.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Danisment Gazi Unal (ubTools) INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Carle, William T (Bill), ALCAS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
OCI and Oracle 9i - Install Question
Hi, One of my developers is trying to use OCI on our recently installed Oracle 9i instance. The size of the file libocijdbc9.sl is 142 kb in the 9i version and libocijdbc8.sl was 9 mb in the 8i version. How can I check to see that this was installed properly? Bill Carle AT&T Database Administrator 816-995-3922 [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Carle, William T (Bill), ALCAS INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists 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).
