RE: Is table with b-tree index is still a heap table?
Date: Wed, 2 Apr 2014 07:50:11 -0400
Message-ID: <09fc01cf4e69$b5493480$1fdb9d80$_at_rsiz.com>
And without knowing the details of your data model design and use, figuring out whether heap, io, index cluster, or hash cluster are appropriate organizations is something we cannot really know. With regard to clusters knowing whether a single table or some group of tables should be grouped and how is non-trivial.
Likewise whether partitioning would be useful.
When the alternatives to heap are just the right fit they can appear magical. Each comes with particular different maintenance overhead different from heap tables for the long haul, so whether an alternative to heap would be good for your situation is something you need to understand, model, and test.
mwf
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
On Behalf Of Chitale, Hemant K
Sent: Wednesday, April 02, 2014 1:25 AM
To: Nik Tek
Cc: ORACLE-L
Subject: RE: Is table with b-tree index is still a heap table?
If a Primary Key isn't part of the design, I wouldn't "force-fit" one.
In most applications 90-99% of the tables would be heap tables. IOTs are the rare exception.
Hemant K Chitale
From: Nik Tek [mailto:niktek2005_at_gmail.com]
Sent: Wednesday, April 02, 2014 12:35 PM
To: Chitale, Hemant K
Cc: ORACLE-L
Subject: Re: Is table with b-tree index is still a heap table?
Thank you Hemant!
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:
- 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
- CREATE TABLE T1 (C1 NUMBER NOT NULL, C2 VARCHAR2(100));
- 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:
- Yes, a) is a heap table I know that, but b) is a heap table?
- Is there any advantage of bumping up the C1 column to PK, like storage, performance?
- Is b) and c) are the same?
- 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.
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-lReceived on Wed Apr 02 2014 - 13:50:11 CEST