Auto_increment

Hello my name is Elisa and I have a problem with this line of code:
CREATE TABLE Film
(IdFilm int not null auto_increment PRIMARY KEY,
Titolo VARCHAR2(20) NOT NULL,
Regista VARCHAR2(20) NOT NULL,
Personaggi_Principali VARCHAR2(50) NOT NULL,
Personaggi_Secondari VARCHAR2(50) NOT NULL,
Nazione VARCHAR2(20) NOT NULL,
Durata NUMBER(3) NOT NULL,
Genere VARCHAR2(10) NOT NULL,
Trama VARCHAR2(500) NOT NULL,
Critica VARCHAR2(10) NOT NULL,
Locandina VARCHAR2(20) NOT NULL,
Note VARCHAR2(100) NOT NULL)
when I execute the result is:
Error: ORA-00922: missing or invalid option.
It's very urgent.
Thank you Elisaauto_increment does not exist in Oracle!

You have to create a sequence:

CREATE SEQUENCE FILM_SEQ;

and then you can either use it during the insert as in:

INSERT INTO Film VALUES (FILM_SEQ.NEXTVAL, ...);

or create a trigger:

CREATE OR REPLACE TRIGGER BI_FILM_TRG
BEFORE INSERT ON FILM FOR EACH ROW
BEGIN
SELECT FILM_SEQ.NEXTVAL INTO :NEW.IdFilm FROM DUAL;
END;
/
NOTE: DO NOT USE IT ON BOTH THE INSERT AND TRIGGER!!!

:cool:|||Thank you very much for your help
Elisa

AUTO_CREATE_STATISTICS & AUTO_UPDATE_STATISTICS are Null

What is the real net affect of these 2 parameters set to NULL?

How can I tell if stats have been generated for all tables in a db?

I am guessing that stats don't exist for the db and this accounts for the high degree of scans I am seeing on a 1.5gb db running on a 4 gb memory machine.

Any thoughts?
MikeThe net effect is that you have to create the stats your self and also update then on a timely basis.

Personnaly would turn them both on. Some people would argue to turn on the create but do the update on a scheduled basis, the thought is that you can get by with slightly out of date stats rather than take the performace hit to auto update.|||so the bottom line is the optimizer will not have any information on the tables and therefore do scans to find the data?

Is this an accurate description?

Mike|||basically, Yes.|||and what is the most expedient mthod of generating the stats for all objects?|||BTW, once you create statistics, you should recompile all views, triggers, stored procs and re-build all indexes starting with any clustered indexes.|||turn AUTO_CREATE_STASTISTICS & AUTO_UPDATE_STATISTICS on.|||Originally posted by Paul Young
turn AUTO_CREATE_STASTISTICS & AUTO_UPDATE_STATISTICS on.

I just found out each weekend the indexes get rebuilt. Dropped and recreated. I read that whenever an index is created on a table containing data the optimizer collects stats and stores them. Does that still occur when these options are not on?

Mike|||Still recommend to turn those two options on. The major purpose of rebuilding index is not for updating statistics. Even if rebuild index will
update statistics, but the statistics may not be updated during the week.

BTW, never recreate index by drop-and-create strategy. Try to use DBCC DBREINDEX, or CREATE INDEX with DROP_EXISTING, or DBCC INDEXDEFAG.

auto_create & update statistics is null

Does this have the effect of not creating any statistics for the db and therefore forces queries to FTS rather than use an index?
How can I tell if stats exist for the tables/indexes?
Thanks,
MikeBetter to leave the option as it is by default, and also make sure to run SP_UPDATESTATS to update statistics and also generate DBCC checks to keep up the performance.

Use SP_HELPSTATS to return the information on stats.