Tuesday, February 27, 2007

Introduction to Oracle9i: SQL (1)

Manipulating Data

##CONTINUE##

  • The only way a function can be removed in Oracle is with the ‘Drop Function’ command
  • (syntax)
    CREATE SEQUENCE sequence
    [INCREMENT BY n]
    [START WITH n]
    [{MAXVALUE n NOMAXVALUE}]
    [{MINVALUE n NOMINVALUE}]
    [{CYCLE NOCYCLE}]
    [{CACHE n NOCACHE}]
    ps. MAXVALUE -> 10^27 , -1
    MINVALUE -> 1 , -10^26
    CACHE -> 20

    CREATE SEQUENCE dep_seq
    INCREMENT BY 10
    START WITH 120
    MAXVALUE 999
    NOCACHE
    NOCYCLE;
    START WITH option can’t be changed using ALTER SEQUENCE command. Must be dropped and re-created.
  • Bitmaps are stored in a compressed format, so take less space than a b-tree index.
  • Function based indexes can be both b-tree or bitmap indexes. The expression or function has to be specified when the index is created. To use the index Query_Rewrite_Enable must be set to TRUE.
  • Only users with special privileges can create Public synonyms.
  • The index created by the Primary key column is called Unique Index.
  • When an index is based on multiple columns, it is referred to as concatenated or composite index.
  • Alter User jack PASSWORD EXPIRE.
  • Alter User jack IDENTIFIED BY psword;
  • (syntax)
    MERGE INTO table1 AS table_alias
    USING (table2viewsub_query) AS alias
    ON (join_condition)
    WHEN MATCHED THEN
    UPDATE SET
    col1 = value,
    col2 = value2
    WHEN NOT MATCHED THEN
    INSERT (col_list)
    VALUES (col_value);
  • It is possible even to use numbers to indicate the column position. -> select ggg from emp order by 2 desc;
  • You can use SELECT statement to display and to insert data into different table.
  • NVL2(expr1, expr2, expr3) -> if expr1 is not null, return expr2, else return expr3
  • Command TRUNCATE is used to remove all row data from the table, while leaving the definition of the table intact, including the definition of constraints and any associated database objects as indexes, constraints, and triggers on the table.
  • NUFFIF (expr1, expr2) -> if equal return null, else return expr1.
  • All character searches are case sensitive.
  • Select, Alter System, Lock Table, Set Role can be used in ‘Read Only’ transactions. Select For Update and Alter Sequence requires manipulation of data and hence cannot be executed on a ‘READ ONLY’.
  • Number(p,s) datatype is used for fixed point numbers.A delete statement does not reset the high water mark of the table and hence performance after delete is significantly slow.


Read more!

No comments: