Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Varray Problem

Varray Problem

From: B3D70 <nareswara_at_balicamp.com>
Date: Thu, 04 Dec 2003 02:12:16 -0800
Message-ID: <F001.005D8C83.20031204021216@fatcity.com> 





Content-Type: text/plain; charset="us-ascii"; format=flowed



Hi there ...


I have a table with a Varray Columns.


When I used a select operation without access the varray columns the 
explain plan show fine.. cost = 4


but when I access the Varray columns then I got the cost = 1310



Why ? What happend ? and why oracle did it ?
Someone can explain it to me ?


or how shoud I optimized it ?



this is my query without varrary


SELECT   a.id,

          a.address
FROM     my_table a

Operation                       Object Name     Rows    Bytes   Cost
SELECT STATEMENT 
Hint=CHOOSE            3               4 

   SORT GROUP 
BY                         3       60      4 

     TABLE ACCESS 
FULL   MY_TABLE        9       180     2 







this is my query with varrary


SELECT   a.id,

          a.address
FROM     my_table a, TABLE(a.no_of_car) b

Operation                     Object Name       Rows    Bytes   Cost
SELECT STATEMENT 
Hint=CHOOSE            3               1310 

   SORT GROUP 
BY                         3       459     1310 

     NESTED LOOPS                                73 K    10 
M    101
       TABLE ACCESS FULL MY_TABLE        9       1 
K     2
       COLLECTION ITERATOR PICKLER 




FETCH



regards


kang bedjo 

--=====================_3629098==.ALT




Content-Type: text/html; charset="us-ascii"



<html>

<body>



Hi there ...<br>


I have a table with a Varray Columns. <br>
When I used a select operation without access the varray columns the
explain plan show fine.. cost = 4<br>


but when I access the Varray columns then I got the cost = 1310<br><br>
Why ? What happend ? and why oracle did it ?<br>
Someone can explain it to me ?<br>


or how shoud I optimized it ?<br><br>


this is my query without varrary<br>


<font face="Courier New, Courier" size=3 color="#0000F0"><b>SELECT</b></font><font face="Courier New, Courier" size=3>&nbsp;&nbsp;

a.id,<br>


&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; a.address<br>


</font><font face="Courier New, Courier" size=3 color="#0000F0"><b>FROM</b></font><font face="Courier New, Courier" size=3>&nbsp;&nbsp;&nbsp;&nbsp;

my_table a<br><br>


</font><font face="Courier New, Courier" size=2>Operation<x-tab>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</x-tab><x-tab>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</x-tab><x-tab>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</x-tab>Object

Name<x-tab>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</x-tab>Rows<x-tab>&nbsp;&nbsp;&nbsp;&nbsp;</x-tab>Bytes<x-tab>&nbsp;&nbsp;&nbsp;</x-tab>Cost<x-tab>&nbsp;&nbsp;&nbsp;&nbsp;</x-tab><br>
SELECT STATEMENT



Hint=CHOOSE<x-tab>&nbsp;&nbsp;&nbsp;&nbsp;</x-tab><x-tab>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</x-tab>3&nbsp;


<x-tab>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</x-tab>

<x-tab>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</x-tab>4&nbsp; <x-tab>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</x-tab> <x-tab>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</x-tab>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <x-tab>&nbsp;&nbsp;</x-tab>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <x-tab>&nbsp;&nbsp;&nbsp;</x-tab> <br>

&nbsp; SORT GROUP BY<x-tab>&nbsp;</x-tab><x-tab>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</x-tab><x-tab>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</x-tab><x-tab>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</x-tab>3&nbsp; <x-tab>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</x-tab>60&nbsp; <x-tab>&nbsp;&nbsp;&nbsp;&nbsp;</x-tab>4&nbsp; <x-tab>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</x-tab> <x-tab>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</x-tab>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <x-tab>&nbsp;&nbsp;</x-tab>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <x-tab>&nbsp;&nbsp;&nbsp;</x-tab> <br>
&nbsp;&nbsp;&nbsp; TABLE ACCESS FULL<x-tab>&nbsp;&nbsp;&nbsp;</x-tab>MY_TABLE<x-tab>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</x-tab>9&nbsp; <x-tab>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</x-tab>180&nbsp; <x-tab>&nbsp;&nbsp;&nbsp;</x-tab>2&nbsp; <x-tab>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</x-tab> <x-tab>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</x-tab>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <x-tab>&nbsp;&nbsp;</x-tab>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <x-tab>&nbsp;&nbsp;&nbsp;</x-tab> <br><br>


