RE: SQL Tuning Help
Dennis, I tend to treat use of hints for exceptional cases only. With collections CASTed as tables, I seem to have a generalized problem of tables involved being scanned FULL (not using the available indexes) and query response being slow. I can't seem to build a query with collections and have it use the indexes. I get a much better response if I: a)parse the input collection parameter and use dynamic SQL with IN list b) use the RULK hint We also Analyze all tables in the schema once a week and were hoping that the cast based optimizer would have detailed information available to use the correct access path. --- DENNIS WILLIAMS [EMAIL PROTECTED] wrote: Sundeep - Have you tried other hints, like FIRST_ROWS? Or are you trying to avoid hints entirely? Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, February 05, 2003 5:30 PM To: Multiple recipients of list ORACLE-L Oracle 8.1.7.4 on HP-UX I am using collections to pass multiple values from client to the database to gather values for more than input values. The queries produce the results but the without the RULE hint the response is dramatically slower. Following is just one of the examples but I have many many queries which exhibit the same behavior. Since RULE based optimization is headed for the chopping block we are wondering what is the alternative. SELECT eqp.equipment_id, eqp.manufacturer_code, eqp.model_num, eqp.equipment_serial_num, DECODE(SIGN(eqp.last_pm_performed_at_hrs - eqp.current_meter_reading_hrs),1, 'SMU_ADJUSTED',eqp.last_pm_performed_at_hrs) smu_adjusted, eqp.productlink_equipment_code, ecps.pm_schedule_name, epp.performed_datetime, DECODE(epp.comment_text,NULL,1,0) comments_available, emr.reading_date, emr.meter_reading_value, equipment_event_log.event_status(eqp.equipment_id,2), equipments_next_pm_due.pms_due_list(eqp.equipment_id) pms_due_list, equipments_next_pm_due.perform_at_hrs_cnt(eqp.equipment_id) next_pm_hrs FROM equipments eqp, equipment_meter_readings emr, equipment_pm_performed epp, equipment_class_pm_schedules ecps, TABLE(CAST(id_table_t(100071,100072,100073,100074) AS id_table_t)) eqp_list WHERE eqp.epp_id_last_pm_performed = epp.epp_id (+) AND eqp.emr_id_current_meter_reading = emr.emr_id (+) AND epp.ecps_id = ecps.ecps_id (+) AND eqp.equipment_id = eqp_list.column_value / Plan: SELECT STATEMENT Hint=CHOOSE 8 K 510 HASH JOIN OUTER 8 K 582 K 510 HASH JOIN OUTER 8 K 510 K 497 HASH JOIN OUTER 8 K 390 K 489 HASH JOIN 8 K 279 K 287 COLLECTION ITERATOR CONSTRUCTOR FETCH TABLE ACCESS FULL EQUIPMENTS 192 K 6 M 256 TABLE ACCESS FULL EQUIPMENT_METER_READINGS221 K 2 M 151 TABLE ACCESS FULL EQUIPMENT_PM_PERFORMED 96 1 K TABLE ACCESS FULL EQUIPMENT_CLASS_PM_SCHEDULES2 K 22 K4 Following is the Plan with /*+ RULE */ hint has the expected fast response and the desired plan: SELECT STATEMENT Hint=HINT: RULE NESTED LOOPS OUTER NESTED LOOPS OUTER NESTED LOOPS OUTER NESTED LOOPS COLLECTION ITERATOR CONSTRUCTOR FETCH TABLE ACCESS BY INDEX ROWID EQUIPMENTS INDEX UNIQUE SCAN EQP_PK TABLE ACCESS BY INDEX ROWID EQUIPMENT_PM_PERFORMED INDEX UNIQUE SCAN EPP_PK TABLE ACCESS BY INDEX ROWID EQUIPMENT_CLASS_PM_SCHEDULES INDEX UNIQUE SCAN ECPMS_PK TABLE ACCESS BY INDEX ROWID EQUIPMENT_METER_READINGS INDEX UNIQUE SCAN EMR_PK I have tried both versions IN (TABLE(CAST( as a predicate and as a pseudo-table in FROM (as in the query above) and it made no difference to the plan. I searched askTOM and heard similar sentiments about performance being echoed by other users but no solutions. Any tips or insights as to how to avoid the full table scans (all of which are 10-100M in size) of the large table without the RULE hint. A more thorough explanation of what is happening and why would be a bonus. TIA = Sundeep Maini Consultant Currently on Assignement at Caterpillar Peoria [EMAIL
SQL Tuning Help
Oracle 8.1.7.4 on HP-UX I am using collections to pass multiple values from client to the database to gather values for more than input values. The queries produce the results but the without the RULE hint the response is dramatically slower. Following is just one of the examples but I have many many queries which exhibit the same behavior. Since RULE based optimization is headed for the chopping block we are wondering what is the alternative. SELECT eqp.equipment_id, eqp.manufacturer_code, eqp.model_num, eqp.equipment_serial_num, DECODE(SIGN(eqp.last_pm_performed_at_hrs - eqp.current_meter_reading_hrs),1, 'SMU_ADJUSTED',eqp.last_pm_performed_at_hrs) smu_adjusted, eqp.productlink_equipment_code, ecps.pm_schedule_name, epp.performed_datetime, DECODE(epp.comment_text,NULL,1,0) comments_available, emr.reading_date, emr.meter_reading_value, equipment_event_log.event_status(eqp.equipment_id,2), equipments_next_pm_due.pms_due_list(eqp.equipment_id) pms_due_list, equipments_next_pm_due.perform_at_hrs_cnt(eqp.equipment_id) next_pm_hrs FROM equipments eqp, equipment_meter_readings emr, equipment_pm_performed epp, equipment_class_pm_schedules ecps, TABLE(CAST(id_table_t(100071,100072,100073,100074) AS id_table_t)) eqp_list WHERE eqp.epp_id_last_pm_performed = epp.epp_id (+) AND eqp.emr_id_current_meter_reading = emr.emr_id (+) AND epp.ecps_id = ecps.ecps_id (+) AND eqp.equipment_id = eqp_list.column_value / Plan: SELECT STATEMENT Hint=CHOOSE8 K 510 HASH JOIN OUTER 8 K 582 K 510 HASH JOIN OUTER 8 K 510 K 497 HASH JOIN OUTER 8 K 390 K 489 HASH JOIN 8 K 279 K 287 COLLECTION ITERATOR CONSTRUCTOR FETCH TABLE ACCESS FULL EQUIPMENTS 192 K 6 M 256 TABLE ACCESS FULL EQUIPMENT_METER_READINGS221 K 2 M 151 TABLE ACCESS FULL EQUIPMENT_PM_PERFORMED 96 1 K TABLE ACCESS FULL EQUIPMENT_CLASS_PM_SCHEDULES2 K 22 K4 Following is the Plan with /*+ RULE */ hint has the expected fast response and the desired plan: SELECT STATEMENT Hint=HINT: RULE NESTED LOOPS OUTER NESTED LOOPS OUTER NESTED LOOPS OUTER NESTED LOOPS COLLECTION ITERATOR CONSTRUCTOR FETCH TABLE ACCESS BY INDEX ROWID EQUIPMENTS INDEX UNIQUE SCAN EQP_PK TABLE ACCESS BY INDEX ROWID EQUIPMENT_PM_PERFORMED INDEX UNIQUE SCAN EPP_PK TABLE ACCESS BY INDEX ROWID EQUIPMENT_CLASS_PM_SCHEDULES INDEX UNIQUE SCAN ECPMS_PK TABLE ACCESS BY INDEX ROWID EQUIPMENT_METER_READINGS INDEX UNIQUE SCAN EMR_PK I have tried both versions IN (TABLE(CAST( as a predicate and as a pseudo-table in FROM (as in the query above) and it made no difference to the plan. I searched askTOM and heard similar sentiments about performance being echoed by other users but no solutions. Any tips or insights as to how to avoid the full table scans (all of which are 10-100M in size) of the large table without the RULE hint. A more thorough explanation of what is happening and why would be a bonus. TIA = Sundeep Maini Consultant Currently on Assignement at Caterpillar Peoria [EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: sundeep maini 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: SQL Tuning Help
Sundeep, Have you reviewed Tim Gorman's paper titled: 'The Search For Intelligent Life In The Cost-Based Optimizer'? Check it out at http://www.evdbt.com. It may help. - Kirti -Original Message- Sent: Wednesday, February 05, 2003 5:30 PM To: Multiple recipients of list ORACLE-L Oracle 8.1.7.4 on HP-UX I am using collections to pass multiple values from client to the database to gather values for more than input values. The queries produce the results but the without the RULE hint the response is dramatically slower. Following is just one of the examples but I have many many queries which exhibit the same behavior. Since RULE based optimization is headed for the chopping block we are wondering what is the alternative. SELECT eqp.equipment_id, eqp.manufacturer_code, eqp.model_num, eqp.equipment_serial_num, DECODE(SIGN(eqp.last_pm_performed_at_hrs - eqp.current_meter_reading_hrs),1, 'SMU_ADJUSTED',eqp.last_pm_performed_at_hrs) smu_adjusted, eqp.productlink_equipment_code, ecps.pm_schedule_name, epp.performed_datetime, DECODE(epp.comment_text,NULL,1,0) comments_available, emr.reading_date, emr.meter_reading_value, equipment_event_log.event_status(eqp.equipment_id,2), equipments_next_pm_due.pms_due_list(eqp.equipment_id) pms_due_list, equipments_next_pm_due.perform_at_hrs_cnt(eqp.equipment_id) next_pm_hrs FROM equipments eqp, equipment_meter_readings emr, equipment_pm_performed epp, equipment_class_pm_schedules ecps, TABLE(CAST(id_table_t(100071,100072,100073,100074) AS id_table_t)) eqp_list WHERE eqp.epp_id_last_pm_performed = epp.epp_id (+) AND eqp.emr_id_current_meter_reading = emr.emr_id (+) AND epp.ecps_id = ecps.ecps_id (+) AND eqp.equipment_id = eqp_list.column_value / Plan: SELECT STATEMENT Hint=CHOOSE8 K 510 HASH JOIN OUTER 8 K 582 K 510 HASH JOIN OUTER 8 K 510 K 497 HASH JOIN OUTER 8 K 390 K 489 HASH JOIN 8 K 279 K 287 COLLECTION ITERATOR CONSTRUCTOR FETCH TABLE ACCESS FULL EQUIPMENTS 192 K 6 M 256 TABLE ACCESS FULL EQUIPMENT_METER_READINGS221 K 2 M 151 TABLE ACCESS FULL EQUIPMENT_PM_PERFORMED 96 1 K TABLE ACCESS FULL EQUIPMENT_CLASS_PM_SCHEDULES2 K 22 K4 Following is the Plan with /*+ RULE */ hint has the expected fast response and the desired plan: SELECT STATEMENT Hint=HINT: RULE NESTED LOOPS OUTER NESTED LOOPS OUTER NESTED LOOPS OUTER NESTED LOOPS COLLECTION ITERATOR CONSTRUCTOR FETCH TABLE ACCESS BY INDEX ROWID EQUIPMENTS INDEX UNIQUE SCAN EQP_PK TABLE ACCESS BY INDEX ROWID EQUIPMENT_PM_PERFORMED INDEX UNIQUE SCAN EPP_PK TABLE ACCESS BY INDEX ROWID EQUIPMENT_CLASS_PM_SCHEDULES INDEX UNIQUE SCAN ECPMS_PK TABLE ACCESS BY INDEX ROWID EQUIPMENT_METER_READINGS INDEX UNIQUE SCAN EMR_PK I have tried both versions IN (TABLE(CAST( as a predicate and as a pseudo-table in FROM (as in the query above) and it made no difference to the plan. I searched askTOM and heard similar sentiments about performance being echoed by other users but no solutions. Any tips or insights as to how to avoid the full table scans (all of which are 10-100M in size) of the large table without the RULE hint. A more thorough explanation of what is happening and why would be a bonus. TIA = Sundeep Maini Consultant Currently on Assignement at Caterpillar Peoria [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Deshpande, Kirti 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: SQL Tuning Help
Sundeep - Have you tried other hints, like FIRST_ROWS? Or are you trying to avoid hints entirely? Dennis Williams DBA, 40%OCP Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, February 05, 2003 5:30 PM To: Multiple recipients of list ORACLE-L Oracle 8.1.7.4 on HP-UX I am using collections to pass multiple values from client to the database to gather values for more than input values. The queries produce the results but the without the RULE hint the response is dramatically slower. Following is just one of the examples but I have many many queries which exhibit the same behavior. Since RULE based optimization is headed for the chopping block we are wondering what is the alternative. SELECT eqp.equipment_id, eqp.manufacturer_code, eqp.model_num, eqp.equipment_serial_num, DECODE(SIGN(eqp.last_pm_performed_at_hrs - eqp.current_meter_reading_hrs),1, 'SMU_ADJUSTED',eqp.last_pm_performed_at_hrs) smu_adjusted, eqp.productlink_equipment_code, ecps.pm_schedule_name, epp.performed_datetime, DECODE(epp.comment_text,NULL,1,0) comments_available, emr.reading_date, emr.meter_reading_value, equipment_event_log.event_status(eqp.equipment_id,2), equipments_next_pm_due.pms_due_list(eqp.equipment_id) pms_due_list, equipments_next_pm_due.perform_at_hrs_cnt(eqp.equipment_id) next_pm_hrs FROM equipments eqp, equipment_meter_readings emr, equipment_pm_performed epp, equipment_class_pm_schedules ecps, TABLE(CAST(id_table_t(100071,100072,100073,100074) AS id_table_t)) eqp_list WHERE eqp.epp_id_last_pm_performed = epp.epp_id (+) AND eqp.emr_id_current_meter_reading = emr.emr_id (+) AND epp.ecps_id = ecps.ecps_id (+) AND eqp.equipment_id = eqp_list.column_value / Plan: SELECT STATEMENT Hint=CHOOSE8 K 510 HASH JOIN OUTER 8 K 582 K 510 HASH JOIN OUTER 8 K 510 K 497 HASH JOIN OUTER 8 K 390 K 489 HASH JOIN 8 K 279 K 287 COLLECTION ITERATOR CONSTRUCTOR FETCH TABLE ACCESS FULL EQUIPMENTS 192 K 6 M 256 TABLE ACCESS FULL EQUIPMENT_METER_READINGS221 K 2 M 151 TABLE ACCESS FULL EQUIPMENT_PM_PERFORMED 96 1 K TABLE ACCESS FULL EQUIPMENT_CLASS_PM_SCHEDULES2 K 22 K4 Following is the Plan with /*+ RULE */ hint has the expected fast response and the desired plan: SELECT STATEMENT Hint=HINT: RULE NESTED LOOPS OUTER NESTED LOOPS OUTER NESTED LOOPS OUTER NESTED LOOPS COLLECTION ITERATOR CONSTRUCTOR FETCH TABLE ACCESS BY INDEX ROWID EQUIPMENTS INDEX UNIQUE SCAN EQP_PK TABLE ACCESS BY INDEX ROWID EQUIPMENT_PM_PERFORMED INDEX UNIQUE SCAN EPP_PK TABLE ACCESS BY INDEX ROWID EQUIPMENT_CLASS_PM_SCHEDULES INDEX UNIQUE SCAN ECPMS_PK TABLE ACCESS BY INDEX ROWID EQUIPMENT_METER_READINGS INDEX UNIQUE SCAN EMR_PK I have tried both versions IN (TABLE(CAST( as a predicate and as a pseudo-table in FROM (as in the query above) and it made no difference to the plan. I searched askTOM and heard similar sentiments about performance being echoed by other users but no solutions. Any tips or insights as to how to avoid the full table scans (all of which are 10-100M in size) of the large table without the RULE hint. A more thorough explanation of what is happening and why would be a bonus. TIA = Sundeep Maini Consultant Currently on Assignement at Caterpillar Peoria [EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: sundeep maini 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
RE: sql tuning help
Tom, Apologies if this has been covered already but isn't this a prime candidate for the sum(decode()) trick with perhaps instr used to do the match. I think you could take count of all records and subtract the 1 or 0 for each of the specific ones. The select part would look horrendous but overall I think it would be faster as it would allow you to drive off the phy_id which I assume would be indexed. Iain Nicoll -Original Message- Sent: 06 December 2002 18:15 To: Multiple recipients of list ORACLE-L Tom, Actually it returns 1 record. Thanks Rick Mercadante, Thomas F To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] [EMAIL PROTECTED] cc: ate.ny.us Subject: RE: sql tuning help Sent by: [EMAIL PROTECTED] 12/06/2002 11:41 AM Please respond to ORACLE-L Rick, does this return *any* records at all? the only reason that I ask is that in the 'where' clause, it is saying: where p.phy_id = board_aaps.phy_id (+) and p.phy_id = board_aba.phy_id (+) and p.phy_id = board_abem.phy_id (+) and p.phy_id = board_abfp.phy_id (+) and p.phy_id = board_abim.phy_id (+) and p.phy_id = board_abp.phy_id (+) and p.phy_id = board_abr.phy_id (+) and p.phy_id = board_aobem.phy_id (+) and p.phy_id = board_aobfp.phy_id (+) and p.phy_id = board_aobim.phy_id (+) and p.phy_id = board_aobr.phy_id (+) and p.phy_id = board_other.phy_id (+) and p.phy_id = 1870; well, from my way of thinking, a single record from p.phy_id cannot be equal to all of the others at the same time. so no records should be returned at all. I think I would re-wright the entire mess using a set of UNIONS to accomplish the same thing. something like: select board_other.description strBrdNameOtherTHQuest ,decode(board_aaps.description, null,' ','X') ysnAAPSBoard ,decode(board_aba.description, null,' ','X') ysnABABoard ,decode(board_abem.description, null,' ','X') ysnABEMBoard ,decode(board_abfp.description, null,' ','X') ysnABFPoard ,decode(board_abim.description, null,' ','X') ysnABIMBoard ,decode(board_abp.description, null,' ','X') ysnABPBoard ,decode(board_abr.description, null,' ','X') ysnABRBoard ,decode(board_aobem.description, null,' ','X') ysnAOBEMBoard ,decode(board_aobfp.description, null,' ','X') ysnAOBFPBoard ,decode(board_aobim.description, null,' ','X') ysnAOBIMBAoard ,decode(board_aobr.description, null,' ','X') ysnAOBRBoard ,decode(board_other.description, null,' ','X') ysnOtherBoard from phy_boards pb, boards b, physicians p where p.phy_id = pb.phy_id and pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN ASSOCIATION OF PHYSICIAN SPECIALIST%' union select board_other.description strBrdNameOtherTHQuest ,decode(board_aaps.description, null,' ','X') ysnAAPSBoard ,decode(board_aba.description, null,' ','X') ysnABABoard ,decode(board_abem.description, null,' ','X') ysnABEMBoard ,decode(board_abfp.description, null,' ','X') ysnABFPoard ,decode(board_abim.description, null,' ','X') ysnABIMBoard ,decode(board_abp.description, null,' ','X') ysnABPBoard ,decode(board_abr.description, null,' ','X') ysnABRBoard ,decode(board_aobem.description, null,' ','X') ysnAOBEMBoard ,decode(board_aobfp.description, null,' ','X') ysnAOBFPBoard ,decode(board_aobim.description, null,' ','X') ysnAOBIMBAoard ,decode(board_aobr.description, null,' ','X') ysnAOBRBoard ,decode(board_other.description, null,' ','X') ysnOtherBoard from phy_boards pb, boards b, physicians p where p.phy_id = pb.phy_id and pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN BOARD OF ANESTHESIOLOGY%' etc. / Does this make sense? Hope this helps. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, December 06, 2002 7:54 AM To: Multiple recipients of list ORACLE-L Hi, Oracle 8.1.6 NT 4.0 I have a rather complex query a developer gave to me to try to improve performance. There are 3 tables used. All relevant columns used are indexed. The tables have been analyzed SQLWKS select count(*) from physicians; COUNT(*) -- 340043 1 row selected. SQLWKS select count(*) from boards; COUNT(*) -- 220 1 row selected. SQLWKS select count(*) from phy_boards; COUNT(*) -- 450674 Below is the sql statement and explain plan. I see one FTS on 440,000+ records but cannot tell exactly what statement it is and how to resolve Any suggestions
sql tuning help
Hi, Oracle 8.1.6 NT 4.0 I have a rather complex query a developer gave to me to try to improve performance. There are 3 tables used. All relevant columns used are indexed. The tables have been analyzed SQLWKS select count(*) from physicians; COUNT(*) -- 340043 1 row selected. SQLWKS select count(*) from boards; COUNT(*) -- 220 1 row selected. SQLWKS select count(*) from phy_boards; COUNT(*) -- 450674 Below is the sql statement and explain plan. I see one FTS on 440,000+ records but cannot tell exactly what statement it is and how to resolve Any suggestions on how to optimize is appreciated. Thanks Rick select board_other.description strBrdNameOtherTHQuest ,decode(board_aaps.description, null,' ','X') ysnAAPSBoard ,decode(board_aba.description, null,' ','X') ysnABABoard ,decode(board_abem.description, null,' ','X') ysnABEMBoard ,decode(board_abfp.description, null,' ','X') ysnABFPoard ,decode(board_abim.description, null,' ','X') ysnABIMBoard ,decode(board_abp.description, null,' ','X') ysnABPBoard ,decode(board_abr.description, null,' ','X') ysnABRBoard ,decode(board_aobem.description, null,' ','X') ysnAOBEMBoard ,decode(board_aobfp.description, null,' ','X') ysnAOBFPBoard ,decode(board_aobim.description, null,' ','X') ysnAOBIMBAoard ,decode(board_aobr.description, null,' ','X') ysnAOBRBoard ,decode(board_other.description, null,' ','X') ysnOtherBoard from physicians p ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN ASSOCIATION OF PHYSICIAN SPECIALIST%') board_aaps ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN BOARD OF ANESTHESIOLOGY%') board_aba ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN BOARD OF EMERGENCY MEDICINE%') board_abem ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN BOARD OF FAMILY PRACTICE%') board_abfp ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN BOARD OF INTERNAL MEDICINE%') board_abim ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN BOARD OF PEDIATRICS%') board_abp ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN BOARD OF RADIOLOGY%') board_abr ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN OSTEOPATHIC BOARD OF EMERGENCY MEDICINE%') board_aobem ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN OSTEOPATHIC BOARD OF FAMILY PHYSICIANS%') board_aobfp ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN OSTEOPATHIC BOARD OF INTERNAL MEDICINE%') board_aobim ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and
Re: sql tuning help
My first thought would be that the plan shows a full table scan for phy_boards for each in-line view. This is almost certainly due to the 'pb.expiration_Date is null' condition in the where clause of each in-line view. Since Oracle does not store NULLs in an index (except for bitmaps), that condition requires a full table scan. -Mark On Fri, 2002-12-06 at 07:53, [EMAIL PROTECTED] wrote: Hi, Oracle 8.1.6 NT 4.0 I have a rather complex query a developer gave to me to try to improve performance. There are 3 tables used. All relevant columns used are indexed. The tables have been analyzed SQLWKS select count(*) from physicians; COUNT(*) -- 340043 1 row selected. SQLWKS select count(*) from boards; COUNT(*) -- 220 1 row selected. SQLWKS select count(*) from phy_boards; COUNT(*) -- 450674 Below is the sql statement and explain plan. I see one FTS on 440,000+ records but cannot tell exactly what statement it is and how to resolve Any suggestions on how to optimize is appreciated. Thanks Rick select board_other.description strBrdNameOtherTHQuest ,decode(board_aaps.description, null,' ','X') ysnAAPSBoard ,decode(board_aba.description, null,' ','X') ysnABABoard ,decode(board_abem.description, null,' ','X') ysnABEMBoard ,decode(board_abfp.description, null,' ','X') ysnABFPoard ,decode(board_abim.description, null,' ','X') ysnABIMBoard ,decode(board_abp.description, null,' ','X') ysnABPBoard ,decode(board_abr.description, null,' ','X') ysnABRBoard ,decode(board_aobem.description, null,' ','X') ysnAOBEMBoard ,decode(board_aobfp.description, null,' ','X') ysnAOBFPBoard ,decode(board_aobim.description, null,' ','X') ysnAOBIMBAoard ,decode(board_aobr.description, null,' ','X') ysnAOBRBoard ,decode(board_other.description, null,' ','X') ysnOtherBoard from physicians p ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN ASSOCIATION OF PHYSICIAN SPECIALIST%') board_aaps ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN BOARD OF ANESTHESIOLOGY%') board_aba ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN BOARD OF EMERGENCY MEDICINE%') board_abem ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN BOARD OF FAMILY PRACTICE%') board_abfp ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN BOARD OF INTERNAL MEDICINE%') board_abim ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN BOARD OF PEDIATRICS%') board_abp ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN BOARD OF RADIOLOGY%') board_abr ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN OSTEOPATHIC BOARD OF EMERGENCY MEDICINE%') board_aobem ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN OSTEOPATHIC BOARD OF FAMILY PHYSICIANS%') board_aobfp ,(select distinct pb.phy_id, b.name, b.description from
RE: sql tuning help
Hmmm - this is a Friday afternoon, you know. My suggestion is to forget it until Monday - don't spoil your weekend peter edinburgh -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: 06 December 2002 12:54 To: Multiple recipients of list ORACLE-L Subject: sql tuning help Hi, Oracle 8.1.6 NT 4.0 I have a rather complex query a developer gave to me to try to improve performance. There are 3 tables used. All relevant columns used are indexed. The tables have been analyzed SQLWKS select count(*) from physicians; COUNT(*) -- 340043 1 row selected. SQLWKS select count(*) from boards; COUNT(*) -- 220 1 row selected. SQLWKS select count(*) from phy_boards; COUNT(*) -- 450674 Below is the sql statement and explain plan. I see one FTS on 440,000+ records but cannot tell exactly what statement it is and how to resolve Any suggestions on how to optimize is appreciated. Thanks Rick select board_other.description strBrdNameOtherTHQuest ,decode(board_aaps.description, null,' ','X') ysnAAPSBoard ,decode(board_aba.description, null,' ','X') ysnABABoard ,decode(board_abem.description, null,' ','X') ysnABEMBoard ,decode(board_abfp.description, null,' ','X') ysnABFPoard ,decode(board_abim.description, null,' ','X') ysnABIMBoard ,decode(board_abp.description, null,' ','X') ysnABPBoard ,decode(board_abr.description, null,' ','X') ysnABRBoard ,decode(board_aobem.description, null,' ','X') ysnAOBEMBoard ,decode(board_aobfp.description, null,' ','X') ysnAOBFPBoard ,decode(board_aobim.description, null,' ','X') ysnAOBIMBAoard ,decode(board_aobr.description, null,' ','X') ysnAOBRBoard ,decode(board_other.description, null,' ','X') ysnOtherBoard from physicians p ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN ASSOCIATION OF PHYSICIAN SPECIALIST%') board_aaps ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN BOARD OF ANESTHESIOLOGY%') board_aba ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN BOARD OF EMERGENCY MEDICINE%') board_abem ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN BOARD OF FAMILY PRACTICE%') board_abfp ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN BOARD OF INTERNAL MEDICINE%') board_abim ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN BOARD OF PEDIATRICS%') board_abp ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN BOARD OF RADIOLOGY%') board_abr ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN OSTEOPATHIC BOARD OF EMERGENCY MEDICINE%') board_aobem ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN OSTEOPATHIC BOARD OF FAMILY PHYSICIANS%') board_aobfp ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id
Re: sql tuning help
|BOARD_NAM | 3 | 207 | | TABLE ACCESS BY INDEX ROWID |PHY_BOARD | 443K| 5M| | INDEX RANGE SCAN |PBRD_BOAR | 443K| | | VIEW| | 8K| 922K| | SORT UNIQUE| | 8K| 649K| |NESTED LOOPS | | 8K| 649K| | INDEX FAST FULL SCAN |BOARD_NAM | 3 | 207 | | TABLE ACCESS BY INDEX ROWID |PHY_BOARD | 443K| 5M| | INDEX RANGE SCAN|PBRD_BOAR | 443K| | | VIEW | | 8K| 922K| | SORT UNIQUE | | 8K| 649K| | NESTED LOOPS | | 8K| 649K| |INDEX FAST FULL SCAN |BOARD_NAM | 3 | 207 | |TABLE ACCESS BY INDEX ROWID |PHY_BOARD | 443K| 5M| | INDEX RANGE SCAN |PBRD_BOAR | 443K| | |VIEW | | 8K| 922K| | SORT UNIQUE | | 8K| 649K| | NESTED LOOPS| | 8K| 649K| | INDEX FAST FULL SCAN |BOARD_NAM | 3 | 207 | | TABLE ACCESS BY INDEX ROWID|PHY_BOARD | 443K| 5M| |INDEX RANGE SCAN |PBRD_BOAR | 443K| | | VIEW | | 443K| 48M| |SORT UNIQUE | | 443K| 34M| | HASH JOIN| | 443K| 34M| | INDEX FAST FULL SCAN|BOARD_NAM | 190 | 12K| | TABLE ACCESS FULL |PHY_BOARD | 443K| 5M| Rick Mark J. Bobak To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] [EMAIL PROTECTED] cc: t Subject: Re: sql tuning help Sent by: [EMAIL PROTECTED] om 12/06/2002 10:19 AM Please respond to ORACLE-L My first thought would be that the plan shows a full table scan for phy_boards for each in-line view. This is almost certainly due to the 'pb.expiration_Date is null' condition in the where clause of each in-line view. Since Oracle does not store NULLs in an index (except for bitmaps), that condition requires a full table scan. -Mark On Fri, 2002-12-06 at 07:53, [EMAIL PROTECTED] wrote: Hi, Oracle 8.1.6 NT 4.0 I have a rather complex query a developer gave to me to try to improve performance. There are 3 tables used. All relevant columns used are indexed. The tables have been analyzed SQLWKS select count(*) from physicians; COUNT(*) -- 340043 1 row selected. SQLWKS select count(*) from boards; COUNT(*) -- 220 1 row selected. SQLWKS select count(*) from phy_boards; COUNT(*) -- 450674 Below is the sql statement and explain plan. I see one FTS on 440,000+ records but cannot tell exactly what statement it is and how to resolve Any suggestions on how to optimize is appreciated. Thanks Rick select board_other.description strBrdNameOtherTHQuest ,decode(board_aaps.description, null,' ','X') ysnAAPSBoard ,decode
RE: sql tuning help
Title: RE: sql tuning help Something like this might help ... SELECT p.phy_id ,CASE WHEN (b.description LIKE 'AMERICAN ASSOCIATION OF PHYSICIAN SPECIALIST%' AND cnt 0 ) THEN 'X' ELSE ' ' END ,CASE WHEN (b.description LIKE 'AMERICAN BOARD OF ANESTHESIOLOGY%' AND cnt 0 ) THEN 'X' ELSE ' ' END ,CASE WHEN (b.description LIKE 'AMERICAN BOARD OF EMERGENCY MEDICINE%' AND cnt 0 ) THEN 'X' ELSE ' ' END ,CASE WHEN (b.description LIKE 'AMERICAN BOARD OF FAMILY PRACTICE%' AND cnt 0 ) THEN 'X' ELSE ' ' END ,CASE WHEN (b.description LIKE 'AMERICAN BOARD OF INTERNAL MEDICINE%' AND cnt 0 ) THEN 'X' ELSE ' ' END ,CASE WHEN (b.description LIKE 'AMERICAN BOARD OF PEDIATRICS%' AND cnt 0 ) THEN 'X' ELSE ' ' END ,CASE WHEN (b.description LIKE 'AMERICAN BOARD OF RADIOLOGY%' AND cnt 0 ) THEN 'X' ELSE ' ' END ,CASE WHEN (b.description LIKE 'AMERICAN OSTEOPATHIC BOARD OF EMERGENCY MEDICINE%' AND cnt 0 ) THEN 'X' ELSE ' ' END ,CASE WHEN (b.description LIKE 'AMERICAN OSTEOPATHIC BOARD OF FAMILY PHYSICIANS%' AND cnt 0 ) THEN 'X' ELSE ' ' END ,CASE WHEN (b.description LIKE 'AMERICAN OSTEOPATHIC BOARD OF INTERNAL MEDICINE%' AND cnt 0 ) THEN 'X' ELSE ' ' END ,CASE WHEN (b.description LIKE 'AMERICAN OSTEOPATHIC BOARD OF RADIOLOGY%' AND cnt 0 ) THEN 'X' ELSE ' ' END ,CASE WHEN (b.description LIKE 'NO BOARDS%' ) THEN 'X' ELSE ' ' END FROM( SELECT p.phy_id ,b.description ,pb.COUNT(board_id) cnt FROM physicians p ,phy_boards pb ,boards b WHERE p.phy_id = 1870 AND NVL(pb_expiration_date, SYSDATE+1) = SYSDATE GROUP BY p.phy_id, b.description ) / I haven't tested this ... buit should be pretty close. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Friday, December 06, 2002 7:54 AM To: Multiple recipients of list ORACLE-L Subject: sql tuning help Hi, Oracle 8.1.6 NT 4.0 I have a rather complex query a developer gave to me to try to improve performance. There are 3 tables used. All relevant columns used are indexed. The tables have been analyzed SQLWKS select count(*) from physicians; COUNT(*) -- 340043 1 row selected. SQLWKS select count(*) from boards; COUNT(*) -- 220 1 row selected. SQLWKS select count(*) from phy_boards; COUNT(*) -- 450674 Below is the sql statement and explain plan. I see one FTS on 440,000+ records but cannot tell exactly what statement it is and how to resolve Any suggestions on how to optimize is appreciated. Thanks Rick select board_other.description strBrdNameOtherTHQuest ,decode(board_aaps.description, null,' ','X') ysnAAPSBoard ,decode(board_aba.description, null,' ','X') ysnABABoard ,decode(board_abem.description, null,' ','X') ysnABEMBoard ,decode(board_abfp.description, null,' ','X') ysnABFPoard ,decode(board_abim.description, null,' ','X') ysnABIMBoard ,decode(board_abp.description, null,' ','X') ysnABPBoard ,decode(board_abr.description, null,' ','X') ysnABRBoard ,decode(board_aobem.description, null,' ','X') ysnAOBEMBoard ,decode(board_aobfp.description, null,' ','X') ysnAOBFPBoard ,decode(board_aobim.description, null,' ','X') ysnAOBIMBAoard ,decode(board_aobr.description, null,' ','X') ysnAOBRBoard ,decode(board_other.description, null,' ','X') ysnOtherBoard from physicians p ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN ASSOCIATION OF PHYSICIAN SPECIALIST%') board_aaps ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN BOARD OF ANESTHESIOLOGY%') board_aba ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN BOARD OF EMERGENCY MEDICINE%') board_abem ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN BOARD OF FAMILY PRACTICE%') board_abfp ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN BOARD OF INTERNAL MEDICINE%') board_abim ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date
RE: sql tuning help
I think the problem (without really getting into how they coded this) is the nested-nested loops. try this hint: /*+ no_merge use_hash(board_aaps) use_hash(board_aba) use_hash(board_abem) use_hash(board_abfp) use_hash(board_abim) use_hash(board_abp) use_hash(board_abr) use_hash(board_aobem) use_hash(board_aobfp) use_hash(board_aobim) use_hash(board_aobr) use_hash(board_other) */ HTH Waleed -Original Message- Sent: Friday, December 06, 2002 7:54 AM To: Multiple recipients of list ORACLE-L Hi, Oracle 8.1.6 NT 4.0 I have a rather complex query a developer gave to me to try to improve performance. There are 3 tables used. All relevant columns used are indexed. The tables have been analyzed SQLWKS select count(*) from physicians; COUNT(*) -- 340043 1 row selected. SQLWKS select count(*) from boards; COUNT(*) -- 220 1 row selected. SQLWKS select count(*) from phy_boards; COUNT(*) -- 450674 Below is the sql statement and explain plan. I see one FTS on 440,000+ records but cannot tell exactly what statement it is and how to resolve Any suggestions on how to optimize is appreciated. Thanks Rick select board_other.description strBrdNameOtherTHQuest ,decode(board_aaps.description, null,' ','X') ysnAAPSBoard ,decode(board_aba.description, null,' ','X') ysnABABoard ,decode(board_abem.description, null,' ','X') ysnABEMBoard ,decode(board_abfp.description, null,' ','X') ysnABFPoard ,decode(board_abim.description, null,' ','X') ysnABIMBoard ,decode(board_abp.description, null,' ','X') ysnABPBoard ,decode(board_abr.description, null,' ','X') ysnABRBoard ,decode(board_aobem.description, null,' ','X') ysnAOBEMBoard ,decode(board_aobfp.description, null,' ','X') ysnAOBFPBoard ,decode(board_aobim.description, null,' ','X') ysnAOBIMBAoard ,decode(board_aobr.description, null,' ','X') ysnAOBRBoard ,decode(board_other.description, null,' ','X') ysnOtherBoard from physicians p ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN ASSOCIATION OF PHYSICIAN SPECIALIST%') board_aaps ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN BOARD OF ANESTHESIOLOGY%') board_aba ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN BOARD OF EMERGENCY MEDICINE%') board_abem ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN BOARD OF FAMILY PRACTICE%') board_abfp ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN BOARD OF INTERNAL MEDICINE%') board_abim ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN BOARD OF PEDIATRICS%') board_abp ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN BOARD OF RADIOLOGY%') board_abr ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN OSTEOPATHIC BOARD OF EMERGENCY MEDICINE%') board_aobem ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN OSTEOPATHIC BOARD OF FAMILY PHYSICIANS%') board_aobfp ,(select distinct pb.phy_id, b.name, b.description from
RE: sql tuning help
Rick, does this return *any* records at all? the only reason that I ask is that in the 'where' clause, it is saying: where p.phy_id = board_aaps.phy_id (+) and p.phy_id = board_aba.phy_id (+) and p.phy_id = board_abem.phy_id (+) and p.phy_id = board_abfp.phy_id (+) and p.phy_id = board_abim.phy_id (+) and p.phy_id = board_abp.phy_id (+) and p.phy_id = board_abr.phy_id (+) and p.phy_id = board_aobem.phy_id (+) and p.phy_id = board_aobfp.phy_id (+) and p.phy_id = board_aobim.phy_id (+) and p.phy_id = board_aobr.phy_id (+) and p.phy_id = board_other.phy_id (+) and p.phy_id = 1870; well, from my way of thinking, a single record from p.phy_id cannot be equal to all of the others at the same time. so no records should be returned at all. I think I would re-wright the entire mess using a set of UNIONS to accomplish the same thing. something like: select board_other.description strBrdNameOtherTHQuest ,decode(board_aaps.description, null,' ','X') ysnAAPSBoard ,decode(board_aba.description, null,' ','X') ysnABABoard ,decode(board_abem.description, null,' ','X') ysnABEMBoard ,decode(board_abfp.description, null,' ','X') ysnABFPoard ,decode(board_abim.description, null,' ','X') ysnABIMBoard ,decode(board_abp.description, null,' ','X') ysnABPBoard ,decode(board_abr.description, null,' ','X') ysnABRBoard ,decode(board_aobem.description, null,' ','X') ysnAOBEMBoard ,decode(board_aobfp.description, null,' ','X') ysnAOBFPBoard ,decode(board_aobim.description, null,' ','X') ysnAOBIMBAoard ,decode(board_aobr.description, null,' ','X') ysnAOBRBoard ,decode(board_other.description, null,' ','X') ysnOtherBoard from phy_boards pb, boards b, physicians p where p.phy_id = pb.phy_id and pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN ASSOCIATION OF PHYSICIAN SPECIALIST%' union select board_other.description strBrdNameOtherTHQuest ,decode(board_aaps.description, null,' ','X') ysnAAPSBoard ,decode(board_aba.description, null,' ','X') ysnABABoard ,decode(board_abem.description, null,' ','X') ysnABEMBoard ,decode(board_abfp.description, null,' ','X') ysnABFPoard ,decode(board_abim.description, null,' ','X') ysnABIMBoard ,decode(board_abp.description, null,' ','X') ysnABPBoard ,decode(board_abr.description, null,' ','X') ysnABRBoard ,decode(board_aobem.description, null,' ','X') ysnAOBEMBoard ,decode(board_aobfp.description, null,' ','X') ysnAOBFPBoard ,decode(board_aobim.description, null,' ','X') ysnAOBIMBAoard ,decode(board_aobr.description, null,' ','X') ysnAOBRBoard ,decode(board_other.description, null,' ','X') ysnOtherBoard from phy_boards pb, boards b, physicians p where p.phy_id = pb.phy_id and pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN BOARD OF ANESTHESIOLOGY%' etc. / Does this make sense? Hope this helps. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Friday, December 06, 2002 7:54 AM To: Multiple recipients of list ORACLE-L Hi, Oracle 8.1.6 NT 4.0 I have a rather complex query a developer gave to me to try to improve performance. There are 3 tables used. All relevant columns used are indexed. The tables have been analyzed SQLWKS select count(*) from physicians; COUNT(*) -- 340043 1 row selected. SQLWKS select count(*) from boards; COUNT(*) -- 220 1 row selected. SQLWKS select count(*) from phy_boards; COUNT(*) -- 450674 Below is the sql statement and explain plan. I see one FTS on 440,000+ records but cannot tell exactly what statement it is and how to resolve Any suggestions on how to optimize is appreciated. Thanks Rick select board_other.description strBrdNameOtherTHQuest ,decode(board_aaps.description, null,' ','X') ysnAAPSBoard ,decode(board_aba.description, null,' ','X') ysnABABoard ,decode(board_abem.description, null,' ','X') ysnABEMBoard ,decode(board_abfp.description, null,' ','X') ysnABFPoard ,decode(board_abim.description, null,' ','X') ysnABIMBoard ,decode(board_abp.description, null,' ','X') ysnABPBoard ,decode(board_abr.description, null,' ','X') ysnABRBoard ,decode(board_aobem.description, null,' ','X') ysnAOBEMBoard ,decode(board_aobfp.description, null,' ','X') ysnAOBFPBoard ,decode(board_aobim.description, null,' ','X') ysnAOBIMBAoard ,decode(board_aobr.description, null,' ','X') ysnAOBRBoard ,decode(board_other.description, null,' ','X') ysnOtherBoard from physicians p ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id
RE: sql tuning help
Title: RE: sql tuning help That is very wise advice. Don't touch production on Fridays has been a rule in previous shops I worked at. Happy Friday all! pow Lisa Koivu Oracle Database Supermom to 4 Boys. Fairfield Resorts, Inc. 5259 Coconut Creek Parkway Ft. Lauderdale, FL, USA 33063 -Original Message- From: Robson, Peter [SMTP:[EMAIL PROTECTED]] Sent: Friday, December 06, 2002 10:30 AM To: Multiple recipients of list ORACLE-L Subject: RE: sql tuning help Hmmm - this is a Friday afternoon, you know. My suggestion is to forget it until Monday - don't spoil your weekend peter edinburgh -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: 06 December 2002 12:54 To: Multiple recipients of list ORACLE-L Subject: sql tuning help Hi, Oracle 8.1.6 NT 4.0 I have a rather complex query a developer gave to me to try to improve performance. There are 3 tables used. All relevant columns used are indexed. The tables have been analyzed SQLWKS select count(*) from physicians; COUNT(*) -- 340043 1 row selected. SQLWKS select count(*) from boards; COUNT(*) -- 220 1 row selected. SQLWKS select count(*) from phy_boards; COUNT(*) -- 450674 Below is the sql statement and explain plan. I see one FTS on 440,000+ records but cannot tell exactly what statement it is and how to resolve Any suggestions on how to optimize is appreciated. Thanks Rick select board_other.description strBrdNameOtherTHQuest ,decode(board_aaps.description, null,' ','X') ysnAAPSBoard ,decode(board_aba.description, null,' ','X') ysnABABoard ,decode(board_abem.description, null,' ','X') ysnABEMBoard ,decode(board_abfp.description, null,' ','X') ysnABFPoard ,decode(board_abim.description, null,' ','X') ysnABIMBoard ,decode(board_abp.description, null,' ','X') ysnABPBoard ,decode(board_abr.description, null,' ','X') ysnABRBoard ,decode(board_aobem.description, null,' ','X') ysnAOBEMBoard ,decode(board_aobfp.description, null,' ','X') ysnAOBFPBoard ,decode(board_aobim.description, null,' ','X') ysnAOBIMBAoard ,decode(board_aobr.description, null,' ','X') ysnAOBRBoard ,decode(board_other.description, null,' ','X') ysnOtherBoard from physicians p ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN ASSOCIATION OF PHYSICIAN SPECIALIST%') board_aaps ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN BOARD OF ANESTHESIOLOGY%') board_aba ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN BOARD OF EMERGENCY MEDICINE%') board_abem ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN BOARD OF FAMILY PRACTICE%') board_abfp ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN BOARD OF INTERNAL MEDICINE%') board_abim ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN BOARD OF PEDIATRICS%') board_abp ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN BOARD OF RADIOLOGY%') board_abr ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN OSTEOPATHIC BOARD OF EMERGENCY MEDICINE%') board_aobem ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN OSTEOPATHIC BOARD OF FAMILY PHYSICIANS%') board_aobfp ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN OSTEOPATHIC BOARD OF INTERNAL MEDICINE%') board_aobim ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id
RE: sql tuning help
I expand that rule slightly: no changes to production on the day before I will be absent from the office. Makes for so many fewer emergency phone calls on my day off --- Koivu, Lisa [EMAIL PROTECTED] wrote: That is very wise advice. Don't touch production on Fridays has been a rule in previous shops I worked at. Happy Friday all! pow Lisa Koivu Oracle Database Supermom to 4 Boys. Fairfield Resorts, Inc. 5259 Coconut Creek Parkway Ft. Lauderdale, FL, USA 33063 -Original Message- From: Robson, Peter [SMTP:[EMAIL PROTECTED]] Sent: Friday, December 06, 2002 10:30 AM To: Multiple recipients of list ORACLE-L Subject:RE: sql tuning help Hmmm - this is a Friday afternoon, you know. My suggestion is to forget it until Monday - don't spoil your weekend peter edinburgh -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: 06 December 2002 12:54 To: Multiple recipients of list ORACLE-L Subject: sql tuning help Hi, Oracle 8.1.6 NT 4.0 I have a rather complex query a developer gave to me to try to improve performance. There are 3 tables used. All relevant columns used are indexed. The tables have been analyzed SQLWKS select count(*) from physicians; COUNT(*) -- 340043 1 row selected. SQLWKS select count(*) from boards; COUNT(*) -- 220 1 row selected. SQLWKS select count(*) from phy_boards; COUNT(*) -- 450674 Below is the sql statement and explain plan. I see one FTS on 440,000+ records but cannot tell exactly what statement it is and how to resolve Any suggestions on how to optimize is appreciated. Thanks Rick select board_other.description strBrdNameOtherTHQuest ,decode(board_aaps.description, null,' ','X') ysnAAPSBoard ,decode(board_aba.description, null,' ','X') ysnABABoard ,decode(board_abem.description, null,' ','X') ysnABEMBoard ,decode(board_abfp.description, null,' ','X') ysnABFPoard ,decode(board_abim.description, null,' ','X') ysnABIMBoard ,decode(board_abp.description, null,' ','X') ysnABPBoard ,decode(board_abr.description, null,' ','X') ysnABRBoard ,decode(board_aobem.description, null,' ','X') ysnAOBEMBoard ,decode(board_aobfp.description, null,' ','X') ysnAOBFPBoard ,decode(board_aobim.description, null,' ','X') ysnAOBIMBAoard ,decode(board_aobr.description, null,' ','X') ysnAOBRBoard ,decode(board_other.description, null,' ','X') ysnOtherBoard from physicians p ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN ASSOCIATION OF PHYSICIAN SPECIALIST%') board_aaps ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN BOARD OF ANESTHESIOLOGY%') board_aba ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN BOARD OF EMERGENCY MEDICINE%') board_abem ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN BOARD OF FAMILY PRACTICE%') board_abfp ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN BOARD OF INTERNAL MEDICINE%') board_abim ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN BOARD OF PEDIATRICS%') board_abp ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate
RE: sql tuning help
| | INDEX FAST FULL SCAN |BOARD_NAM | 3 | 207 | | TABLE ACCESS BY INDEX ROWID|PHY_BOARD | 443K| 5M| |INDEX RANGE SCAN |PBRD_BOAR | 443K| | | VIEW | | 8K| 922K| |SORT UNIQUE | | 8K| 649K| | NESTED LOOPS | | 8K| 649K| | INDEX FAST FULL SCAN|BOARD_NAM | 3 | 207 | | TABLE ACCESS BY INDEX ROWID |PHY_BOARD | 443K| 5M| | INDEX RANGE SCAN |PBRD_BOAR | 443K| | | VIEW| | 8K| 922K| | SORT UNIQUE| | 8K| 649K| |NESTED LOOPS | | 8K| 649K| | INDEX FAST FULL SCAN |BOARD_NAM | 3 | 207 | | TABLE ACCESS BY INDEX ROWID |PHY_BOARD | 443K| 5M| | INDEX RANGE SCAN|PBRD_BOAR | 443K| | | VIEW | | 8K| 922K| | SORT UNIQUE | | 8K| 649K| | NESTED LOOPS | | 8K| 649K| |INDEX FAST FULL SCAN |BOARD_NAM | 3 | 207 | |TABLE ACCESS BY INDEX ROWID |PHY_BOARD | 443K| 5M| | INDEX RANGE SCAN |PBRD_BOAR | 443K| | |VIEW | | 8K| 922K| | SORT UNIQUE | | 8K| 649K| | NESTED LOOPS| | 8K| 649K| | INDEX FAST FULL SCAN |BOARD_NAM | 3 | 207 | | TABLE ACCESS BY INDEX ROWID|PHY_BOARD | 443K| 5M| |INDEX RANGE SCAN |PBRD_BOAR | 443K| | | VIEW | | 443K| 48M| |SORT UNIQUE | | 443K| 34M| | HASH JOIN| | 443K| 34M| | INDEX FAST FULL SCAN|BOARD_NAM | 190 | 12K| | TABLE ACCESS FULL |PHY_BOARD | 443K| 5M| Rick Mark J. Bobak To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] [EMAIL PROTECTED] cc: t Subject: Re: sql tuning help Sent by: [EMAIL PROTECTED] om 12/06/2002 10:19 AM Please respond to ORACLE-L My first thought would be that the plan shows a full table scan for phy_boards for each in-line view. This is almost certainly due to the 'pb.expiration_Date is null' condition in the where clause of each in-line view. Since Oracle does not store NULLs in an index (except for bitmaps), that condition requires a full table scan. -Mark On Fri, 2002-12-06 at 07:53, [EMAIL PROTECTED] wrote: Hi, Oracle 8.1.6 NT 4.0 I have a rather complex query a developer gave to me to try to improve performance. There are 3 tables used. All relevant columns used are indexed. The tables have been analyzed SQLWKS select count(*) from physicians; COUNT(*) -- 340043 1 row selected. SQLWKS select count(*) from boards; COUNT(*) -- 220 1 row selected. SQLWKS select count(*) from phy_boards; COUNT(*) -- 450674 Below is the sql statement and explain plan. I see one FTS on 440,000+ records but cannot tell exactly what statement it is and how to resolve Any suggestions on how to optimize is appreciated. Thanks Rick select board_other.description strBrdNameOtherTHQuest ,decode(board_aaps.description, null,' ','X') ysnAAPSBoard ,decode(board_aba.description, null,' ','X') ysnABABoard ,decode(board_abem.description, null,' ','X') ysnABEMBoard ,decode(board_abfp.description, null,' ','X') ysnABFPoard ,decode(board_abim.description, null,' ','X') ysnABIMBoard ,decode(board_abp.description, null,' ','X') ysnABPBoard ,decode(board_abr.description, null,' ','X') ysnABRBoard ,decode(board_aobem.description, null,' ','X') ysnAOBEMBoard ,decode(board_aobfp.description, null,' ','X') ysnAOBFPBoard ,decode(board_aobim.description, null,' ','X') ysnAOBIMBAoard ,decode(board_aobr.description, null,' ','X') ysnAOBRBoard ,decode(board_other.description, null,' ','X') ysnOtherBoard from physicians p ,(select distinct pb.phy_id, b.name, b.description
RE: sql tuning help
Title: RE: sql tuning help bad news for me I guess - doing a Production Install right now! -Original Message-From: Koivu, Lisa [mailto:[EMAIL PROTECTED]]Sent: Friday, December 06, 2002 11:29 AMTo: Multiple recipients of list ORACLE-LSubject: RE: sql tuning help That is very wise advice. Don't touch production on Fridays has been a rule in previous shops I worked at. Happy Friday all! pow Lisa Koivu Oracle Database Supermom to 4 Boys. Fairfield Resorts, Inc. 5259 Coconut Creek Parkway Ft. Lauderdale, FL, USA 33063 -Original Message- From: Robson, Peter [SMTP:[EMAIL PROTECTED]] Sent: Friday, December 06, 2002 10:30 AM To: Multiple recipients of list ORACLE-L Subject: RE: sql tuning help Hmmm - this is a Friday afternoon, you know. My suggestion is to forget it until Monday - don't spoil your weekend peter edinburgh -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: 06 December 2002 12:54 To: Multiple recipients of list ORACLE-L Subject: sql tuning helpHi, Oracle 8.1.6 NT 4.0 I have a rather complex query a developer gave to me to try to improve performance. There are 3 tables used. All relevant columns used are indexed. The tables have been analyzed SQLWKS select count(*) from physicians; COUNT(*) -- 340043 1 row selected. SQLWKS select count(*) from boards; COUNT(*) -- 220 1 row selected. SQLWKS select count(*) from phy_boards; COUNT(*) -- 450674 Below is the sql statement and explain plan. I see one FTS on 440,000+ records but cannot tell exactly what statement it is and how to resolve Any suggestions on how to optimize is appreciated. Thanks Rick select board_other.description strBrdNameOtherTHQuest ,decode(board_aaps.description, null,' ','X') ysnAAPSBoard ,decode(board_aba.description, null,' ','X') ysnABABoard ,decode(board_abem.description, null,' ','X') ysnABEMBoard ,decode(board_abfp.description, null,' ','X') ysnABFPoard ,decode(board_abim.description, null,' ','X') ysnABIMBoard ,decode(board_abp.description, null,' ','X') ysnABPBoard ,decode(board_abr.description, null,' ','X') ysnABRBoard ,decode(board_aobem.description, null,' ','X') ysnAOBEMBoard ,decode(board_aobfp.description, null,' ','X') ysnAOBFPBoard ,decode(board_aobim.description, null,' ','X') ysnAOBIMBAoard ,decode(board_aobr.description, null,' ','X') ysnAOBRBoard ,decode(board_other.description, null,' ','X') ysnOtherBoard from physicians p ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN ASSOCIATION OF PHYSICIAN SPECIALIST%') board_aaps ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN BOARD OF ANESTHESIOLOGY%') board_aba ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN BOARD OF EMERGENCY MEDICINE%') board_abem ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN BOARD OF FAMILY PRACTICE%') board_abfp ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN BOARD OF INTERNAL MEDICINE%') board_abim ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN BOARD OF PEDIATRICS%') board_abp ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN BOARD OF RADIOLOGY%') board_abr ,(select distinct pb.phy_id, b.name, b.description from phy_boards pb, boards b where pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null
RE: sql tuning help
Tom, Actually it returns 1 record. Thanks Rick Mercadante, Thomas F To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] [EMAIL PROTECTED] cc: ate.ny.us Subject: RE: sql tuning help Sent by: [EMAIL PROTECTED] 12/06/2002 11:41 AM Please respond to ORACLE-L Rick, does this return *any* records at all? the only reason that I ask is that in the 'where' clause, it is saying: where p.phy_id = board_aaps.phy_id (+) and p.phy_id = board_aba.phy_id (+) and p.phy_id = board_abem.phy_id (+) and p.phy_id = board_abfp.phy_id (+) and p.phy_id = board_abim.phy_id (+) and p.phy_id = board_abp.phy_id (+) and p.phy_id = board_abr.phy_id (+) and p.phy_id = board_aobem.phy_id (+) and p.phy_id = board_aobfp.phy_id (+) and p.phy_id = board_aobim.phy_id (+) and p.phy_id = board_aobr.phy_id (+) and p.phy_id = board_other.phy_id (+) and p.phy_id = 1870; well, from my way of thinking, a single record from p.phy_id cannot be equal to all of the others at the same time. so no records should be returned at all. I think I would re-wright the entire mess using a set of UNIONS to accomplish the same thing. something like: select board_other.description strBrdNameOtherTHQuest ,decode(board_aaps.description, null,' ','X') ysnAAPSBoard ,decode(board_aba.description, null,' ','X') ysnABABoard ,decode(board_abem.description, null,' ','X') ysnABEMBoard ,decode(board_abfp.description, null,' ','X') ysnABFPoard ,decode(board_abim.description, null,' ','X') ysnABIMBoard ,decode(board_abp.description, null,' ','X') ysnABPBoard ,decode(board_abr.description, null,' ','X') ysnABRBoard ,decode(board_aobem.description, null,' ','X') ysnAOBEMBoard ,decode(board_aobfp.description, null,' ','X') ysnAOBFPBoard ,decode(board_aobim.description, null,' ','X') ysnAOBIMBAoard ,decode(board_aobr.description, null,' ','X') ysnAOBRBoard ,decode(board_other.description, null,' ','X') ysnOtherBoard from phy_boards pb, boards b, physicians p where p.phy_id = pb.phy_id and pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN ASSOCIATION OF PHYSICIAN SPECIALIST%' union select board_other.description strBrdNameOtherTHQuest ,decode(board_aaps.description, null,' ','X') ysnAAPSBoard ,decode(board_aba.description, null,' ','X') ysnABABoard ,decode(board_abem.description, null,' ','X') ysnABEMBoard ,decode(board_abfp.description, null,' ','X') ysnABFPoard ,decode(board_abim.description, null,' ','X') ysnABIMBoard ,decode(board_abp.description, null,' ','X') ysnABPBoard ,decode(board_abr.description, null,' ','X') ysnABRBoard ,decode(board_aobem.description, null,' ','X') ysnAOBEMBoard ,decode(board_aobfp.description, null,' ','X') ysnAOBFPBoard ,decode(board_aobim.description, null,' ','X') ysnAOBIMBAoard ,decode(board_aobr.description, null,' ','X') ysnAOBRBoard ,decode(board_other.description, null,' ','X') ysnOtherBoard from phy_boards pb, boards b, physicians p where p.phy_id = pb.phy_id and pb.board_id = b.board_id and (pb.expiration_date = sysdate or pb.expiration_date is null) and b.description like 'AMERICAN BOARD OF ANESTHESIOLOGY%' etc. / Does this make sense? Hope this helps. Tom Mercadante Oracle Certified
SQL tuning help
Hello everybody, I have the following query that runs every week. UPDATE tmp_brian_metareward1 tmp SET offers_seen = (SELECT count(f.fastcash_id) FROM metareward.fastcash f WHERE f.subsite_id = tmp.subsite_id and attempt = trunc(sysdate-1) and attempt trunc(sysdate) group by tmp.subsite_id); This week it began to hang and I can't figure out why. No changes were made to a database. Please advise me on how I can tune it, which hints to add, or anything else I can do. Thank you Sergei -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sergei 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: SQL tuning help
Title: RE: SQL tuning help Sergei, How many records in each table? What indexes are in these tables? What version of Oracle? What do you mean by 'began to hang'? I'd try making the attempts in the WHERE clause into a Between. I'd also try grouping by f.subsite_id. You could always throw a Rule hint at it and see what happens. Jerry Whittle ACIFICS DBA NCI Information Systems Inc. [EMAIL PROTECTED] 618-622-4145 -Original Message- From: Sergei [SMTP:[EMAIL PROTECTED]] Hello everybody, I have the following query that runs every week. UPDATE tmp_brian_metareward1 tmp SET offers_seen = (SELECT count(f.fastcash_id) FROM metareward.fastcash f WHERE f.subsite_id = tmp.subsite_id and attempt = trunc(sysdate-1) and attempt trunc(sysdate) group by tmp.subsite_id); This week it began to hang and I can't figure out why. No changes were made to a database. Please advise me on how I can tune it, which hints to add, or anything else I can do. Thank you Sergei
Re: SQL tuning help
Sergei, When the query is running try to collect some stats, especially session waits, from v$session_wait and see where the waits are happening. Or you could do this from command line alter session set event '10046 trace name context forever, level 8'; your query alter session set event '10046 trace name context off'; This will produce a trace file in user_dump_dest directory. Tkprof that fiel to see the explain plans and all, see if everything is as per expectation. From the raw trace file you could see the wait events occuring and where they occur. My guess is you have seen buffer busy waits on most cases. Increase the initrans, maxtrans, freelist and freelist groups parameter of the indexes used in this query and rebuild them. This will alleviate several problems. Did someone chaneg the optimizer_goal? Did you have RULE before and CHOOSE now? If you use RULE, did someone analyzed any of the tables, including SYS owner tables? HTH Arup Nanda www.proligence.com - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, November 26, 2002 1:54 PM Hello everybody, I have the following query that runs every week. UPDATE tmp_brian_metareward1 tmp SET offers_seen = (SELECT count(f.fastcash_id) FROM metareward.fastcash f WHERE f.subsite_id = tmp.subsite_id and attempt = trunc(sysdate-1) and attempt trunc(sysdate) group by tmp.subsite_id); This week it began to hang and I can't figure out why. No changes were made to a database. Please advise me on how I can tune it, which hints to add, or anything else I can do. Thank you Sergei -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sergei 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: Arup Nanda 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: SQL tuning help
Remove the group by clause. It does nothing. Also, if the cardinality for subsite_id in the table tmp_brian_metareward1 is low, you may use a PL/SQL block instead of a single update statement. HTH, Krishna - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, November 26, 2002 10:54 AM Hello everybody, I have the following query that runs every week. UPDATE tmp_brian_metareward1 tmp SET offers_seen = (SELECT count(f.fastcash_id) FROM metareward.fastcash f WHERE f.subsite_id = tmp.subsite_id and attempt = trunc(sysdate-1) and attempt trunc(sysdate) group by tmp.subsite_id); This week it began to hang and I can't figure out why. No changes were made to a database. Please advise me on how I can tune it, which hints to add, or anything else I can do. Thank you Sergei -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Krishna Rao Kakatur 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: SQL tuning help
Sergei, By hang I'm going to assume that I can replace that with the phrase running really slow. If it is actually hanging then I think a call to Oracle is in order. I'm guessing that perhaps statistics were updated or one of the tables changed in size enough to convince the optimisor to take a different approach to the query. Try to get an explain plan for the query. Looking at the query I have a couple of other questions... 1) Since the subquery is updating a single row and joins to that row using subsite_id, I see no need for the group by clause - there is only 1 subsite_id that's going to appear. If there is something I don't understand here let me know - I have seen this type of query before and questioned its significance. 2) It looks like the ideal indexes are metareward.fastcash(subsite_id) and metareward.fastcash(attempt), or perhaps a concatenated index with subsite_id as the leading column. What indexes currently exist? Is the query using them (hence the need for an explain plan)? Have you analyzed the tables recently? Hopefully I have given you something to work from. To help you further we'd need the explain plan, the row counts and perhaps table/index statistics (details in user_tables and user_indexes, etc). It's difficult to suggest a hint without knowing further details. Finally how many different subsite_id's exist in fastcash and how many of those are you gathering details about? Perhaps a CTAS that calculates details for every subsite_id at once would be much faster if you are looking at most of the fastcash table anyway. Regards, Mark. Sergei sergei@netfli To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] p.com cc: Sent by: Subject: SQL tuning help [EMAIL PROTECTED] om 27/11/2002 05:54 Please respond to ORACLE-L Hello everybody, I have the following query that runs every week. UPDATE tmp_brian_metareward1 tmp SET offers_seen = (SELECT count(f.fastcash_id) FROM metareward.fastcash f WHERE f.subsite_id = tmp.subsite_id and attempt = trunc(sysdate-1) and attempt trunc(sysdate) group by tmp.subsite_id); This week it began to hang and I can't figure out why. No changes were made to a database. Please advise me on how I can tune it, which hints to add, or anything else I can do. Thank you Sergei -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sergei 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). Privileged/Confidential information may be contained in this message. If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone. In such case, you should destroy this message and kindly notify the sender
RE: SQL tuning help
Title: RE: SQL tuning help Let me clearfy. I am running Oracle 8.1.6 on solaris 8 Fastcash has 50M record tmp_brian_metareward1 has 600 records. I was able to tune the query so it runs fast now. I created a combined index on subsite_id and attempt and I added a hint UPDATE tmp_brian_metareward1 tmp SET offers_seen = ( SELECT /*+ INDEX(f IN_FASTCASH_SIDATMP) */ count(f.fastcash_id) FROM metareward.fastcash f WHERE f.subsite_id = tmp.subsite_id and attempt = trunc(sysdate-1) and attempt trunc(sysdate) group by tmp.subsite_id); I am still having a problem with a similar query below: IN_FASTCASH_SIDFIDVER index is for subsite_id fastcash_id and verified UPDATE tmp_brian_metareward1 tmp SET revenue = (SELECT /*+ INDEX(f IN_FASTCASH_SIDFIDVER) */ sum(f.mr_amount)/100 FROM metareward.fastcash f, metareward.transaction_fastcash tf WHERE f.subsite_id = tmp.subsite_id and f.fastcash_id = tf.fastcash_id and f.verified = trunc(sysdate-1) and f.verified trunc(sysdate) group by tmp.subsite_id); transaction_fastcash table has 2.5M records fastcash table has 50M record Thank you Sergei -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] On Behalf Of Whittle Jerome Contr NCI Sent: Tuesday, November 26, 2002 12:04 PM To: Multiple recipients of list ORACLE-L Subject: RE: SQL tuning help Sergei, How many records in each table? What indexes are in these tables? What version of Oracle? What do you mean by 'began to hang'? I'd try making the attempts in the WHERE clause into a Between. I'd also try grouping by f.subsite_id. You could always throw a Rule hint at it and see what happens. Jerry Whittle ACIFICS DBA NCI Information Systems Inc. [EMAIL PROTECTED] 618-622-4145 -Original Message- From: Sergei [SMTP:[EMAIL PROTECTED]] Hello everybody, I have the following query that runs every week. UPDATE tmp_brian_metareward1 tmp SET offers_seen = (SELECT count(f.fastcash_id) FROM metareward.fastcash f WHERE f.subsite_id = tmp.subsite_id and attempt = trunc(sysdate-1) and attempt trunc(sysdate) group by tmp.subsite_id); This week it began to hang and I can't figure out why. No changes were made to a database. Please advise me on how I can tune it, which hints to add, or anything else I can do. Thank you Sergei
RE: SQL tuning help
This week it began to hang and I can't figure out why. The first thing I would check are locks. The statement is trying to update a table. Try something like the following while the statement appears to be hung. These are two different ways (and certainly not the only ways) of checking for lockers and waiters. SELECT substr(s1.username,1,12)WAITING User, substr(s1.osuser,1,8)OS User, substr(to_char(w.session_id),1,5)Sid, P1.spid PID, substr(s2.username,1,12)HOLDING User, substr(s2.osuser,1,8)OS User, substr(to_char(h.session_id),1,5)Sid, P2.spid PID FROM sys.v_$process P1, sys.v_$process P2, sys.v_$session S1, sys.v_$session S2, sys.dba_lock w, sys.dba_lock h WHERE h.mode_held= 'None' ANDh.mode_held= 'Null' ANDw.mode_requested != 'None' ANDw.lock_type (+)= h.lock_type ANDw.lock_id1 (+)= h.lock_id1 ANDw.lock_id2 (+)= h.lock_id2 ANDw.session_id = S1.sid (+) ANDh.session_id = S2.sid (+) ANDS1.paddr = P1.addr (+) ANDS2.paddr = P2.addr (+) / - set lines 150 set pages 600 col mode_held for a12 col mode_requested for a12 select /*+ all_rows */ a.osuser waiter, nvl(b.osuser,'NOBODY') blocker, w.lock_type, h.mode_held, w.mode_requested -- w.lock_id1, w.lock_id2 from dba_locks w, dba_locks h, v$session a, v$session b where h.blocking_others = 'Blocking' and h.mode_held != 'None' and h.mode_held != 'Null' and w.mode_requested != 'None' and w.lock_type = h.lock_type and w.lock_id1 = h.lock_id1 and w.lock_id2 = h.lock_id2 and w.session_id in (select sid from v$session where last_call_et 100 and sid 10 and osuser is not null) and w.session_id = a.sid and h.session_id = b.sid; -- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephen Lee 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: SQL tuning help
check out the status in v$sess Regards, Sathyanarayanan |+--- || Sergei | || sergei@netfl| || ip.com | || | || 27/11/2002 | || 00:24| || Please | || respond to | || ORACLE-L | || | |+--- --| | | | To: Multiple recipients of list ORACLE-L | | [EMAIL PROTECTED] | | cc: (bcc: Sathyanaryanan K/VGIL) | | Subject: SQL tuning help | --| Hello everybody, I have the following query that runs every week. UPDATE tmp_brian_metareward1 tmp SET offers_seen = (SELECT count(f.fastcash_id) FROM metareward.fastcash f WHERE f.subsite_id = tmp.subsite_id and attempt = trunc(sysdate-1) and attempt trunc(sysdate) group by tmp.subsite_id); This week it began to hang and I can't figure out why. No changes were made to a database. Please advise me on how I can tune it, which hints to add, or anything else I can do. Thank you Sergei -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sergei 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: 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).
Sql Tuning help
I have been having
some problems with this statement
SELECT
to_char(NVL(SUM(bet_amount),0))FROM sb_betsWHERE processed_DATE =
add_months(TO_DATE('07011999
00','MMDD HH24MISS'),19-1)
AND processed_DATE
add_months(TO_DATE('07011999
00','MMDD HH24MISS'),19)
AND customer_id in (select customer_idfrom customerswhere
customers.customer_id=sb_bets.customer_id and
LICENSEE_ID=6130)
Both tables are full access no indexes
used. There is an index on sb_bets.processed_date and
customers.customer_id is a primary key and customers.licensee_id has an index
also. Of course this query may just pull too many customer ids to bother
with an index. But that is not too bad only 20 records in customers
but over 12 million in sb_bets. Is there a better way of writing this
query? I have tried hints but still nothing changed. Any ideas would
be greatly appreciated.
Please email me for any further info
thanks.
Matt
Southcott
DBA
Starnetsystems
(268) 480
1734
Matthew Southcott.vcf
RE: Sql Tuning help
Matt,
have you tried replacing the IN statement with an EXISTS
statement?
-Original Message-From:
[EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]]Sent: Wednesday, February 07,
2001 8:31 AMTo: Multiple recipients of list
ORACLE-LSubject: Sql Tuning help
I have been having
some problems with this statement
SELECT
to_char(NVL(SUM(bet_amount),0))FROM sb_betsWHERE processed_DATE =
add_months(TO_DATE('07011999 00','MMDD HH24MISS'),19-1)
AND processed_DATE add_months(TO_DATE('07011999 00','MMDD
HH24MISS'),19) AND
customer_id in (select customer_idfrom
customerswhere
customers.customer_id=sb_bets.customer_id and
LICENSEE_ID=6130)
Both tables are full access no indexes
used. There is an index on sb_bets.processed_date and
customers.customer_id is a primary key and customers.licensee_id has an index
also. Of course this query may just pull too many customer ids to bother
with an index. But that is not too bad only 20 records in customers
but over 12 million in sb_bets. Is there a better way of writing this
query? I have tried hints but still nothing changed. Any ideas
would be greatly appreciated.
Please email me for any further info
thanks.
Matt
Southcott
DBA
Starnetsystems
(268) 480
1734
RE: Sql Tuning help
Here's
some thoughts.
I
don't know if between is faster, but it might be, and won't a straight join do
the same as your subquery?
SELECT
to_char(NVL(SUM(bet_amount),0))
FROM
sb_bets
WHERE
processed_DATE between add_months( .) and
add_months( ..) AND
customers.customer_id = sb_bets.customer_id AND
customer.licensee_id = 6130;
Dan
"Just my thought"
-Original Message-From:
[EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]]Sent: Wednesday, February 07,
2001 8:31 AMTo: Multiple recipients of list
ORACLE-LSubject: Sql Tuning help
I have been having
some problems with this statement
SELECT
to_char(NVL(SUM(bet_amount),0))FROM sb_betsWHERE processed_DATE =
add_months(TO_DATE('07011999 00','MMDD HH24MISS'),19-1)
AND processed_DATE add_months(TO_DATE('07011999 00','MMDD
HH24MISS'),19) AND
customer_id in (select customer_idfrom
customerswhere
customers.customer_id=sb_bets.customer_id and
LICENSEE_ID=6130)
Both tables are full access no indexes
used. There is an index on sb_bets.processed_date and
customers.customer_id is a primary key and customers.licensee_id has an index
also. Of course this query may just pull too many customer ids to bother
with an index. But that is not too bad only 20 records in customers
but over 12 million in sb_bets. Is there a better way of writing this
query? I have tried hints but still nothing changed. Any ideas
would be greatly appreciated.
Please email me for any further info
thanks.
Matt
Southcott
DBA
Starnetsystems
(268) 480
1734
_This
message has been checked for all known viruses by UUNET delivered through
the MessageLabs Virus Control Centre. For further information visithttp://www.uk.uu.net/products/security/virus/
RE: Sql Tuning help
Try joining the 2 tables as below. I think the IN will do a FTS always.
Correct me if I'm wrong.
Rick
SELECT to_char(NVL(SUM(a.bet_amount),0))
FROM sb_bets a, customer b
WHERE a.processed_DATE = add_months(TO_DATE('07011999 00','MMDD
HH24MISS'),19-1)
AND a.processed_DATE add_months(TO_DATE('07011999 00','MMDD
HH24MISS'),19)
AND a.customer_id = b.customer_id
AND b.licensee_id = 6130;
-Original Message-
From: [EMAIL PROTECTED] [SMTP:[EMAIL PROTECTED]]
Sent: Wednesday, February 07, 2001 8:31 AM
To: Multiple recipients of list ORACLE-L
Subject: Sql Tuning help
I have been having some problems with this statement
SELECT to_char(NVL(SUM(bet_amount),0))
FROM sb_bets
WHERE processed_DATE = add_months(TO_DATE('07011999 00','MMDD
HH24MISS'),19-1) AND processed_DATE add_months(TO_DATE('07011999
00','MMDD HH24MISS'),19) AND customer_id in (select customer_id
from customers
where customers.customer_id=sb_bets.customer_id and LICENSEE_ID=6130)
Both tables are full access no indexes used. There is an index on
sb_bets.processed_date and customers.customer_id is a primary key and
customers.licensee_id has an index also. Of course this query may just
pull too many customer ids to bother with an index. But that is not too
bad only 20 records in customers but over 12 million in sb_bets. Is
there a better way of writing this query? I have tried hints but still
nothing changed. Any ideas would be greatly appreciated.
Please email me for any further info thanks.
Matt Southcott
DBA
Starnetsystems
(268) 480 1734
File: Matthew Southcott.vcf
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Cale, Rick T (Richard)
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 Tuning help
Instead of customer_id in...try
where exists (select 'x' from customers
where sb_bets.customer_id = customers.customer_id
and sb_bets.customer_id and LICENSEE_ID=6130)
[EMAIL PROTECTED] 02/07/01 08:30AM
I have been having some problems with this statement
SELECT to_char(NVL(SUM(bet_amount),0))
FROM sb_bets
WHERE processed_DATE = add_months(TO_DATE('07011999 00','MMDD
HH24MISS'),19-1) AND processed_DATE add_months(TO_DATE('07011999
00','MMDD HH24MISS'),19) AND customer_id in (select customer_id
from customers
where customers.customer_id=sb_bets.customer_id and LICENSEE_ID=6130)
Both tables are full access no indexes used. There is an index on
sb_bets.processed_date and customers.customer_id is a primary key and
customers.licensee_id has an index also. Of course this query may just pull
too many customer ids to bother with an index. But that is not too bad only
20 records in customers but over 12 million in sb_bets. Is there a
better way of writing this query? I have tried hints but still nothing
changed. Any ideas would be greatly appreciated.
Please email me for any further info thanks.
Matt Southcott
DBA
Starnetsystems
(268) 480 1734
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Tim Sawmiller
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).
