Unanswered: how can i know the DB size & How to speed up in searching
i am using oracle 8i, how can i know the oracle database size already exists in the server & what is the way of increase the database size, & one other question i have a table MPI with Primary Key MRN & among the other columns CiviId is defined as Unique Key, now when i search record using CivilId (where clause) it's taking much time to search a record(from 300000 records are in table), how to speed up the searching,
i created table as
create table MPI
CivilId Char(10) not null,
Constraint pk_mpi_mrn PRIMARY KEY(mrn),
Constraint Uq_mpi_CvlId Unique (CivilId)
even though when i check unique value on both of above during validation (before inserting row into table) using Front End Developer 2000 CivilId takes much time where MRN's performance good, why?
please guide, i m awaiting eagerly
thanx in advance
One that also will help with speed and tuning is a script something similar to this one.
SET ECHO ON
SET TIMING ON
EXECUTE DBMS_UTILITY.ANALYZE_schema ('SYS','DELETE');
EXECUTE DBMS_UTILITY.ANALYZE_schema ('SYSADM','COMPUTE');
EXECUTE DBMS_UTILITY.ANALYZE_schema ('SYSTEM','COMPUTE');
EXECUTE DBMS_UTILITY.ANALYZE_schema ('PS','COMPUTE');
EXECUTE DBMS_UTILITY.ANALYZE_schema ('OUTLN','COMPUTE');
You need to do the analyze for all your major schemas. A significant note in 8i (and possibly 9i) is that analyzing the 'SYS' schema will bring the server to it's knees! Of course I don't know this because my full dumps were taking 14 hours (huh?) until I discovered the little tidbit in Metalink. The other thing is that while the analyze is going on it will take significant resources. We have it scheduled for about 5AM so that it is out of the general work hours.