(sorry for long post again)
No, but there's an order by clause on COL3 and
apparently an index as well, so RBO thinks it's cheaper to read keys+rows in
order using index full scan than reading all rows and sorting them. Note that
optimizer needs to know that all table's rows have corresponding entries in
indexes well, in order to order full table using an index (for the reason that
indexes do not index rows where all relevant column values are NULLs). An
example follows:
SQL> create table t (a number, b
number);
Table created.
SQL> create index i on
t(a);
Index created.
SQL> select * from t order by
a;
Execution
Plan
----------------------------------------------------------
0 SELECT STATEMENT
Optimizer=CHOOSE
1 0 SORT (ORDER
BY)
2 1 TABLE ACCESS
(FULL) OF 'T'
SQL> alter table t modify a not
null;
Table altered.
SQL> select * from t order by
a;
Execution
Plan
----------------------------------------------------------
0 SELECT STATEMENT
Optimizer=CHOOSE
1 0 TABLE ACCESS
(BY INDEX ROWID) OF 'T'
2
1 INDEX (FULL SCAN) OF 'I' (NON-UNIQUE)
When I added a not null constraint, RBO knows,
that every table row will be in the index and can use it for
sorting.
SQL> alter table t modify a
null;
Column A can be null again.
Table altered.
SQL> drop index i;
Index dropped.
Lets create a composite index on both
columns.
SQL> create index i on
t(a,b);
Index created.
SQL> select * from t order by
a;
Execution
Plan
----------------------------------------------------------
0 SELECT STATEMENT
Optimizer=CHOOSE
1 0 SORT (ORDER
BY)
2 1 TABLE ACCESS
(FULL) OF 'T'
And set one of the columns not null (now all
entries will be indexed, since at least one field of index entries will have a
value)
SQL> alter table t modify b not
null;
Table altered.
SQL> select * from t order by
a;
Execution
Plan
----------------------------------------------------------
0 SELECT STATEMENT
Optimizer=CHOOSE
1 0 SORT (ORDER
BY)
2 1 TABLE ACCESS
(FULL) OF 'T'
Hm, no change?
SQL> analyze table t compute
statistics;
Table analyzed.
SQL> select * from t order by
a;
Execution
Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1
Bytes=26)
1 0 INDEX (FULL SCAN) OF
'I' (NON-UNIQUE) (Cost=1 Card=1
Bytes
=26)
Rule based optimizer was too dumb to take into
account a NOT NULL constraint of another field of a composite index.
So, here's one reason for upgrading to CBO :)
Tanel.
----- Original Message -----
Sent: Wednesday, January 07, 2004 4:04
PM
Subject: RE: Problem with understanding
Optimization methods.
> Actually from what is given I'd expect the optimizer to select a
full table scan in anycase, there's no where clause.
>
> Dick
Goulet
> Senior Oracle DBA
> Oracle Certified 8i DBA
>
> -----Original Message-----
> Sent: Wednesday, January 07, 2004
1:09 AM
> To: Multiple recipients of list ORACLE-L
>
>
> You can find out by employing the event 10053, lev 8.
Looking from afar, however, it seems more
> likely that you haven't
configured your CBO properly. Here is something you can try:
>
>
Execute the following commands:
>
> alter session set
optimizer_index_caching=40;
> alter session set
optimizer_index_cost_adj=25;
>
>
>
> After
that, retry the query. If I'm correct, optimizer will now know that index
I/O is much cheaper
> then the table one and will be much more likely to
select full index scan over the full table scan.
> When you're really,
really bored, you can read Practical Oracle 8i - Building Efficient
Databases,
> it has a few pages about the parameters above. Read the
Gospel of Jonathan and enjoy.
>
>
>
> On 2004.01.07
00:29, Denham Eva wrote:
> > Hello Listers,
> >
> >
A normal sql query from a data warehouse tool called Sagent.
> >
SELECT COL1, COL2, COL3
> > FROM TABLE
> > ORDER BY 3;
>
>
> > The table has approximately 2 mil records.
> > table
has 22 indexes.
> >
> > The database is set up optimizer
CHOOSE.
> > I run DBMS_Stats.Gather_Schema_Stats('SchemaName')
regularly.
> > OS is Win2k
> > ORACLE 81741
> >
> > OK, when doing a explain plan on the above sql, I get the
following...
> > SELECT STATEMENT Optimizer Mode=CHOOSE
>
> SORT ORDER BY
>
> TABLE ACCESS
FULL
TABLENAME -- Very slow and takes
> > hours!
> >
> > When adding the hint /*+RULE*/ for example I get
> >
SELECT STATEMENT Optimizer Mode=Hint:RULE
> > TABLE
ACCESS BY INDEX
ROWID
TABLENAME
> > INDEX FULL
SCAN
TABLE_INDEX --
> > Much faster!!!
> >
> > Have
I given enough info that anyone can explain why the CHOOSE mode insists
>
> on doing a TABLE ACCESS FULL?
> > Is there anything I can do to
improve performance? Please remember that this
> > query comes from a
Data Warehouse tool and hence does not appear to accept
> >
hints.
> >
> > Any help will be much appreciated!
>
> Denham
> >
> > --
> > Please see the
official ORACLE-L FAQ: http://www.orafaq.net> > --
> > Author: Denham Eva
> > INET: EVAD@TFMC.co.za> >
>
> 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@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).
> >
>
> --
>
Mladen Gogala
> Oracle DBA
> --
> Please see the official
ORACLE-L FAQ: http://www.orafaq.net> --
> Author: Mladen Gogala
> INET: mgogala@adelphia.net>
>
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@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: Goulet, Dick
> INET: DGoulet@vicr.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@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: Tanel Poder
INET: tanel.poder.003_at_mail.ee
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).
Received on Wed Jan 07 2004 - 13:44:24 CST