Re: Query rewrite help needed

Notes inline.

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" <ORACLE-L_at_fatcity.com> Sent: 10 March 2003 17:04

> I'm assuming that the query should read:
>
> where trunc(d1) = '01-Dec-2002'
>

Correct.

> And you can save the separate ANALYZE TABLE command by adding the
COMPUTE
> STATISTICS clause to the CREATE INDEX since 8.1.6, as well...

A moot point here - Oracle tells us that we should use dbms_stats to collect statistics - should we therefore ignore the option to collect statistics 'efficiently' in this fashion when rebuilding an index. (Mind you, for mickey mouse (tm) demos, I still tend
to use the analyze command).

> As far as how this works, I'll guess (without taking time to test)
that the
> 9.2 CBO has been fitted to recognize "TRUNC(date-column)" as the
functional
> equivalent of "date-column >= value and date-column < value+1"?

It's much cuter (which means more generic) than that. I found it in an article on dbazine by a chap called Vadim Tropashko. Oracle will raid the constraint definitions to generate new predicates for your query, apply them, and then use transitivity to come up with some very clever answers.

So my table definition had

        check (d1 = trunc(d1))
my query had

        trunc(d1) = '01-Dec-2002'

so oracle came up with an argument like
(excuse the anthropomorphism):

    if (d1 = trunc(d1)) is always true
and

    trunc(d1) = '01-Dec-2002'
then your query is implicitly

    d1 = '01-Dec-2002'
Hey! I can use the index on that predicate !

Yet another reason for trying to define as much of the truth into the database as
possible.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jonathan Lewis
  INET: jonathan_at_jlcomp.demon.co.uk

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: ListGuru_at_fatcity.com (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).