Re: Is table with b-tree index is still a heap table?

Can you please elaborate a bit on 4).
Is it because d) doesn't provide any advantage?

Is there an alternative in fixing the schema, in the current schema almost 40% of the tables are straight heap tables or heap+index.

Thank you
Nik

On Tue, Apr 1, 2014 at 8:21 PM, Chitale, Hemant K <Hemant-K.Chitale_at_sc.com>wrote:

> 1) Yes, a) is a heap table I know that, but b) is a heap table?
>
> Yes, b) is a Heap Table PLUS an Index - the Table and Index are separate
> objects.
>
>
>
>
>
> 2) Is there any advantage of bumping up the C1 column to PK, like storage,
> performance?
>
> It depends. Does C1 not have a Unique Index definition yet ? You
> probably need to cleanup duplicates first. There is no storage benefit
> with a PK. A PK, in itself, doesn't offer a performance benefit if there's
> already an index. A PK is more about "clean" data and proper design.
>
>
>
> 3) Is b) and c) are the same?
>
> No. b) has a heap table and a separate index. c) is an IOT.
>
>
>
> 4) Say my application is already out in the customers site, changing the
> C1 to PK would be tricky, instead can do solution d) ?
>
> I wouldn't advise it.
>
>
>
> Hemant K Chitale
>
>
>
>
>
> *From:* oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *Nik Tek
> *Sent:* Wednesday, April 02, 2014 7:21 AM
> *To:* oracle-l_at_freelists.org
> *Subject:* Is table with b-tree index is still a heap table?
>
>
>
> 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
>
>
>
> This email and any attachments are confidential and may also be
> privileged. If you are not the intended recipient, please delete all copies
> and notify the sender immediately. You may wish to refer to the
> incorporation details of Standard Chartered PLC, Standard Chartered Bank
> and their subsidiaries at https://www.sc.com/en/incorporation-details.html
> .
>

--
http://www.freelists.org/webpage/oracle-l