Re: Why Adding "+0" Make A Query Run Faster

From: Niall Litchfield <n-litchfield_at_audit-commission.gov.uk>
Date: Thu, 20 Jun 2002 13:41:13 +0100
Message-ID: <3d11cd6a$0$8512$ed9e5944@reading.news.pipex.net>

"Roman Mirzaitov" <rmirzaitov_at_kt.kg> wrote in message news:aeshkv$9ph8c$1_at_ID-127142.news.dfncis.de...
> > a) He's not using RBO, he's using CBO (on partitioned table, that's
> > the only way).
> Oracle manuals says:
> "If OPTIMIZER_MODE=CHOOSE, if statistics do not exist, and if you do not
add
> hints to your SQL statements, then your statements use the RBO."
> So we don't know for sure which optimizer mode used in our case.
> Besides, you can use RBO mode with partitioned table, you just will not
gain
> any performance benefits from partitioning. But you can.

We do know. Its the CBO. Again from the manuals

Features that Require the CBO
The use of any of the following features requires the use of the CBO:

  a.. Partitioned tables

  b.. Index-organized tables

  c.. Reverse key indexes

  d.. Function-based indexes

  e.. SAMPLE clauses in a SELECT statement

  f.. Parallel execution and parallel DML

  g.. Star transformations

  h.. Star joins

  i.. Extensible optimizer

  j.. Query rewrite (materialized views)

  k.. Progress meter

  l.. Hash joins

  m.. Bitmap indexes

  n.. Partition views (release 7.3)

---

        Note:
        Even if the parameter OPTIMIZER_MODE is set to RULE, the use of

these features enables the CBO.

---

--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer

******************************************

Received on Thu Jun 20 2002 - 07:41:13 CDT