Is table with b-tree index is still a heap table?
- From: Nik Tek <niktek2005@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Tue, 1 Apr 2014 16:21:29 -0700
Hi,
I have question about oracle heap tables vs. tables with b-tree index vs.
IOT tables.
Say I have a table T1
a) CREATE TABLE T1 (C1 NUMBER NOT NULL, C2 VARCHAR2(100));
b) CREATE TABLE T1 (C1 NUMBER NOT NULL, C2 VARCHAR2(100));
CREATE INDEX IDX_T1_C1 ON T1(C1);
c) CREATE TABLE T1 (C1 NUMBER NOT NULL, C2 VARCHAR2(100),
CONSTRAINT PK_T1_C1 PRIMARY KEY (C1) ORGANIZATION INDEX);
d)
CREATE SEQUENCE T1_SEQ INCREMENT BY 1;
/
ALTER TABLE T1
ADD SURR_KEY INTEGER NOT NULL;
/
ALTER TABLE T1
ADD CONSTRAINT PK_T1_C1_SK PRIMARY KEY (C1,SURR_KEY);
/
CREATE OR REPLACE TRIGGER T1_TRIG
BEFORE INSERT ON T1
FOR EACH ROW
BEGIN
SELECT T1_SEQ.nextval INTO :NEW.SURR_KEY FROM dual;
END;
Questions:
1) Yes, a) is a heap table I know that, but b) is a heap table?
2) Is there any advantage of bumping up the C1 column to PK, like storage,
performance?
3) Is b) and c) are the same?
4) Say my application is already out in the customers site, changing the C1
to PK would be tricky, instead can do solution d) ?
Thank you
Nik
Other related posts:
