Finding size of a ito
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Finding size of a ito

  1. #1
    Join Date
    Nov 2000
    Posts
    440

    Finding size of a ito

    create table iot (a primary key, owner) organization index as
    select object_id, owner from dba_objects where object_id is not null and rownum < 11

    Table created.

    explain plan for select * from iot;

    Explained.

    @utlxpls


    PLAN_TABLE_OUTPUT
    -------------------------------------------------------------------------------------------
    Plan hash value: 12198353

    -------------------------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    -------------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 10 | 300 | 2 (0)| 00:00:01 |
    | 1 | INDEX FAST FULL SCAN| SYS_IOT_TOP_128290 | 10 | 300 | 2 (0)| 00:00:01 |
    -------------------------------------------------------------------------------------------



    Notice it only read SYS_OIT_TOP_128290 object.


    select owner, index_name, index_type from dba_indexes where table_name ='IOT';

    OWNER INDEX_NAME INDEX_TYPE
    ------------------------------ ------------------------------ ---------------------------
    SYS SYS_IOT_TOP_128290 IOT - TOP

    select index_name, column_name from dba_ind_columns where index_name = 'SYS_IOT_TOP_128290';

    INDEX_NAME COLUMN_NAME
    ------------------------------ ------------------------------
    SYS_IOT_TOP_128290 A


    Where is the column owner?

    When i issue a query on my iot, it only access the object SYS_IOT_TOP_128290.
    When i query SYS_IOT_TOP_128290, in dba_ind_columns, only column a show up. Not owner.

    Where does the column owner is stored?

  2. #2
    Join Date
    Mar 2004
    Location
    DC,USA
    Posts
    650
    Its in DBA_TABLES, ALL_IND_COLUMNS

  3. #3
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Columns do not have an owner, objects do.

    Follows layout of the view you are quering...

    Code:
    SQL> desc dba_ind_columns
     Name                                      Null?    Type
     ----------------------------------------- -------- ------------------
     INDEX_OWNER                               NOT NULL VARCHAR2(30)
     INDEX_NAME                                NOT NULL VARCHAR2(30)
     TABLE_OWNER                               NOT NULL VARCHAR2(30)
     TABLE_NAME                                NOT NULL VARCHAR2(30)
     COLUMN_NAME                                        VARCHAR2(4000)
     COLUMN_POSITION                           NOT NULL NUMBER
     COLUMN_LENGTH                             NOT NULL NUMBER
     DESCEND                                            VARCHAR2(4)
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  4. #4
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    Did not got u. As PAVB said, columns does not have an owner. They belongs to a table and the table has owner.

    The plan on the other hand seems completely OK

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width