1 Replies - 6269 Views - Last Post: 28 February 2013 - 08:37 AM

#1 Btu  Icon User is offline

  • D.I.C Regular

Reputation: 36
  • View blog
  • Posts: 250
  • Joined: 16-May 11

Index organised tables

Posted 20 February 2013 - 02:28 PM

I'm trying to understand when and where to use IOT's in an Oracle 11G DB.

I've read that IOT's store data directly on the leafs of the index but I'm still confused about when and where to use them.
Let's say I have a VLDB with a table called 'Customers' that has 10 million customers in it:

CREATE TABLE customers
    ( customer_id        NUMBER(6),
      cust_first_name    VARCHAR2(20)  CONSTRAINT cust_fname_nn NOT NULL,
      cust_last_name     VARCHAR2(20)  CONSTRAINT cust_lname_nn NOT NULL,
      cust_address       VARCHAR2(20),
     phone_numbers      VARCHAR2(20),
     nls_language       VARCHAR2(3),
     nls_territory      VARCHAR2(30),
     credit_limit       NUMBER(9,2),
     cust_email         VARCHAR2(30), CONSTRAINT customers_pk PRIMARY KEY (customer_id)
    ) ;


And there's an Order table with an FK customer_id in it, which has many millions of rows in it:

CREATE TABLE orders
    ( order_id           NUMBER(12),
      order_date         DATE CONSTRAINT order_date_nn NOT NULL,
      order_mode         VARCHAR2(8),
      customer_id        NUMBER(6) CONSTRAINT order_customer_id_nn NOT NULL,
      order_status       NUMBER(2)    ,
      order_total        NUMBER(8,2),
      CONSTRAINT order_pk PRIMARY KEY (order_id)
    ) ;


Would it be a good or bad idea to create Customers as an IOT?
What questions do I need to ask myself about these tables to determine the need of an IOT?

If anyone can help me answer these questions, I'd be very happy.

Thanks in advance

Is This A Good Question/Topic? 0
  • +

Replies To: Index organised tables

#2 Btu  Icon User is offline

  • D.I.C Regular

Reputation: 36
  • View blog
  • Posts: 250
  • Joined: 16-May 11

Re: Index organised tables

Posted 28 February 2013 - 08:37 AM

After doing some research, and speaking with my prof, I've come to this conclusion about IOT's:

-An IOT is typically a small table where the index for the PK is the only one needed
-'Customers' would not be a great choice for this since there are several candidate keys used in queries
-An IOT is suitable for a lookup/validation table
-'Organized' is spelled with a 'z' and not an 's' (topic is mis-spelled)

If anyone would care to add or comment to this, it would be appreciated.

Thanks
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1