<br>

</font>this is my query with varrary<br>

<font face="Courier New, Courier" size=3 color="#0000F0"><b>SELECT</b></font><font face="Courier New, Courier" size=3>&nbsp;&nbsp; a.id,<br>

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; a.address<br>


</font><font face="Courier New, Courier" size=3 color="#0000F0"><b>FROM</b></font><font face="Courier New, Courier" size=3>&nbsp;&nbsp;&nbsp;&nbsp; my_table a, TABLE(a.no_of_car) b<br><br>

</font><font face="Courier New, Courier" size=2>Operation<x-tab>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</x-tab>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Object Name<x-tab>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</x-tab>Rows<x-tab>&nbsp;&nbsp;&nbsp;&nbsp;</x-tab>Bytes<x-tab>&nbsp;&nbsp;&nbsp;</x-tab>Cost<x-tab>&nbsp;&nbsp;&nbsp;&nbsp;</x-tab><br>


SELECT STATEMENT Hint=CHOOSE<x-tab>&nbsp;&nbsp;&nbsp;&nbsp;</x-tab><x-tab>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</x-tab>3&nbsp; <x-tab>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</x-tab> <x-tab>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</x-tab>1310&nbsp; <x-tab>&nbsp;&nbsp;</x-tab> <x-tab>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</x-tab>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <x-tab>&nbsp;&nbsp;</x-tab>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <x-tab>&nbsp;&nbsp;&nbsp;</x-tab> <br>
&nbsp; SORT GROUP BY<x-tab>&nbsp;</x-tab><x-tab>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</x-tab><x-tab>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</x-tab><x-tab>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</x-tab>3&nbsp; <x-tab>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</x-tab>459&nbsp; <x-tab>&nbsp;&nbsp;&nbsp;</x-tab>1310&nbsp; <x-tab>&nbsp;&nbsp;</x-tab> <x-tab>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</x-tab>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <x-tab>&nbsp;&nbsp;</x-tab>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <x-tab>&nbsp;&nbsp;&nbsp;</x-tab> <br>
&nbsp;&nbsp;&nbsp; NESTED LOOPS<x-tab>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</x-tab><x-tab>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</x-tab><x-tab>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</x-tab><x-tab>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</x-tab>73 K<x-tab>&nbsp;&nbsp;&nbsp;&nbsp;</x-tab>10 M<x-tab>&nbsp;&nbsp;&nbsp;&nbsp;</x-tab>101&nbsp; <x-tab>&nbsp;&nbsp;&nbsp;</x-tab> <x-tab>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</x-tab>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <x-tab>&nbsp;&nbsp;</x-tab>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <x-tab>&nbsp;&nbsp;&nbsp;</x-tab> <br>


&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; TABLE ACCESS FULL<x-tab>&nbsp;</x-tab>MY_TABLE<x-tab>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</x-tab>9&nbsp; <x-tab>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</x-tab>1 K<x-tab>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</x-tab>2&nbsp; <x-tab>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</x-tab> <x-tab>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</x-tab>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <x-tab>&nbsp;&nbsp;</x-tab>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <x-tab>&nbsp;&nbsp;&nbsp;</x-tab> <br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; COLLECTION ITERATOR PICKLER FETCH<x-tab>&nbsp;</x-tab><x-tab>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</x-tab>&nbsp; <x-tab>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</x-tab> <x-tab>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</x-tab> <x-tab>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</x-tab> <x-tab>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</x-tab>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <x-tab>&nbsp;&nbsp;</x-tab>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; <x-tab>&nbsp;&nbsp;&nbsp;</x-tab> <br><br>


<br>



regards<br>


kang bedjo</font></body>


</html>



--=====================_3629098==.ALT--




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


Received on Thu Dec 04 2003 - 04:12:16 CST












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US