10 oracle tips part 3

 

************************

1)


set echo off
set heading off
set feedback off
set verify off
set pagesize 0
set linesize 132
define schema=&1
set pagesize 0
set long 90000
set feedback off
set echo off

spool scott_schema.sql

connect scott/tiger;

SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name)
     FROM USER_TABLES u;

SELECT DBMS_METADATA.GET_DDL('INDEX',u.index_name)
     FROM USER_INDEXES u;

spool off;

2) decode, case when,

3) tnsnames.ora &tnsping

4) SYS? SYSTEM>

5) who am I in oracle?

show user;

6) how to disconnect?
disc
 
 

7)Partitioned Tables And Indexes
https://oracle-base.com/articles/8i/partitioned-tables-and-indexes

8)views


9) materialized views
https://oracle-base.com/articles/misc/materialized-views

10)Difference between Views and Materialized Views in SQL




1) execution plan

2) performance and tuning

3)indexes

4)ROWNUM or ROWID

5) stored procedures

6)SOME     TRUE if any of the subquery values meet the condition

7) subqueries
SELECT column_name(s)
FROM table_name
WHERE column_name IN (SELECT STATEMENT);

8) where not
SQL> select distinct job from emp where not job='PRESIDENT';

JOB
---------
CLERK
SALESMAN
MANAGER
ANALYST

SQL> select distinct job from emp where job!='PRESIDENT';

JOB
---------
CLERK
SALESMAN
MANAGER
ANALYST

SQL> select distinct job from emp where job<>'PRESIDENT';

JOB
---------
CLERK
SALESMAN
MANAGER
ANALYST


9)select 'D' from table (dev) or select 'P' from table (prod)

datastage variable: %ENV%

select * from x
where
'%ENV%' = 'D' and ...
or
'%ENV%' = 'P' and ...



10) limiting rows

select top 5 * from table (sqlserver)

select * from table
fetch first 5 rows only (db2)

select * from table
limit 100 (db2)

select * from table
where rownum>5 (oracle)