|
Organizing tables and indexes
Introduction
ALTER or MOVE
statement
Normally, to
re-organize a table, you should use ALTER
command with MOVE options.
SQL> alter table mytable move;
Check previous
hands-on for more information.
dbms_stats.gather_schema_stats procedure
You can use
GATHER_SCHEMA_STATS procedure in the
DBMS_STATS package to analyze all tables and
indexes in a specific schema.
SQL> EXECUTE
dbms_stats.gather_schema_stats
('your_schema_name',cascade=>true);
- OR -
SQL> EXECUTE
dbms_stats.gather_schema_stats ('your_schema_name');
LONG datatype
in a table
Unfortunately,
you can�t do this is you have a long
datatype. If your table contains a LONG
datatype you get the following error
message:
ORA-00997: illegal use of LONG datatype
For example:
SQL> alter table table_with_long move;
alter table
table_with_long move
*
ERROR at line
1:
ORA-00997:
illegal use of LONG datatype
To re-organize
a table with a LONG datatype do the
following steps:
STEP #1:
Assuming this
is your table that can not be re-organized.
SQL> ed
Wrote file
afiedt.buf
1 CREATE TABLE
table_with_long
2 (c1
VARCHAR2(100) PRIMARY KEY,
3 c2
number(5),
4* c3 LONG)
SQL> /
Table created.
Write a PL/SQL
procedure to insert 100 records.
SQL> ed
Wrote file
afiedt.buf
1 BEGIN
2 FOR this IN
1..100 LOOP
3 INSERT INTO
table_with_long
4 VALUES
('PK_' ||
this, this, 'This is a very long long long
data...');
5 -- save
transaction
6 COMMIT;
7 END LOOP;
8* END;
SQL> /
PL/SQL
procedure successfully completed.
Query the
table...
SQL> COL c1
FORMAT a10
SQL> COL c3
FORMAT a50
SQL> SET
PAGESIZE 1000
SQL> SELECT *
FROM table_with_long;
C1 C2 C3
----------
----------
--------------------------------------------------
PK_1 1 This is
a very long long long data...
PK_2 2 This is
a very long long long data...
PK_3 3 This is
a very long long long data...
PK_4 4 This is
a very long long long data...
PK_5 5 This is
a very long long long data...
PK_6 6 This is
a very long long long data...
PK_7 7 This is
a very long long long data...
PK_8 8 This is
a very long long long data...
PK_9 9 This is
a very long long long data...
PK_10 10 This
is a very long long long data...
PK_11 11 This
is a very long long long data...
PK_12 12 This
is a very long long long data...
PK_13 13 This
is a very long long long data...
PK_14 14 This
is a very long long long data...
PK_15 15 This
is a very long long long data...
PK_16 16 This
is a very long long long data...
PK_17 17 This
is a very long long long data...
PK_18 18 This
is a very long long long data...
PK_19 19 This
is a very long long long data...
PK_20 20 This
is a very long long long data...
...
...
100 rows
selected.
Assuming that
there is lots of fragmentation and you need
to re-organize the table ("table_with_long").
Create a table with the same columns name
and datatypes...
SQL> DESC table_with_long
Name Null?
Type
-----------------------------------------
-------- ----------------------------
C1 NOT NULL
VARCHAR2(100)
C2 NUMBER(5)
C3 LONG
SQL> CREATE
TABLE table_with_long$$recovery
2 (c1
VARCHAR2(100) PRIMARY KEY,
3 c2
NUMBER(5),
4 c3 LONG);
Table created.
Write a PL/SQL
procedure to copy all data from
table_with_long to
table_with_long$$recovery. Notice that you
can not use insert DML statement to move
data. You will get the following error
message: ORA-00997: illegal use of LONG
datatype
SQL> INSERT
INTO table_with_long$$recovery
2 SELECT *
FROM table_with_long;
SELECT * FROM
table_with_long
*
ERROR at line
2:
ORA-00997:
illegal use of LONG datatype
This procedure
will perform the copy process.
SQL> DECLARE
2 CURSOR
c_table_with_long
3 IS SELECT *
FROM table_with_long;
4
5 BEGIN
6 FOR this IN
c_table_with_long LOOP
7 INSERT INTO
table_with_long$$recovery
8 VALUES
(this.c1, this.c2, this.c3);
9 COMMIT;
10 END LOOP;
11 END;
12 /
PL/SQL
procedure successfully completed.
Query
table_with_long$$recovery to see the data.
SQL> SELECT * FROM
table_with_long$$recovery;
C1 C2 C3
----------
----------
--------------------------------------------------
PK_1 1 This is
a very long long long data...
PK_2 2 This is
a very long long long data...
PK_3 3 This is
a very long long long data...
PK_4 4 This is
a very long long long data...
PK_5 5 This is
a very long long long data...
PK_6 6 This is
a very long long long data...
PK_7 7 This is
a very long long long data...
PK_8 8 This is
a very long long long data...
PK_9 9 This is
a very long long long data...
...
...
...
100 rows
selected.
Truncate
table_with_long;
SQL> TRUNCATE TABLE table_with_long;
Table
truncated.
Check to make
sure there are no data in that table.
SQL> SELECT * FROM table_with_long;
no rows
selected
Now, write a
PL/SQL procedure to copy all data from
table_with_long$$recovery to the truncated
table.
SQL> ed
Wrote file
afiedt.buf
1 DECLARE
2 CURSOR
c_table_with_long$$recovery
3 IS SELECT *
FROM table_with_long$$recovery;
4 BEGIN
5 FOR this IN
c_table_with_long$$recovery LOOP
6 INSERT INTO
table_with_long
7 VALUES
(this.c1, this.c2, this.c3);
8 COMMIT;
9 END LOOP;
10* END;
11 /
PL/SQL
procedure successfully completed.
Now, check to
see the data is back to its table.
SQL> SELECT * FROM table_with_long;
C1 C2 C3
----------
----------
--------------------------------------------------
PK_1 1 This is
a very long long long data...
PK_2 2 This is
a very long long long data...
PK_3 3 This is
a very long long long data...
PK_4 4 This is
a very long long long data...
PK_5 5 This is
a very long long long data...
PK_6 6 This is
a very long long long data...
PK_7 7 This is
a very long long long data...
PK_8 8 This is
a very long long long data...
PK_9 9 This is
a very long long long data...
...
...
...
100 rows
selected.
This is one
way to re-organize table with a long
datatype.
Questions:
Q: How do you
re-organize a table?
Q: How do you
re-organize a table when the table contains
a LONG datatype?
Q: Describe
the DBMS_STATS package/
Q: What does
the GATHER_SCHEMA_STATS procedure in the
DBMS_STATS package?
Q: What do the
following SQL and PL/SQL statements do?
SQL> EXECUTE
dbms_stats.gather_schema_stats
('your_schema_name',cascade=>true);
SQL>
1 BEGIN
2 FOR this IN
1..100 LOOP
3 INSERT INTO
table_with_long
4 VALUES
('PK_' ||
this, this, 'This is a very long long long
data...');
5 -- save
transaction
6 COMMIT;
7 END LOOP;
8* END;
1 DECLARE
2 CURSOR
c_table_with_long$$recovery
3 IS SELECT *
FROM table_with_long$$recovery;
4 BEGIN
5 FOR this IN
c_table_with_long$$recovery LOOP
6 INSERT INTO
table_with_long
7 VALUES
(this.c1, this.c2, this.c3);
8 COMMIT;
9 END LOOP;
10* END; |
