|
"When ideas
fail, words come in very handy." - Goethe (1749-1832) |
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:
Assuming this is your table
that can not be re-organized.
SQL> ed
Wrote file afiedt.buf
CREATE TABLE table_with_long
(c1 VARCHAR2(100) PRIMARY
KEY,
c2 number(5),
c3 LONG)
SQL> /
Table created.
Write a PL/SQL procedure to
insert 100 records.
SQL> ed
Wrote file afiedt.buf
BEGIN
FOR this IN 1..100 LOOP
INSERT INTO table_with_long
VALUES
('PK_' || this, this, 'This
is a very long long long data...');
-- save transaction
COMMIT;
END LOOP;
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
(c1 VARCHAR2(100) PRIMARY
KEY,
c2 NUMBER(5),
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
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
CURSOR c_table_with_long
IS SELECT * FROM table_with_long;
BEGIN
FOR this IN c_table_with_long LOOP
INSERT INTO table_with_long$$recovery
VALUES (this.c1, this.c2,
this.c3);
COMMIT;
END LOOP;
END;
/
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
DECLARE
CURSOR c_table_with_long$$recovery
IS SELECT * FROM table_with_long$$recovery;
BEGIN
FOR this IN c_table_with_long$$recovery LOOP
INSERT INTO table_with_long
VALUES (this.c1, this.c2,
this.c3);
COMMIT;
END LOOP;
END;
/
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.
|
"The nice thing
about egotists is that they don't talk about other people."
- Lucille S. Harper |
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?
|