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