Star transformations still seem to be disabled by bind variables in predicates against the dimension tables, even in 9.2. (I have an idea that materialized views have the same problem. but I haven't tested that).
Also, bind variable peeking isn't much help with histograms as the peek only takes on the first (hard) parse - then the same execution plan is used for all subsequent executions, even if the bound values merit a completely different plan. By the way, generating histograms isn't entirely pointless when using bind variables, as the column density is adjusted to a certain degree to factor out some of the impact of recognised popular values. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Now available One-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) ____UK_______March 19th ____UK_______April 8th ____UK_______April 22nd ____USA_(FL)_May 2nd Next dates for the 3-day seminar: (see http://www.jlcomp.demon.co.uk/seminar.html ) ____UK_(Manchester)_May ____USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html ----- Original Message ----- To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: 10 March 2003 21:39 I thought that went out the window with 9i because it can snoop at the bind variables?? Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -----Original Message----- Sent: Monday, March 10, 2003 12:09 PM To: Multiple recipients of list ORACLE-L Well, I wouldn't say you should *always* use bind variables. Many situations in a database warehouse preclude that. Potentially long running queries may need to have literals to help the CBO make the right choice. Star transforms don't work with bind variables, and histograms can't be used with bind variables. Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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).
