| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Varray Problem
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
cost is irrelevant. ignore it. it doesnt matter. its internal for oracle. what docs are you using that say to use cost? none from oracle. they dont exist.
have your Logical I/Os gone up? Has your response time gone up?
I can guess as to why its more 'costly'? By accessing the varray do you do this:
SELECT VARRAY
FROM TABLE
Or
SELECT COLS
FROM TABLE
WHERE VARRAY = <Some value>
VARRAY isnt atomic. Oracle has to do more work in retrieving it. Its a different data structure than a standard row.
however, ignore the cost. Its completely useless. Show me any credible documentation that says to use the cost and not just some person who wrote an article on some website.
>
> From: B3D70 <nareswara_at_balicamp.com>
> Date: 2003/12/04 Thu AM 05:12:16 EST
> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> Subject: Varray Problem
>
> 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
>
------=____1070543476178_OMlJCWxDbh
Content-Type: text/html;
name="reply"
Content-Disposition: inline;
filename="reply"
<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>
a.id,<br>
a.address<br>
</font><font face="Courier New, Courier" size=3 color="#0000F0"><b>FROM</b></font><font face="Courier New, Courier" size=3>
my_table a<br><br>
</font><font face="Courier New, Courier" size=2>Operation<x-tab> </x-tab><x-tab> </x-tab><x-tab> </x-tab>Object
Name<x-tab> </x-tab>Rows<x-tab> </x-tab>Bytes<x-tab> </x-tab>Cost<x-tab> </x-tab><br>
SELECT STATEMENT
Hint=CHOOSE<x-tab> </x-tab><x-tab> </x-tab>3
<x-tab> </x-tab>
<x-tab> </x-tab>4 <x-tab> </x-tab> <x-tab> </x-tab> <x-tab> </x-tab> <x-tab> </x-tab> <br> SORT GROUP BY<x-tab> </x-tab><x-tab> </x-tab><x-tab> </x-tab><x-tab> </x-tab>3 <x-tab> </x-tab>60 <x-tab> </x-tab>4 <x-tab> </x-tab> <x-tab> </x-tab> <x-tab> </x-tab> <x-tab> </x-tab> <br> TABLE ACCESS FULL<x-tab> </x-tab>MY_TABLE<x-tab> </x-tab>9 <x-tab> </x-tab>180 <x-tab> </x-tab>2 <x-tab> </x-tab> <x-tab> </x-tab> <x-tab> </x-tab> <x-tab> </x-tab> <br><br> <br>
SELECT STATEMENT Hint=CHOOSE<x-tab> </x-tab><x-tab> </x-tab>3 <x-tab> </x-tab> <x-tab> </x-tab>1310 <x-tab> </x-tab> <x-tab> </x-tab> <x-tab> </x-tab> <x-tab> </x-tab> <br> SORT GROUP BY<x-tab> </x-tab><x-tab> </x-tab><x-tab> </x-tab><x-tab> </x-tab>3 <x-tab> </x-tab>459 <x-tab> </x-tab>1310 <x-tab> </x-tab> <x-tab> </x-tab> <x-tab> </x-tab> <x-tab> </x-tab> <br> NESTED LOOPS<x-tab> </x-tab><x-tab> </x-tab><x-tab> </x-tab><x-tab> </x-tab>73 K<x-tab> </x-tab>10 M<x-tab> </x-tab>101 <x-tab> </x-tab> <x-tab> </x-tab> <x-tab> </x-tab> <x-tab> </x-tab> <br> TABLE ACCESS FULL<x-tab> </x-tab>MY_TABLE<x-tab> </x-tab>9 <x-tab> </x-tab>1 K<x-tab> </x-tab>2 <x-tab> </x-tab> <x-tab> </x-tab> <x-tab> </x-tab> <x-tab> </x-tab> <br> COLLECTION ITERATOR PICKLER FETCH<x-tab> </x-tab><x-tab> </x-tab> <x-tab> </x-tab> <x-tab> </x-tab> <x-tab> </x-tab> <x-tab> </x-tab> <x-tab> </x-tab> <x-tab> </x-tab> <br><br> <br>
------=____1070543476178_OMlJCWxDbh--
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: <ryan_oracle_at_cox.net INET: ryan_oracle_at_cox.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_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 - 09:39:41 CST
 |
 |