=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= SEARCHDATABASE.COM | Database Administrator Tip August 22, 2001 =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Welcome to the searchDatabase.com Database Administrator Tips newsletter! Today's tip, "Improving performance with histograms" can also be viewed online at: http://www.searchDatabase.com/tip/1,289483,sid13_gci758792,00.html Check out collection of over 200 DBA, developer, Oracle, DB2, and SQL Server tips FREE to searchDatabase members: http://searchdatabase.techtarget.com/tips/0,289484,sid13_tax281808,00.html ================================================ SPONSORED BY: **Storage Decisions 2001** ================================================ If you're going to spend $5+ million on storage hardware and software next year, don't miss a must-attend FREE conference developed just for you. Attend Storage Decisions 2001 in Chicago September 26-28. Learn to set strategy/make the smartest decisions to most effectively manage your storage initiatives for 2002. Pre-qualified audience of your peers and NO vendor sales pitches. Visit: http://ad.doubleclick.net/clk;3230220;5058249;w?http://www.StorageDecisions2001.com ================================================ "Improving performance with histograms" By James Giordano When dealing with a table that has highly skewed data distribution, histograms are an excellent way to improve performance by getting the optimizer to choose your index. Recently I was tuning some SQL that used a column to flag whether a row should be processed or not. For example the table had a column called JOB_ID, 99% of the million rows in the table had a value of 0, and the other 1% of the rows had a JOB_ID of the PID of the job processing the data. There was an index built on JOB_ID, but the optimizer wouldn't use the index, because the cardinality of the index was so poor. In this case a histogram helped to get the optimizer to use the index. Histograms are created using the "FOR COLUMNS" option of the analyze command. For example, I used: ANALYZE TABLE INVENTORY_COST ESTIMATE STATISTICS SAMPLE 20 PERCENT FOR COLUMNS JOB_ID SIZE 10; Determining where histograms are used in your database is a little tricky because Oracle considers all cost-based statistics as histograms, so if you look in DBA_HISTOGRAMS, you will find 2 rows for every table in the database. I have been using the following query to determine what table have histograms: select distinct TABLE_NAME, COLUMN_NAME from dba_histograms where endpoint_number not in (1,0) order by table_name, column_name ; ABOUT THE AUTHOR: James Giordano is an Oracle database administrator. He has been working with Oracle for about seven years, and also has experience with UNIX and PeopleSoft/Oracle financials. SUBMIT YOUR OWN TIP AND WIN A PRIZE: http://searchdatabase.techtarget.com/tipsPrize/0,289492,sid13_prz520733_cts520732,00.html ======================================= MORE GREAT STUFF ON SEARCHDATABASE! ======================================= Featured Topic this week: XML: Hit or hype? http://searchdatabase.techtarget.com/featuredTopic/0,290042,sid13_gci761146,00.html XML began as a simple markup language for defining and sharing documents containing structured data. Then came database-like initiatives such as XML Query and XML Schemas. Where is XML headed? Where should it be headed? Check out our XML resources and join the debate in our discussion forums. "If XML is just a physical format for data exchange, why does it incorporate logical constructs such as data model components?" More: http://searchdatabase.techtarget.com/tip/0,289483,sid13_gci759453,00.html Mark your calendars: Bill Inmon, the father of data warehousing, will be presenting "Ten critical data warehousing success factors" LIVE on searchDatabase.com September 6 at 1 pm Eastern (18:00 GMT). He will also be available to take your questions. Don't miss this opportunity to chat with the premier data warehousing guru in the world! http://searchdatabase.techtarget.com/onlineEvents/0,289675,sid13,00.html Over 200 DBA, developer, Oracle, DB2, and SQL Server tips FREE to searchDatabase members: http://searchdatabase.techtarget.com/tips/0,289484,sid13_tax281808,00.html Hundreds of hand-picked and reviewed resources about every aspect of database administration, including backup, performance tuning, database design, normalization, and much more: http://searchdatabase.techtarget.com/bestWebLinks/0,289521,sid13_tax281570,00.html Ask the Expert category of the day: "SQL" http://searchdatabase.techtarget.com/ateAnswers/0,289620,sid13_cid376817_tax285649,00.html SQL guru extraordinaire Rudy Limeback is waiting to tackle your toughest SQL scripting problems. ======================================= LEARNING ZONE FEATURED BOOK OF THE WEEK ======================================= "Oracle 9i SQLJ Programming" By Nirva Morisseau-Leroy Take advantage of greater programming capabilities with SQLJ--a standard language specification that embeds SQL statements in Java--in the Oracle 9i environment. Officially authorized by Oracle Corporation, this authoritative resource explains how to develop hosted applications, achieve remarkable scalability, implement real-time business intelligence features, and reach a new level of programming functionality. Get comprehensive coverage of SQLJ programming techniques, including SQL programming and development, SQLJ stored programs, triggers, and object-relational processing. Oracle9i SQLJ Programming will help you control each piece of the development process. Discover the benefits of Oracle's highly developed SQL-embedded Java programming standard. http://www.digitalguru.com/dgstore/product.asp?isbn=0072190930&ac_id=58 ================================================ SUBMIT A TECHNICAL TIP AND WIN A PRIZE! ================================================ Do you have a time-saving shortcut, trick, or script that you want to share with other database pros? The highest rated tips each month will win our "Tip of the Month" contest and receive a high-quality searchDatabase.com denim shirt AND a free book of your choice from Wrox Press. We're accepting short, focused tips or code snippets on topics of interest to DBA's and database developers, such as Oracle, DB2, SQL Server, database design, SQL, performance tuning, etc. Click here for more info and to submit your tip: http://searchdatabase.techtarget.com/tipsPrize/0,289492,sid13_prz520733_cts520732,00.html This will be a great way to share your knowledge, cement your status as an industry expert, and maybe win a prize. Send us your tip today! ================================================ If you would like to sponsor this or any TechTarget newsletter, please contact Gabrielle DeRussy at [EMAIL PROTECTED] ================================================ If you no longer wish to receive this newsletter simply reply to this message with "REMOVE" in the subject line. Or, visit http://searchDatabase.techtarget.com/register and adjust your subscriptions accordingly. If you choose to unsubscribe using our automated processing, you must send the "REMOVE" request from the email account to which this newsletter was delivered. Please allow 24 hours for your "REMOVE" request to be processed.
