Re: Varray Problem

[ryan_oracle]

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 <[EMAIL PROTECTED]>
> Date: 2003/12/04 Thu AM 05:12:16 EST
> To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> 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 
> 

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