RE: which should be faster

One thing that hasn't been asked is if whether the most commonly run queries *only* access those two columns. In that case even if there is one distinct value for the 2nd column adding it to the index could give greatly increased performance since only the index blocks would be read. By the same token, if there's a frequently used and performance critical query that only reads 3 columns the index could be created with those 3 columns.

Jay Miller

-----Original Message-----
Sent: Monday, May 05, 2003 4:22 PM
To: Multiple recipients of list ORACLE-L

Thanks Jonathan,

I`ll spent some time testing.

Stephane Paquette
Administrateur de bases de donnees
Database Administrator
Standard Life
www.standardlife.ca
Tel. (514) 925-7187
stephane.paquette_at_standardlife.ca <mailto:stephane.paquette_at_standardlife.ca>

-----Original Message-----
Lewis
Sent: Monday, May 05, 2003 2:50 PM
To: Multiple recipients of list ORACLE-L

I suspect that this comment is highly
context-sensitive.

Oracle 9 can re-arrange the order of
filter predicates to reduce the estimated cost of a query.

Oracle 8 cannot.

Note - it is implicit in the software that the order of predicates can have an impact on the performance of a query, otherwise
there would be no need for the
/*+ ordered_predicates */ hint.
On the other hand, the order of the
(simple filter) predicates cannot affect the calculated cost of a query prior to
9i, because the calculated cost is
I/O based, not CPU based.

Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr

One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html

____Denmark__May 21-23rd
____Sweden___June
____Finland__September
____Norway___September

Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html

____UK_(Manchester)_May x 2
____Estonia___June (provisional)
____Australia_June (provisional)
____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: 05 May 2003 16:52

> I've check on asktom and he is positive that the order of predicate
has no
> impact (DB version for that comment is 815).
> From the site :
>
> No, the order of evaluation of a predicate in SQL is purposely
> "indeterminate".
>

--
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).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephane Paquette
  INET: stephane.paquette_at_standardlife.ca

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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: 
  INET: JayMiller_at_TDWaterhouse.com

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).