Yes.
On 2004.01.08 01:14, Denham Eva wrote:
Mladen
Thank You for this suggestion, works in that the CBO now see it this way
SELECT STATEMENT Optimizer Mode=CHOOSE
TABLE ACCESS BY INDEX ROWID TABLENAME
INDEX FULL SCAN TABLENAME_NDX
Can these parameters be set
On that I really, really have to disagree with you.
Jonathan's book is not something to read When you're really, really
bored. You should read it when you're wide awake and eager to learn. Short
of a database that's in pieces on the floor I can't think of anything that
should have higher
Thank You!
-Original Message-
Sent: Thursday, January 08, 2004 9:04 AM
To: Multiple recipients of list ORACLE-L
Yes.
On 2004.01.08 01:14, Denham Eva wrote:
Mladen
Thank You for this suggestion, works in that the CBO now see it this
way
SELECT STATEMENT Optimizer Mode=CHOOSE
Hear, hear!
Wolfgang,
Without wanting to appear really dense here. But, how about putting some
titles and surnames to that list of yours?
As much as I would love to buy books, with our exchange rate and import
taxes, it becomes very expensive!
But I do have a To Get list that I like to update.
On that I really, really have to disagree with you.
Mladen was probably joking anyway :)
Tanel.
Jonathan's book is not something to read When you're really, really
bored. You should read it when you're wide awake and eager to learn.
Short
of a database that's in pieces on the floor I
E-LSubject: Fw: Problem with understanding Optimization
methods.
(I suspect my last post didn't go through -
sorry for long repost 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 u
Give that guy (Mladen) a break ... soon he is going to be 43 in geek language he is
over the hill (i.e. 42) ...
Raj
Rajendra dot Jamadagni at nospamespn dot com
All Views expressed in this email are strictly
Sorry, they are so engrained in my toolset that it didn't even occur to me
that they could be unlnown.
My apologies also to Mladen. I didn't mean to admonish him, I just found
the term boring in connection with Jonathan Lewis, or his book, inappropriate.
Jonathan Lewis: Practical Oracle 8i,
Conversely, the CBO is a lot smarter with
this scenario that people realise. How many
people knew that Oracle can resolve a query
of the type:
where colX is null
using a b-tree index ?
Try this --
drop table t1;
create table t1 (n1 number, n2 number not null, n3 number);
create index i1
Hi Jonathan,
What release did this NULL_CHECK start with?
I used to see:
Execution Plan
--
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=6)
1 0 TABLE ACCESS (FULL) OF 'T1' (Cost=1 Card=1 Bytes=6)
As I recall, I used-to need an
I don't know, but it's been in Steve Adams'
seminar material for a long time.
It doesn't apply to a single-column b-tree
index - you need at least one column available
to be the mandatory column.
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
The educated person is not the person
To what Mladen said about the optimizer_blah parameters, there is another thing which
can be added. Most third-party software is over-indexed (as you have noticed) because
they optimistically want to cover *all* possible cases and quite often a column which
is highly discreminant at one site
Check the costs of the two queries (autotrace will be
sufficient).
In this example, rule based uses the index because
it exists and will return the right answer.
Cost based works out that the scan and sort is
cheaper.
The cost of an index full scan is approximately
blevel + leaf_blocks
Apologies to the list.
The previous reply may have been informative,
but it was irrelevant. I failed to notice that the
indexed access path was followed up by a
table access by rowid. (This does explain why
the index_FFS path wasn't used, of course).
In this case, the cost of the indexed
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
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
Eva,
Any chance this system is CPU-bound? I have seen similar problems when
cpu-intensive operations are performed. Another thought is to check your
temporary tablespace settings. When in doubt, okay list, repeat after me...
Do a 10046, level 8 trace
Execution plan 1 does a sort which will use
ednesday, 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-
[mailto:[EMAIL PROTECTED]Sent: Wednesday, January 07, 2004
4:30 PMTo: Multiple recipients of list ORACLE-LSubject:
Fw: Problem with understanding Optimization methods.
(I suspect my last post didn't go through - sorry
for long repost again)
No, but there's an order by clause on COL3
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-
Mladen
Thank You for this suggestion, works in that the CBO now see it this way
SELECT STATEMENT Optimizer Mode=CHOOSE
TABLE ACCESS BY INDEX ROWID TABLENAME
INDEX FULL SCAN TABLENAME_NDX
Can these parameters be set in the init.ora?
Many Thanks Once Again!
Denham
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
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
23 matches
Mail list logo
