Compress Primary key
A reader, April 21, 2002 - 8:38 pm UTC
Hi Tom,
Does "organization index" make it an Index Organized Table,
Can compress be done on regular table ?
Thanks
April 21, 2002 - 8:43 pm UTC
Yup -- organization index = index organized.
Now I see the problem with my answer!!! "a non IOT table"... I read over the work NON....
Ok, here we go:
ops$tkyte@ORA9I.WORLD> create table t
2 ( x int, y int, z int )
3 /
Table created.
ops$tkyte@ORA9I.WORLD>
ops$tkyte@ORA9I.WORLD> create unique index t_idx on t(x,y,z) compress 2;
Index created.
ops$tkyte@ORA9I.WORLD> alter table t add constraint t_pk primary key (x,y,z);
Table altered.
ops$tkyte@ORA9I.WORLD>
ops$tkyte@ORA9I.WORLD> drop index t_idx;
drop index t_idx
*
ERROR at line 1:
ORA-02429: cannot drop index used for enforcement of unique/primary key
sorry about that.
Compress Primary key
Daniel H. Liu, April 21, 2002 - 8:46 pm UTC
Hi Tom,
Sorry, I forgot my Email last time.
Does "organization index" make it an Index Organized Table,
Can compress be done on regular table ?
Thanks
April 21, 2002 - 8:53 pm UTC
See above, i misread -- sorry about that.
Rule vs Cost.
A reader, May 12, 2002 - 1:04 am UTC
Tom,
Does it matter what optimizer_mode my database is running in for using compressed indexes. I know that compression is for storage but if i use a compressed index will my query use the index in a rule based setup.
Your comments are valuable.
Regards,
Ganesh R
May 12, 2002 - 9:09 am UTC
It is RBO "safe"
ops$tkyte@ORA817DEV.US.ORACLE.COM> create table t ( x int, y int, z int );
Table created.
ops$tkyte@ORA817DEV.US.ORACLE.COM> create index t_idx on t(x,y,z) COMPRESS 2;
Index created.
ops$tkyte@ORA817DEV.US.ORACLE.COM> set autotrace on
ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from t where x=1 and y=1 and z=1;
no rows selected
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE)
See -- no bytes/card info to indicate the CBO being used like this does:
ops$tkyte@ORA817DEV.US.ORACLE.COM> analyze table t compute statistics;
Table analyzed.
ops$tkyte@ORA817DEV.US.ORACLE.COM> select * from t where x=1 and y=1 and z=1;
no rows selected
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=39)
1 0 INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost=1 Card=1 Bytes=39)
Does it means
Reader, May 13, 2002 - 12:30 am UTC
Hello Tom
Sorry. Dont want to get wrong interpretation out of your answer. So RBO Safe means, Should not be advisable to use Compress with RBO or Can use irrespective of Optimizer selected ?
Thanks in advance for your detailed answer.
May 13, 2002 - 7:13 am UTC
What I mean is that index key compression will not affect the usage of RBO. You can use it if you like.
Why is COMPRESS off-limits in the CONSTRAINT clause?
Michael Mannion, April 18, 2005 - 7:17 am UTC
Tom,
I have run into this behavior several times when providing support for a third-party application that does not (easily) permit me to use your suggested solution of pre-creating the index and associating it with the constraint after-the-fact.
In order to better explain this to my clients, and for my own understanding, would you please summarize why Oracle does not allow the user to specify COMPRESS when creating the constraint, even though the end product works well when employing your workaround? Thank you.
April 18, 2005 - 7:50 am UTC
ops$tkyte@ORA9IR2> create table t ( x int, y int );
Table created.
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> alter table t add constraint
2 t_pk
3 primary key(x,y)
4 using index (create index t_pk on t(x,y) compress 2)
5 /
Table altered.
is another way to do it.