Friday, February 6, 2015

VIEW-SEQUENCE-INDEX



 VIEW=  THE SUBSET OF THE TABLE. USE FOR COMPLICATED COMPLEX QUERY.

SQL> SELECT ENAME,JOB,SAL FROM EMP
  2  WHERE DEPTNO IN (10,20)
  3  OR JOB = 'SALESMAN' OR
  4  SAL <= 2500;

ENAME      JOB              SAL
---------- --------- ----------
AHMAD      CLERK          40000
MARIA      OFFICER         7000
ALI        CLERK           5000
SAHIR      CLERK           2000
ARIF       CLERK           2500
ASIF       DBA            77788
KIRAN      MANAGER         2000
SANA       MANAGER         2200
KASHIF     OFFICER         1400
IRFAN      CLERK           1200
EMRAN      CLERK           1000

ENAME      JOB              SAL
---------- --------- ----------
SIDRA      ASSISTANT        900
SMITH      CLERK            800
ALLEN      SALESMAN        1600
WARD       SALESMAN        1250
JONES      MANAGER         2975
MARTIN     SALESMAN        1250
CLARK      MANAGER         2450
SCOTT      ANALYST         3000
KING       PRESIDENT       5000
TURNER     SALESMAN        1500
ADAMS      CLERK           1100

ENAME      JOB              SAL
---------- --------- ----------
JAMES      CLERK            950
FORD       ANALYST         3000
MILLER     CLERK           1300

25 rows selected.

SQL> CREATE VIEW ABCVIEW
  2  AS
  3   SELECT ENAME,JOB,SAL FROM EMP
  4   WHERE DEPTNO IN (10,20)
  5   OR JOB = 'SALESMAN' OR
  6   SAL <= 2500;

View created.

SQL> SELECT * FROM ABCVIEW;

ENAME      JOB              SAL
---------- --------- ----------
AHMAD      CLERK          40000
MARIA      OFFICER         7000
ALI        CLERK           5000
SAHIR      CLERK           2000
ARIF       CLERK           2500
ASIF       DBA            77788
KIRAN      MANAGER         2000
SANA       MANAGER         2200
KASHIF     OFFICER         1400
IRFAN      CLERK           1200
EMRAN      CLERK           1000

ENAME      JOB              SAL
---------- --------- ----------
SIDRA      ASSISTANT        900
SMITH      CLERK            800
ALLEN      SALESMAN        1600
WARD       SALESMAN        1250
JONES      MANAGER         2975
MARTIN     SALESMAN        1250
CLARK      MANAGER         2450
SCOTT      ANALYST         3000
KING       PRESIDENT       5000
TURNER     SALESMAN        1500
ADAMS      CLERK           1100

ENAME      JOB              SAL
---------- --------- ----------
JAMES      CLERK            950
FORD       ANALYST         3000
MILLER     CLERK           1300

25 rows selected.

SQL> DROP VIEW ABCVIEW;

 SEQUENCE=  TO GENERATE AUTO NO

SQL> CREATE SEQUENCE ABCSQ
  2  START WITH 10
  3  INCREMENT BY 5;

Sequence created.

SQL> CREATE TABLE ABCDEF
  2  (RNO NUMBER(10),
  3  NAME VARCHAR2(20));

Table created.

SQL> INSERT INTO ABCDEF
  2  (RNO,NAME)
  3  VALUES
  4  (ABCSQ.NEXTVAL,'USAMA');

1 row created.

SQL> ED
Wrote file afiedt.buf

  1  INSERT INTO ABCDEF
  2  (RNO,NAME)
  3  VALUES
  4* (ABCSQ.NEXTVAL,'SAMI')
SQL> /

1 row created.

SQL> ED
Wrote file afiedt.buf

  1  INSERT INTO ABCDEF
  2  (RNO,NAME)
  3  VALUES
  4* (ABCSQ.NEXTVAL,'EJ')
SQL> /

1 row created.

SQL> ED
Wrote file afiedt.buf

  1  INSERT INTO ABCDEF
  2  (RNO,NAME)
  3  VALUES
  4* (ABCSQ.NEXTVAL,'HA')
SQL> /

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM ABCDEF;

       RNO NAME
---------- --------------------
        10 USAMA
        15 SAMI
        20 EJ
        25 HA

SQL> SELECT * FROM USER_SEQUENCES;

SEQUENCE_NAME                   MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE
------------------------------ ---------- ---------- ------------ - - ----------
LAST_NUMBER
-----------
EMPSEQ                                  1 1.0000E+27           10 N N         20
       8336

DBOBJECTID_SEQUENCE                     1 1.0000E+24           50 N N         50
          1

ABCSQ                                   1 1.0000E+27            5 N N         20
        110


SQL> SET LINES 300 PAGES 300
SQL> /

SEQUENCE_NAME                   MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
------------------------------ ---------- ---------- ------------ - - ---------- -----------
EMPSEQ                                  1 1.0000E+27           10 N N         20        8336
DBOBJECTID_SEQUENCE                     1 1.0000E+24           50 N N         50           1
ABCSQ                                   1 1.0000E+27            5 N N         20         110

SQL> DROP SEQUENCE ABCSEQ;
DROP SEQUENCE ABCSEQ
              *
ERROR at line 1:
ORA-02289: sequence does not exist


SQL> ED
Wrote file afiedt.buf

  1* DROP SEQUENCE ABCSQ
SQL> /

Sequence dropped.

 INDEX= TO IMPROVE THE PERFORMANCE OF THE TABLE.WHEN UR DATA > 200 IN A TABLE THEN U APPLY INDEX.
     PK  UK IN COLUMNS AUTOMATIC CREATE INDEX.

SQL> CREATE INDEX ABCIDX
  2  ON EMP(JOB);
ON EMP(JOB)
       *
ERROR at line 2:
ORA-01408: such column list already indexed


SQL> ED
Wrote file afiedt.buf

  1  CREATE INDEX ABCIDX
  2* ON EMP(SAL)
SQL> /
ON EMP(SAL)
       *
ERROR at line 2:
ORA-01408: such column list already indexed


SQL> ED
Wrote file afiedt.buf

  1  CREATE INDEX ABCIDX
  2* ON ABCDEF(NAME)
SQL> /

Index created.

SQL> SELECT * FROM ABCDEF;

       RNO NAME
---------- --------------------
        10 USAMA
        15 SAMI
        20 EJ
        25 HA

SQL> DROP INDEX ABCIDX;

Index dropped.

No comments:

Post a Comment