Now, comes an important decision on from which column or value of the column should be moved on to the overflow segment. This is optionally specified by INCLUDING clause. All the columns specified after the name in this clause will be moved to OVERFLOW segment. In this example, AGE_PHOBIA and AGE_IDEAS will be stored in a separate overflow segment.
Another interesting factor when we include OVERFLOW clause is as follows:
SQL> SELECT DBMS_METADATA.GET_DDL('TABLE','AGEING_DEFECTS') FROM DUAL;
DBMS_METADATA.GET_DDL('TABLE','AGEING_DEFECTS')
--------------------------------------------------------------------------------
CREATE TABLE "INXQA"."AGEING_DEFECTS"
("NORMAL_HUMAN_AGE" VARCHAR2(10),
"AGE_ISSUES" VARCHAR2(30),
"AGE_PHOBIA" VARCHAR2(30),
"AGE_IDEAS" VARCHAR2(10),
PRIMARY KEY ("NORMAL_HUMAN_AGE") ENABLE
) ORGANIZATION INDEX NOCOMPRESS PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING
STORAGE (INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "PORT_DATA"
PCTTHRESHOLD 20 INCLUDING "AGE_ISSUES" OVERFLOW
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "PORT_DATA"
Please note that the above options include PCTUSED with 40% as its value. This is because OVERFLOW segment is a separate segment, which has all the features and options of regular segments and hence we have PCTUSED.
NOCOMPRESS and COMPRESS options
When we uncompress the data, the primary key (or key columns) are repeated in the block along with the non-key columns. When we compress the index, based on the number of columns we specify, the indexes will be compressed (i.e.) the key columns will not be repeated. Please note that for the compress option to work, you need to have composite primary key. Otherwise, you will get the following error:
SQL> ALTER TABLE IOT_TABLE MOVE COMPRESS;
ALTER TABLE IOT_TABLE MOVE COMPRESS
*
ERROR AT LINE 1:
ORA-25193: CANNOT USE COMPRESS OPTION FOR A SINGLE COLUMN KEY
In order to show the COMPRESS option, let us create another table as follows:
CREATE TABLE IOT_TABLES
(IOT1 VARCHAR2(10),
IOT2 VARCHAR2(10),
IOT3 VARCHAR2(10),
IOT4 VARCHAR2(10),
IOT5 VARCHAR2(10),
PRIMARY KEY(IOT1,IOT2,IOT3))
ORGANIZATION INDEX;
Assume that we have a primary key on three columns with the following distribution of data in NOCOMPRESS option:
9,4,1,5,6
9,4,2,5,7
9,4,3,5,8
9,4,0,5,9
9,4,9,6,0
9,5,4,1,2
9,5,5,1,3
9,5,6,1,4
9,5,7,1,5
9,5,8,1,6
If we compress as follows:
ALTER TABLE IOT_TABLES MOVE COMPRESS 2;
9,4 and 9,5 are stored only once along with the data.
The main advantage of using COMPRESS technique is to save usage space.
Comparing the performance between Regular tables and IOT
Now, we will compare the results obtained from regular tables and IOT. Let us create two tables viz NON_IOT_TABLE and IOT_TABLE as follows:
CREATE TABLE NON_IOT_TABLE
(NON_IOTNUMBER NUMBER,
NON_IOTNAME VARCHAR2(20),
NON_IOTDETAILS VARCHAR2(20),
PRIMARY KEY(NON_IOTNUMBER));
Now, let us create a small procedure to insert equal number of rows into these tables and see the results.
CREATE OR REPLACE PROCEDURE IOTINSERT AS
INTERNALCOUNTER NUMBER;
BEGIN
INTERNALCOUNTER := 1;
WHILE INTERNALCOUNTER <= 100000
LOOP
INSERT INTO IOT_TABLE VALUES (INTERNALCOUNTER, 'AA' || INTERNALCOUNTER, 'AABBCC' || INTERNALCOUNTER);
INSERT INTO NON_IOT_TABLE VALUES (INTERNALCOUNTER, 'AA' || INTERNALCOUNTER, 'AABBCC' || INTERNALCOUNTER);
INTERNALCOUNTER := INTERNALCOUNTER + 1;
END LOOP;
COMMIT;
END;
SQL> SELECT * FROM IOT_TABLE WHERE IOTNUMBER < 2000;
1999 ROWS SELECTED.
EXECUTION PLAN
----------------------------------------------------------
PLAN HASH VALUE: 451903227
---------------------------------------------------------------------------------------
| ID | OPERATION | NAME | ROWS | BYTES | COST (%CPU)| TIME |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 37 | 0 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| SYS_IOT_TOP_232322 | 1 | 37 | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
PREDICATE INFORMATION (IDENTIFIED BY OPERATION ID):
---------------------------------------------------
1 - ACCESS("IOTNUMBER"<2000)
STATISTICS
----------------------------------------------------------
1 RECURSIVE CALLS
0 DB BLOCK GETS
143 CONSISTENT GETS
0 PHYSICAL READS
124 REDO SIZE
55870 BYTES SENT VIA SQL*NET TO CLIENT
1839 BYTES RECEIVED VIA SQL*NET FROM CLIENT
135 SQL*NET ROUNDTRIPS TO/FROM CLIENT
0 SORTS (MEMORY)
0 SORTS (DISK)
1999 ROWS PROCESSED
SQL> SELECT * FROM NON_IOT_TABLE WHERE NON_IOTNUMBER < 2000;
1999 ROWS SELECTED.
EXECUTION PLAN
----------------------------------------------------------
PLAN HASH VALUE: 1269025442
---------------------------------------------------------------------------------------------
| ID | OPERATION | NAME | ROWS | BYTES | COST (%CPU)| TIME |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 37 | 0 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| NON_IOT_TABLE | 1 | 37 | 0 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | SYS_C00104050 | 1 | | 0 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
PREDICATE INFORMATION (IDENTIFIED BY OPERATION ID):
---------------------------------------------------
2 - ACCESS("NON_IOTNUMBER"<2000)
STATISTICS
----------------------------------------------------------
1 RECURSIVE CALLS
0 DB BLOCK GETS
283 CONSISTENT GETS
0 PHYSICAL READS
124 REDO SIZE
55882 BYTES SENT VIA SQL*NET TO CLIENT
1839 BYTES RECEIVED VIA SQL*NET FROM CLIENT
135 SQL*NET ROUNDTRIPS TO/FROM CLIENT
0 SORTS (MEMORY)
0 SORTS (DISK)
1999 ROWS PROCESSED
If we see the execution plan for both the select statements, we observe that "Consistent Gets" are more in NON_IOT_TABLE. This is very much true because the index will be searched first and then the data will be retrieved from the respective table. However, in case if IOT_TABLE table, the index and data resides in the same place and hence the "Consistent Gets" remains less. The same reason is provided for the 3 steps execution plan v/s 2 step execution plan, provided above.
No comments:
Post a Comment