In-Reply-To: <3edcb66e0811110249u52d1bcfg8967c69b777f7558@mail.gmail.com>
References: <3edcb66e0811110249u52d1bcfg8967c69b777f7558@mail.gmail.com>
To: ujang.jaenudin@gmail.com
Cc: indo-oracle@yahoogroups.com,        "Oracle Discussion List" <oracle-l@freelists.org>,        oracle-l-bounce@freelists.org
MIME-Version: 1.0
Subject: Re: need enhance feature for CBO :)
Message-ID: <OF0CCC54C3.C7D1AD81-ON852574FE.0042B25C-852574FE.0042B091@lnotes-gw.ent.nwie.net>
From: TESTAJ3@nationwide.com
Date: Tue, 11 Nov 2008 07:08:53 -0500
Content-Type: multipart/alternative; boundary="=_alternative 0042B08E852574FE_="
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
Precedence: normal
Reply-to: TESTAJ3@nationwide.com
--=_alternative 0042B08E852574FE_=
Content-Type: text/plain; charset="US-ASCII"

Ok i'll ask the obvious, did you gather stats on the table and indexes?

joe

_______________________________________
Joe Testa, Oracle Certified Professional 
Senior Consultant
Nationwide Investments
Data Engineering and Administration

(Work) 614-677-1668
614-312-6715


From:
"Ujang Jaenudin" <ujang.jaenudin@gmail.com>
To:
"Oracle Discussion List" <oracle-l@freelists.org>, 
indo-oracle@yahoogroups.com
Date:
11/11/2008 05:50 AM
Subject:
need enhance feature for CBO :)
Sent by:
oracle-l-bounce@freelists.org


dear all, (sorry cross posting)

oracle 10.1.0.5
huge machine :)

CREATE TABLE PVC
(
  ........
  CONSTRAINT PVC_PK
 PRIMARY KEY
 (AN, SEQ, ES, ETI, ER)
)
ORGANIZATION INDEX   <=== IOT :(
  PARTITION BY RANGE (SEQ)
........... (500 partitions even more)
)
NOPARALLEL;


CREATE UNIQUE INDEX PVC_U1 ON PVC
(SEQ, ER)
LOCAL (
............ (500 partitions even more)
)
NOPARALLEL;


there are more than billion of rows

select /*+ use_index(pvc pvc_pk) */ distinct seq from pvc;

SELECT STATEMENT ALL_ROWS
   PARTITION RANGE ALL
      SORT UNIQUE NOSORT
         INDEX FULL SCAN INDEX(UNIQUE) PVC_U1


so, my question:
- why don't oracle utilize "partition key" for this case....
  (by not scanning full index will be better, because partition key
based on SEQ column).
  getting list of partition key is the fastest way :)
- even when force using PVC_PK, CBO won't do it, I think scanning Primary 
key
  more efficient rather than roundtrip read PVC_U1 index and then again
  read PVC_PK index, due to IOT mapping...


-- 
thanks and regards
ujang | oracle dba
jakarta | http://ora62.wordpress.com
--
http://www.freelists.org/webpage/oracle-l


--=_alternative 0042B08E852574FE_=
Content-Type: text/html; charset="US-ASCII"


<br><font size=2 face="sans-serif">Ok i'll ask the obvious, did you gather
stats on the table and indexes?</font>
<br>
<br><font size=2 face="sans-serif">joe</font>
<br>
<br><font size=2 face="sans-serif">_______________________________________<br>
Joe Testa, Oracle Certified Professional <br>
Senior Consultant<br>
Nationwide Investments<br>
Data Engineering and Administration<br>
<br>
(Work) 614-677-1668<br>
614-312-6715<br>
</font>
<br>
<br>
<br>
<table width=100%>
<tr valign=top>
<td><font size=1 color=#5f5f5f face="sans-serif">From:</font>
<td><font size=1 face="sans-serif">&quot;Ujang Jaenudin&quot; &lt;ujang.jaenudin@gmail.com&gt;</font>
<tr valign=top>
<td><font size=1 color=#5f5f5f face="sans-serif">To:</font>
<td><font size=1 face="sans-serif">&quot;Oracle Discussion List&quot; &lt;oracle-l@freelists.org&gt;,
indo-oracle@yahoogroups.com</font>
<tr valign=top>
<td><font size=1 color=#5f5f5f face="sans-serif">Date:</font>
<td><font size=1 face="sans-serif">11/11/2008 05:50 AM</font>
<tr valign=top>
<td><font size=1 color=#5f5f5f face="sans-serif">Subject:</font>
<td><font size=1 face="sans-serif">need enhance feature for CBO :)</font>
<tr valign=top>
<td><font size=1 color=#5f5f5f face="sans-serif">Sent by:</font>
<td><font size=1 face="sans-serif">oracle-l-bounce@freelists.org</font></table>
<br>
<hr noshade>
<br>
<br>
<br><tt><font size=2>dear all, (sorry cross posting)<br>
<br>
oracle 10.1.0.5<br>
huge machine :)<br>
<br>
CREATE TABLE PVC<br>
(<br>
 &nbsp;........<br>
 &nbsp;CONSTRAINT PVC_PK<br>
 PRIMARY KEY<br>
 (AN, SEQ, ES, ETI, ER)<br>
)<br>
ORGANIZATION INDEX &nbsp; &lt;=== IOT :(<br>
 &nbsp;PARTITION BY RANGE (SEQ)<br>
........... (500 partitions even more)<br>
)<br>
NOPARALLEL;<br>
<br>
<br>
CREATE UNIQUE INDEX PVC_U1 ON PVC<br>
(SEQ, ER)<br>
LOCAL (<br>
............ (500 partitions even more)<br>
)<br>
NOPARALLEL;<br>
<br>
<br>
there are more than billion of rows<br>
<br>
select /*+ use_index(pvc pvc_pk) */ distinct seq from pvc;<br>
<br>
SELECT STATEMENT ALL_ROWS<br>
 &nbsp; PARTITION RANGE ALL<br>
 &nbsp; &nbsp; &nbsp;SORT UNIQUE NOSORT<br>
 &nbsp; &nbsp; &nbsp; &nbsp; INDEX FULL SCAN INDEX(UNIQUE) PVC_U1<br>
<br>
<br>
so, my question:<br>
- why don't oracle utilize &quot;partition key&quot; for this case....<br>
 &nbsp;(by not scanning full index will be better, because partition key<br>
based on SEQ column).<br>
 &nbsp;getting list of partition key is the fastest way :)<br>
- even when force using PVC_PK, CBO won't do it, I think scanning Primary
key<br>
 &nbsp;more efficient rather than roundtrip read PVC_U1 index and then
again<br>
 &nbsp;read PVC_PK index, due to IOT mapping...<br>
<br>
<br>
-- <br>
thanks and regards<br>
ujang | oracle dba<br>
jakarta | </font></tt><a href=http://ora62.wordpress.com/><tt><font size=2>http://ora62.wordpress.com</font></tt></a><tt><font size=2><br>
--<br>
</font></tt><a href="http://www.freelists.org/webpage/oracle-l"><tt><font size=2>http://www.freelists.org/webpage/oracle-l</font></tt></a><tt><font size=2><br>
<br>
<br>
</font></tt>
<br>
<br>
--=_alternative 0042B08E852574FE_=--
--
http://www.freelists.org/webpage/oracle-l