Message-Id: <10660.120219@fatcity.com> From: "Jesse, Rich" Date: Wed, 25 Oct 2000 15:21:45 -0500 Subject: ANALYZE and performance once again... So, there I am. A CBO database with no objects ANALYZEd. I will ANALYZE these tables smartly, however. I will test this on our development DBs on our development box first. Development is an HP L-class running 11.00 and Oracle 8.0.6.2.0 32-bit. I have a specific new query for a new table (and two old ones) that I'd like to scream. First, the tables: 1) PART_MASTER (130K rows), columns: PART_NUMBER (PK) DESCRIPTION ONHAND ONORDER LOCATION... 2) PART_DESCRIPTION (120K rows), columns: PART_NUMBER \ DESCRIPTION > All three make up PK. SEQUENCE_NO / 3) PART_DESC_PARSED (900K rows), columns: PART_NUMBER \ DESCRIPTION / Both make up PK; DESCRIPTION also indexed. Quick breakdown on the tables: PART_MASTER's just that -- the master. PART_NUMBERs are the PK. PART_DESCRIPTION contains secondary descriptions. PART_MASTER relates to PART_DESCRIPTION one-to-many on PART_NUMBER with PART_DESCRIPTION being optional. PART_DESC_PARSED is all DESCRIPTIONs in PART_MASTER and PART_DESCRIPTION parsed out to individual words and relates to PART_MASTER the same as PART_DESCRIPTION. (e.g a DESCRIPTION 'FOO,BLEAH KNOCK:TUKE' would be parsed to four rows in PART_DESC_PARSED as 'FOO', 'BLEAH', 'KNOCK', and 'TUKE', each with the same PART_NUMBER. Please don't yell at me for this setup. The first two tables were kindly layed out by a software vendor. I'm adding the third to make searching descriptions more efficient. Triggers are in place to take care of synching the tables. Names have been changed to protect the ignorant. So, a user wants to search the descriptions. Here's a sample of a proposed query: SELECT pm.part_number, pm.description, pd.description FROM part_master pm, part_description pd WHERE pm.part_number = pd.part_number(+) AND pm.part_number IN ( SELECT p1.part_number FROM part_desc_parsed p1 ,part_desc_parsed p2 ,part_desc_parsed p3 WHERE p1.part_number = p2.part_number AND p1.part_number = p3.part_number AND p1.description like 'RES%' AND p2.description like '100%' AND p3.description like 'WW%' ) ORDER BY pm.part_number, pd.sequence_no; Using rule-based, and running this several times in rapid succession, the query returns in about 240 ms. Perfect! Doing an ANALYZE COMPUTE on the three tables and their associated indexes causes that figure to drop to 340 ms. Still good, but disappointed at the drop in effeciency. And, adding histograms to PART_NUMBER in all three tables and DESCRIPTION in PART_DESC_PARSED causes the query to take over 4 seconds. Anyone see anything I'm missing? Please tell me I'm missing something from the Perf Tuning class or tell me that the optimizer gets better in 8.1.6.... TIA, Rich Jesse System/Database Administrator Rich.Jesse@qtiworld.com Quad/Tech International, Sussex, WI USA p.s. MS Lookout doesn't have a edit option to replace text, and the spell czecher bites, but I don't think I mistyped anything in this message... ------------------------------------------------------------------------ This message has been scanned for viruses with Trend Micro's Interscan VirusWall.