Oracle

 difference between package/function/procedure what is mutation error can we create index on views/mviews Types of exception/pragma Types of trigger If i have null values. what inner join returns/outer join returns

 1:- On which column we need to create index 2 :- Index depends on workload then on column 3:- For example if DEPTNO and DEPTNAME  is available then index need to be created on deptno and deptname 4:- Index to avoid sorting (group by and order by), to improve sorting ,which can improve performance 5 :- Create index for uniqueness 6 :- Create index for foreign key 7:- Be aware of modification on applications

  Draw.io  is a free online diagram software for making flowcharts, process diagrams, etc. Allows different types of charts like flowcharts, org charts, UML, ER and network diagrams. Allows saving your diagrams to cloud storage services like GDrive, Dropbox and OneDrive, or to your own computer Visual interfaces not as nice as lucidchart.

  SELECT deptno, SUBSTR (LISTAGG(longname, ';' ON OVERFLOW TRUNCATE ) WITHIN GROUP ( ORDER BY EMPNO), 1 , 50 ) AS "Starts..." , '........' , SUBSTR (LISTAGG(longname, ';' ON OVERFLOW TRUNCATE ) WITHIN GROUP ( ORDER BY EMPNO), - 50 ) AS "...Ends" FROM empln WHERE deptno = 20 GROUP BY deptno; However, we can now also trap the error using the new  ON OVERFLOW TRUNCATE  syntax Note:  In the code below I have chopped-up the string returned by LISTAGG so that you don't have to scroll across 32,000 characters to see the effect on the new keywords Removing the count of missing values The final set of new keywords control whether the count of truncated values is shown or not shown. By default, the count is shown and this can be explicitly stated using the  WITH COUNT  syntax. Similarly, it is possible to remove the count by using the  WITHOUT COUNT  keywords after the on overflow... keywords. SELECT deptno, SUBSTR (LISTAGG...

  This is a simple demo showing how to use SQL pattern matching for fraud analysis. It is part of a presentation for the Oracle CODE events program for developers. In Database 12c there is a new SQL construct MATCH_RECOGNIZE for finding rows matching a pattern across a sequence of rows, using a regular expressions. Pattern matching using SQL is based around four logical concepts: Logically partition and order the data/li> Define pattern using regular expression and pattern variables/li> Regular expression is matched against a sequence of rows/li> Each pattern variable is defined using conditions on rows and aggregates irst step is to create the JSON table that will hold our transaction log. The log will provide the details of the time, account/user id, type of event (deposit or transfer) and the transaction amount. Oracle Database 12c supports storing JSON documents inside the database. Use following code to create a table to store the transaction log which is in JSON form...

A materialized view is  a view that contains the result of a query. The From clause of the query can have a name from a table, view and a materialized view. It needs privileges before executing create command, you must have system privilege. To create table ,query rewrite enabled must have privilege, you must have SELECT privilege with GRANT OPTION  on the container table.

  Deterministic and Non-deterministic Two approximate algorithms are offered for the approximate percentile functions: Non-deterministic: fastest method, whenever reproducibility is not required. This is the default method. Deterministic: slightly slower, but delivers deterministic results. Note that the differences will not be apparent with the small dataset in use here. REM Approximate median, NON DETERMINISTIC select approx_median(volume), approx_median(volume, 'ERROR_RATE' ) from approxt where state = 'CA' ; REM Approximate median, DETERMINISTIC select approx_median(volume deterministic), approx_median(volume deterministic, 'ERROR_RATE' ) from approxt where state = 'CA' ; REM Approximate percentile, DETERMINISTIC select approx_percentile( 0.1 deterministic) within group ( order by volume) from approxt where state = 'CA' ;

  In my introduction to table functions, I showed how to build and "query" from a table function that returns a collection of scalars (number, date, string, etc.). If that's all you need to do, well, lucky you! Most of the time, however, you need to pass back a row of data consisting of more than one value, just as you would with "regular" tables when, say, you needed the ID and last name of all employees in department 10, as in: SELECT employee_id, last_name FROM hr.employees WHERE department_id = 10 / This module explores how you can go about doing that with table functions. ou will undoubtedly be tempted, as I was tempted when first working with table functions, to use the %ROWTYPE as an attribute for the nested table type. This will not work. Let's take a quick look. Suppose I want my table function to return rows that could be inserted into this table: CREATE TABLE animals ( name VARCHAR2 ( 10 ), species VARCHAR2 ( 20 ), date_of_birth ...

  flashback table < table > to timestamp < when it was good>; flashback table orders to timestamp systimestamp - interval '1' hour ; alter table < table > enable row movement; select * from < table > as of timestamp systimestamp - interval '1' hour ; select * from < table > as of scn 1234567; insert into table    select * from < table > as of timestamp sysdate – interval '1' hour    where <conditions to find the rows >; select * from < table > as of timestamp sysdate – interval '1' hour minus select * from < table >; insert into < table >    select * from < table > as of timestamp sysdate – interval '1' hour    minus    select * from < table >; update < table > cur set (col1, col2, col3) = (    select col1, col2, col3 from < table >      as of timestamp systimestamp ...